On 28.07.2016 17:53, Vladimir Sitnikov wrote:
>> That's a recipe for runaway table bloat; VACUUM can't do much because
>> there's always some minutes-old transaction hanging around (and SNAPSHOT
>> TOO OLD doesn't really help, we're talking about minutes here), and
>> because of all of the indexes HOT isn't effective.
Just curious: what if PostgreSQL supported index that stores "primary key" (or unique key) instead of tids?
Am I right that kind of index would not suffer from that bloat? I'm assuming the primary key is not updated, thus secondary indices build in that way should be much less prone to bloat when updates land to other columns (even if tid moves, its PK does not change, thus secondary index row could be reused).
If that works, it could reduce index bloat, reduce the amount of WAL (less indices will need be updated). Of course it will make index scan a bit worse, however it looks like at least Uber is fine with that extra cost of index scan.
Does it make sense to implement that kind of index as an access method?
Vladimir
You mean IOT like Oracle have?
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company