3 ms·
That seems inherently impossible - either you need to include the date in the lookup, include the ID in the partition, or look at all the partitions.
by Diggsey 8y ago
That seems inherently impossible - either you need to include the date in the lookup, include the ID in the partition, or look at all the partitions.
- anarazel 8y agoIt's technically possible to have both global indexes, and constraint checking that just checks every single partition for conflicts. The former isn't trivial to implement in PG (there's a fixed-width scheme for the target tuples in several index implementations RN) and obviously adds a point of contention (but a smaller than the sidetable proposed nearby). The latter is fairly simple to implement (we do something very similar internally for exclusion constraints), but scales linearly with the number of partitions...
- Diggsey 8y agoYes, that's a reasonable solution and how ElasticSearch handles partitions. It still would "require an index scan per partition." though, which the OP wanted to avoid.
- londons_explore 8y agoPG should allow an index to have a different partitioning scheme and locality from the data it refers to. That would solve nearly all the major remaining limitations.
- yashap 8y agoIndeed. Or similarly, allow indexes to not be partitioned at all - i.e. your records and most indexes could be partitioned by date, but you could also declare indexes that apply to the entire logical table (very useful for things like primary keys).
- pgaddict 8y agoThat is what was mentioned as "global indexes" in this discussion. The problem with this approach is twofold - firstly it negates many of the partitioning benefits (e.g. removing data is not merely a DROP PARTITION but you have to walk the global structure), secondly it requires fairly significant surgery in the indexes (that now point to rows in a single table, and so [block,item] is enough, but global indexes will require [partition,block,item]).
- anarazel 8y ago> so [block,item] is enough, but global indexes will require [partition,block,item]). That's probably the easier bit, I'd guess that adjusting a lot of the relevant in-memory structures to share relations, uniqueness checks across relations, adjusting the locking-model to deal with multiple underlying relations etc. is going to be more work.
- pgaddict 8y agoI think eventually we'll go with the second option, and do stuff to eliminate the scalability issues with many partitions. For example we could maintain a global bloom index (still global, but tiny compared to the sidetable), which should tell us whether there might be a duplicate value. If yes, it might be a false positive, in which case we have to do the expensive per-partition stuff. I'm sure it's going to be more complicated than this in practice, of course ...
- anarazel 8y agoI suspect we'll want both. The bloom index only helps if you have decent spatial locality. Which you won't for say a serial, uuid, etc. There's also types of indexes where it's probably going to be more efficient to have one index (I'd suspect a spatial indexes are often going to be that) for a number of usecases.
- pgaddict 8y agoPossibly, but considering how much easier is the second case to implement (compared to global indexes), I'd expect that to get in first. I'm not sure why the bloom filter would require good spatial locality?