Currently PostgreSQL has a feature which is called snapshot too old. While each release of PostgreSQL comes with a bunch of new features, sometimes features also get removed. Starting with PostgreSQL 17 there will be most likely no more “snapshot too old”. Before looking at why that got removed lets first have a look at what it is.

The main goal of this feature was to reduce table bloat and it is very well described in the documentation.

By default this feature is disabled:

postgres=# select version();
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 16beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# \dconfig *snapshot*
List of configuration parameters
       Parameter        | Value 
------------------------+-------
 old_snapshot_threshold | -1
(1 row)

To see this is in action we can do the following: Enable the feature by setting it to one minute:

postgres=# select context from pg_settings where name = 'old_snapshot_threshold';
  context   
------------
 postmaster
(1 row)

postgres=# alter system set old_snapshot_threshold = 1;
ALTER SYSTEM
postgres=# \! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-09-08 11:58:10.718 CEST - 1 - 15692 -  - @ - 0LOG:  redirecting log output to logging collector process
2023-09-08 11:58:10.718 CEST - 2 - 15692 -  - @ - 0HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select 1;
 ?column? 
----------
        1
(1 row)
postgres=# show old_snapshot_threshold ;
 old_snapshot_threshold 
------------------------
 1min
(1 row)

postgres=#

We’ve set old_snapshot_threshold to 1 minute and now we can easily produce a snapshot too old error. The procedure is:

  • Create a table in the first session
  • Start a transaction in a second session and select from that table
  • Insert, update and delete all data from the table in the first session. The transaction in the second session started before all that so it will never see any rows as everything is gone, but not committed
  • In a third session do a vacuum short before 1min passed (you can use pg_sleep to add a delay)
  • In the second session also add some delay and then select from the table
-- session 1
postgres=# create table t ( a int );
CREATE TABLE

-- session 2
postgres=# begin transaction isolation level repeatable read ;
BEGIN
postgres=*# select * from t;
 a 
---
(0 rows)

-- session 1
postgres=# begin;
BEGIN
postgres=*# insert into t select * from generate_series(1,1000);
INSERT 0 1000
postgres=*# update t set a = 3;
UPDATE 1000
postgres=*# delete from t;
DELETE 1000
postgres=*# commit;
COMMIT

-- session 3
postgres=# select pg_sleep(50); vacuum t;

-- session 2
postgres=*# select pg_sleep(50);
 pg_sleep 
----------
 
(1 row)

postgres=*# select * from t;
ERROR:  snapshot too old
postgres=!#

This means vacuum already did the cleanup of those rows and this triggers the “snapshot too old” error. This was mean as a last defense for long running transaction or reporting queries. From PostgreSQL 17 on, this is not anymore possible. The commit message has all the details and links to the discussions around this:

Remove the "snapshot too old" feature.

Remove the old_snapshot_threshold setting and mechanism for producing
the error "snapshot too old", originally added by commit 848ef42b.
Unfortunately it had a number of known problems in terms of correctness
and performance, mostly reported by Andres in the course of his work on
snapshot scalability.  We agreed to remove it, after a long period
without an active plan to fix it.

This is certainly a desirable feature, and someone might propose a new
or improved implementation in the future.

Reported-by: Andres Freund <[email protected]>
Discussion: https://postgr.es/m/CACG%3DezYV%2BEvO135fLRdVn-ZusfVsTY6cH1OZqWtezuEYH6ciQA%40mail.gmail.com
Discussion: https://postgr.es/m/20200401064008.qob7bfnnbu4w5cw4%40alap3.anarazel.de
Discussion: https://postgr.es/m/CA%2BTgmoY%3Daqf0zjTD%2B3dUWYkgMiNDegDLFjo%2B6ze%3DWtpik%2B3XqA%40mail.gmail.com
Post Views: 3,032
Thumbnail [60x60]
by
Daniel Westermann