What’s so exciting about Postgres?

Re:What’s so exciting about Postgres? by anarazel on Friday October 30, 2020 @06:14PM (#24947374)

> can you elaborate?

On what exactly?

The freeze map bit I referenced above is the following commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

    Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough
    infrastructure to avoid vacuuming pages where every tuple on the
    page is already frozen.  So, replace the notion of a scan_all or
    whole-table vacuum with the less onerous notion of an "aggressive"
    vacuum, which will pages that are all-visible, but still skip those
    that are all-frozen.
    
    This should greatly reduce the cost of anti-wraparound vacuuming
    on large clusters where the majority of data is never touched
    between one cycle and the next, because we'll no longer have to
    read all of those pages only to find out that we don't need to
    do anything with them.

This means that an anti-wraparound vacuum (automatically started, even when autovacuum is disabled) is cheaper than it used to be. Still not necessarily cheap, as indexes still need to be scanned (but see below).

Some of the additional changes were (reverse chronological order):

* 2020-03-28 - "Trigger autovacuum based on number of INSERTs" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This is important because it will reduce the cost of an eventual index wraparound, as there will be less work in a later anti-wraparound vacuum

* 2020-01-20 - "Allow vacuum command to process indexes in parallel." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

As index processing can be expensive (no equivalent to scanning only changed parts of table), processing them in parallel can greatly reduce the time for a vacuum. Note that this isn't yet done by autovacuum.

* 2019-04-04 - "Allow VACUUM to be run with index cleanup disabled." - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This can be extremely useful for manual vacuum when getting close to a wraparound, since the index processing step is not necessary to stave of wraparound.

* 2018-04-04 - "Skip full index scan during cleanup of B-tree indexes when possible" - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

This can make vacuums for pretty clean tables vastly cheaper.

There's also a number of changes that make indexes smaller / less likely to bloat. That in turn makes vacuuming more efficient. E.g.

* https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Edit: formatting (gah, why is HN formatting so limited)

[ Parent | on HN ]

Re:What’s so exciting about Postgres? by craigkerstiens on Friday October 30, 2020 @06:51PM (#24947602)

Just chiming in to say, Andres above can answer this far more in detail than I can. I suspect most people that do hit this today 1. have not nearly as a bad pain from it and 2. recover in generally much more quickly than previously. Today vs. say Postgres 9.6 is a huge difference.

[ Parent | on HN ]

Re:What’s so exciting about Postgres? by shay_ker on Sunday November 01, 2020 @09:20PM (#24964323)

awesome, that's an above and beyond answer! i really appreciate the detail!

[ Parent | on HN ]