Re: Why we lost Uber as a user

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Why we lost Uber as a user
Date: 2016-07-26 22:07:33
Message-ID: 13659.1469570853@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> 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.

Hm, I'm not following why this is a disaster. OK, you have circa 100%
turnover of the table in the lifespan of the slower transactions, but I'd
still expect vacuuming to be able to hold the bloat to some small integer
multiple of the minimum possible table size. (And if the table is small,
that's still small.) I suppose really long transactions (pg_dump?) could
be pretty disastrous, but there are ways around that, like doing pg_dump
on a slave.

Or in short, this seems like an annoyance, not a time-for-a-new-database
kind of problem.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Vik Fearing 2016-07-26 22:14:51 Re: PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
Previous Message Robert Haas 2016-07-26 22:07:18 Re: Why we lost Uber as a user