Re: insert/update performance

Jinhua Luo <luajit(dot)io(at)gmail(dot)com> writes:
> I have a table with 70 columns, and 6 indexes. The data flow is a
> special OLTP model: frequent inserts (2000 tps), and each inserted row
> would be updated very soon (i.e. the number of inserts is equal to the
> number of updates).

Do those predictable updates change any of the indexed columns?

> I do a simple test: I truncate the table, disable the autovacuum, and
> run the application for a few minutes, then I invokes vacuum manually,
> it gives a strange output:
> found 598 removable, 25662 nonremovable row versions in 3476 pages
> DETAIL: 0 dead row versions cannot be removed yet
> As said before, the number of inserts is equal to the number of
> updates. So the bloat of the table should be 100%, and the number of
> removable rows should be equal to the number of nonremovable rows,
> which is the real number of inserts issued by the application.

What seems likely is that most of the updates are HOT (because they
don't change any indexed columns) and then the freed space is reclaimable
by subsequent updates on the same page without needing a VACUUM.

Watching the insert/update/hot-update counts in pg_stat_all_tables would
provide some evidence.

regards, tom lane