Primers split by what the formula is built on, and the split predicts both what the product is good at and what it will do to the layer above it. It is one of the few genuinely useful technical distinctions in makeup.
Silicone-based primers are built on dimethicone, cyclopentasiloxane and silicone elastomers. They sit on the surface as a film, they have high slip, and the elastomer particles scatter light — which is why every blurring primer in existence is silicone-based. They do not hydrate, they are among the least reactive things in cosmetics, and they feel slippery, which some people like and some cannot stand.
Water-based primers are built on water, humectants and emollients. They hydrate, they absorb partly into the skin rather than sitting entirely on top, and they feel like a light moisturizer. They do not blur much, and because they contain more components they are statistically more likely to include something your skin reacts to.
The interaction between the two is where people get into trouble. Silicone is hydrophobic — water does not spread on it, it beads up. So a water-based product applied on top of a silicone layer does not form an even film: it breaks up, and the two layers roll together into the little grains people call pilling. The reverse order works fine, because silicone will spread happily over a water-based layer that has absorbed.
Hence the one rule worth memorizing: water under silicone, and let each layer set. Hydrating serum or primer first, two minutes, then the silicone blurring primer, then a minute, then foundation. The why primer pills guide walks through the diagnostics when it still happens.