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. -- -- Josh Berkus Red Hat OSAS (any opinions are my own) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
