Horton

Re: New strategies for freezing, advancing relfrozenxid early

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Jeff Davis <pgsql@j-davis.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, John Naylor <john.naylor@enterprisedb.com>, Justin Pryzby <pryzby@telsasoft.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-01-26T21:51:03Z
Lists: pgsql-hackers 
On Thu, Jan 26, 2023 at 1:22 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jan 26, 2023 at 4:06 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > There is very good reason to believe that the large majority of all
> > data that people store in a system like Postgres is extremely cold
> > data:
>
> The systems where I end up troubleshooting problems seem to be, most
> typically, busy OLTP systems. I'm not in a position to say whether
> that's more or less common than systems with extremely cold data, but
> I am in a position to say that my employer will have a lot fewer happy
> customers if we regress that use case. Naturally I'm keen to avoid
> that.

This is the kind of remark that makes me think that you don't get it.

The most influential OLTP benchmark of all time is TPC-C, which has
exactly this problem. In spades -- it's enormously disruptive. Which
is one reason why I used it as a showcase for a lot of this work. Plus
practical experience (like the Heroku database in the blog post I
linked to) fully agrees with that benchmark, as far as this stuff goes
-- that was also a busy OLTP database.

Online transaction involves transactions. Right? There is presumably
some kind of ledger, some kind of orders table. Naturally these have
entries that age out fairly predictably. After a while, almost all the
data is cold data. It is usually about that simple.

One of the key strengths of systems like Postgres is the ability to
inexpensively store a relatively large amount of data that has just
about zero chance of being read, let alone modified. While at the same
time having decent OLTP performance for the hot data. Not nearly as
good as an in-memory system, mind you -- and yet in-memory systems
remain largely a niche thing.

-- 
Peter Geoghegan

Commits

  1. Revert "Add eager and lazy freezing strategies to VACUUM."

  2. Add eager and lazy freezing strategies to VACUUM.

  3. Refine the definition of page-level freezing.

  4. Avoid special XID snapshotConflictHorizon values.

  5. Add page-level freezing to VACUUM.

  6. Remove overzealous MultiXact freeze assertion.

  7. Refactor how VACUUM passes around its XID cutoffs.

  8. Deduplicate freeze plans in freeze WAL records.

  9. Set relfrozenxid to oldest extant XID seen by VACUUM.

  10. Only skip pages marked as clean in the visibility map, if the last 32

  11. Add vacuum_freeze_table_age GUC option, to control when VACUUM should