Identify Insert-Only Tables in PostgreSQL

calendar Apr 2, 2026 / · 5 min read · postgresql database administration performance autovacuum monitoring pg_stat_user_tables pg_stat_bgwriter  ·
Share on: linkedin copy

Identify Insert-Only Tables in PostgreSQL Using pg_stat_user_tables

Not all PostgreSQL tables behave the same way. Most application tables have a mix of inserts, updates, and deletes. A minority of tables receive only inserts. These append-only tables — event logs, audit trails, sensor readings, message queues — have fundamentally different storage and maintenance characteristics.

A DBA who treats an insert-only table the same as a general-purpose table will misconfigure autovacuum, miss partitioning opportunities, and misread bloat signals. The starting point is knowing which tables are insert-only. PostgreSQL tracks per-table DML counts in pg_stat_user_tables, and a simple query against that view identifies them.

SQL Script

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

Notes: Statistics in pg_stat_user_tables accumulate since the last pg_stat_reset() call or since the statistics collector was started. Run as any role with SELECT access to the view. To find all insert-only tables across the database, remove the WHERE clause and filter with WHERE n_tup_upd = 0 AND n_tup_del = 0 AND n_tup_ins > 0.

Code Breakdown

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del

relname is the table name. The three counter columns are:

  • n_tup_ins — total rows inserted since last stats reset
  • n_tup_upd — total rows updated
  • n_tup_del — total rows deleted

These are cumulative counters. A COPY command counts toward n_tup_ins. A VACUUM that removes dead tuples does not change these counters.

FROM pg_stat_user_tables

pg_stat_user_tables returns one row per user-defined table in the current database, excluding catalog and system tables. The view also contains vacuum and analyse timing columns (last_autovacuum, last_autoanalyze) that are useful for follow-up investigation.

WHERE relname = 'trip_positions'

The original script targets a specific table. In practice, remove this and add:

1WHERE n_tup_upd = 0
2  AND n_tup_del = 0
3  AND n_tup_ins > 1000
4ORDER BY n_tup_ins DESC;

This finds all insert-only tables with meaningful traffic, sorted by busiest first.

Key Points

  • A table with n_tup_upd = 0 and n_tup_del = 0 is a candidate insert-only table.
  • Insert-only tables produce no dead tuples from DELETE or UPDATE. Autovacuum's default thresholds are calibrated for mixed workloads and are often poorly matched to append-only tables.
  • The absence of dead tuples does not mean autovacuum can be disabled. The visibility map still needs updating, and the freeze counter must be advanced to prevent transaction ID wraparound.
  • Large insert-only tables that grow without bound are strong candidates for time-based partitioning.
  • If pg_stat_reset() has been called recently, counters return to zero. A table that looks insert-only may simply have had its counters cleared.

Insights and Explanations

Insert-only tables appear in nearly every production PostgreSQL database. Common examples:

  • Audit logs: Events recorded by triggers or application logic. Rows are written once and read for compliance or debugging. Never updated, rarely deleted.
  • Event streams: Click events, page views, API request logs. Very high insert rate. Queries are time-bounded aggregations.
  • Sensor or telemetry data: IoT devices writing readings at fixed intervals. Inserts only. Queries aggregate over time windows.
  • Job queues (simpler implementations): New jobs inserted, completed jobs left in place or status-updated.

The trip_positions table in the script example — GPS position data written as a vehicle moves — is a clean example of this pattern.

Autovacuum Behaviour for Insert-Only Tables

Autovacuum has two main triggers: dead tuple accumulation and freeze age. Insert-only tables accumulate no dead tuples, so the dead-tuple trigger never fires. The freeze trigger does fire eventually, but for a table that is only growing, the oldest unfrozen rows are always the earliest inserts.

PostgreSQL 13 introduced autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor. These parameters trigger autovacuum on insert-only tables based on the number of new inserts, ensuring the visibility map stays current and freeze work is done incrementally. For PostgreSQL 12 and earlier, insert-only tables were essentially invisible to autovacuum's vacuum trigger.

Bloat vs. Unbounded Growth

Insert-only tables do not bloat in the traditional sense. Traditional bloat comes from dead tuples that autovacuum has not reclaimed. Insert-only tables have none of that.

What they do have is unbounded growth. If rows are never deleted, the table grows until it hits disk capacity or a manual archival process runs. The distinction matters because the remediation is different. Bloat is fixed with VACUUM. Unbounded growth is fixed with a data lifecycle policy: archival, partitioned drop, or time-to-live deletion.

Follow-Up Queries

Once you have a list of insert-only tables, check their size and vacuum history:

 1SELECT
 2    relname,
 3    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
 4    n_tup_ins,
 5    last_autovacuum,
 6    last_autoanalyze
 7FROM pg_stat_user_tables
 8WHERE n_tup_upd = 0
 9  AND n_tup_del = 0
10  AND n_tup_ins > 0
11ORDER BY pg_total_relation_size(relid) DESC;

Additional Considerations

  • Time-based partitioning: Insert-only tables are ideal candidates for range partitioning by time. Old partitions can be detached and dropped instantly — no DELETE, no bloat, no long lock.
  • Autovacuum tuning for PG13+: Lower autovacuum_vacuum_insert_scale_factor per table to keep the visibility map current on high-insert-rate tables.
  • Statistics reset warning: Check SELECT stats_reset FROM pg_stat_bgwriter before drawing conclusions. If reset was recent, the insert-only identification is unreliable until counters accumulate meaningful history.
  • Retention policy: Confirm with the application team whether a data retention policy exists before taking any action on large insert-only tables.

References

Posts in this series