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)