From here we understand that PostgreSQL uses the feature called vacuum to clear dead tuples. More information about vacuum and dead tuples is already covered extensively in multiple posts.

PostgreSQL : Snapshot too old

VACUUM physically deletes old tuples until the last transaction can see them.

But sometimes the transaction takes too long and holds the tuples for a very long time.

Long-running report or cursor displaying query results could block cleanup of dead rows, bloating all volatile tables in the database, causing performance problems and excessive use of storage space.

To address the issue PostgreSQL has introduced old_snapshot_threshold in v9.6.

when old_snapshot_threshold is set to a non default value the vacuum doesn’t wait for the long running selects to complete, proceeding the dead tuple is a candidate for deletion, and if the transaction uses that tuple, it gets an sanpshot too old error.

PostgreSQL has now added a contrib in version 14 to view the contents of the time to XID mapping which the server maintains when old_snapshot_threshold != -1 (default it is umlimited).

[postgres@rhel8 ~]$ psql
psql (14devel)
Type “help” for help.

postgres=# select version();
version
—————————————————————————–
PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

postgres=# create extension old_snapshot;
CREATE EXTENSION

Example.,

• set old_snapshot_threshold to a non default value, here I set 1 min.

[postgres@rhel8 14]$ cat postgresql.conf | grep old_snapshot_threshold

old_snapshot_threshold = 1min # 1min-60d; -1 disables; 0 is immediate

• create few dead tuples in a table.

postgres=# update pgbench_accounts set abalance=’123′;
UPDATE 1000000

• select the table with pg_sleep() to ensure it takes the time more than old_snapshot_threshold

select *, pg_sleep(85) from pgbench_accounts;

• In another session try to do vacuum in the first minute.

• After old_snapshot_threshold time is reached, I have initiated vacuum again.
• After sometime, session one received ERROR: snapshot too old

Received the following message in alert log

What’s new in PostgreSQL 14?

PostgreSQL 14 has come up with an extension old_snapshot enabling inspection of the server state that is used to implement old_snapshot_threshold

PostgreSQL comments

You can use this to view the contents of the time to XID mapping which the server maintains when old_snapshot_threshold != -1.
Being able to view that information may be interesting for users,
and it’s definitely useful for figuring out whether the mapping
is being maintained correctly. It isn’t, so that will need to be
fixed in a subsequent commit.

Use the below function to map the things

You can use below query for further details

select *, age(newest_xmin), clock_timestamp() from pg_old_snapshot_time_mapping();

 

Words from postgreshelp

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.