Tag Archives: PostgreSQL transaction management

Are Uncommitted Transactions Also Written to WAL?

PostgreSQL, , , , , , , , , , , , , , , , , , , ,

Are Uncommitted Transactions Also Written to WAL?

Table of Contents

1. The Big Question

When you run BEGIN; INSERT INTO ... ; but have not yet committed, is that data already written to the WAL (Write-Ahead Log)?

Short answer: YES. Every data modification is written to WAL the moment the statement executes, regardless of whether the transaction is later committed or rolled back. The WAL records the action, not the outcome. The commit or rollback is recorded as a separate WAL record afterward.

This post proves it with a hands-on demo — we will insert data, leave it uncommitted, and physically inspect the WAL file on disk to find our data sitting there.

2. What is WAL and Why Does It Exist?

The Write-Ahead Log (WAL) is PostgreSQL’s crash-recovery mechanism. The core rule is simple:

  • Before any change is written to the actual data files (heap), the change is first appended to the WAL.
  • If the server crashes, PostgreSQL replays the WAL on startup to restore the database to a consistent state.
  • Committed transactions are replayed. Rolled-back transactions are undone during recovery using the ABORT records in the WAL.

This means the WAL must record everything — including uncommitted work — so that recovery knows what to undo if the server crashes mid-transaction.

3. Setup — Create Demo Table

postgres=# DROP TABLE IF EXISTS wal_demo;
DROP TABLE
postgres=#

postgres=# CREATE TABLE wal_demo (
    id      SERIAL PRIMARY KEY,
    fname   TEXT
);
CREATE TABLE
postgres=#

4. Check synchronous_commit Setting

This setting controls when WAL is flushed to disk. The default on means every COMMIT waits for the WAL to be physically written to disk.

postgres=# SHOW synchronous_commit;
 synchronous_commit
--------------------
 on   <-------------
(1 row)

postgres=#

Important: Even with synchronous_commit = on, individual INSERT statements within a transaction write to the WAL buffer immediately. The COMMIT is what triggers the flush-to-disk guarantee. The data still hits the WAL file on disk very quickly either way.

5. Record WAL Position Before Any Transaction

We capture the current WAL position (LSN = Log Sequence Number) so we can compare it after our insert.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02E8C0  | 00000001000000050000005F
(1 row)

postgres=#

Our starting point is LSN 5/5F02E8C0 in WAL file 00000001000000050000005F

6. BEGIN + INSERT (Uncommitted) — Check WAL Position

Now we start a transaction and insert a row, but we do NOT commit.

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# SELECT txid_current();
 txid_current
--------------
         2384 <-----------
(1 row)

postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY');
INSERT 0 1
postgres=*#

-- The row is visible inside this transaction
postgres=*# SELECT * FROM wal_demo;
 id |                   fname
----+--------------------------------------------
  1 | SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
(1 row)

postgres=*#

Now check the WAL position without committing:

postgres=*# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EBE0  | 00000001000000050000005F
(1 row)

postgres=*#


-- Also check the flush position
postgres=*# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EBE0
(1 row)

postgres=*#

The WAL LSN moved! It went from 5/5F02E8C0 to 5/5F02EBE0. The INSERT was written to WAL even though we have not committed. The flush LSN matches, meaning the data is already on disk.

7. Grep the WAL File — Is the Uncommitted Data There?

Let’s physically inspect the WAL file on disk using the strings command.

[postgres@pgdb01 pg_wal]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 pg_wal]$ 

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

There it is! The uncommitted row’s data is physically present in the WAL file on disk. Anyone with read access to the WAL directory can see it. This is why WAL files should be protected with proper file permissions and encryption.

8. ROLLBACK — Table is Empty, But WAL Remembers

postgres=*# ROLLBACK;
ROLLBACK
postgres=#


-- The table is empty — the row is gone from the user's perspective
postgres=# SELECT * FROM wal_demo;
 id | fname
----+-------
(0 rows)

postgres=#
postgres=# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EE18
(1 row)

postgres=#

Our ending point is LSN 5/5F02EE18 in WAL file 00000001000000050000005F

But is the data still in the WAL file?

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

The data is STILL in the WAL file even after ROLLBACK. The ROLLBACK did not erase the WAL record. Instead, it wrote a new ABORT record to the WAL. During crash recovery, PostgreSQL replays the INSERT and then sees the ABORT, so it undoes the change. The WAL is an append-only log — records are never deleted or modified in place.

9. Inspect WAL with pg_waldump — The Full Story

pg_waldump decodes binary WAL files into human-readable records. Let’s inspect what PostgreSQL wrote for our uncommitted transaction.

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02E8C0 --end 5/5F02EE18 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):    102/   102, tx:       2384, lsn: 5/5F02E9D0, prev 5/5F02E998, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:       2384, lsn: 5/5F02EA98, prev 5/5F02EA38, desc: INSERT_LEAF off: 1, blkref #0: rel 1663/5/66000 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:       2384, lsn: 5/5F02EBE0, prev 5/5F02EBA8, desc: ABORT 2026-09-25 19:23:15.559348 +08

[postgres@pgdb01 ~]$

Let’s break down every record in order:

#Record TypeTX IDWhat It Means
1Heap INSERT+INIT2384The actual row data was written to table block 0. The INIT flag indicates that this insert initialized a brand-new page.
2Btree INSERT_LEAF2384The primary key index (id SERIAL PRIMARY KEY) was updated with a new leaf entry in index block 1 under the same transaction ID.
3ABORT2384The ROLLBACK command logged an ABORT record along with the exact timestamp. This tells crash recovery to ignore/undo records #1 and #2.

Key Takeaway: Both the heap insertion and the primary key index update were written to the WAL before the transaction ended. When we executed ROLLBACK, PostgreSQL appended an ABORT record. The WAL is an append-only log: rolled-back records are never wiped or overwritten.

Why is this necessary? If the database crashes mid-transaction, PostgreSQL recovery reads the WAL sequentially. It must replay the uncommitted changes and then read the ABORT record to cleanly roll them back. Without logging uncommitted work, crash recovery would have no way to restore consistency.

10. Now COMMIT — Compare the WAL Records

Let’s do the same experiment, but this time we COMMIT.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EE18  | 00000001000000050000005F
(1 row)

postgres=#

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('RAJASEKHAR AMUDALA - COMMITTED COPY');
INSERT 0 1
postgres=*#

postgres=*# COMMIT;
COMMIT
postgres=#


-- Record the new WAL position
postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02F080  | 00000001000000050000005F
(1 row)

postgres=#


-- Verify the row is in the table
postgres=# SELECT * FROM wal_demo;
 id |                fname
----+-------------------------------------
  2 | RAJASEKHAR AMUDALA - COMMITTED COPY
(1 row)

postgres=#


-- Check the transaction visibility metadata
postgres=# SELECT
    fname,
    xmin                                      AS inserting_xid,
    xmin::text::bigint < txid_current()       AS is_in_past
FROM wal_demo;
                fname                | inserting_xid | is_in_past
-------------------------------------+---------------+------------
 RAJASEKHAR AMUDALA - COMMITTED COPY |          2385 | t
(1 row)

postgres=#

Grep the WAL file for the committed data:

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "RAJASEKHAR"
IRAJASEKHAR AMUDALA - COMMITTED COPYP
[postgres@pgdb01 ~]$

Inspect with pg_waldump:

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02EE18 --end 5/5F02F080 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):     54/   222, tx:       2385, lsn: 5/5F02EE80, prev 5/5F02EE18, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0 FPW
rmgr: Btree       len (rec/tot):     53/   133, tx:       2385, lsn: 5/5F02EF60, prev 5/5F02EE80, desc: INSERT_LEAF off: 2, blkref #0: rel 1663/5/66000 blk 1 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:       2385, lsn: 5/5F02F020, prev 5/5F02EFE8, desc: COMMIT 2026-09-25 19:50:32.487755 +08

[postgres@pgdb01 ~]$

Detailed breakdown of the committed records:

#Record TypeTX IDDetails & Explanation
1Heap INSERT (FPW)2385The tuple was inserted at offset 2 of table block 0. Because a checkpoint finished right before this statement, the first touch to this page triggered a Full Page Write (FPW), storing the entire backup image into WAL (total length: 222 bytes).
2Btree INSERT_LEAF (FPW)2385The index entry for the primary key was written to index block 1. Just like the heap page, modifying this index block also produced an FPW.
3COMMIT2385The transaction committed successfully. Instead of ABORT, WAL logged a COMMIT record containing the exact timestamp (2026-09-25 19:50:32.487755 +08).

The Proof:

  • Uncommitted / Aborted: Heap INSERT → Btree INSERT_LEAF → ABORT
  • Committed: Heap INSERT → Btree INSERT_LEAF → COMMIT

The heap modification and index updates were written to the WAL file in both scenarios before the transaction ended. The only structural difference is the final record: ABORT vs COMMIT.

11. Side-by-Side Comparison

AspectUncommitted (ROLLBACK)Committed (COMMIT)
INSERT written to WAL?✅ Yes — immediately✅ Yes — immediately
Data visible in WAL file?✅ Yes — strings finds it✅ Yes — strings finds it
WAL LSN advances?✅ Yes — before commit/rollback✅ Yes — before commit
Transaction end recordABORTCOMMIT
Row visible in table?❌ No — rolled back✅ Yes — committed
Crash recovery behaviorReplay INSERT, then undo via ABORTReplay INSERT, keep via COMMIT
WAL record structureINSERT → ABORTINSERT → COMMIT

Key takeaway: The WAL does not wait for COMMIT to record your data. It writes every modification as it happens. The only difference between a committed and uncommitted transaction in the WAL is the final record: COMMIT vs ABORT. This is by design — if the server crashes mid-transaction, PostgreSQL needs the WAL to know what to undo.

Security implication: Sensitive data (passwords, credit card numbers, PII) written in a transaction that is later rolled back is still present in the WAL files on disk. WAL files should be protected with strict file permissions (0700).

12. Quick Summary Cheatsheet

ConceptDetail
Are uncommitted writes in WAL?Yes. Every INSERT/UPDATE/DELETE is written to WAL immediately, before commit.
Why?Crash recovery needs to know what to undo. Without the INSERT record, the ABORT record is meaningless.
What differs between COMMIT and ROLLBACK?Only the final WAL record: COMMIT vs ABORT. The data records are identical.
Is the WAL append-only?Yes. Records are never modified or deleted in place. Old WAL files are eventually recycled or archived.
Check current WAL positionSELECT pg_current_wal_lsn();
Check WAL flush positionSELECT pg_current_wal_flush_lsn();
Find WAL file name from LSNSELECT pg_walfile_name(pg_current_wal_lsn());
Inspect WAL contentspg_waldump $PGDATA/pg_wal/<file>
Search WAL for datastrings $PGDATA/pg_wal/<file> | grep "text"
Security concernRolled-back sensitive data persists in WAL. Protect WAL files and consider encryption.
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

VACUUM in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , ,

VACUUM in PostgreSQL

Table of Contents

1. Why Do We Need VACUUM? (The Problem)

Because of  MVCC, PostgreSQL does not directly overwrite a row when you run an UPDATE, and it does not immediately remove the old row data when you run a DELETE.

  • An UPDATE creates a brand-new row and marks the old one as “dead”.
  • A DELETE simply marks the existing row as “dead”.

Over time, these “dead rows” (also called dead tuples) accumulate. This is called table bloat. It wastes disk space and slows down your queries.

VACUUM is PostgreSQL’s garbage collector. It cleans up dead rows so your database stays fast and lean.

2. Setting Up a Test Table

Let’s create a playground table and insert 1,00,00,000 rows to see VACUUM in action.

-- Create a sample items table
postgres=# DROP TABLE IF EXISTS items;
DROP TABLE
postgres=# CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price INT
);
CREATE TABLE
postgres=#

-- Disable autovacuum for one test table, just for DEMO

postgres=# ALTER TABLE items SET (autovacuum_enabled = false);
ALTER TABLE
postgres=#

postgres=# SELECT relname, reloptions FROM pg_class WHERE relname = 'items';
 relname |         reloptions
---------+----------------------------
 items   | {autovacuum_enabled=false}
(1 row)

postgres=#

-- Insert 1,00,00,000 rows

postgres=# INSERT INTO items (name, price)
SELECT
    'Item ' || generate_series,
    (random() * 100)::INT
FROM generate_series(1, 10000000);
INSERT 0 10000000
postgres=#

-- Table physical path
postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65690
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 144K Sep 22 19:09 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 498M Sep 22 19:13 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

3. Creating Table Bloat (Generating Dead Rows)

Now let’s update all 1,00,00,000 rows. This will generate 1,00,00,000 dead rows alongside the 1,00,00,000 new rows.

-- Update every row
postgres=# UPDATE items SET price = price + 10;
UPDATE 10000000
postgres=#

4. Inspecting Dead Rows & Table Size

Let’s check how many dead rows exist and how large the table has become.

-- Check dead row count

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000147 |   10000000
(1 row)

postgres=# 

-- Check physical disk size of the table

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 272K Sep 22 19:16 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 996M Sep 22 19:16 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

5. Standard VACUUM (Reclaiming Space for Reuse)

A standard VACUUM marks the dead rows as free space so future INSERT or UPDATE operations can reuse it, but it won’t release free space to the OS

-- Run standard vacuum
postgres=# VACUUM VERBOSE items;
INFO:  vacuuming "postgres.public.items"
INFO:  finished vacuuming "postgres.public.items": index scans: 1
pages: 0 removed, 127389 remain, 127389 scanned (100.00% of total)
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2176, which is 4 XIDs ahead of previous value
frozen: 48685 pages from table (38.22% of total) had 7643430 tuples frozen
index scan needed: 63695 pages from table (50.00% of total) had 10000000 dead item identifiers removed
index "items_pkey": pages: 54839 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 64.788 MB/s, avg write rate: 69.883 MB/s
buffer usage: 146023 hits, 227350 misses, 245229 dirtied
WAL usage: 455518 records, 245755 full page images, 1191493291 bytes
system usage: CPU: user: 10.29 s, system: 2.81 s, elapsed: 27.41 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65690"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65690": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2178, which is 6 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 1.178 MB/s, avg write rate: 1.178 MB/s
buffer usage: 9 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 7245 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
postgres=#


-- Check dead rows again

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000000 |          0  <---- all dead rows deleted.
(1 row)

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB      <--------- free space won't release to the OS, We can only reuse the unused space.
(1 row)

postgres=#

Important Note on Standard VACUUM:

  • n_dead_tup dropped to 0 (the garbage was cleaned).
  • However, if you check pg_total_relation_size('items'), it will still show 1424 MB.
  • Standard VACUUM does not shrink the file size back to the operating system. Instead, it keeps the empty slots ready inside PostgreSQL so new inserts won’t require growing the file again.
  • Non-blocking: Standard VACUUM allows normal SELECT, INSERT, UPDATE, and DELETE queries to continue running while it works.

6. VACUUM FULL (Shrinking Disk Space to the OS)

If you deleted millions of rows and you must return the unused disk space back to your operating system, you need VACUUM FULL.

-- Check table size
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

-- Run VACUUM FULL
postgres=# VACUUM FULL items;
VACUUM
postgres=#

-- Check table size again
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 712 MB  <--- shrunk back down to original size!, free space released to the OS. 
(1 row)

postgres=#

-- VACUUM FULL rewrites the entire table into a new file on disk.

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 0 Sep 22 19:23 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65698
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65698*
-rw-------. 1 postgres postgres 498M Sep 22 19:23 /pgData/pgsql17/data/base/5/65698
[postgres@pgdb01 5]$

65690 -- This file no more availble.

Warning: VACUUM FULL rewrites the entire table into a new file on disk. It places an EXCLUSIVE LOCK on the table, blocking all reads and writes until it finishes. Use with caution in production!

7. VACUUM ANALYZE (Optimizing Query Performance)

Whenever data changes significantly, PostgreSQL’s query optimizer needs updated statistics to choose the fastest query plan.

VACUUM ANALYZE cleans up dead rows and refreshes the planner’s statistics in a single command:

-- Clean dead rows + update query planner statistics
postgres=# VACUUM ANALYZE items;
VACUUM
postgres=#

Pro Tip: Always run VACUUM ANALYZE after large bulk imports or mass data updates.

8. Autovacuum: The Background Worker

You don’t need to manually run VACUUM all day. PostgreSQL includes a daemon called Autovacuum that triggers automatically when a table exceeds a dead-tuple threshold (by default, when ~20% of rows are modified).

-- It was disabled for DEMO, enable back to ture.
postgres=# ALTER TABLE items SET (autovacuum_enabled = true);
ALTER TABLE
postgres=#

--- Confirming default autovacuum settings

postgres=# SHOW autovacuum;
 autovacuum
------------
 on
(1 row)

postgres=# SHOW autovacuum_naptime;
 autovacuum_naptime
--------------------
 1min
(1 row)

postgres=# SHOW autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

postgres=# SHOW autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

postgres=#

-- Check when autovacuum last cleaned your tables
postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          | last_autovacuum |         last_analyze          | last_autoanalyze
---------+------------------------------+-----------------+-------------------------------+------------------
 items   | 2026-09-22 19:28:22.29634+08 |                 | 2026-09-22 19:28:22.992613+08 |
(1 row)

postgres=#

-- LOAD some data and check again autovacuum last cleaned status

postgres=# UPDATE items SET price = price + 20;
UPDATE 10000000
postgres=#

postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze
---------+------------------------------+-------------------------------+-------------------------------+-------------------------------
 items   | 2026-09-22 19:28:22.29634+08 | 2026-09-22 19:40:56.685873+08 | 2026-09-22 19:28:22.992613+08 | 2026-09-22 19:40:58.096743+08
(1 row)

postgres=#

Note: Autovacuum automatically performs VACUUM to clean up obsolete row versions and ANALYZE to maintain optimizer statistics. It does NOT performs VACUUM FULL.

9. Quick Summary Cheatsheet

CommandWhat it DoesReclaims OS Disk?Locks Table?
VACUUMCleans dead rows; marks space reusable for PostgresNoNo (Safe)
VACUUM ANALYZECleans dead rows + updates optimizer statisticsNoNo (Safe)
VACUUM FULLRewrites table completely, shrinks file sizeYesYes (Exclusive lock)
AutovacuumRuns standard vacuum automatically in backgroundNoNo (Safe)
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala