DEV Community: Shinya Kato

Reducing row count estimation errors in PostgreSQL

Shinya Kato — Fri, 06 Feb 2026 05:41:10 +0000

Introduction

PostgreSQL's query planner relies on table statistics to estimate the number of rows (estimated rows) each operation will process, and then selects an optimal execution plan based on these estimates. When the estimated rows diverge significantly from the actual rows, the planner can choose a suboptimal plan, leading to severe query performance degradation.

This article walks through four approaches I used to reduce row count estimation errors, ordered from least to most invasive. Due to confidentiality constraints, I cannot share actual SQL or execution plans, so the focus is on the diagnostic thought process and the techniques applied.

Prerequisites

The approaches in this article are applicable to any modern PostgreSQL version, as the underlying mechanisms (autovacuum, pg_statistic, extended statistics) have been stable across versions
The target table had a high update frequency
Actual SQL and execution plans cannot be shared; this article focuses on methodology

Approach 1: Tuning autovacuum auto-ANALYZE frequency per table

The target table was known to have a very high update rate, so the first hypothesis was that the statistics were simply stale.

In PostgreSQL, the autovacuum daemon automatically runs ANALYZE to update statistics stored in pg_statistic. However, for tables with heavy write activity, auto-ANALYZE may not keep up with the rate of change, causing the statistics to drift from reality.

To address this, I adjusted the auto-ANALYZE frequency for the specific table rather than changing the server-wide settings in postgresql.conf.

The two key parameters are:

autovacuum_analyze_threshold: The minimum number of tuple modifications before auto-ANALYZE is triggered (default: 50)
autovacuum_analyze_scale_factor: The fraction of the table size added to the threshold (default: 0.1, i.e., 10%)

ALTER TABLE table_name SET (
    autovacuum_analyze_threshold = 0,
    autovacuum_analyze_scale_factor = 0.01
);

In this example, autovacuum_analyze_threshold is set to 0 (default: 50) and autovacuum_analyze_scale_factor is reduced from 0.1 to 0.01, so that auto-ANALYZE triggers after just 1% of the table has been modified.

Note: You can verify whether auto-ANALYZE is keeping up by querying the pg_stat_user_tables view. Check the last_autoanalyze column for the timestamp of the last auto-ANALYZE, and n_mod_since_analyze for the number of row modifications since the last ANALYZE.

For the full list of per-table storage parameters, see the PostgreSQL documentation on storage parameters.

Approach 2: Increasing the statistics sampling target per column

After adjusting autovacuum frequency, the next hypothesis was that ANALYZE was running often enough but the sample size was too small to produce accurate statistics.

PostgreSQL's ANALYZE collects samples from each column and stores most-common values (MCVs) and histograms in pg_statistic. The precision of this information is governed by default_statistics_target (default: 100), which controls the number of histogram buckets and MCV entries.

Rather than changing the server-wide setting, I increased the statistics target on a per-column basis for the affected table:

ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 500;

As a general guideline, setting the target to 500-1000 for columns frequently used in WHERE clauses is a common tuning step. However, higher values increase ANALYZE execution time and the size of pg_statistic, so there is a trade-off to consider.

Note: After changing the statistics target with SET STATISTICS, you must run ANALYZE (or wait for the next auto-ANALYZE) for the new setting to take effect.

Approach 3: Using extended statistics

Even after improving the freshness and precision of the base statistics, row count estimation errors can persist. This happens when the planner's estimation model itself has structural limitations.

By default, PostgreSQL's planner assumes that conditions on different columns are independent. When this assumption is violated, the planner multiplies selectivities independently, often resulting in a dramatic underestimation of the actual row count.

This occurs when:

A table has two columns a1 and a2
There is a functional dependency between them (e.g., a1 determines a2)
The WHERE clause includes conditions on both columns

A concrete example would be columns like country and city -- knowing the country largely determines the set of possible cities. The planner treats the selectivities of each condition as independent, producing an estimate far lower than the actual row count.

To address this, I created extended statistics on the correlated columns:

CREATE STATISTICS stat_name ON a1, a2 FROM table_name;

CREATE STATISTICS supports three kinds of statistics: ndistinct (multi-column distinct counts), dependencies (functional dependencies), and mcv (multi-column most-common values). If you omit the kind specification, all three are collected, which is what I did as a starting point.

Note: CREATE STATISTICS only defines the statistics object. The actual statistics are not populated until ANALYZE runs on the table.

For more details, see the PostgreSQL documentation on CREATE STATISTICS.

Approach 4: Using pg_hint_plan as a last resort

When statistics-based approaches are not sufficient, pg_hint_plan provides a way to directly control the planner's behavior through SQL comment-based hints.

For row count estimation issues specifically, the Rows hint allows you to override the planner's row estimate for a given table:

/*+ Rows(table_name #1000) */ SELECT ...

In the example above, the estimated row count for table_name is fixed at 1000. You can also use +, -, or * instead of # to add, subtract, or multiply the planner's original estimate.

However, hint-based approaches come with significant drawbacks:

Fragile to data changes: Fixed row counts become inaccurate as data volumes change
Reduced maintainability: Team members unfamiliar with the hints may be confused by them
Masks root causes: Hints can hide underlying statistics or schema issues that should be properly addressed

For these reasons, I recommend using hints only when statistics-based approaches have been exhausted, or as a temporary measure while investigating the root cause.

Conclusion

This article covered four approaches to reducing row count estimation errors in PostgreSQL, ordered by increasing invasiveness:

Tune autovacuum frequency: Are the statistics stale?
Increase the statistics target: Is the sample size sufficient?
Create extended statistics: Can the planner account for cross-column correlations?
Apply hint clauses: A last resort when statistics alone cannot solve the problem

When facing row estimation errors, a systematic approach works best: start with EXPLAIN ANALYZE to compare estimated vs. actual row counts, then work through the possible causes in order -- statistics freshness, then precision, then structural limitations of the estimation model. I hope this article serves as a useful reference in your troubleshooting process.

References

4 causes of table bloat in PostgreSQL and how to address them

Shinya Kato — Tue, 20 Jan 2026 08:55:13 +0000

What Is Table Bloat?

Table bloat in PostgreSQL refers to the phenomenon where "dead tuples" generated by UPDATE or DELETE operations remain uncollected by VACUUM, causing data files to grow unnecessarily large.

For VACUUM to reclaim dead tuples, it must be guaranteed that those tuples "cannot possibly be referenced by any currently running transaction." If old transactions persist for any reason, VACUUM's garbage collection stops at that point.

This article explains the following four causes of table bloat: how each manifests, how to identify the root cause, and how to resolve it.

Long-running transactions
Uncommitted prepared transactions
Queries on standby servers with hot_standby_feedback enabled
Logical replication lag

Test Environment

PostgreSQL 19dev (34740b90bc123d645a3a71231b765b778bdcf049)

Long-Running Transactions

This is probably the most familiar cause. Whether active or idle, a long-running transaction prevents VACUUM from reclaiming dead tuples generated by UPDATE or DELETE operations that occurred after the transaction started. This is because the long-running transaction might need to read the pre-update versions of those tuples.

Setup

In Terminal 1, start a transaction and obtain a transaction ID.

Terminal 1:
=# BEGIN;
BEGIN
=*# SELECT txid_current();
 txid_current
--------------
          782
(1 row)

In a separate Terminal 2, delete data and run VACUUM.

Terminal 2:
=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 782, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 15 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 0 removed, 100 remain, 100 are dead but not yet removable indicates that dead tuples were not reclaimed.

Identifying the cause

The message removable cutoff: 782, which was 2 XIDs old when operation ended suggests that transaction ID 782 is the culprit. Check the pg_stat_activity view.

Terminal 2:
=# SELECT * FROM pg_stat_activity WHERE backend_xid = 782;
-[ RECORD 1 ]----+------------------------------
datid            | 5
datname          | postgres
pid              | 94076
leader_pid       | [NULL]
usesysid         | 10
usename          | shinya
application_name | psql
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 13:41:30.049678+09
xact_start       | 2026-01-19 13:54:38.856466+09
query_start      | 2026-01-19 13:54:43.501664+09
state_change     | 2026-01-19 13:54:43.50271+09
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 782
backend_xmin     | [NULL]
query_id         | [NULL]
query            | SELECT txid_current();
backend_type     | client backend

The backend process with PID 94076 is in an "idle in transaction" state, which prevented VACUUM from reclaiming the dead tuples.

Resolution

Terminate the backend process with PID 94076.

Terminal 2:
=# SELECT pg_terminate_backend(94076);
 pg_terminate_backend
----------------------
 t
(1 row)

=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: table "t": truncated 1 to 0 pages
LOCATION:  lazy_truncate_heap, vacuumlazy.c:3401
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 792, which was 1 XIDs old when operation ended
new relfrozenxid: 792, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 7.796 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 100 removed, 0 remain, 0 are dead but not yet removable confirms that all dead tuples have been reclaimed.

Note: Always verify that it is safe to terminate the backend process before doing so.

Uncommitted Prepared Transactions

This can occur when applications or distributed database middleware use two-phase commit (2PC). Uncommitted prepared transactions persist in the database even after the session is disconnected, preventing VACUUM from reclaiming dead tuples.

Setup

To use prepared transactions, set max_prepared_transactions to 1 or higher and restart PostgreSQL.

postgresql.conf:
max_prepared_transactions = 10

Start a transaction and prepare it for 2PC.

=# BEGIN;
BEGIN
=*# PREPARE TRANSACTION 'foobar';
PREPARE TRANSACTION

Delete data and run VACUUM.

=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 796, which was 2 XIDs old when operation ended
new relfrozenxid: 793, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 14.974 MB/s, avg write rate: 14.974 MB/s
buffer usage: 13 hits, 4 reads, 4 dirtied
WAL usage: 4 records, 4 full page images, 25678 bytes, 25412 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 0 removed, 100 remain, 100 are dead but not yet removable indicates that dead tuples were not reclaimed.

Identifying the cause

The message removable cutoff: 796, which was 2 XIDs old when operation ended suggests that transaction ID 796 is the culprit. Check the pg_prepared_xacts view.

=# SELECT * FROM pg_prepared_xacts;
 transaction |  gid   |           prepared            | owner  | database
-------------+--------+-------------------------------+--------+----------
         796 | foobar | 2026-01-19 14:49:30.089288+09 | shinya | postgres
(1 row)

The prepared transaction with global transaction ID "foobar" prevented VACUUM from reclaiming the dead tuples.

Resolution

Either commit or roll back the prepared transaction with global transaction ID "foobar" using COMMIT PREPARED or ROLLBACK PREPARED.

=# COMMIT PREPARED 'foobar';
COMMIT PREPARED
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: table "t": truncated 1 to 0 pages
LOCATION:  lazy_truncate_heap, vacuumlazy.c:3401
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 798, which was 1 XIDs old when operation ended
new relfrozenxid: 798, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 9.778 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 100 removed, 0 remain, 0 are dead but not yet removable confirms that all dead tuples have been reclaimed.

Queries on Standby with `hot_standby_feedback` Enabled

In a streaming replication setup with hot_standby_feedback enabled, queries on the standby server can prevent VACUUM from reclaiming dead tuples on the primary. This is by design: hot_standby_feedback exists specifically to prevent dead tuple removal by VACUUM and avoid query conflicts on the standby.

Setup

Set up a streaming replication environment, enable hot_standby_feedback on the standby, and reduce wal_receiver_status_interval to increase the frequency of feedback from the standby to the primary.

postgresql.conf:
hot_standby_feedback = on
wal_receiver_status_interval = 1s

On the standby, start a transaction and execute a query.

Standby:
=# BEGIN;
BEGIN
=*# SELECT pg_sleep(300);
 pg_sleep
----------

(1 row)

Note: We use pg_sleep() to prevent the snapshot from being released immediately when the query completes. You could also set the transaction isolation level to REPEATABLE READ to maintain the snapshot within the transaction.

On the primary, delete data and run VACUUM.

Primary:
=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 831, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 12 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 0 removed, 100 remain, 100 are dead but not yet removable indicates that dead tuples were not reclaimed.

Identifying the cause

The message removable cutoff: 831, which was 1 XIDs old when operation ended suggests that transaction ID 831 is the culprit. Check the pg_stat_replication view.

Primary:
=# SELECT * FROM pg_stat_replication WHERE backend_xmin = 831;
-[ RECORD 1 ]----+------------------------------
pid              | 171632
usesysid         | 10
usename          | shinya
application_name | walreceiver
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 16:32:24.33927+09
backend_xmin     | 831
state            | streaming
sent_lsn         | 0/03081178
write_lsn        | 0/03081178
flush_lsn        | 0/03081178
replay_lsn       | 0/03081178
write_lag        | [NULL]
flush_lag        | [NULL]
replay_lag       | [NULL]
sync_priority    | 0
sync_state       | async
reply_time       | 2026-01-19 17:29:08.063083+09

Transaction ID 831 is being reported via hot_standby_feedback.

Next, check the pg_stat_activity view on the standby.

Standby:
=# SELECT * FROM pg_stat_activity WHERE backend_xmin = 831;
-[ RECORD 1 ]----+------------------------------
datid            | 5
datname          | postgres
pid              | 197146
leader_pid       | [NULL]
usesysid         | 10
usename          | shinya
application_name | psql
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 17:25:19.931946+09
xact_start       | 2026-01-19 17:28:33.640921+09
query_start      | 2026-01-19 17:28:36.617061+09
state_change     | 2026-01-19 17:28:36.617067+09
wait_event_type  | Timeout
wait_event       | PgSleep
state            | active
backend_xid      | [NULL]
backend_xmin     | 831
query_id         | [NULL]
query            | SELECT pg_sleep(300);
backend_type     | client backend

The query SELECT pg_sleep(300) running on the standby backend process with PID 197146 is the cause.

Resolution

Cancel the query running on the standby backend process (PID 197146).

Standby:
=# SELECT pg_cancel_backend(197146);
 pg_cancel_backend
-------------------
 t
(1 row)

Primary:
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: table "t": truncated 1 to 0 pages
LOCATION:  lazy_truncate_heap, vacuumlazy.c:3401
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 792, which was 1 XIDs old when operation ended
new relfrozenxid: 792, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 7.796 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 100 removed, 0 remain, 0 are dead but not yet removable confirms that all dead tuples have been reclaimed.

Logical Replication Lag

Logical replication reads WAL records, decodes them, and sends them to subscribers. A key point is that the decoding process requires the system catalog state as it existed at the "past point in time" when the WAL record was generated.

For example, when decoding WAL for a table that had a column dropped in the past, the decoder must reference the table definition from "before the column was dropped" rather than the current definition to decode correctly. Therefore, the system catalog dead tuples required for that point in time must be retained until the logical replication slot finishes processing that WAL.

Setup

Create a logical replication slot.

=# SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding');
 pg_create_logical_replication_slot
------------------------------------
 (test_slot,0/017BBC00)
(1 row)

Drop an existing table and run VACUUM on the pg_class catalog.

=# DROP TABLE t;
DROP TABLE
=# VACUUM (VERBOSE) pg_class;
INFO:  00000: vacuuming "postgres.pg_catalog.pg_class"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.pg_catalog.pg_class": index scans: 0
pages: 0 removed, 14 remain, 14 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 418 remain, 1 are dead but not yet removable
removable cutoff: 780, which was 1 XIDs old when operation ended
new relfrozenxid: 779, which is 19 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 16.421 MB/s
buffer usage: 45 hits, 0 reads, 4 dirtied
WAL usage: 4 records, 4 full page images, 32658 bytes, 32392 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 0 removed, 418 remain, 1 are dead but not yet removable indicates that a dead tuple was not reclaimed.

Identifying the cause

The message removable cutoff: 780, which was 1 XIDs old when operation ended suggests that transaction ID 780 is the culprit. Check the pg_replication_slots view.

=# SELECT * FROM pg_replication_slots WHERE catalog_xmin = 780;
-[ RECORD 1 ]--------+------------------------------
slot_name            | test_slot
plugin               | test_decoding
slot_type            | logical
datoid               | 5
database             | postgres
temporary            | f
active               | f
active_pid           | [NULL]
xmin                 | [NULL]
catalog_xmin         | 780
restart_lsn          | 0/017BBBC8
confirmed_flush_lsn  | 0/017BBC00
wal_status           | reserved
safe_wal_size        | [NULL]
two_phase            | f
two_phase_at         | [NULL]
inactive_since       | 2026-01-19 20:09:07.152103+09
conflicting          | f
invalidation_reason  | [NULL]
failover             | f
synced               | f
slotsync_skip_reason | [NULL]

This logical replication slot requires transaction ID 780 as its oldest transaction affecting the system catalogs.

Resolution

Since this logical replication slot is not in use, drop it.

=# SELECT pg_drop_replication_slot('test_slot');
 pg_drop_replication_slot
--------------------------

(1 row)

=# VACUUM (VERBOSE) pg_class;
INFO:  00000: vacuuming "postgres.pg_catalog.pg_class"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.pg_catalog.pg_class": index scans: 1
pages: 0 removed, 14 remain, 14 scanned (100.00% of total), 0 eagerly scanned
tuples: 1 removed, 417 remain, 0 are dead but not yet removable
removable cutoff: 781, which was 0 XIDs old when operation ended
new relfrozenxid: 781, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan needed: 1 pages from table (7.14% of total) had 1 dead item identifiers removed
index "pg_class_oid_index": pages: 4 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "pg_class_relname_nsp_index": pages: 6 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "pg_class_tblspc_relfilenode_index": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 7.032 MB/s, avg write rate: 63.288 MB/s
buffer usage: 62 hits, 1 reads, 9 dirtied
WAL usage: 9 records, 9 full page images, 55417 bytes, 54864 full page image bytes, 0 buffers full
memory usage: dead item storage 0.03 MB accumulated across 1 reset (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

The message tuples: 1 removed, 417 remain, 0 are dead but not yet removable confirms that all dead tuples have been reclaimed.

Ongoing Community Discussions

This article has explained the causes and solutions for table bloat.

Since we intentionally created these situations to demonstrate the solutions, they may not have seemed particularly difficult to handle. However, resolving these issues in practice requires significant PostgreSQL operational knowledge and involves checking multiple system views, which can be quite challenging.

Furthermore, when table bloat is accompanied by an impending transaction ID wraparound crisis, executing these procedures calmly while avoiding operational errors is stressful, and ideally, we would want to avoid such situations.

Currently, I am proposing a feature to the PostgreSQL community that would output the reason why dead tuples could not be reclaimed in the VACUUM log. It does not seem close to being committed yet, but once it is, I plan to update this blog post.

Report oldest xmin source when autovacuum cannot remove tuples

There have also been past discussions about displaying the reason why dead tuples could not be reclaimed in a system view, but this has not been implemented yet either. I would like to work on this as well.

Proposal: Expose oldest xmin as SQL function for monitoring

Summary

This article explained the causes of table bloat in PostgreSQL and how to address them. I hope you found it helpful.

New PostgreSQL features I developed in 2025

Shinya Kato — Thu, 25 Dec 2025 23:00:00 +0000

Introduction

I started contributing to PostgreSQL around 2020. This year I wanted to work harder, so I will explain the PostgreSQL features I developed and committed in 2025.

I also committed some other patches, but they were bug fixes or small document changes. Here I explain the ones that seem most useful.

These are mainly features in PostgreSQL 19, now in development. They may be reverted before the final release.

Added a document that recommends default psql settings when restoring pg_dump backups

Title: Doc: recommend "psql -X" for restoring pg_dump scripts.
Committer: Tom Lane
Date: Sat, 25 Jan 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d83a108c10a3ec886a24c620a915aa2c5bc023aa

When you restore a dump file made by pg_dump with psql, you may get errors if psql is using non-default settings (I saw it with AUTOCOMMIT=off). The change is only in the docs. It recommends using the psql option -X (--no-psqlrc) to avoid reading the psql config file.

For psql config file psqlrc, see my past blog:
https://zenn.dev/shinyakato/articles/543dae5d2825ee

Here is a test with \set AUTOCOMMIT off:

-- create a test database
$ createdb test1

-- dump all databases to an SQL script file
-- -c issues DROP for databases, roles, and tablespaces before recreating them
$ pg_dumpall -c -f test1.sql

-- restore with psql
$ psql -f test1.sql
~snip~
psql:test1.sql:14: ERROR:  DROP DATABASE cannot run inside a transaction block
psql:test1.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test1.sql:30: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test1.sql:31: ERROR:  current transaction is aborted, commands ignored until end of transaction block
~snip~

DROP DATABASE from -c cannot run inside a transaction block, so you get these errors. Also, by default, when a statement fails inside a transaction block, the whole transaction aborts, so later statements also fail.

Other statements also cannot run inside a transaction block, so you will see similar errors when restoring them.

Added backup_type column to pg_stat_progress_basebackup view

Title: Add backup_type column to pg_stat_progress_basebackup.
Committer: Masahiko Sawada
Date: Tue, 5 Aug 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=deb674454c5cb7ecabecee2e04ca929eee570df4

PostgreSQL 17 added incremental backup to pg_basebackup. But the pg_stat_progress_basebackup view had no column to tell full or incremental.

So I added backup_type, which shows full or incremental.

Run pg_basebackup and check pg_stat_progress_basebackup from another terminal:

$ pg_basebackup -D full

backup_type shows full.

=# TABLE pg_stat_progress_basebackup;
-[ RECORD 1 ]--------+-------------------------
pid                  | 853626
phase                | streaming database files
backup_total         | 1592460800
backup_streamed      | 622124544
tablespaces_total    | 1
tablespaces_streamed | 0
backup_type          | full                     <- new!

Now take an incremental backup with pg_basebackup -i.

$ pg_basebackup -i full/backup_manifest -D incl

backup_type shows incremental.

=# TABLE pg_stat_progress_basebackup;
-[ RECORD 1 ]--------+-------------------------
pid                  | 854435
phase                | streaming database files
backup_total         | 1613615104
backup_streamed      | 726617088
tablespaces_total    | 1
tablespaces_streamed | 0
backup_type          | incremental              <- new!

COPY FROM now supports files with multi-line headers

Title: Support multi-line headers in COPY FROM command.
Committer: Fujii Masao
Date: Thu, 3 Jul 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bc2f348e87c02de63647dbe290d64ff088880dbe

The COPY command has a HEADER option. For COPY FROM it controls skipping header rows. For COPY TO it controls outputting column names. Before, COPY FROM could not skip multi-line headers. It only accepted a boolean.

Now it accepts an integer, so it skips the given number of header lines.

=# \! cat /tmp/copy.csv
first header
second header
1,one
2,two
3,three

=# COPY t FROM '/tmp/copy.csv' WITH (HEADER 2, FORMAT csv);
COPY 3

=# TABLE t;
 id | data  
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

By the way, MySQL, SQL Server, and Oracle SQL*Loader already had similar functions.

MySQL: LOAD DATA ... IGNORE N LINES ¹
SQL Server: BULK INSERT … WITH (FIRST ROW=N) ²
Oracle SQL*Loader: sqlldr … SKIP=N ³

Split the autovacuum log settings for VACUUM and ANALYZE

Title: Add log_autoanalyze_min_duration
Committer: Peter Eisentraut
Date: Wed, 15 Oct 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd3ae378301f7e84c18f7a90f183c3cd4165c0da

We already had log_autovacuum_min_duration. It logged VACUUM and ANALYZE by autovacuum when they took more time than the setting (default unit: ms). But ANALYZE mostly reads sampled rows, while VACUUM reads and writes table and index pages, so VACUUM often takes longer (it depends on table design, data size, stats target, workload, extended stats, etc.). We could not set them separately.

Now log_autovacuum_min_duration is VACUUM-only, and new log_autoanalyze_min_duration is ANALYZE-only. At first we proposed log_autovacuum_vacuum_min_duration and log_autovacuum_analyze_min_duration for consistency, but changing the existing name would break backward compatibility and affect pg_dump/pg_upgrade, so we stopped.

=# CREATE TABLE t (i int, d text) WITH (
  -- autoanalyze settings
  autovacuum_analyze_threshold = 1,
  autovacuum_analyze_scale_factor = 0,
  log_autoanalyze_min_duration = 0,
  -- autovacuum settings
  autovacuum_vacuum_threshold = 1,
  autovacuum_vacuum_scale_factor = 0,
  log_autovacuum_min_duration = 100_000_000
);
=# INSERT INTO t VALUES (1, 'a');
=# DELETE FROM t WHERE i = 1;

2025-12-03 15:15:39.608 JST [401368] LOG:  automatic analyze of table "postgres.public.t"
avg read rate: 18.229 MB/s, avg write rate: 0.000 MB/s
buffer usage: 155 hits, 7 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 530 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

Show compressed full-page image size in pg_stat_wal

Title: Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal()
Committer: Michael Paquier
Date: Tue, 28 Oct 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f9a09aa2952039a9956b44d929b9df74d62a4cd4

When wal_compression is on, full-page images (FPI) in WAL are compressed. Before, to measure the effect you had to:

Run the same benchmark with compression on/off and compare wal_bytes in pg_stat_wal. But this is for all WAL, not only FPI, so it is not the exact compression ratio.
Run pg_waldump --fullpage on the server to see compressed WAL sizes and calculate the ratio. This needs WAL files and server access, so it was not usable in clouds where you cannot log in. (If you can use pg_wal_inspect, maybe you can calculate in the cloud, but I have not checked.)

So I added wal_fpi_bytes to pg_stat_wal.

=# TABLE pg_stat_wal;
-[ RECORD 1 ]----+-----------------------------
wal_records      | 2031667
wal_fpi          | 288581
wal_bytes        | 6346674376
wal_fpi_bytes    | 1932610356                   <- new!
wal_buffers_full | 424447
stats_reset      | 2025-12-02 19:31:44.16184+09

There are also patches to add this info to EXPLAIN (WAL) and VACUUM logs. ⁴⁵

Added mode and started_by columns to pg_stat_progress_vacuum view

Title: Add mode and started_by columns to pg_stat_progress_vacuum view.
Committer: Masahiko Sawada
Date: Tue, 9 Dec 2025
Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f9a09aa2952039a9956b44d929b9df74d62a4cd4

VACUUM has several reasons to start (auto, manual, wraparound) and several modes (normal, aggressive, fail-safe). The pg_stat_progress_vacuum view for progress did not show these.

So I added mode (normal, aggressive, failsafe) and started_by (manual, autovacuum, autovacuum_wraparound).

Here is pg_stat_progress_vacuum:

=# TABLE pg_stat_progress_vacuum;
-[ RECORD 1 ]--------+--------------
pid                  | 362895
datid                | 5
datname              | postgres
relid                | 24602
phase                | scanning heap
heap_blks_total      | 8850
heap_blks_scanned    | 5327
heap_blks_vacuumed   | 0
index_vacuum_count   | 0
max_dead_tuple_bytes | 67108864
dead_tuple_bytes     | 0
num_dead_item_ids    | 0
indexes_total        | 0
indexes_processed    | 0
delay_time           | 0
mode                 | normal       <- new!
started_by           | autovacuum   <- new!

A similar patch adds started_by (manual, autovacuum) to pg_stat_progress_analyze. ⁶

Conclusion

I introduced the PostgreSQL features I developed in 2025. I also proposed some other patches, but they are still under discussion. I hope to share them in next year.

Analyzing psqlrc settings on GitHub: How PostgreSQL engineers configure PostgreSQL

Shinya Kato — Sun, 30 Nov 2025 13:48:55 +0000

Introduction

Recently, I read an article titled "Alias Settings of Engineers Around the World" (in Japanese). As a PostgreSQL engineer, this got me thinking: "If they are customizing their bash aliases, how are they configuring their psql environments?"

Driven by curiosity, I decided to investigate GitHub repositories to see how developers commonly configure their psqlrc files.

What is psqlrc?

psql is the terminal-based front-end for PostgreSQL, allowing you to execute SQL interactively. Its configuration file is named psqlrc, and it has the following characteristics:

System-wide settings: The system-wide psqlrc file is stored in ../etc/ relative to the directory containing the PostgreSQL executable. This directory can be explicitly set using the environment variable PGSYSCONFDIR.
User-specific settings: The user-specific .psqlrc file is stored in the executing user's home directory. This file can be explicitly set using the environment variable PSQLRC.
Versioning: You can target specific psql versions by appending the version number to the filename, such as .psqlrc-14.
References: For more details, refer to the official documentation or the PostgreSQL Wiki.

Investigation

Methodology

Use the GitHub API to extract URLs of files containing the word psqlrc.
Insert the contents of these files into a PostgreSQL database.
Analyze the most frequently used settings.

Extracting URLs via GitHub API

I used the Search API. Here are the key points to consider:

The GitHub API returns a maximum of 1,000 results. To bypass this, I sliced the search query by file size.
The API returns only 100 results per page, so I used a for loop to fetch all pages.
To avoid hitting rate limits and angering GitHub, I inserted sleep commands.

#!/bin/bash

GITHUB_TOKEN=hoge # Set your own GitHub token here

# API call for files 0 to 1000 bytes
for size in `seq 0 50 950`;
do
  for page in `seq 10`;
  do
    curl -ksS \
      -H "Accept: application/vnd.github.v3+json" \
      -H "Authorization: token ${GITHUB_TOKEN}" \
      -o results/${size}-$((size+49))-${page}.json \
      "[https://api.github.com/search/code?q=filename:psqlrc+size:$](https://api.github.com/search/code?q=filename:psqlrc+size:$){size}..$((size+49))&per_page=100&page=${page}"
    sleep 60
  done
done

# API call for files over 1000 bytes
for page in `seq 10`;
do
  curl -ksS \
    -H "Accept: application/vnd.github.v3+json" \
    -H "Authorization: token ${GITHUB_TOKEN}" \
    -o results/1000-1049-${page}.json \
    "[https://api.github.com/search/code?q=filename:psqlrc+size](https://api.github.com/search/code?q=filename:psqlrc+size):>1000&per_page=100&page=${page}"
  sleep 60
done

# Extract html_url
for size in `seq 0 50 1000`;
do
  for page in `seq 10`;
  do
    cat results/${size}-$((size+49))-${page}.json | \
    jq -r ".items[] | .html_url" | \
    sed "s#/blob/#/raw/#g" >> results/url_list.txt
  done
done

Analyzing the Data in PostgreSQL

I analyzed the 2,005 files obtained from the process above. Since I'm a Postgres engineer, I naturally decided to insert the data into PostgreSQL for analysis.

I'll skip the insertion script, but I stored the psqlrc contents line by line into a table like this:

CREATE TABLE psqlrc (
  id INT,
  line_num INT,
  statement TEXT,
  PRIMARY KEY (id, line_num)
);

SELECT * FROM psqlrc WHERE id = 1;
 id | line_num |           statement
----+----------+-----------------------------
  1 |        1 | \set QUIET 1
  1 |        2 | \x auto
  1 |        3 | \set VERBOSITY verbose
  1 |        4 | \set HISTCONTROL ignoredups
  1 |        5 | \unset QUIET
(5 rows)

I executed the following SQL to rank the commands based on the first word of the statement. (I excluded comment lines and converted text to lowercase to treat SET and set as the same).

SELECT
  lower(split_part(statement, ' ', 1)) AS command,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY command
HAVING left(lower(split_part(statement, ' ', 1)), 2) != '--'
ORDER BY count DESC
LIMIT 10;
  command  | count 
-----------+-------
 \set      |  8929
 \pset     |  2565
 \echo     |  1076
 \x        |  1018
 \timing   |   995
 \unset    |   565
 \setenv   |   273
 \r        |   207
 set       |   132
 \encoding |   123
(10 rows)

Here is a summary of the commands and their formats:

Command	Format	Description
`\set`	`\set [ name [ value [ ... ] ] ]`	Sets the psql variable `name` to `value`.
`\pset`	`\pset [ option [ value ] ]`	Sets options affecting the output of query result tables.
`\echo`	`\echo text [ ... ]`	Prints arguments to standard output, separated by spaces and followed by a newline.
`\x`	`\x [ on \| off \| auto ]`	Toggles or sets extended table formatting mode.
`\timing`	`\timing [ on \| off ]`	Toggles or sets the display of how long each SQL statement takes to execute.
`\unset`	`\unset name`	Unsets (deletes) the psql variable `name`.
`\setenv`	`\setenv name [ value ]`	Sets the environment variable `name` to `value`.
`\r`	`\r` (`\reset`)	Resets (clears) the query buffer.
`SET` (SQL)	N/A	Changes a run-time parameter.
`\encoding`	`\encoding [ encoding ]`	Sets the client character set encoding.

Let's look at each of them individually.

`\set`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\set'
ORDER BY count DESC
LIMIT 30;
 item1 |       item2       | count 
-------+-------------------+-------
 \set  | prompt1           |  1013
 \set  | quiet             |   919
 \set  | comp_keyword_case |   860
 \set  | histcontrol       |   823
 \set  | histfile          |   819
 \set  | prompt2           |   766
 \set  | verbosity         |   717
 \set  | histsize          |   261
 \set  | on_error_rollback |   254
 \set  | on_error_stop     |    85
 \set  | locks             |    84
 \set  | uptime            |    83
 \set  | clear             |    83
 \set  | settings          |    70
 \set  | dbsize            |    70
 \set  | activity          |    67
 \set  | tablesize         |    64
 \set  | conninfo          |    64
 \set  | waits             |    63
 \set  | sp                |    59
 \set  | uselesscol        |    59
 \set  | ll                |    57
 \set  | show_slow_queries |    54
 \set  | autocommit        |    46
 \set  | pager             |    43
 \set  | version           |    43
 \set  | echo_hidden       |    42
 \set  | menu              |    40
 \set  | tsize             |    35
 \set  | extensions        |    35
(30 rows)

`\set PROMPT1`, `\set PROMPT2`

SELECT              
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'prompt1'
ORDER BY count DESC
LIMIT 10;
                                statement                                | count 
-------------------------------------------------------------------------+-------
 \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '                      |   258
 \set PROMPT1 '%[%033[1m%][%/] # '                                       |    53
 \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' |    51
 \set PROMPT1 '(%n@%M:%>) [%/] > '                                       |    30
 \set PROMPT1 '%[%033[1m%]%M/%/%R%[%033[0m%]%# '                         |    24
 \set PROMPT1 '%~%x%# '                                                  |    23
 \set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '              |    23
 \set PROMPT1 '%n@%M %~>'                                                |    18
 \set PROMPT1 '%M:%> %n@%/%R%#%x '                                       |    16
 \set PROMPT1 '%n@%M:%>%x %/# '                                          |    12
(10 rows)

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'prompt2'
ORDER BY count DESC
LIMIT 10;
                   statement                   | count 
-----------------------------------------------+-------
 \set PROMPT2 '[more] %R > '                   |   305
 \set PROMPT2 ''                               |    97
 \set PROMPT2 '... > '                         |    47
 \set PROMPT2 '... # '                         |    31
 \set PROMPT2 '%M %n@%/%R %# '                 |    24
 \set PROMPT2 '%R%x%# '                        |    15
 \set PROMPT2 '%R%# '                          |    12
 \set PROMPT2 '%[%033[1;33m%]%R%#%[%033[0m%] ' |    12
 \set PROMPT2 '> '                             |    12
 \set PROMPT2 '%R> '                           |     8
(10 rows)

PROMPT1, PROMPT2, and PROMPT3 are variables that configure the psql command prompt.

PROMPT1: The normal prompt issued when psql requests a new command.
PROMPT2: The prompt issued when more input is expected (e.g., when a command is not terminated by a semicolon or a quote is left open).
PROMPT3: The prompt issued during a SQL COPY FROM STDIN command when row input is expected.

It seems many people customize PROMPT1 and PROMPT2.

Here are examples of what these prompts look like when connected as the rocky user (superuser) to the postgres database via a Unix domain socket:

PROMPT1 Examples

PROMPT1 Setting	Display Result
`%/%R%x%#` (Default)	`postgres=#` (Default)
`%[%033[1m%]%M %n@%/%R%[%033[0m%]%#`	`[local] rocky@postgres=#`
`%[%033[1m%][%/] #`	`[postgres] #`
`%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%#`	`postgres=#` (Colored)
`(%n@%M:%>) [%/] >`	`(rocky@[local]:5432) [postgres] >`
`%[%033[1m%]%M/%/%R%[%033[0m%]%#`	`[local]/postgres=#`

PROMPT2 Examples

PROMPT2 Setting	Display Result
`%/%R%x%#` (Default)	`postgres(#` (Default)
`[more] %R >`	`[more] ( >`
`''`	(No display)
`... >`	`... >`
`... #`	`... #`
`%M %n@%/%R %#`	`[local] rocky@postgres( #`

It appears many users prefer to display the connection user, database, port, and hostname.

`\set QUIET`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'quiet'
ORDER BY count DESC
LIMIT 10;
      statement      | count 
---------------------+-------
 \set QUIET 1        |   641
 \set QUIET 0        |    78
 \set QUIET OFF      |    42
 \set QUIET ON       |    42
 \set QUIET on       |    37
 \set QUIET yes      |    27
 \set QUIET off      |    26
 \set QUIET          |    18
 \set QUIET 1\r      |     3
 \set QUIET :QUIETRC |     2
(10 rows)

The QUIET variable has the same effect as the command-line option -q (--quiet). It configures psql to work silently without printing informational messages.

For example, normally CREATE TABLE outputs a confirmation text:

postgres=# CREATE TABLE t1 (i INT);
CREATE TABLE
postgres=# \set QUIET ON
postgres=# CREATE TABLE t2 (i INT);
postgres=#

However, rather than using it interactively, it seems most users set \set QUIET ON at the beginning of their psqlrc and \set QUIET OFF at the end to suppress the output of the configuration commands running within the psqlrc itself.

`\set COMP_KEYWORD_CASE`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'comp_keyword_case'
ORDER BY count DESC
LIMIT 10;
               statement               | count 
---------------------------------------+-------
 \set COMP_KEYWORD_CASE upper          |   818
 \set COMP_KEYWORD_CASE lower          |    26
 \set COMP_KEYWORD_CASE preserve-upper |     5
 \set COMP_KEYWORD_CASE preserve-lower |     4
 \set COMP_KEYWORD_CASE upper\r        |     4
 \set COMP_KEYWORD_CASE 'upper'        |     2
 \set comp_keyword_case lower          |     1
(7 rows)

This variable determines whether to use upper or lower case when using tab completion for SQL keywords.

upper: Use upper case.
lower: Use lower case.
preserve-upper (Default): Use the case of the already entered text; otherwise, use upper case.
preserve-lower: Use the case of the already entered text; otherwise, use lower case.

The vast majority of users set this to upper to force uppercase auto-completion.

`\set HISTCONTROL`, `\set HISTFILE`, `\set HISTSIZE`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'histcontrol'
ORDER BY count DESC
LIMIT 10;
           statement           | count 
-------------------------------+-------
 \set HISTCONTROL ignoredups   |   748
 \set HISTCONTROL ignoreboth   |    60
 \set HISTCONTROL ignorespace  |    11
 \set HISTCONTROL ignoredups\r |     3
 \set HISTCONTROL none         |     1
(5 rows)

You can control the command history list using the HISTCONTROL variable.

ignorespace: Lines starting with a space are not added to the history list.
ignoredups: Lines matching the previous history entry are not added to the history list.
ignoreboth: Enables both ignorespace and ignoredups.
none (Default): All lines are added to the history list.

It seems many users configure \set HISTCONTROL ignoredups to prevent lines identical to the previous entry from being added to the history list.

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'histfile'
ORDER BY count DESC
LIMIT 10;
                                                        statement                                                        | count 
-------------------------------------------------------------------------------------------------------------------------+-------
 \set HISTFILE ~/.psql_history- :DBNAME                                                                                  |   514
 \set HISTFILE `[[ -z $PSQL_HISTFILE ]] && echo $HOME/.psql_history || echo $PSQL_HISTFILE`                              |    46
 \set HISTFILE ~/.psql_history- :HOST - :DBNAME                                                                          |    39
 \set HISTFILE ~/.psql/history- :DBNAME                                                                                  |    26
 \set HISTFILE ~/psql_history- :DBNAME                                                                                   |    18
 \set HISTFILE ~/.psql_history-:DBNAME                                                                                   |    14
 \set HISTFILE ~/.history/psql- :HOST - :DBNAME                                                                          |    11
 \set HISTFILE <%= ENV['OPENSHIFT_DATA_DIR'] %>/.psql_history- :DBNAME                                                   |    10
 \set HISTFILE ~/.psql_history                                                                                           |     9
 \set HISTFILE ~/.psql_history/ :DBNAME                                                                                  |     8
(10 rows)

You can configure the filename for saving history using the HISTFILE variable.

It seems many users include the database name or hostname in the filename.

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'histsize'
ORDER BY count DESC
LIMIT 10;
       statement       | count 
-----------------------+-------
 \set HISTSIZE 2000    |   109
 \set HISTSIZE 10000   |    31
 \set HISTSIZE 100000  |    30
 \set HISTSIZE 5000    |    23
 \set HISTSIZE 20000   |    13
 \set HISTSIZE 1000000 |    10
 \set HISTSIZE 1000    |     5
 \set HISTSIZE 12000   |     4
 \set HISTSIZE -1      |     4
 \set HISTSIZE 6000    |     4
(10 rows)

You can set the maximum number of commands to store in the command history (default is 500) using the HISTSIZE variable.

It seems many users configure this to increase the maximum number of commands stored in the history.

`\set VERBOSITY`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'verbosity'
ORDER BY count DESC
LIMIT 10;
         statement        | count 
--------------------------+-------
 \set VERBOSITY verbose   |   691
 \set VERBOSITY terse     |    15
 \set VERBOSITY default   |     6
 \set VERBOSITY verbose\r |     3
 \set VERBOSITY 'terse'   |     1
 \set VERBOSITY verbose;  |     1
(6 rows)

This controls the verbosity of error reports. The trend is overwhelmingly verbose (\set VERBOSITY verbose), ensuring error details are fully displayed.

`\set ON_ERROR_ROLLBACK`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'on_error_rollback'
ORDER BY count DESC
LIMIT 10;
                                                        statement                                                         | count 
--------------------------------------------------------------------------------------------------------------------------+-------
 \set ON_ERROR_ROLLBACK interactive                                                                                       |   227
 \set ON_ERROR_ROLLBACK on                                                                                                |    14
 \set ON_ERROR_ROLLBACK                                                                                                   |     4
 \set ON_ERROR_ROLLBACK 1                                                                                                 |     2
 \set ON_ERROR_ROLLBACK off                                                                                               |     2
 \set ON_ERROR_ROLLBACK 'interactive'                                                                                     |     2
 \set ON_ERROR_ROLLBACK 'on'\r                                                                                            |     1
 \set ON_ERROR_ROLLBACK 'on'                                                                                              |     1
 \set ON_ERROR_ROLLBACK off|on|interactive -- interactive lets you fix your fat fingers... Prefer to redo the whole thing |     1
(9 rows)

This controls behavior when an error occurs inside a transaction block.

on: If a statement errors, the error is ignored, and the transaction continues.
interactive: Errors are ignored only in interactive sessions.
off (Default): An error aborts the entire transaction.

Most users set this to interactive, which allows you to "fix your fat fingers" without losing the whole transaction while typing manually.

`\set ON_ERROR_STOP`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\set' AND
  lower(split_part(statement, ' ', 2)) = 'on_error_stop'
ORDER BY count DESC
LIMIT 10;
        statement        | count 
-------------------------+-------
 \set ON_ERROR_STOP on   |    69
 \set ON_ERROR_STOP      |     8
 \set ON_ERROR_STOP 1    |     5
 \set ON_ERROR_STOP true |     2
 \set ON_ERROR_STOP off  |     1
(5 rows)

By setting \set ON_ERROR_STOP on, command processing stops immediately after an error. In interactive mode, it returns to the prompt; in scripts, psql exits with error code 3.

`\pset`

\pset controls the formatting of table output.

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\pset'
ORDER BY count DESC
LIMIT 30;
 item1 |          item2           | count 
-------+--------------------------+-------
 \pset | null                     |  1201
 \pset | linestyle                |   374
 \pset | border                   |   313
 \pset | pager                    |   289
 \pset | format                   |   144
 \pset | unicode_header_linestyle |    64
 \pset | unicode_column_linestyle |    57
 \pset | unicode_border_linestyle |    57
 \pset | expanded                 |    22
 \pset | numericlocale            |     9
 \pset | columns                  |     8
 \pset | footer                   |     8
 \pset | fieldsep                 |     6
 \pset | tuples_only              |     5
 \pset | pager_min_lines          |     3
 \pset | title                    |     3
 \pset | recordsep                |     2
(17 rows)

`\pset null`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'null'
ORDER BY count DESC
LIMIT 10;
      statement      | count 
---------------------+-------
 \pset null '[NULL]' |   416
 \pset null '¤'      |   142
 \pset null ¤        |   127
 \pset null '(null)' |    98
 \pset null 'NULL'   |    93
 \pset null '[null]' |    51
 \pset null [null]   |    31
 \pset null NULL     |    28
 \pset null ∅        |    26
 \pset null '∅'      |    20
(10 rows)

Users configure this to display a specific string (like [NULL], (null), or ¤) instead of a blank space when a value is NULL, making it easier to spot.

`\pset linestyle`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'linestyle'
ORDER BY count DESC
LIMIT 10;
         statement         | count 
---------------------------+-------
 \pset linestyle unicode   |   320
 \pset linestyle 'unicode' |    44
 \pset linestyle ascii     |     5
 \pset linestyle u         |     2
 \pset linestyle unicode\r |     2
 \pset linestyle 'ascii'   |     1
(6 rows)

Many users set this to unicode (Default is ascii) to use smoother lines for tables.

Example (Unicode):

           statement           │ count 
───────────────────────────────┼───────
 \pset linestyle unicode       │   320
 \pset linestyle 'unicode'     │    44
 \pset linestyle ascii         │     5
 \pset linestyle u             │     2
 \pset linestyle unicode\r     │     2
 \pset linestyle 'ascii'       │     1
(6 rows)

`\pset border`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'border'
ORDER BY count DESC
LIMIT 10;
                     statement                      | count 
----------------------------------------------------+-------
 \pset border 2                                     |   250
 \pset border 1                                     |    48
 \pset border 0                                     |     7
 \pset border 3                                     |     3
 \pset border 2\r                                   |     2
 \pset border 1 -- Makes the borders a little nicer |     1
 \pset border 4                                     |     1
 \pset border 7                                     |     1
(8 rows)

This changes the border style of tables (Default is 1).

Example (border 0):

                    statement                       count 
-------------------------------------------------- -----
\pset border 2                                       250
\pset border 1                                        48
\pset border 0                                         7
\pset border 3                                         3
\pset border 2\r                                       2
\pset border 1 -- Makes the borders a little nicer     1
\pset border 4                                         1
\pset border 7                                         1
(8 rows)

Example (border 2):

+----------------------------------------------------+-------+
|                    statement                       | count |
+----------------------------------------------------+-------+
| \pset border 2                                     |   250 |
| \pset border 1                                     |    48 |
| \pset border 0                                     |     7 |
| \pset border 3                                     |     3 |
| \pset border 2\r                                   |     2 |
| \pset border 1 -- Makes the borders a little nicer |     1 |
| \pset border 4                                     |     1 |
| \pset border 7                                     |     1 |
+----------------------------------------------------+-------+
(8 rows)

`\pset pager`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'pager'
ORDER BY count DESC
LIMIT 10;
      statement       | count 
----------------------+-------
 \pset pager off      |   181
 \pset pager always   |    69
 \pset pager on       |    28
 \pset pager          |     4
 \pset pager on\r     |     2
 \pset pager auto     |     2
 \pset pager always\r |     1
 \pset pager 1        |     1
 \pset pager 0        |     1
(9 rows)

off: Disable the pager.
always: Always use the pager.
on (Default): Use pager only when needed.

`\pset format`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'format'
ORDER BY count DESC
LIMIT 10;
        statement        | count 
------------------------+-------
 \pset format wrapped   |   127
 \pset format aligned   |    12
 \pset format           |     2
 \pset format csv       |     2
 \pset format unaligned |     1
(5 rows)

Setting wrapped helps display wide data values by wrapping them across multiple lines to fit the target column width. (Default is aligned).

`\echo`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING lower(split_part(statement, ' ', 1)) = '\echo'
ORDER BY count DESC
LIMIT 30;
                              statement                               | count 
----------------------------------------------------------------------+-------
 \echo 'Administrative queries:\n'                                    |    35
 \echo '\nCurrent Host Server Date Time : '`date` '\n'                |    33
 \echo                                                                |    29
 \echo 'Development queries:\n'                                       |    28
 \echo '\t\t\t\\h\t\t-- Help with SQL commands'                       |    20
 \echo '\t\t\t:uselesscol\t-- Useless columns'                        |    20
 \echo '\t\t\t:activity\t-- Server activity'                          |    20
 \echo '\t\t\t:conninfo\t-- Server connections'                       |    20
 \echo '\t\t\t:uptime\t\t-- Server uptime'                            |    20
 \echo 'Type :extensions to see the available extensions. \n'          |    20
 \echo '\t\t\t:locks\t\t-- Lock info'                                 |    20
 \echo '\t\t\t:clear\t\t-- Clear screen'                              |    19
 \echo '\t\t\t:tablesize\t-- Tables Size'                             |    19
 \echo 'Type :version to see the PostgreSQL version. \n'              |    19
 \echo '\t\t\t:menu\t\t-- Help Menu'                                  |    19
 \echo 'Type \\q to exit. \n'                                         |    19
 \echo '\t\t\t\\?\t\t-- Help with psql commands\n'                    |    19
 \echo '\t\t\t:sp\t\t-- Current Search Path'                          |    19
 \echo '\t\t\t:dbsize\t\t-- Database Size'                            |    19
 \echo '\t\t\t:ll\t\t-- List\n'                                       |    19
 \echo '\t\t\t:settings\t-- Server Settings'                          |    19
 \echo '\t\t\t:waits\t\t-- Waiting queires'                           |    18
 \echo 'Welcome to PostgreSQL! \n'                                    |    15
 \echo '\t:activity\t-- Server activity'                              |    11
 \echo '\t:dbsize\t\t-- Database Size'                                |    11
 \echo '\t:tablesize\t-- Tables Size'                                 |    11
 \echo '\t:uptime\t\t-- Server uptime'                                |    11
 \echo '\t:conninfo\t-- Server connections'                           |    11
 \echo '\t:settings\t-- Server Settings'                              |    11
 \echo '\t\\?\t\t-- Help with psql commands\n'                        |    10
(30 rows)

\echo is used to print messages.
Looking at the data, users utilize this to display "Welcome" messages, server information (Host, Date), or cheat sheets for custom aliases upon psql startup.

`\x`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\x'
ORDER BY count DESC
LIMIT 30;
 item1 | item2  | count 
-------+--------+-------
 \x    | auto   |   987
 \x    |        |    11
 \x    | off    |    10
 \x    | on     |     9
 \x    | auto\r |     1
(5 rows)

\x auto is the most common setting (987 uses). This automatically switches to extended table formatting (where columns are listed vertically) when the output is too wide to fit the screen.

`\timing`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\timing'
ORDER BY count DESC
LIMIT 30;
  item1  | item2 | count 
---------+-------+-------
 \timing |       |   833
 \timing | on    |   159
 \timing | off   |     2
 \timing | on\r  |     1
(4 rows)

Using \timing or \timing on toggles the display of execution time for every SQL statement.

`\unset`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\unset'
ORDER BY count DESC
LIMIT 30;
 item1  |   item2    | count 
--------+------------+-------
 \unset | quiet      |   554
 \unset | timing     |     3
 \unset | quiet\r    |     3
 \unset | quietrc    |     2
 \unset | singlestep |     2
 \unset | quite      |     1
(6 rows)

As mentioned in the \set QUIET section, \unset is primarily used to \unset QUIET at the end of the file to restore message output after loading the configuration.

`\setenv`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\setenv'
ORDER BY count DESC
LIMIT 30;
  item1  |            item2             | count 
---------+----------------------------+-------
 \setenv | pager                      |   129
 \setenv | less                       |    89
 \setenv | editor                     |    41
 \setenv | psql_editor                |     9
 \setenv | psql_pager                 |     2
 \setenv | psql_editor_linenumber_arg |     2
 \setenv | echo                       |     1
(7 rows)

SELECT              
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING 
  lower(split_part(statement, ' ', 1)) = '\setenv' AND
  lower(split_part(statement, ' ', 2)) = 'pager'
ORDER BY count DESC
LIMIT 10;
                               statement                               | count 
-----------------------------------------------------------------------+-------
 \setenv PAGER 'less -SX'                                              |    28
 \setenv PAGER less                                                    |    26
 \setenv PAGER pspg                                                    |    14
 \setenv PAGER 'less -S'                                               |    11
 \setenv PAGER 'pspg --no-mouse -bX --no-commandbar --no-topbar'       |     5
 \setenv PAGER 'less'                                                  |     4
 \setenv PAGER 'less -XS'                                              |     4
 \setenv PAGER 'pspg -FX -s 17 --no-mouse --no-bars --only-for-tables' |     2
 \setenv PAGER 'less -FXE'                                             |     2
 \setenv PAGER '/usr/bin/less'                                         |     2
(10 rows)

It seems users use less or pspg as their pager by configuring the PAGER or PSQL_PAGER environment variables. Also, it seems they configure options for less by setting the LESS environment variable.

📝 Note:
pspg is a pager specifically designed for PostgreSQL tables.
https://github.com/okbob/pspg

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING 
  lower(split_part(statement, ' ', 1)) = '\setenv' AND
  lower(split_part(statement, ' ', 2)) = 'editor'
ORDER BY count DESC
LIMIT 10;
                 statement                | count 
------------------------------------------+-------
 \setenv EDITOR 'nvim'                    |     5
 \setenv EDITOR 'vim'                     |     5
 \setenv EDITOR '/usr/bin/vim'            |     5
 \setenv EDITOR '/usr/local/bin/vim'      |     4
 \setenv EDITOR vim                       |     3
 \setenv EDITOR '/usr/bin/nvim'           |     2
 \setenv EDITOR emacs                     |     2
 \setenv EDITOR nvim                      |     2
 \setenv EDITOR '~/.nix-profile/bin/nvim' |     2
 \setenv EDITOR /usr/bin/vim              |     1
(10 rows)

It seems users use Neovim, Vim, or Emacs as their editor by configuring the EDITOR or PSQL_EDITOR environment variables.

`\r`

This stands for \reset, which clears the query buffer. In my data, it looks mostly like a parsing artifact where \r (carriage return) characters were caught, but some users might be ensuring a clean slate.

`SET` (SQL Command)

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = 'set'
ORDER BY count DESC
LIMIT 30;
 item1 |                          item2                          | count 
-------+--------------------------------------------------------+-------
 set   | search_path                                            |    47
 set   | intervalstyle                                          |    41
 set   | timezone                                               |     9
 set   | application_name                                       |     7
 set   | client_min_messages                                    |     6
 set   | bytea_output                                           |     5
 set   | client_encoding                                        |     4
 set   | search_path=public;                                    |     1
 set   | session                                                |     1
 set   | search_path=site_config_service,project_service,public |     1
 set   | work_mem                                               |     1
 set   | timezone='us/eastern';                                 |     1
 set   | default_transaction_read_only                          |     1
 set   | search_path=dwh,mart,stage                             |     1
 set   | tcp_keepalives_idle                                    |     1
 set   | maintenance_work_mem='1gb';                            |     1
 set   | search_path=public,postgis                             |     1
 set   | work_mem='512mb';                                      |     1
 set   | enable_bitmapscan                                      |     1
 set   | statement_timeout                                      |     1
(20 rows)

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING 
  lower(split_part(statement, ' ', 1)) = 'set' AND
  lower(split_part(statement, ' ', 2)) = 'search_path'
ORDER BY count DESC
LIMIT 10;
                        statement                        | count 
--------------------------------------------------------+-------
 set search_path to pdfbox,mycore,public;               |     3
 set search_path to setcore,public;                     |     3
 set search_path to curl7,mycore,public;                |     3
 set search_path to jsonorg,setcore,public;             |     3
 set search_path to mycore,setcore,public;              |     3
 set search_path to public,setcore,libxml2;             |     3
 SET search_path TO setcore,public;                     |     3
 SET search_path TO expat2,mycore,setcore,public;       |     3
 set search_path to gnuzip,setcore,public;              |     3
 SET search_path TO arxiv,pdfbox,mycore,setcore,public; |     2
(10 rows)

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement            
HAVING 
  lower(split_part(statement, ' ', 1)) = 'set' AND
  lower(split_part(statement, ' ', 2)) = 'intervalstyle'
ORDER BY count DESC
LIMIT 10;
                 statement                | count 
------------------------------------------+-------
 set intervalstyle to 'postgres_verbose'; |    39
 SET intervalstyle to 'postgres_verbose'; |     1
 set intervalstyle to 'postgres_verbose'  |     1
(3 rows)

These are standard SQL commands executed at startup.

search_path: Users set custom search paths for their schemas.
intervalstyle: Users often set this to 'postgres_verbose' to control how interval data types are formatted.

`\encoding`

SELECT
  lower(split_part(statement, ' ', 1)) AS item1,
  lower(split_part(statement, ' ', 2)) AS item2,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2            
HAVING lower(split_part(statement, ' ', 1)) = '\encoding'
ORDER BY count DESC
LIMIT 30;
   item1   |   item2   | count 
-----------+-----------+-------
 \encoding | unicode   |   105
 \encoding | utf8      |     6
 \encoding | latin1    |     5
 \encoding | utf-8     |     2
 \encoding | sql_ascii |     2
 \encoding | 'utf8'    |     1
 \encoding | latin1\r  |     1
 \encoding |           |     1
(8 rows)

Most users explicitly set the client encoding to unicode.

Conclusion

I had never customized my psqlrc before, so this investigation was a great opportunity to learn about the available settings.

Personally, since I often connect to multiple different PostgreSQL servers, I plan to customize my PROMPT settings to clearly distinguish between environments (and avoid accidents!).

I hope this article helps you create a more comfortable psql life.

Can we use Rust to Develop Extensions for PostgreSQL?

Shinya Kato — Mon, 24 Nov 2025 01:13:02 +0000

Although it has been a while since the event, I am sharing the full transcript of my talk from POSETTE 2025. Please note that some information may be outdated as time has passed.

Additionally, the Call for Proposals (CFP) for POSETTE 2026 is now open. Let's make the next POSETTE a great success together!

Slides:
YouTube:

Can We Use Rust to Develop Extensions for PostgreSQL? #1

Hello everyone.
Thank you for joining my session today.
My name is Shinya Kato and I'm a database engineer at NTT DATA Japan Corporation.

Today, I will talk about Postgres extensions.
The title of my talk is "Can We Use Rust to Develop Extensions for PostgreSQL".
If you're someone who's passionate about Postgres extension development or curious about how Rust fits into the picture, this presentation is for you.
Let's dive into it.

About me #2

Here we go.

Let me introduce myself a little bit.
As I said, I'm a database engineer at NTT Data Japan Corporation which is the largest IT company in Japan.
I've been here with our company for about 5 years.

I specialize in Postgres, offering technical support, conducting R&D, contributing to the Postgres project, and maintaining two key extensions: pg_bulkload and pg_rman.

Can we use Rust to develop extensions for PostgreSQL? #3

To begin, can we use Rust to develop extensions for PostgreSQL?

Yes, we can ✅ #4

Yes, we can.
Instead of legacy C language, it's possible to develop Postgres extensions using modern Rust and frameworks.

But limited resources make it challenging ❎ #5

However, getting started with Postgres extension development in Rust can be a little bit challenging due to limited information.
This talk aims to provide assistance for developing Postgres extensions using Rust.
Furthermore, drawing from my own experience implementing Postgres extensions in Rust, I will share practical insights, covering both the pros and cons.

Why Rust? #6

Why Rust?
Currently, system security is compromised by numerous vulnerabilities caused by memory corruption issues.
Google's security team, Project Zero, reports that about 68% of zero-day vulnerabilities are caused by memory-related problems.
These critical memory safety risks are commonly found in C and C++ applications.

On the other hand, Rust's strong memory safety features make these issues much less likely to occur.
Furthermore, Rust's ownership system guarantees memory safety without sacrificing performance.
This is why a partial migration from C and C++ to Rust is underway.

[1] https://googleprojectzero.blogspot.com/p/0day.html

Why Rust? #7

For example, we can point to projects like 'Rust for Linux', which enables implementing Linux kernel modules and device drivers in Rust.
Another significant development is Chrome's ongoing migration of its font handling library from C's FreeType to Rust's Skrifa to ensure safer font processing.
Additionally, in the Postgres ecosystem, Neon is notable for implementing Postgres' storage engine in Rust.

[2] https://rust-for-linux.com
[3] https://developer.chrome.com/blog/memory-safety-fonts
[4] https://neon.tech/blog/how-we-scale-an-open-source-multi-tenant-storage-engine-for-postgres-written-rust

What is pgrx? #8

So, how can we implement Postgres extensions in Rust?
You can easily develop them using pgrx, which is a framework specifically designed for developing Postgres extensions in Rust.
Let me explain the key features of pgrx.
First, pgrx provides an integrated development environment.
Using this environment, you can easily implement Postgres extension features.
Second, it safely exposes the Postgres C API to Rust.
This allows you to securely call Postgres functions from your Rust implementation.
Third, it automatically handles the mapping between Rust types and Postgres types.
For example, if a Rust function returns an i32 type, pgrx converts it to the integer type;
if it returns an i64 type, it converts it to bigint;
and if it returns a string type, it converts it to TEXT.

DEMO #10

pgrx: Pros & Cons #11

Alright, that's it for the demo.
Based on the explanation so far, I hope you now have a general idea of how to use pgrx.
From this point, I'd like to discuss the pros and cons of pgrx, based on my own experience developing extensions with it.

Setting up development environment ✅ #12

The first point I'd like to introduce is about setting up the development environment.
As you saw in the demo earlier, you can set up the development environment very easily.
With the command cargo pgrx init, you can set up development environments for all Postgres versions supported by pgrx, from 13 to 17.
You can also create an environment for only a specific version, for example, using a flag like --pg17.
I believe this feature is very user-friendly, especially for beginners in Postgres extension development.

Setting up development environment ❎ #13

However, you can only specify major Postgres versions; minor versions cannot be specified.
While you do not often need to worry about minor versions, the ability to specify them would be convenient, for instance, when you want to check the impact of a bug fixed in a specific minor release.
Furthermore, you cannot specify the Postgres master branch, which is currently under development.
Developing a Postgres extension isn't a one-time task; it needs to keep pace with Postgres version upgrades.
When a new major version of Postgres is released, pgrx will likely add support for it eventually.
However, waiting for pgrx to support new major versions after their official release slows down the delivery of updated extensions.
If it were possible to develop extensions against the master branch, I believe pgrx would become an even more practical framework for extension development.

Requirements for C-Based Extensions #14

The second point I'd like to introduce is automatic SQL generation.
With pgrx, SQL statements like CREATE FUNCTION can be generated automatically.
But first, let's review how user-defined functions and other components are typically implemented using the traditional C language approach.

When implementing a user-defined function called func and providing it as part of an extension using C, you need both a C source file and an SQL file.
On the left, I'm showing the C source file, and on the right is the SQL file.
After implementing the function logic in C, you then define the SQL function using a CREATE FUNCTION statement that calls the C function.
Furthermore, if your extension provides more complex features like custom types or index support, you also need corresponding SQL definitions such as CREATE OPERATOR, CREATE OPERATOR CLASS, or CREATE OPERATOR FAMILY.
As you can see, this means you need to implement and maintain both the C source file and all these SQL definition files.

Automatic SQL generation ✅ #15

Using pgrx, the SQL file is automatically generated based on the Rust function's attributes, argument types, and return value types.

Automatic SQL generation ✅ #16

In the pg_extern attribute, you specify parameters equivalent to those used in CREATE FUNCTION, such as IMMUTABLE, STRICT, STABLE, or VOLATILE.

Automatic SQL generation ✅ #17

Additionally, the Rust function's argument and return value types are mapped directly to the arguments and return type in the generated CREATE FUNCTION statement.
In this way, automatically generating the SQL reduces the cost of implementing and maintaining SQL files.

Automatic SQL generation ✅ #18

Similar to functions, pgrx can also generate the necessary CREATE FUNCTION and CREATE OPERATOR statements for operators.
Just like before, in the pg_operator attribute, you specify parameters for CREATE FUNCTION, such as STABLE or STRICT.

Automatic SQL generation ✅ #19

In the opname attribute, you specify the desired operator name.
In the restrict and join attributes, you specify operator-specific parameters.

Automatic SQL generation ❎ #20

Automatic SQL generation is also available for operator classes, similar to functions and operators.
However, this capability is currently restricted to B-tree and Hash indexes only.
Support for other index types like GIN, GiST, and SP-GiST is currently lacking, which is an important limitation to consider.
For instance, an extension I developed required GIN indexes, so this auto-generation feature wasn't applicable, and I needed to write the necessary SQL definitions directly.

Memory management in C/PostgreSQL #21

The third point I'd like to introduce is memory management.

Memory management in the C language requires explicit memory allocation and deallocation using malloc and free.
Postgres' implementation, uses a region-based system for memory management, utilizing MemoryContexts.
This involves creating these MemoryContexts and allocating memory associated with a specific context.
To free memory, you simply delete the MemoryContext, which releases all memory associated with it at once.
In this way, Postgres provides an implementation for efficient and relatively easy memory management.
However, even with this system, developers still need to be careful about issues like memory leaks, double frees, and dangling pointers.

Memory management in Rust #22

Next, I will very briefly explain Rust's memory management.
Rust manages memory using a concept called the ownership system, which has three main rules:
Each value in Rust has an owner.
There can only be one owner at a time.
When the owner goes out of scope, the value will be dropped.

Memory management in Rust #23

As shown in this diagram, when s1 is declared, s1 becomes the owner of the hello.

Memory management in Rust #24

Similarly, s2 becomes the owner of world.

Memory management in Rust #25

Next, s2 goes out of scope and then is dropped, freeing its memory.

Memory management in Rust #26

Likewise, s1 goes out of scope and is dropped, freeing its memory.

Memory management in Rust ✅ #27

pgrx's memory management system is designed to bridge the gap between Postgres' MemoryContexts and Rust's ownership.
Specifically, deallocation of pointers received from the C API is handled by Postgres.
Conversely, objects created in Rust are deallocated by Rust itself, based on its ownership rules.
To help safely manage pointers allocated by C from within Rust, pgrx provides the PgBox type.

Memory management in Rust ❎ #28

It's already difficult to understand C and Postgres memory management and Rust's memory management individually.
Then, you also need to learn pgrx memory management.
This makes it much harder.
Let me give some difficult examples.
One is handling memory created in C from your Rust code. Another is passing Rust memory to C and telling Rust not to free it, which goes against Rust's ownership system.

Toolchain ✅ #29

The fourth point I'd like to introduce is toolchain.
When developing with pgrx, you directly benefit from the rich Rust ecosystem, particularly its excellent toolchain.
This familiarity offers great convenience.
For instance, you have cargo clippy for Linting to help write more robust and idiomatic Rust.
Code style consistency is easily maintained using cargo fmt for automatic Formatting.
And crucially for extensions, cargo pgrx test provides a streamlined way to handle integration testing directly against Postgres.
This ability to use standard Rust tooling really makes the development workflow much smoother.

Toolchain ❎ #30

Now, let's look at a point marked here as a challenge, which relates to toolchain integration.
While the Rust side is well-supported, there's currently weaker integration with some traditional Postgres tools.
Specifically, as noted here, there isn't direct integration with pg_regress, the standard framework for running SQL-level regression tests in the Postgres ecosystem.
While pgrx provides its own testing mechanism, it doesn't replace the pg_regress workflow.
This is a recognized limitation, as shown by the GitHub issues listed on the slide.
For now, this means that setting up pg_regress-style tests might require separate configuration.

Note:

[5] https://github.com/pgcentralfoundation/pgrx/issues/284
[6] https://github.com/pgcentralfoundation/pgrx/issues/1511

Documentation ✅ #31

The fifth point I'd like to introduce is documentation.

While learning pgrx involves navigating different memory models as we discussed, there are several excellent resources available to help you.
Let me highlight the key ones shown on this slide.
First, for the specific details of the pgrx crate's functions, types, and macros, your primary reference is Docs.rs, which is the API documentation.
Next, The PGRX Book serves as the official guide and documentation.
This is the best place to understand core concepts, learn how to set up your environment, and follow tutorials.
Finally, if you want to see practical examples and common patterns, the pgrx-examples is invaluable.
It contains various code examples that you can learn from and adapt.
I highly recommend utilizing these resources as you explore pgrx development.

[7] https://docs.rs/pgrx/latest/pgrx/
[8] https://github.com/pgcentralfoundation/pgrx/blob/develop/docs/src/SUMMARY.md
[9] https://github.com/pgcentralfoundation/pgrx/tree/develop/pgrx-examples

Documentation ❎ #32

While resources are growing, it's true that there is currently less information available for pgrx development compared to the vast resources accumulated over many years for traditional C extensions.
So, because there isn't enough documentation or examples yet, development can still be quite hard sometimes.
This is especially true for complex problems or unusual situations.

I hope this presentation was helpful for you.

PostgreSQL & Rust: Core vs. Extensions #33

Finally, let's discuss the future of Postgres and Rust.
There have been multiple proposals in the past suggesting the use of Rust for implementing Postgres' core.
However, due to various reasons, the adoption of Rust for the core has been deferred, so the chance of this happening is quite low.
For example, there's the huge existing C codebase, Postgres' unique memory management system, and its specific error handling mechanisms (like PG_TRY()).
Additionally, Postgres needs to support a wide range of platforms, which presents portability challenges.
But, as mentioned in this talk, Rust-based Postgres extensions are expected to grow substantially in the future.

[10] https://www.postgresql.org/message-id/flat/CAASwCXdQUiuUnhycdRvrUmHuzk5PsaGxr54U4t34teQjcjb%3DAQ%40mail.gmail.com
[11] https://www.postgresql.org/message-id/flat/CANvSX4wsVhDrqy4Ku81wHs--aViYthJ_G5G-2VeZ%3DJboEE9TNQ%40mail.gmail.com

Summary #34

Okay, let's quickly summarize the main points of this presentation.
First, I explained how you can implement Postgres extensions using Rust with the help of pgrx.
We also took some time to discuss the pros and cons of using the pgrx framework.
pgrx offers a powerful way to build extensions.
So, let's try it out!
Thank you.

pgcentralfoundation / pgrx

Build Postgres Extensions with Rust!

`pgrx`

Build Postgres Extensions with Rust!

pgrx is a framework for developing PostgreSQL extensions in Rust and strives to be as idiomatic and safe as possible.

pgrx supports Postgres 13 through Postgres 18.

Want to chat with us or get a question answered?

Please join our Discord Server.
We are also in need of financial sponsorship.

Key Features

A fully managed development environment with cargo-pgrx
- cargo pgrx new: Create new extensions quickly
- cargo pgrx init: Install new (or register existing) PostgreSQL installs
- cargo pgrx run: Run your extension and interactively test it in psql (or pgcli)
- cargo pgrx test: Unit-test your extension across multiple PostgreSQL versions
- cargo pgrx package: Create installation packages for your extension
- More in the README.md!
Target Multiple Postgres Versions
- Support from Postgres 13 to Postgres 17 from the same codebase
- Use Rust feature gating to use version-specific…

View on GitHub

DEV Community: Shinya Kato

Reducing row count estimation errors in PostgreSQL

Introduction

Prerequisites

Approach 1: Tuning autovacuum auto-ANALYZE frequency per table

Approach 2: Increasing the statistics sampling target per column

Approach 3: Using extended statistics

Approach 4: Using pg_hint_plan as a last resort

Conclusion

References

4 causes of table bloat in PostgreSQL and how to address them

What Is Table Bloat?

Test Environment

Long-Running Transactions

Setup

Identifying the cause

Resolution

Uncommitted Prepared Transactions

Setup

Identifying the cause

Resolution

Queries on Standby with hot_standby_feedback Enabled

Setup

Identifying the cause

Resolution

Logical Replication Lag

Setup

Identifying the cause

Resolution

Ongoing Community Discussions

Summary

New PostgreSQL features I developed in 2025

Introduction

Added a document that recommends default psql settings when restoring pg_dump backups

Added backup_type column to pg_stat_progress_basebackup view

COPY FROM now supports files with multi-line headers

Split the autovacuum log settings for VACUUM and ANALYZE

Show compressed full-page image size in pg_stat_wal

Added mode and started_by columns to pg_stat_progress_vacuum view

Conclusion

Analyzing psqlrc settings on GitHub: How PostgreSQL engineers configure PostgreSQL

Introduction

What is psqlrc?

Investigation

Methodology

Extracting URLs via GitHub API

Analyzing the Data in PostgreSQL

\set

\set PROMPT1, \set PROMPT2

\set QUIET

\set COMP_KEYWORD_CASE

\set HISTCONTROL, \set HISTFILE, \set HISTSIZE

\set VERBOSITY

\set ON_ERROR_ROLLBACK

\set ON_ERROR_STOP

\pset

\pset null

\pset linestyle

\pset border

\pset pager

\pset format

\echo

\x

\timing

\unset

\setenv

\r

SET (SQL Command)

\encoding

Conclusion

Can we use Rust to Develop Extensions for PostgreSQL?

Can We Use Rust to Develop Extensions for PostgreSQL? #1

About me #2

Can we use Rust to develop extensions for PostgreSQL? #3

Yes, we can ✅ #4

But limited resources make it challenging ❎ #5

Why Rust? #6

Why Rust? #7

What is pgrx? #8

Popular PostgreSQL extensions written in Rust #9

Queries on Standby with `hot_standby_feedback` Enabled

`\set`

`\set PROMPT1`, `\set PROMPT2`

`\set QUIET`

`\set COMP_KEYWORD_CASE`

`\set HISTCONTROL`, `\set HISTFILE`, `\set HISTSIZE`

`\set VERBOSITY`

`\set ON_ERROR_ROLLBACK`

`\set ON_ERROR_STOP`

`\pset`

`\pset null`

`\pset linestyle`

`\pset border`

`\pset pager`

`\pset format`

`\echo`

`\x`

`\timing`

`\unset`

`\setenv`

`\r`

`SET` (SQL Command)

`\encoding`

`pgrx`