Tag Archives: PostgreSQL internals

Free Space Map (FSM) in PostgreSQL

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

Free Space Map (FSM) in PostgreSQL

Table of Contents

A. What is the Free Space Map (FSM)?

When you run an INSERT or UPDATE, how does PostgreSQL know which page on disk has enough empty space to fit the new row?

Scanning through gigabytes of table pages from disk to find empty spots would be terribly slow. Instead, PostgreSQL maintains a lightweight binary tree called the Free Space Map (FSM) for every table and index.

B. How PostgreSQL Stores Data (Pages & Blocks)

PostgreSQL splits table files on disk into chunks called Pages (or Blocks). By default, each page is 8 KB (8192 bytes).

  • Each page can hold multiple rows (tuples).
  • Each row has an address called a ctid written as (page_number, item_number). For example, (0, 1) means Page 0, 1st row.
  • The FSM records roughly how many bytes of free space remain on each 8 KB page.

1. Prerequisites & Extension Setup

To inspect the Free Space Map directly inside PostgreSQL, enable the official pg_freespacemap extension.

-- Enable extension
postgres=# CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
CREATE EXTENSION
postgres=#

2. Create Demo Table & Insert Data

We will create a table and insert 10,000 rows. We explicitly set the storage of description to PLAIN to disable TOAST compression so each row consumes ~200 bytes directly on the 8 KB page.

-- 1. Create a simple table
postgres=# DROP TABLE IF EXISTS demo_table;
DROP TABLE
postgres=# CREATE TABLE demo_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT
);
CREATE TABLE
postgres=#


-- 2. Disable compression to maintain exact byte sizes
postgres=# ALTER TABLE demo_table ALTER COLUMN description SET STORAGE PLAIN;
ALTER TABLE
postgres=#

-- 3. Insert 10,000 rows (~200 bytes per row)
postgres=# INSERT INTO demo_table (name, description)
SELECT
    'Name_' || generate_series,
    repeat('x', 200)
FROM generate_series(1, 10000);
INSERT 0 10000
postgres=#


-- 4. Check total disk size
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS table_size;
 table_size
------------
 2504 kB
(1 row)

postgres=#

-- 5. Check total 8 KB pages (blocks)
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS total_pages;
 total_pages
-------------
         313
(1 row)

postgres=#

3. Inspect the Physical Files (Main, FSM, VM)

PostgreSQL splits table data on disk into multiple companion files (called forks).

-- Find the physical file path and relation filenode
postgres=# SELECT pg_relation_filepath('demo_table');
 pg_relation_filepath
----------------------
 base/5/65851
(1 row)

postgres=#

If you check your PostgreSQL data directory in the terminal, you will see three forks for this relation:

# Run in terminal inside $PGDATA
[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65851*
-rw-------. 1 postgres postgres 2564096 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851
-rw-------. 1 postgres postgres   24576 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851_fsm  <-----------
-rw-------. 1 postgres postgres    8192 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851_vm
[postgres@pgdb01 ~]$

65851        # Main data file (heap blocks/pages)
65851_fsm    # Free Space Map (tracks free bytes per block)
65851_vm     # Visibility Map (tracks all-visible / frozen blocks)

4. Check FSM Before Any Modifications

Let’s inspect the FSM to see how much room is available on each block.

-- Check free space in the first 20 pages
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |               96
     1 |               96
     2 |               96
     3 |               96
     4 |               96
     5 |               96
     6 |               96
     7 |               96
     8 |               96
     9 |               96
    10 |               96
    11 |               96
    12 |               96
    13 |               96
    14 |               96
    15 |               96
    16 |               96
    17 |               96
    18 |               96
    19 |               96
(20 rows)

postgres=#


-- Overall summary of free space across the entire table
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    min(avail) AS min_free_space,
    max(avail) AS max_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | min_free_space | max_free_space | total_free_space
-------------+----------------+----------------+----------------+------------------
         313 |            111 |             96 |           4864 |            34816
(1 row)

postgres=#

Observation: Most pages show very little available space (96 bytes free). The pages are packed full.

5. Delete Rows to Create Free Space (The FSM Lag)

Now let’s delete 50% of the rows (all even IDs) and immediately check the FSM.

-- Delete 50% of rows
postgres=# DELETE FROM demo_table WHERE id % 2 = 0;
DELETE 5000
postgres=#

-- Verify remaining count (should be 5,000)
postgres=# SELECT count(*) FROM demo_table;
 count
-------
  5000
(1 row)

postgres=#

-- Check FSM IMMEDIATELY after DELETE (before VACUUM)
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |               96
     1 |               96
     2 |               96
     3 |               96
     4 |               96
     5 |               96
     6 |               96
     7 |               96
     8 |               96
     9 |               96
    10 |               96
    11 |               96
    12 |               96
    13 |               96
    14 |               96
    15 |               96
    16 |               96
    17 |               96
    18 |               96
    19 |               96
(20 rows)

postgres=#

⚠️ Crucial Observation: The FSM still shows the exact same low free space as before! Because of MVCC, DELETE only flags rows as dead. The space has not been reclaimed, and the FSM has not been updated.

6. Run VACUUM & Observe FSM Changes

Running standard VACUUM clears dead rows and registers all newly available space into the FSM.

-- Run VACUUM to reclaim dead space and update FSM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Check FSM per block after VACUUM
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |             3936
     1 |             4192
     2 |             3936
     3 |             4064
     4 |             4064
     5 |             4064
     6 |             4064
     7 |             4064
     8 |             4064
     9 |             4064
    10 |             4064
    11 |             4064
    12 |             4064
    13 |             4064
    14 |             4064
    15 |             4064
    16 |             4064
    17 |             4064
    18 |             4064
    19 |             4064
(20 rows)

postgres=#

-- Summary after VACUUM
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    min(avail) AS min_free_space,
    max(avail) AS max_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | min_free_space | max_free_space | total_free_space
-------------+----------------+----------------+----------------+------------------
         313 |           4072 |           3936 |           6624 |          1274464
(1 row)

postgres=#

Result: Each page now reports substantial free space (roughly 3,936 to 4,064 bytes available per page).

7. Insert New Rows & Watch FSM Guide Placement

Let’s insert 2,000 new rows. Watch how PostgreSQL uses the FSM to place them inside existing pages instead of growing the file.

-- 1. Record current page count
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS pages_before_insert;
 pages_before_insert
---------------------
                 313
(1 row)

postgres=#

-- 2. Insert 2,000 new rows
postgres=# INSERT INTO demo_table (name, description)
SELECT
    'NewName_' || generate_series,
    repeat('y', 200)
FROM generate_series(1, 2000);
INSERT 0 2000
postgres=#

-- 3. Check page count again
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS pages_after_insert;
 pages_after_insert
--------------------
                313
(1 row)

postgres=#

What happened?

  • pages_before_insert and pages_after_insert are identical!
  • The table file did not expand at all.
  • The FSM directed PostgreSQL to pages with available free space, allowing the new rows to be inserted into the existing space.
-- Check free space reduction after inserts
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | total_free_space
-------------+----------------+------------------
         313 |           2489 |           778976
(1 row)

postgres=#

8. Compare VACUUM vs VACUUM FULL on FSM

Let’s see how standard VACUUM differs from VACUUM FULL regarding physical disk size and the FSM.

-- 1. Delete more rows
postgres=# DELETE FROM demo_table WHERE id % 3 = 0;
DELETE 2334
postgres=#

-- 2. Run standard VACUUM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Table size remains the same (space is kept for Postgres reuse, not given back to OS)
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS size_after_vacuum;
 size_after_vacuum
-------------------
 2504 kB
(1 row)

postgres=#
postgres=# SELECT sum(avail) AS total_free_after_vacuum FROM pg_freespace('demo_table');
 total_free_after_vacuum
-------------------------
                 1351904
(1 row)

postgres=#

-- 3. Run VACUUM FULL (Rewrites and compacts entire table)
postgres=# VACUUM FULL demo_table;
VACUUM
postgres=#

-- Physical table size shrinks on disk
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS size_after_vacuum_full;
 size_after_vacuum_full
------------------------
 1168 kB
(1 row)

postgres=#

-- The FSM free space is reset because the table has been reduced to fewer pages. As a result, PostgreSQL has fewer pages to track for free space.
Note: VACUUM FULL rewrites the entire table into a new file on disk.

postgres=# SELECT coalesce(sum(avail), 0) AS total_free_after_vacuum_full FROM pg_freespace('demo_table');
 total_free_after_vacuum_full
------------------------------
                            0
(1 row)

postgres=#
postgres=# SELECT pg_relation_filepath('demo_table');
 pg_relation_filepath
----------------------
 base/5/65859
(1 row)

postgres=#
[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65859*
-rw-------. 1 postgres postgres 1196032 Sep 23 18:18 /pgData/pgsql17/data/base/5/65859
[postgres@pgdb01 ~]$

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!

9. Quick Cheatsheet

ConceptDescription
What is FSM?A binary tree tracking free space across all 8 KB pages in a table.
PurposeSpeeds up INSERT/UPDATE by finding pages with room instantly.
Who updates it?VACUUM / autovacuum updates the FSM after cleaning dead rows.
Inspection ToolSELECT * FROM pg_freespace('table_name');
Disk FileStored alongside the main table file with the _fsm suffix.
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

MVCC in PostgreSQL

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

MVCC in PostgreSQL

Table of Contents

1. The Core Concept (Why MVCC?)

MVCC stands for Multi-Version Concurrency Control.

Its primary goal is simple: Readers should never block Writers, and Writers should never block Readers.

Instead of locking a row when someone is updating it (which makes everyone else wait), PostgreSQL keeps multiple versions of the same row active at the same time. Different transactions see different versions depending on when they started.

2. Hidden Columns (xmin & xmax)

Every table you create in PostgreSQL has hidden system columns. The two most important ones for MVCC are:

  • xmin: The Transaction ID that created/inserted this row version.
  • xmax: The Transaction ID that deleted or updated this row version. (If it is 0, the row is still actively alive).
-- To view these columns by explicitly querying them:
SELECT xmin, xmax, * FROM table_name;

3. Create a Test Table

-- Create a simple products table and  insert our first record and see how Postgres assigns the transaction ID.

[postgres@pgdb01 ~]$ psql -c "CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT);"
CREATE TABLE
[postgres@pgdb01 ~]$

-- Insert a row
[postgres@pgdb01 ~]$ psql -c "INSERT INTO products (name, price) VALUES ('Laptop', 1000);"
INSERT 0 1
[postgres@pgdb01 ~]$ 

-- Query the row with xmin and xmax
[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 |    0 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
  • xmin = 2156: xmin represents the transaction ID that created the row version. xmin = 2156
  • xmax = 0: xmax represents the transaction ID that deleted or updated. xmax = 0 means No one has deleted/updated it yet . It is actively visible to everyone.

4. UPDATE Creates a New Version

Now, let’s see MVCC in action. Open two separate database sessions/terminal windows.

Terminal 1 (The Writer): Start a transaction and update the price, but do not commit yet.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*#

Terminal 2 (The Reader): Check what Terminal 2 sees:

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 | 2157 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
Internally, PostgreSQL creates a new row version.

Before UPDATE

Row Version A
+----------------+
| id = 1         |
| price = 1000   |
+----------------+


After UPDATE

Old Version                New Version
+----------------+          +----------------+
| id = 1         |          | id = 1         |
| price = 1000   |          | price = 1200   |
| xmax = 2157    |          | xmin = 2157    |
+----------------+          +----------------+
        |                           |
        | old version               | new version
        v                           v
      xmax                         xmin

xmax = old version (xmax refers Dead rows)
xmin = new version
The old row version is not immediately physically removed.
  • The reader still sees the original price of 1000. They are not blocked!
  • The row now has an xmax of 2157. This tells Postgres: “Transaction 2157 is currently changing or deleting this row. If you aren’t Transaction 2157, keep looking at the old data.”
  • Under the hood, Postgres has already written a new row version with a price of 1200 and xmin = 2157, but it is completely invisible to Terminal 2 because transaction 2157 hasn’t committed yet.

5. After COMMIT

Let’s finalize the transaction and see the transition.

Terminal 1 (The Writer): Commit the change.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*# COMMIT;   <------------------ (only run COMMIT)
COMMIT
postgres=#

Terminal 2 (The Reader): Query the table again. What Terminal 2 sees now

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2157 |    0 |  1 | Laptop |  1200
(1 row)

[postgres@pgdb01 ~]$
  • The old row version (price 1000) is now marked as dead(invisible).
  • The new row version (price 1200) is now alive(current row) with xmin = 2157 and xmax = 0.

6. DELETE Doesn’t Actually Delete

In Postgres, a DELETE statement does not physically erase data from the hard drive immediately. It just changes a flag.

postgres=# BEGIN;
BEGIN
postgres=*# DELETE FROM products WHERE id = 1;
DELETE 1
postgres=*# SELECT txid_current();
 txid_current
--------------
         2158
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#


A DELETE Internally, simply marks the existing row as dead.

Before DELETE

+----------------+
| id=1           |
| pirce = 1200   |
+----------------+

After DELETE

+----------------+
| id=1           |
| price = 1200   |
| xmax = 2158    |
+----------------+

Later, VACUUM can remove dead tuples when they are no longer needed by active transactions.

Under the hood, Postgres simply updated the existing row’s xmax value to 2158. Because transaction 2158 is committed, Postgres now knows that this row is dead and shouldn’t be shown to any new queries. The actual space on the disk won’t be freed until we vacuum.

7. Dead tuples and VACUUM

Because UPDATE creates new rows and DELETE just marks them as dead, database will accumulate “dead tuples” (bloat) over time.
In simple way, Since UPDATE and DELETE don’t remove old rows, dead rows pile up. This is called bloat.

Postgres uses a process called VACUUM to find these dead rows, clear them out, and mark that space as reusable for future inserts.

-- 1. Check how many dead rows are lurking a table

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          2   <--------------------
(1 row)

[postgres@pgdb01 ~]$


-- 2. Run a manual vacuum to clean up

[postgres@pgdb01 ~]$ psql -c "VACUUM VERBOSE products;"
INFO:  vacuuming "postgres.public.products"
INFO:  table "products": truncated 1 to 0 pages
INFO:  finished vacuuming "postgres.public.products": index scans: 1
pages: 1 removed, 0 remain, 1 scanned (100.00% of total)
tuples: 2 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2159, which was 1 XIDs old when operation ended
new relfrozenxid: 2159, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
index "products_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 11.905 MB/s
buffer usage: 61 hits, 0 misses, 8 dirtied
WAL usage: 11 records, 8 full page images, 47529 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65663"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65663": 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: 2160, which was 0 XIDs old when operation ended
new relfrozenxid: 2160, which is 5 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: 22.978 MB/s, avg write rate: 22.978 MB/s
buffer usage: 27 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 8097 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
[postgres@pgdb01 ~]$


-- 3. Check again; dead rows should now be 0

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          0  
(1 row)

[postgres@pgdb01 ~]$

Note: PostgreSQL runs an Autovacuum background process automatically, so you rarely need to run manual vacuums in production.

8. Quick Summary Cheatsheet

To keep MVCC simple, remember these four rules:

OperationWhat Postgres Actually Does
INSERTCreates a row with xmin = Current TXID.
DELETEMark row as dead, does not physically erase it. Sets xmax = Current TXID
UPDATE INSERT new version + DELETE (mark old version as dead).
VACUUMSweeps up the dead rows to reclaim storage space.
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

How ABORT/ROLLBACK Works in PostgreSQL

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

How ABORT Works in PostgreSQL

 

  • Signal: The database detects an error or receives a ROLLBACK command and marks the transaction as aborted.

  • WAL File Write: An ABORT record is written to the WAL buffer in RAM and flushed into the actual 16MB WAL files on disk to keep a complete history.

  • CLOG Status: The status of the Transaction ID (XID) is instantly switched to “Aborted” in the Commit Log bitmap.

  • MVCC Isolation: The modified data rows are left on the disk but immediately become invisible to all other users.

  • Cleanup: The background Autovacuum process later scans the database, clears out these dead rows, and reclaims the disk space.

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

WAL Commit Process

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

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):

  • The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”

  • Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).

2. The COMMIT Command Issued

When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”

3. Flushing to Disk (XLogFlush)

To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:

  • The internal function XLogFlush() is called.

  • It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.

  • It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.

  • An fsync() system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.

4. Acknowledgment to the Client

Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.

Crucial Architectural Concepts

  • Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).

  • Asynchronous Commit Alternative: If you set the configuration parameter synchronous_commit = off, Postgres will acknowledge the client’s COMMIT before the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 times wal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.

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

Database Directory Layout

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

PostgreSQL Physical Directory Layout

Installation Directory Layout

[postgres@pgdb02 ~]$ ls -ltr /usr/pgsql-17/
total 16
drwxr-xr-x. 3 postgres postgres 23 Jun 4 20:38 doc
drwxr-xr-x. 8 postgres postgres 4096 Jun 22 21:08 share
drwxr-xr-x. 2 postgres postgres 4096 Jun 22 21:08 bin
drwxr-xr-x. 3 postgres postgres 4096 Jun 22 21:08 lib
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ ls -lt /usr/pgsql-17/bin
total 14492
-rwxr-xr-x. 1 postgres postgres 71072 May 21 13:49 clusterdb
-rwxr-xr-x. 1 postgres postgres 75240 May 21 13:49 createdb
-rwxr-xr-x. 1 postgres postgres 75624 May 21 13:49 createuser
-rwxr-xr-x. 1 postgres postgres 66632 May 21 13:49 dropdb
-rwxr-xr-x. 1 postgres postgres 66576 May 21 13:49 dropuser
-rwxr-xr-x. 1 postgres postgres 113288 May 21 13:49 initdb
-rwxr-xr-x. 1 postgres postgres 41400 May 21 13:49 oid2name
-rwxr-xr-x. 1 postgres postgres 96824 May 21 13:49 pg_amcheck
-rwxr-xr-x. 1 postgres postgres 41056 May 21 13:49 pg_archivecleanup
-rwxr-xr-x. 1 postgres postgres 142928 May 21 13:49 pg_basebackup
-rwxr-xr-x. 1 postgres postgres 162984 May 21 13:49 pgbench
-rwxr-xr-x. 1 postgres postgres 49632 May 21 13:49 pg_checksums
-rwxr-xr-x. 1 postgres postgres 116872 May 21 13:49 pg_combinebackup
-rwxr-xr-x. 1 postgres postgres 36656 May 21 13:49 pg_config
-rwxr-xr-x. 1 postgres postgres 44984 May 21 13:49 pg_controldata
-rwxr-xr-x. 1 postgres postgres 70768 May 21 13:49 pg_createsubscriber
-rwxr-xr-x. 1 postgres postgres 62296 May 21 13:49 pg_ctl
-rwxr-xr-x. 1 postgres postgres 447368 May 21 13:49 pg_dump
-rwxr-xr-x. 1 postgres postgres 117680 May 21 13:49 pg_dumpall
-rwxr-xr-x. 1 postgres postgres 36952 May 21 13:49 pg_isready
-rwxr-xr-x. 1 postgres postgres 92000 May 21 13:49 pg_receivewal
-rwxr-xr-x. 1 postgres postgres 54544 May 21 13:49 pg_recvlogical
-rwxr-xr-x. 1 postgres postgres 53808 May 21 13:49 pg_resetwal
-rwxr-xr-x. 1 postgres postgres 194496 May 21 13:49 pg_restore
-rwxr-xr-x. 1 postgres postgres 108584 May 21 13:49 pg_rewind
-rwxr-xr-x. 1 postgres postgres 41120 May 21 13:49 pg_test_fsync
-rwxr-xr-x. 1 postgres postgres 32632 May 21 13:49 pg_test_timing
-rwxr-xr-x. 1 postgres postgres 171856 May 21 13:49 pg_upgrade
-rwxr-xr-x. 1 postgres postgres 95928 May 21 13:49 pg_verifybackup
-rwxr-xr-x. 1 postgres postgres 95968 May 21 13:49 pg_waldump
-rwxr-xr-x. 1 postgres postgres 41184 May 21 13:49 pg_walsummary
-rwxr-xr-x. 1 postgres postgres 10875088 May 21 13:49 postgres
-rwxr-xr-x. 1 postgres postgres 729400 May 21 13:49 psql
-rwxr-xr-x. 1 postgres postgres 83816 May 21 13:49 reindexdb
-rwxr-xr-x. 1 postgres postgres 88344 May 21 13:49 vacuumdb
-rwxr-xr-x. 1 postgres postgres 41200 May 21 13:49 vacuumlo
-rwxr-xr-x. 1 postgres postgres 2167 May 21 13:49 postgresql-17-check-db-dir
-rwxr-xr-x. 1 postgres postgres 9617 May 21 13:49 postgresql-17-setup
[postgres@pgdb02 ~]$

Database (Data) Directory Layout

[postgres@pgdb02 ~]$ ls -l /pgData/pgsql17/data
total 68
drwx------. 9 postgres postgres    89 Jun 30 19:40 base
-rw-------. 1 postgres postgres    30 Jul  3 14:30 current_logfiles
drwx------. 2 postgres postgres  4096 Jul  3 14:58 global
drwx------. 2 postgres postgres   162 Jul  3 00:00 log
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_commit_ts
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_dynshmem
-rw-------. 1 postgres postgres  5798 Jun 29 23:45 pg_hba.conf
-rw-------. 1 postgres postgres  2640 Jun 22 21:11 pg_ident.conf
drwx------. 4 postgres postgres    68 Jul  3 14:30 pg_logical
drwx------. 4 postgres postgres    36 Jun 22 21:11 pg_multixact
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_notify
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_replslot
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_serial
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_snapshots
drwx------. 2 postgres postgres     6 Jul  3 14:29 pg_stat
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_stat_tmp
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_subtrans
drwx------. 2 postgres postgres    19 Jul  2 18:32 pg_tblspc
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_twophase
-rw-------. 1 postgres postgres     3 Jun 22 21:11 PG_VERSION
lrwxrwxrwx. 1 postgres postgres    18 Jun 22 21:11 pg_wal -> /pgWal/pgsql17/wal
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_xact
-rw-------. 1 postgres postgres    88 Jun 22 21:11 postgresql.auto.conf
-rw-------. 1 postgres postgres 30881 Jun 29 23:37 postgresql.conf
-rw-------. 1 postgres postgres    55 Jul  3 14:30 postmaster.opts
-rw-------. 1 postgres postgres    89 Jul  3 14:30 postmaster.pid
[postgres@pgdb02 ~]$
NameDirectory or FileDescription
baseDirectoryContains subdirectories for each database. Each database is stored using its own database OID.
current_logfilesFileStores information about the log file currently being written by the logging collector process.
globalDirectoryContains cluster-wide shared system catalog tables such as pg_database, pg_authid, pg_tablespace, etc.
logDirectoryStores PostgreSQL server logs and error log files.
pg_commit_tsDirectoryStores transaction commit timestamp data when track_commit_timestamp is enabled.
pg_dynshmemDirectoryStores files used by PostgreSQL dynamic shared memory subsystem.
pg_hba.confFileClient authentication configuration file. Controls which users and hosts can connect.
pg_ident.confFileStores user name mapping rules between operating system users and database users.
pg_logicalDirectoryStores metadata and status information used for logical replication and logical decoding.
pg_multixactDirectoryStores multitransaction status information used for shared row-level locking.
pg_notifyDirectoryStores LISTEN and NOTIFY asynchronous notification status data.
pg_replslotDirectoryStores physical and logical replication slot information.
pg_serialDirectoryStores metadata related to committed serializable transactions.
pg_snapshotsDirectoryStores exported transaction snapshot files.
pg_statDirectoryStores permanent statistics data used by PostgreSQL statistics subsystem.
pg_stat_tmpDirectoryStores temporary statistics files generated by statistics collector.
pg_subtransDirectoryStores subtransaction status information for nested transactions.
pg_tblspcDirectoryContains symbolic links pointing to user-created tablespaces.
pg_twophaseDirectoryStores prepared transaction state files used for two-phase commit.
PG_VERSIONFileStores the PostgreSQL major version number of the database cluster.
pg_walDirectory (Symlink)Stores WAL (Write Ahead Log) files for crash recovery and replication. Example: /pgWal/pgsql17/wal
pg_xactDirectoryStores transaction commit status information and transaction metadata logs.
postgresql.auto.confFileStores configuration parameters automatically updated by ALTER SYSTEM.
postgresql.confFileMain PostgreSQL configuration file containing server parameter settings.
postmaster.optsFileStores command-line options used when PostgreSQL server was last started.
postmaster.pidFileStores Postmaster PID, startup time, port number, socket path, listen address, and shared memory details.

Difference between Base Directory and Global Directory

In PostgreSQL, both the Base Directory and the Global Directory reside within the PGDATA (the data cluster) folder. The primary difference lies in the scope and type of data they store.

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres | <----- pg_default physical location: Located at $PGDATA/base/
 pg_global  | postgres | <----- pg_global physical location: Located at $PGDATA/global/
(2 rows)

postgres=#

-- List of Files and Directoires from Global Directory

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/global
total 580
drwx------.  2 postgres postgres  4096 Jul  3 16:03 .
drwx------. 19 postgres postgres  4096 Jul  3 16:01 ..
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1213_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213_vm
-rw-------.  1 postgres postgres     0 Jul  3 16:01 1214
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1232
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1233
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1260_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1261_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1262_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 2396_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396_vm
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2397
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2671
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2672
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2676
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2677
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2694
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2695
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2697
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2698
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2846
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2847
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2964
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2965
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2966
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2967
-rw-------.  1 postgres postgres     0 Jul  3 16:01 3592
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 3593
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4060
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4061
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4175
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4176
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4177
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4178
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4181
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4182
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4183
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4184
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4185
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4186
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6000
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6001
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6002
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6100
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6114
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6115
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6243
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6244
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6245
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6246
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6247 
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6302
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6303
-rw-------.  1 postgres postgres  8192 Jul  3 16:06 pg_control
-rw-------.  1 postgres postgres   524 Jul  3 16:01 pg_filenode.map
-rw-------.  1 postgres postgres 28724 Jul  3 16:03 pg_internal.init
[postgres@lxceftsgvdb01 ~]$

-- List of Files and Directories from Base Directory 

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/base
total 40
drwx------.  5 postgres postgres   33 Jul  3 16:01 .
drwx------. 19 postgres postgres 4096 Jul  3 16:01 ..
drwx------.  2 postgres postgres 8192 Jul  3 16:03 1
drwx------.  2 postgres postgres 8192 Jul  3 16:01 4
drwx------.  2 postgres postgres 8192 Jul  3 16:02 5
[postgres@lxceftsgvdb01 ~]$

1. The Global Directory (pg_global)

The global directory contains data that is shared across the entire database cluster.

  • Path: $PGDATA/global/
  • Scope: Cluster-wide.
  • Contents: It stores “Shared System Catalogs.” These are tables that contain information applicable to every database in the instance.
  • Key Files:
    • pg_database: Information about all databases in the cluster.
    • pg_authid: User roles and passwords (since a user can log into any database in the cluster).
    • pg_tablespace: Information about defined storage locations.
  • Identification: Objects in this directory always have an OID (Object Identifier), but they do not belong to a specific database ID.

Lists only actual shared/global catalog tables (no indexes, no TOAST tables) from the global directory

-- Let's look at inside postgres global directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_global_tables.sh
#!/bin/bash
cd $PGDATA
# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "TABLESPACE"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Loop through files in global directory
for i in $(find global -maxdepth 1 -type f | awk -F/ '{print $2}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details from oid2name
    result=$(oid2name -x -S -q -o $i)

    # Parse fields
    oid=$(echo $result | awk '{print $1}')
    name=$(echo $result | awk '{print $2}')
    tableoid=$(echo $result | awk '{print $3}')
    schema=$(echo $result | awk '{print $4}')
    db=$(echo $result | awk '{print $5}')

    # Check relation type from pg_class
    relkind=$(psql -At -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables (relkind = r)
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_global_tables.sh
OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1262     | pg_database                              | 1262       | pg_catalog      | pg_global
2964     | pg_db_role_setting                       | 2964       | pg_catalog      | pg_global
1213     | pg_tablespace                            | 1213       | pg_catalog      | pg_global
1260     | pg_authid                                | 1260       | pg_catalog      | pg_global
1261     | pg_auth_members                          | 1261       | pg_catalog      | pg_global
1214     | pg_shdepend                              | 1214       | pg_catalog      | pg_global
2396     | pg_shdescription                         | 2396       | pg_catalog      | pg_global
6000     | pg_replication_origin                    | 6000       | pg_catalog      | pg_global
3592     | pg_shseclabel                            | 3592       | pg_catalog      | pg_global
6243     | pg_parameter_acl                         | 6243       | pg_catalog      | pg_global
6100     | pg_subscription                          | 6100       | pg_catalog      | pg_global
[postgres@pgdb02 ~]$

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/global
/pgData/pgsql17/data/global
├── 1213
├── 1213_fsm
├── 1213_vm
├── 1214
├── 1232
├── 1233
├── 1260
├── 1260_fsm
├── 1260_vm
├── 1261
├── 1261_fsm
├── 1261_vm
├── 1262
├── 1262_fsm
├── 1262_vm
├── 2396
├── 2396_fsm
├── 2396_vm
├── 2397
├── 2671
├── 2672
├── 2676
├── 2677
├── 2694
├── 2695
├── 2697
├── 2698
├── 2846
├── 2847
├── 2964
├── 2965
├── 2966
├── 2967
├── 3592
├── 3593
├── 4060
├── 4061
├── 4175
├── 4176
├── 4177
├── 4178
├── 4181
├── 4182
├── 4183
├── 4184
├── 4185
├── 4186
├── 6000
├── 6001
├── 6002
├── 6100
├── 6114
├── 6115
├── 6243
├── 6244
├── 6245
├── 6246
├── 6247
├── 6302
├── 6303
├── pg_control
├── pg_filenode.map
└── pg_internal.init

0 directories, 63 files
[postgres@lxceftsgvdb01 ~]$

2. The Base Directory (pg_base)

The base directory contains the actual user data and system catalogs that are private to each specific database.

  • Path: $PGDATA/base/
  • Scope: Database-specific.
  • Structure: Inside base, there is a subdirectory for every database in the cluster, named after the database’s OID (e.g., $PGDATA/base/16384/).
  • Contents:
    • User Tables and Indexes: The actual data you insert into your tables.
    • Local System Catalogs: Tables like pg_class, pg_attribute, and pg_type which describe the structure of that specific database.
  • Identification: If you want to find where a specific table is stored, you look in base/[Database OID]/[Table Filenode]
[postgres@pgdb02 ~]$ psql -c "SELECT oid, datname FROM pg_database order by oid;"
 oid |  datname
-----+-----------
   1 | template1
   4 | template0
   5 | postgres
(3 rows)

[postgres@pgdb02 ~]$
[postgres@lxceftsgvdb01 ~]$ ls /pgData/pgsql17/data/base | sort -n
1  <---- template1
4  <---- template0
5  <---- postgres
[postgres@pgdb02 ~]$

-- Let's look at inside postgres Base directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_base_tables.sh
#!/bin/bash

export PGHOME=/usr/pgsql-17
export PGDATA=/pgData/pgsql17/data
export PATH=$PGHOME/bin:$PATH

DBNAME="postgres"

# Get database OID
DBOID=$(psql -At -d $DBNAME -c "SELECT oid FROM pg_database WHERE datname='$DBNAME'")

# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "Tablespace"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Use absolute path ($PGDATA/base/)
for i in $(find $PGDATA/base/$DBOID -maxdepth 1 -type f | awk -F/ '{print $NF}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details
    result=$(oid2name -d $DBNAME -x -q -o $i)

    # Parse fields
    oid=$(echo "$result" | awk '{print $1}')
    name=$(echo "$result" | awk '{print $2}')
    tableoid=$(echo "$result" | awk '{print $3}')
    schema=$(echo "$result" | awk '{print $4}')
    db=$(echo "$result" | awk '{print $5}')

    # Check relation type
    relkind=$(psql -At -d $DBNAME -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_base_tables.sh

OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1255     | pg_proc                                  | 1255       | pg_catalog      | pg_default
1247     | pg_type                                  | 1247       | pg_catalog      | pg_default
1249     | pg_attribute                             | 1249       | pg_catalog      | pg_default
1259     | pg_class                                 | 1259       | pg_catalog      | pg_default
2604     | pg_attrdef                               | 2604       | pg_catalog      | pg_default
2606     | pg_constraint                            | 2606       | pg_catalog      | pg_default
2611     | pg_inherits                              | 2611       | pg_catalog      | pg_default
2610     | pg_index                                 | 2610       | pg_catalog      | pg_default
2617     | pg_operator                              | 2617       | pg_catalog      | pg_default
2753     | pg_opfamily                              | 2753       | pg_catalog      | pg_default
2616     | pg_opclass                               | 2616       | pg_catalog      | pg_default
2601     | pg_am                                    | 2601       | pg_catalog      | pg_default
2602     | pg_amop                                  | 2602       | pg_catalog      | pg_default
2603     | pg_amproc                                | 2603       | pg_catalog      | pg_default
2612     | pg_language                              | 2612       | pg_catalog      | pg_default
2995     | pg_largeobject_metadata                  | 2995       | pg_catalog      | pg_default
2613     | pg_largeobject                           | 2613       | pg_catalog      | pg_default
2600     | pg_aggregate                             | 2600       | pg_catalog      | pg_default
2619     | pg_statistic                             | 2619       | pg_catalog      | pg_default
3381     | pg_statistic_ext                         | 3381       | pg_catalog      | pg_default
3429     | pg_statistic_ext_data                    | 3429       | pg_catalog      | pg_default
2618     | pg_rewrite                               | 2618       | pg_catalog      | pg_default
2620     | pg_trigger                               | 2620       | pg_catalog      | pg_default
3466     | pg_event_trigger                         | 3466       | pg_catalog      | pg_default
2609     | pg_description                           | 2609       | pg_catalog      | pg_default
2605     | pg_cast                                  | 2605       | pg_catalog      | pg_default
3501     | pg_enum                                  | 3501       | pg_catalog      | pg_default
2615     | pg_namespace                             | 2615       | pg_catalog      | pg_default
2607     | pg_conversion                            | 2607       | pg_catalog      | pg_default
2608     | pg_depend                                | 2608       | pg_catalog      | pg_default
3602     | pg_ts_config                             | 3602       | pg_catalog      | pg_default
3603     | pg_ts_config_map                         | 3603       | pg_catalog      | pg_default
3600     | pg_ts_dict                               | 3600       | pg_catalog      | pg_default
3601     | pg_ts_parser                             | 3601       | pg_catalog      | pg_default
3764     | pg_ts_template                           | 3764       | pg_catalog      | pg_default
3079     | pg_extension                             | 3079       | pg_catalog      | pg_default
2328     | pg_foreign_data_wrapper                  | 2328       | pg_catalog      | pg_default
1417     | pg_foreign_server                        | 1417       | pg_catalog      | pg_default
1418     | pg_user_mapping                          | 1418       | pg_catalog      | pg_default
3118     | pg_foreign_table                         | 3118       | pg_catalog      | pg_default
3256     | pg_policy                                | 3256       | pg_catalog      | pg_default
826      | pg_default_acl                           | 826        | pg_catalog      | pg_default
3394     | pg_init_privs                            | 3394       | pg_catalog      | pg_default
3596     | pg_seclabel                              | 3596       | pg_catalog      | pg_default
3456     | pg_collation                             | 3456       | pg_catalog      | pg_default
3350     | pg_partitioned_table                     | 3350       | pg_catalog      | pg_default
3541     | pg_range                                 | 3541       | pg_catalog      | pg_default
3576     | pg_transform                             | 3576       | pg_catalog      | pg_default
2224     | pg_sequence                              | 2224       | pg_catalog      | pg_default
6104     | pg_publication                           | 6104       | pg_catalog      | pg_default
6237     | pg_publication_namespace                 | 6237       | pg_catalog      | pg_default
6106     | pg_publication_rel                       | 6106       | pg_catalog      | pg_default
6102     | pg_subscription_rel                      | 6102       | pg_catalog      | pg_default
14382    | sql_features                             | 14382      | information_schema | pg_default
14387    | sql_implementation_info                  | 14387      | information_schema | pg_default
14392    | sql_parts                                | 14392      | information_schema | pg_default
14397    | sql_sizing                               | 14397      | information_schema | pg_default
[postgres@lxceftsgvdb01 ~]$

-- Let's look at inside postgres Base directory (Complete List)

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/base/5
/pgData/pgsql17/data/base/5
├── 112
├── 113
├── 1247
├── 1247_fsm
├── 1247_vm
├── 1249
├── 1249_fsm
├── 1249_vm
├── 1255
├── 1255_fsm
├── 1255_vm
├── 1259
├── 1259_fsm
├── 1259_vm
├── 1417
├── 1418
├── 14382
├── 14382_fsm
├── 14382_vm
├── 14385
├── 14386
├── 14387
├── 14387_fsm
├── 14387_vm
├── 14390
├── 14391
├── 14392
├── 14392_fsm
├── 14392_vm
├── 14395
├── 14396
├── 14397
├── 14397_fsm
├── 14397_vm
├── 14400
├── 14401
├── 174
├── 175
├── 2187
├── 2224
├── 2228
├── 2328
├── 2336
├── 2337
├── 2579
├── 2600
├── 2600_fsm
├── 2600_vm
├── 2601
├── 2601_fsm
├── 2601_vm
├── 2602
├── 2602_fsm
├── 2602_vm
├── 2603
├── 2603_fsm
├── 2603_vm
├── 2604
├── 2605
├── 2605_fsm
├── 2605_vm
├── 2606
├── 2606_fsm
├── 2606_vm
├── 2607
├── 2607_fsm
├── 2607_vm
├── 2608
├── 2608_fsm
├── 2608_vm
├── 2609
├── 2609_fsm
├── 2609_vm
├── 2610
├── 2610_fsm
├── 2610_vm
├── 2611
├── 2612
├── 2612_fsm
├── 2612_vm
├── 2613
├── 2615
├── 2615_fsm
├── 2615_vm
├── 2616
├── 2616_fsm
├── 2616_vm
├── 2617
├── 2617_fsm
├── 2617_vm
├── 2618
├── 2618_fsm
├── 2618_vm
├── 2619
├── 2619_fsm
├── 2619_vm
├── 2620
├── 2650
├── 2651
├── 2652
├── 2653
├── 2654
├── 2655
├── 2656
├── 2657
├── 2658
├── 2659
├── 2660
├── 2661
├── 2662
├── 2663
├── 2664
├── 2665
├── 2666
├── 2667
├── 2668
├── 2669
├── 2670
├── 2673
├── 2674
├── 2675
├── 2678
├── 2679
├── 2680
├── 2681
├── 2682
├── 2683
├── 2684
├── 2685
├── 2686
├── 2687
├── 2688
├── 2689
├── 2690
├── 2691
├── 2692
├── 2693
├── 2696
├── 2699
├── 2701
├── 2702
├── 2703
├── 2704
├── 2753
├── 2753_fsm
├── 2753_vm
├── 2754
├── 2755
├── 2756
├── 2757
├── 2830
├── 2831
├── 2832
├── 2833
├── 2834
├── 2835
├── 2836
├── 2836_fsm
├── 2836_vm
├── 2837
├── 2838
├── 2838_fsm
├── 2838_vm
├── 2839
├── 2840
├── 2840_fsm
├── 2840_vm
├── 2841
├── 2995
├── 2996
├── 3079
├── 3079_fsm
├── 3079_vm
├── 3080
├── 3081
├── 3085
├── 3118
├── 3119
├── 3164
├── 3256
├── 3257
├── 3258
├── 3350
├── 3351
├── 3379
├── 3380
├── 3381
├── 3394
├── 3394_fsm
├── 3394_vm
├── 3395
├── 3429
├── 3430
├── 3431
├── 3433
├── 3439
├── 3440
├── 3455
├── 3456
├── 3456_fsm
├── 3456_vm
├── 3466
├── 3467
├── 3468
├── 3501
├── 3502
├── 3503
├── 3534
├── 3541
├── 3541_fsm
├── 3541_vm
├── 3542
├── 3574
├── 3575
├── 3576
├── 3596
├── 3597
├── 3598
├── 3599
├── 3600
├── 3600_fsm
├── 3600_vm
├── 3601
├── 3601_fsm
├── 3601_vm
├── 3602
├── 3602_fsm
├── 3602_vm
├── 3603
├── 3603_fsm
├── 3603_vm
├── 3604
├── 3605
├── 3606
├── 3607
├── 3608
├── 3609
├── 3712
├── 3764
├── 3764_fsm
├── 3764_vm
├── 3766
├── 3767
├── 3997
├── 4143
├── 4144
├── 4145
├── 4146
├── 4147
├── 4148
├── 4149
├── 4150
├── 4151
├── 4152
├── 4153
├── 4154
├── 4155
├── 4156
├── 4157
├── 4158
├── 4159
├── 4160
├── 4163
├── 4164
├── 4165
├── 4166
├── 4167
├── 4168
├── 4169
├── 4170
├── 4171
├── 4172
├── 4173
├── 4174
├── 5002
├── 548
├── 549
├── 6102
├── 6104
├── 6106
├── 6110
├── 6111
├── 6112
├── 6113
├── 6116
├── 6117
├── 6175
├── 6176
├── 6228
├── 6229
├── 6237
├── 6238
├── 6239
├── 826
├── 827
├── 828
├── pg_filenode.map
├── pg_internal.init
└── PG_VERSION

0 directories, 299 files
[postgres@lxceftsgvdb01 ~]$
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: https://www.linkedin.com/in/rajasekhar-amudala/

Types of Shutdown in PostgreSQL

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

Types of Shutdown in PostgreSQL

PostgreSQL Shutdown TypeOracleBehavior
Smart (SIGTERM)SHUTDOWN NORMALThe server stops accepting new connections but allows existing sessions to continue running normally. PostgreSQL shuts down only after all active sessions disconnect and their transactions complete successfully.
Fast (SIGINT – Default)SHUTDOWN IMMEDIATEThe server stops accepting new connections and immediately terminates all active sessions. Any uncommitted transactions are rolled back, and PostgreSQL performs a clean shutdown gracefully.
Immediate (SIGQUIT)SHUTDOWN ABORTThe server terminates all processes instantly without performing a proper shutdown. No cleanup or checkpoint occurs, so PostgreSQL performs crash recovery by replaying WAL files during the next startup.
Shutdown TypeSignalCommand ExampleBehavior
SmartSIGTERMpg_ctl stop -D /pgData -m smartStops new connections, waits for existing sessions to finish normally, then shuts down.
Fast (Default)SIGINTpg_ctl stop -D /pgdata -m fastStops new connections, terminates active sessions, rolls back uncommitted transactions, and shuts down cleanly.
ImmediateSIGQUITpg_ctl stop -D /pgdata -m immediateStops immediately without proper shutdown. Recovery from WAL files happens during next startup.

Difference Between Reload and Restart

  • When we change server configuration parameters, PostgreSQL needs to read those changes before they become active.
  • Reload applies the new configuration without stopping or restarting the database service. Existing connections and database activity continue normally.
  • Some configuration parameter changes cannot be applied through reload and will only take effect after a restart.
  • Restart stops the PostgreSQL server gracefully, ends all running activities, releases resources, closes open files, and then starts the server again with the new configuration.

 

Smart Shutdown (SIGTERM):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m smart
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:26:59.741 +08 [3476] LOG:  received smart shutdown request
2026-07-03 14:26:59.752 +08 [3476] LOG:  background worker "logical replication launcher" (PID 3483) exited with exit code 1
2026-07-03 14:26:59.753 +08 [3478] LOG:  shutting down
2026-07-03 14:26:59.755 +08 [3478] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:26:59.765 +08 [3478] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/105465F0, redo lsn=1/105465F0
2026-07-03 14:26:59.771 +08 [3476] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:27:18.394 +08 [3487] LOG:  redirecting log output to logging collector process
2026-07-03 14:27:18.394 +08 [3487] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:27:18.394 +08 [3487] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:27:18.399 +08 [3487] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:27:18.404 +08 [3487] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:27:18.413 +08 [3491] LOG:  database system was shut down at 2026-07-03 14:26:59 +08
2026-07-03 14:27:18.425 +08 [3487] LOG:  database system is ready to accept connections

Fast Shutdown (SIGINT – Default):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:28:26.839 +08 [3487] LOG:  received fast shutdown request
2026-07-03 14:28:26.844 +08 [3487] LOG:  aborting any active transactions
2026-07-03 14:28:26.854 +08 [3487] LOG:  background worker "logical replication launcher" (PID 3494) exited with exit code 1
2026-07-03 14:28:26.860 +08 [3489] LOG:  shutting down
2026-07-03 14:28:26.862 +08 [3489] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:28:26.874 +08 [3489] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.015 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=1/105466A0, redo lsn=1/105466A0
2026-07-03 14:28:26.882 +08 [3487] LOG:  database system is shut down

[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:29:00.574 +08 [3505] LOG:  redirecting log output to logging collector process
2026-07-03 14:29:00.574 +08 [3505] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:29:00.575 +08 [3505] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:29:00.579 +08 [3505] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:29:00.580 +08 [3505] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:29:00.582 +08 [3505] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:29:00.587 +08 [3505] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:29:00.595 +08 [3509] LOG:  database system was shut down at 2026-07-03 14:28:26 +08
2026-07-03 14:29:00.607 +08 [3505] LOG:  database system is ready to accept connections

Immediate Shutdown (SIGQUIT):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m immediate
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:29:47.471 +08 [3505] LOG:  received immediate shutdown request
2026-07-03 14:29:47.485 +08 [3505] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:30:17.965 +08 [3520] LOG:  redirecting log output to logging collector process
2026-07-03 14:30:17.965 +08 [3520] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:30:17.966 +08 [3520] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:30:17.970 +08 [3520] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:30:17.974 +08 [3520] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:30:17.983 +08 [3524] LOG:  database system was interrupted; last known up at 2026-07-03 14:29:00 +08
2026-07-03 14:30:19.211 +08 [3524] LOG:  database system was not properly shut down; automatic recovery in progress
2026-07-03 14:30:19.216 +08 [3524] LOG:  redo starts at 1/10546718
2026-07-03 14:30:19.216 +08 [3524] LOG:  invalid record length at 1/10546750: expected at least 24, got 0
2026-07-03 14:30:19.217 +08 [3524] LOG:  redo done at 1/10546718 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-07-03 14:30:19.221 +08 [3522] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-03 14:30:19.231 +08 [3522] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/10546750, redo lsn=1/10546750
2026-07-03 14:30:19.239 +08 [3520] LOG:  database system is ready to accept connections

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: https://www.linkedin.com/in/rajasekhar-amudala/

How SELECT, INSERT, UPDATE, and DELETE Work Internally in PostgreSQL

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

 

How SELECT, INSERT, UPDATE, and DELETE work internally in PostgreSQL?

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means every modification creates a new version of the row instead of overwriting the old one. This is the core concept behind all DML operations.

1. Common Foundation (Applies to All Operations)

  • Heap: Data is stored in heap files (tables). Each row is a tuple.
  • Tuple Header: Every row contains:
    • xmin: Transaction ID that created the row
    • xmax: Transaction ID that deleted/updated the row (null if active)
    • Other visibility information
  • WAL (Write-Ahead Log): All changes are first written to WAL for durability before being applied to data files.
  • Shared Buffers: Data pages are read into memory before being modified.
  • Visibility Rules: A row is visible to a transaction based on its snapshot (which transactions it can see).

2. How SELECT Works

          Visibility Check: For each row, PostgreSQL checks:

    • The row’s xmin must be committed and visible to the current transaction.
    • The row’s xmax must be either null or not visible to the current transaction.

 

3. How INSERT Works

  1. Transaction Starts: A new transaction ID (xid) is assigned.
  2. Buffer Allocation: PostgreSQL finds a free slot in a heap page (or allocates a new page).
  3. Tuple Creation: A new row is created with:
    • xmin = current transaction ID
    • xmax = null
  4. WAL Logging: The change is written to the WAL buffer first.
  5. Page Modification: The new tuple is inserted into the shared buffer.
  6. Commit: On commit, the WAL is flushed to disk (fsync), making the change durable.

4. How UPDATE Works

UPDATE in PostgreSQL does not modify the existing row. It creates a new version of the row.

  1. Find the row using the same visibility rules as SELECT.
  2. Mark old row as dead: Set xmax = current transaction ID on the old tuple.
  3. Insert new row: Create a completely new tuple with:
    • xmin = current transaction ID
    • xmax = null
    • Updated column values
  4. Update indexes (if needed): New index entries point to the new tuple.
  5. WAL Logging: Both the old row’s xmax change and the new tuple are logged in WAL.
  6. Commit: Changes become visible to other transactions after commit.

Important: The old row version remains in the table until VACUUM cleans it up.

5. How DELETE Works

DELETE also uses MVCC — it doesn’t remove the row immediately.

  1. Locate the row using visibility rules.
  2. Mark as deleted: Set xmax = current transaction ID on the tuple.
  3. WAL Logging: The change is logged.
  4. Commit: The row becomes invisible to new transactions.
  5. Physical Removal: The row is not physically deleted from disk until VACUUM runs.

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: https://www.linkedin.com/in/rajasekhar-amudala/

PostgreSQL

Uncategorized, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PSQL

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

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/