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 insertedn_tup_upd— total number of rows updatedn_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
- PostgreSQL: Cumulative Statistics System, pg_stat_all_tables view — column definitions for the table counters, statistics persistence across restarts and the
stats_resetcolumns. - PostgreSQL: Routine Vacuuming — the reasons for vacuuming, the insert threshold formula and advice on freezing for insert-mostly tables.
- PostgreSQL: Automatic Vacuuming parameters — defaults and per-table override for the insert threshold and scale factor.
- PostgreSQL: Table Partitioning — partition maintenance with
DROP TABLEandDETACH PARTITION. - PostgreSQL 13 release notes — where insert-triggered autovacuum was added.
- andyatkinson/pg_scripts: insert_only_pg_stat_user_tables.sql — original seed script.
Posts in this series
- How Many Connections Can Your PostgreSQL Database Handle?
- PostgreSQL Backend Connections via pg_stat_database
- pg_blocking_pids — Find Blocking Queries in PostgreSQL
- List PostgreSQL Databases by Size with Access Check
- Assess PostgreSQL Database Sizes Quickly and Easily
- Unveiling Your PostgreSQL Server - A Diagnostic Powerhouse
- Keep Your PostgreSQL Database Clean, Identify Idle Connections
- Query the PostgreSQL Configuration
- pg_is_in_recovery — Monitor PostgreSQL Standby Status
- ALTER SEQUENCE RESTART WITH in PostgreSQL — Examples
- Monitor Running Queries in PostgreSQL using pg_stat_activity
- Monitor PostgreSQL Active Sessions with pg_stat_activity
- PostgreSQL Error Handling Settings via pg_settings
- PostgreSQL File Location Settings Query via pg_settings
- Inspect PostgreSQL Lock Management Settings with pg_settings
- PostgreSQL Logging Configuration Query via pg_settings
- Monitor PostgreSQL Memory Settings with pg_settings
- PostgreSQL Table Row Count Estimates with SQL
- List PostgreSQL Tables by Size with SQL
- PostgreSQL WAL Settings Query Guide
- log_parser_stats, log_planner_stats, log_executor_stats — PostgreSQL
- PostgreSQL SSL Settings Query Guide
- PostgreSQL Resource Settings Query Guide
- PostgreSQL Replication Settings Query Guide
- PostgreSQL Query Planning Settings Query Guide
- PostgreSQL Preset Options Settings Query Guide
- PostgreSQL Miscellaneous Settings Query Guide
- Count PostgreSQL Sessions by State with SQL
- Kill Idle PostgreSQL Sessions with SQL
- GRANT SELECT on All Tables in PostgreSQL — with Examples
- pg_stat_user_tables — Find Insert-Only Tables in PostgreSQL
- Detect Soft Delete Patterns in PostgreSQL
- List PostgreSQL Object Comments with SQL
- List Foreign Key Constraints in PostgreSQL
- List PostgreSQL Enum Types and Their Values with SQL
- List All Views in a PostgreSQL Database with SQL
- Find PostgreSQL Tables Without a Primary Key
- List PostgreSQL Partitioned Tables with SQL
- List All Schemas in Your PostgreSQL Database
- pg_stat_database — Query PostgreSQL Database Statistics
- List PostgreSQL Roles and Their Privileges
- Scrubbing Email PII in PostgreSQL for GDPR Compliance
- List Installed Extensions in PostgreSQL
- List PostgreSQL Collations with pg_collation and ICU
- PostgreSQL Replica Identity for Logical Replication
- Monitor PostgreSQL Vacuum Progress with pg_stat_progress_vacuum
- Monitor PostgreSQL Wait Events Using pg_stat_activity
- Monitor PostgreSQL Replication Lag with pg_stat_replication
- List PostgreSQL Wait Events with the pg_wait_events View
- PostgreSQL Column-Level Permissions Audit Query
- List All PostgreSQL Triggers with Their State
- timestamptz and tzdata: Avoid Shifted PostgreSQL Timestamps
- Inspect PostgreSQL Sequences with the pg_sequences View
- List PostgreSQL Functions with pg_proc
- Query PostgreSQL Tablespace Info with pg_tablespace
- Audit PostgreSQL Authentication with pg_hba_file_rules
- List PostgreSQL Materialized Views with pg_matviews
- Audit Row-Level Security Policies with pg_policies
- List PostgreSQL Extension Objects with pg_describe_object