On Tue, Jul 26, 2016 at 5:26 PM, Josh Berkus <j...@agliodbs.com> wrote: > On 07/26/2016 01:53 PM, Josh Berkus wrote: >> The write amplification issue, and its correllary in VACUUM, certainly >> continues to plague some users, and doesn't have any easy solutions. > > To explain this in concrete terms, which the blog post does not: > > 1. Create a small table, but one with enough rows that indexes make > sense (say 50,000 rows). > > 2. Make this table used in JOINs all over your database. > > 3. To support these JOINs, index most of the columns in the small table. > > 4. Now, update that small table 500 times per second. > > 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. Removing the indexes > is equally painful because it means less efficient JOINs. > > The Uber guy is right that InnoDB handles this better as long as you > don't touch the primary key (primary key updates in InnoDB are really bad). > > This is a common problem case we don't have an answer for yet.
This is why I think we need a pluggable heap storage layer, which could be done either by rebranding foreign data wrappers as data wrappers (as I have previously proposed) or using the access method interface (as proposed by Alexander Korotkov) at PGCon. We're reaching the limits of what can be done using our current heap format, and we need to enable developers to experiment with new things. Aside from the possibility of eventually coming up with something that's good enough to completely (or mostly) replace our current heap storage format, we need to support specialized data storage formats that are optimized for particular use cases (columnar, memory-optimized, WORM). I know that people are worried about ending up with too many heap storage formats, but I think we should be a lot more worried about not having enough heap storage formats. Anybody who thinks that the current design is working for all of our users is not paying very close attention. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
