pg_stat_user_tables — Find Insert-Only Tables in PostgreSQL

Identify Insert-Only Tables in PostgreSQL Using pg_stat_user_tables

pg_stat_user_tables keeps three cumulative counters for every user table: rows inserted, rows updated and rows deleted. A table whose update and delete counters sit at zero while the insert counter keeps climbing is an insert-only table. Those tables need different autovacuum settings and a different growth plan than a table that sees a mix of statements, and one query against the view finds them.

Purpose and Overview

Most application tables see inserts, updates and deletes. Some see only inserts: event logs, audit trails, sensor readings. Their maintenance needs differ. Because no UPDATE or DELETE ever runs against them, they leave no dead tuples behind from those statements, so the usual reason to vacuum a table, recovering space from updated or deleted rows, does not apply to them in the same way.

Vacuum has other jobs, though. The PostgreSQL manual lists four reasons for it: recover or reuse disk space occupied by updated or deleted rows, update planner statistics, update the visibility map (which speeds up index-only scans), and protect against loss of very old data due to transaction ID wraparound. Three of those four still apply to a table that only grows. Knowing which tables are insert-only tells you where to check that the other three are being handled.

The view pg_stat_user_tables has the same columns as pg_stat_all_tables, filtered to user tables. The query below reads three of its counters: n_tup_ins, n_tup_upd and n_tup_del. The first query checks one named table. The second pattern, shown after it, scans the whole database.

Sample Code

1SELECT
2  relname,
3  n_tup_ins,
4  n_tup_upd,
5  n_tup_del
6FROM pg_stat_user_tables
7WHERE relname = 'trip_positions';

To find insert-only tables across the whole database, remove the relname filter and use this version:

 1SELECT
 2  relname,
 3  n_tup_ins,
 4  n_tup_upd,
 5  n_tup_del
 6FROM pg_stat_user_tables
 7WHERE n_tup_upd = 0
 8  AND n_tup_del = 0
 9  AND n_tup_ins > 1000
10ORDER BY n_tup_ins DESC;

Notes: The counters are cumulative and are reset under the conditions described in Version Compatibility below. Change the 1000 threshold to suit the database. The first query is the single-table check; the second lists every insert-only table with meaningful traffic, busiest first.

Code Breakdown

The query reads one view and filters on three counters. Each part is described below.

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del

relname is the table name. The three counters are described in the manual like this:

  • n_tup_ins — total number of rows inserted
  • n_tup_upd — total number of rows updated
  • n_tup_del — total number of rows deleted

All three are totals since the statistics were last reset, not rates. A table with n_tup_ins = 4817 and zero in the other two columns has seen 4,817 inserts and nothing else since the last reset. It says nothing about when those inserts happened.

FROM pg_stat_user_tables

The manual says pg_stat_user_tables and pg_stat_sys_tables contain the same information, filtered to user and system tables respectively. The view has more columns than this query uses. Two useful ones for follow-up work are last_autovacuum and last_autoanalyze, which give the last time the autovacuum daemon vacuumed or analyzed the table. Another is n_ins_since_vacuum, an estimate of rows inserted since the table was last vacuumed (not counting VACUUM FULL).

WHERE n_tup_upd = 0 AND n_tup_del = 0

This is the insert-only test. It is a test of the counters, not of the table's design: nothing stops an application from updating the table tomorrow. A table that passes the test today is a candidate, and the application owner confirms whether it is truly append-only.

ORDER BY n_tup_ins DESC

Sorting by insert count puts the highest-traffic tables first. Those are the tables where autovacuum settings and a growth plan matter most.

Key Autovacuum Settings for Insert-Only Tables

Two autovacuum parameters control whether inserts alone can trigger a vacuum. A third setting, on the freezing side, is worth a look for these tables.

autovacuum_vacuum_insert_threshold

The manual describes it as the number of inserted tuples needed to trigger a VACUUM in any one table. The default is 1000 tuples. Setting it to -1 stops autovacuum from triggering a vacuum based on insert counts. The setting lives in postgresql.conf or on the server command line, and it can be overridden for individual tables through table storage parameters.

autovacuum_vacuum_insert_scale_factor

This is a fraction of the unfrozen pages in the table that is added to the threshold when deciding whether to vacuum. The default is 0.2, which the manual describes as 20 percent of unfrozen pages in the table. Like the threshold, it can be overridden per table with storage parameters.

The manual's routine-vacuuming chapter gives the formula: the vacuum insert threshold equals the base insert threshold plus the scale factor times the number of tuples times the percent of the table not frozen. Lowering the scale factor on a table makes insert-triggered vacuums come sooner.

autovacuum_freeze_min_age

The routine-vacuuming chapter says that for tables which receive INSERT operations but no or almost no UPDATE or DELETE operations, it may be beneficial to lower the table's autovacuum_freeze_min_age, because that may allow tuples to be frozen by earlier vacuums. That is the manual's own suggestion for exactly the kind of table this query finds.

Practical Applications

The counters turn a vague idea ("this looks like a log table") into a list you can act on. Three common uses follow.

Autovacuum Review

Run the database-wide query, then look at last_autovacuum and n_ins_since_vacuum for the tables it returns. If a high-traffic insert-only table has never been vacuumed by autovacuum, that is a reason to check the insert threshold and scale factor for it. The manual says such insert-triggered vacuums may allow portions of the table to be marked all visible and allow tuples to be frozen, which can reduce the work required in later vacuums.

Wraparound Planning

Without insert-triggered vacuums, freezing work builds up. The PostgreSQL 13 release notes describe this: allowing an insert-only table to receive periodic vacuuming helps spread out the work of freezing old tuples, so there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once. The manual also says that when normal vacuums have not frozen every old row version, a vacuum will eventually need to perform an aggressive vacuum, which freezes all eligible unfrozen transaction ID values. For a large insert-only table, an aggressive vacuum is a big job, and spreading it out is the point of tuning these settings.

Partitioning Candidates

A table that only ever grows will reach disk capacity unless something removes old rows. For tables where old data ages out, time-based partitioning is the manual's answer. It says that bulk loads and deletes can be accomplished by adding or removing partitions, and that dropping a partition with DROP TABLE, or running ALTER TABLE DETACH PARTITION, is far faster than a bulk operation and entirely avoids the VACUUM overhead of a bulk DELETE. The manual's own examples are:

1DROP TABLE measurement_y2006m02;
2ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
3ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

These are example statements from the partitioning chapter, not part of this script. The locking matters: the manual says DROP TABLE requires an ACCESS EXCLUSIVE lock on the parent table, and the first form of DETACH PARTITION does too. Adding CONCURRENTLY lets the detach operation require only a SHARE UPDATE EXCLUSIVE lock on the parent. The manual points to the ALTER TABLE page for the restrictions on the concurrent form.

Version Compatibility

Insert-triggered autovacuum (PostgreSQL 13). The PostgreSQL 13 release notes say that previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. From version 13, inserts can trigger vacuuming, through the new parameters autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor or the equivalent table storage options. On PostgreSQL 12 and earlier those two parameters do not exist, so the tuning described in this post applies to version 13 and later.

How counters behave across restarts. The current manual says cumulative statistics are collected in shared memory. When a server, including a physical replica, shuts down cleanly, a permanent copy of the statistics is stored in the pg_stat subdirectory, so statistics are retained across restarts. When starting from an unclean shutdown, such as after an immediate shutdown, a server crash, starting from a base backup or point-in-time recovery, all statistics counters are reset. This section reflects the current documentation; read the manual for your own version.

Checking whether the counters were reset. The manual says pg_stat_reset() resets all statistics counters for the current database to zero. It also says pg_stat_reset_shared with the argument bgwriter resets the counters shown in the pg_stat_bgwriter view, so the stats_reset column of pg_stat_bgwriter belongs to that cluster-wide set and is the wrong place to look for table counter resets. The pg_stat_database view has its own stats_reset column, described as the time at which those statistics were last reset. Read that column for the database you are examining before drawing conclusions from the counters, and treat an unexplained zero as a reason to ask before concluding that a table is insert-only.

Best Practices

  • Check for a reset first — a table can look insert-only only because its update and delete counters were cleared. Look at the reset time for the database and remember that crashes and point-in-time recovery reset all counters.
  • Confirm with the application owner — the counters show what has happened, not what the application is allowed to do. Ask whether the table is meant to be append-only before changing its settings.
  • Keep autovacuum on for these tables — the absence of dead tuples does not remove the need to update the visibility map and advance freezing, two of the four reasons the manual gives for vacuuming.
  • Tune per table, not globally — the insert threshold and scale factor can be overridden with table storage parameters, so one high-traffic table can be changed without touching the rest of the database.
  • Plan removal of old rows early — a retention policy and a partition scheme are easier to add before the table is very large. Confirm with the application team whether a retention policy exists before taking any action on a large insert-only table.
  • Re-run the check on a schedule — counters are cumulative, so a table that is insert-only today may start receiving updates later. A periodic run of the database-wide query catches the change.

References

Posts in this series