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:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
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
Hi Daniel. I guess the workaround is transaction timeout, I see it has been accepted: https://commitfest.postgresql.org/45/4040/
Everybody is welcome to provide or propose patches. It is not me/us deciding about this, it is the commumity
This feature is extremely important for us and removing it will prevent us from upgrading to Postgres 17 when it comes out without a solid solution for the problem it solves for us.
Hi, currently there is no solution but maybe someone comes up with one.
See these threads that discuss the problem:
https://dba.stackexchange.com/questions/239777/how-do-we-get-around-postgresql-autovacuum-taking-a-table-level-lock-access-excl
https://dba.stackexchange.com/questions/323724/postgresql-12-streaming-replication-standbys-timing-out-due-to-autovacuum-tasks
In our system, we’ve had to set old_snapshot_threshold in order to deal with waits on very large and active tables (multi-terrabyte) in our streaming replicas that caused connections to time out and cancel. We ended up setting the value to our longest query + some additional factor (which in our case was the backup which also runs from a standby). Previous to this we tried other solutions and none worked. We do not want to have to specifically set vacuum_truncate=off either, as these same tables previously had a lot of table bloat to contend with, and this allowed atleast a watermark so that those older transactions could be truncated and space returned back to the OS. So how will this be solved in version 17?