Tag Archives: PostgreSQL performance tuning

PostgreSQL Configuration File

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

PostgreSQL Configuration File (postgresql.conf & auto.conf)

Table of Contents

1. What is postgresql.conf?

postgresql.conf is the main configuration file for PostgreSQL. It controls nearly every aspect of the server’s behavior — memory, connections, WAL, logging, query planner, autovacuum, and more.

But there is actually a second file most DBAs overlook:

  • postgresql.conf — the main file you edit by hand.
  • postgresql.auto.conf — created and managed by ALTER SYSTEM. It is read after postgresql.conf, so its values always win.

This post walks through both files, shows the difference between static (restart-required) and dynamic (reload-only) parameters, demonstrates the four levels of parameter scope, and shows what happens when things go wrong.

2. Locate the Configuration Files

The location varies by OS and installation method. Ask PostgreSQL directly:

postgres=# SHOW data_directory;
    data_directory
----------------------
 /pgData/pgsql17/data
(1 row)

postgres=# SHOW config_file;
             config_file
--------------------------------------
 /pgData/pgsql17/data/postgresql.conf
(1 row)

postgres=# SHOW hba_file;
             hba_file
----------------------------------
 /pgData/pgsql17/data/pg_hba.conf
(1 row)

postgres=# SHOW ident_file;
             ident_file
------------------------------------
 /pgData/pgsql17/data/pg_ident.conf
(1 row)

postgres=#

Key fact: postgresql.auto.conf always lives in the same directory as postgresql.conf (normally $PGDATA). It is created and updated only by ALTER SYSTEM.

3. Understand pg_settings — The Columns That Matter

The pg_settings view is your single source of truth for every configuration parameter. Here are the columns you need to know:

postgres=# SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting | unit |  context   |       source       |              sourcefile              | sourceline | pending_restart
-----------------+---------+------+------------+--------------------+--------------------------------------+------------+-----------------
 max_connections | 100     |      | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf |          1 | f   <--------------
(1 row)

postgres=#

Column breakdown:

  • context — WHO/WHEN can change it. This is what defines static vs dynamic.
  • source — Where the current active value came from (default, configuration file, override, session, etc.).
  • sourcefile — The actual file that set it (.conf or .auto.conf).
  • sourceline — Line number within that file.
  • pending_restart — true = value was changed but needs a RESTART to apply.

4. Static vs Dynamic Parameters

Every parameter has a context that determines how and when it can be changed.

STATIC — context = postmaster (restart required)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster'
ORDER BY name
LIMIT 20;
                name                 |               setting                |  context
-------------------------------------+--------------------------------------+------------
 archive_mode                        | on                                   | postmaster
 autovacuum_freeze_max_age           | 200000000                            | postmaster
 autovacuum_max_workers              | 3                                    | postmaster
 autovacuum_multixact_freeze_max_age | 400000000                            | postmaster
 bonjour                             | off                                  | postmaster
 bonjour_name                        |                                      | postmaster
 cluster_name                        |                                      | postmaster
 commit_timestamp_buffers            | 32                                   | postmaster
 config_file                         | /pgData/pgsql17/data/postgresql.conf | postmaster
 data_directory                      | /pgData/pgsql17/data                 | postmaster
 data_sync_retry                     | off                                  | postmaster
 debug_io_direct                     |                                      | postmaster
 dynamic_shared_memory_type          | posix                                | postmaster
 event_source                        | PostgreSQL                           | postmaster
 external_pid_file                   |                                      | postmaster
 hba_file                            | /pgData/pgsql17/data/pg_hba.conf     | postmaster
 hot_standby                         | on                                   | postmaster
 huge_pages                          | try                                  | postmaster
 huge_page_size                      | 0                                    | postmaster
 ident_file                          | /pgData/pgsql17/data/pg_ident.conf   | postmaster
(20 rows)

postgres=#

DYNAMIC — everything else (no restart needed)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context IN ('sighup','backend','superuser','user')
ORDER BY context, name
LIMIT 20;
                 name                  |            setting            | context
---------------------------------------+-------------------------------+---------
 ignore_system_indexes                 | off                           | backend
 post_auth_delay                       | 0                             | backend
 allow_alter_system                    | on                            | sighup
 archive_cleanup_command               |                               | sighup
 archive_command                       | cp %p /pgArch/pgsql17/arch/%f | sighup
 archive_library                       |                               | sighup
 archive_timeout                       | 0                             | sighup
 authentication_timeout                | 60                            | sighup
 autovacuum                            | on                            | sighup
 autovacuum_analyze_scale_factor       | 0.1                           | sighup
 autovacuum_analyze_threshold          | 50                            | sighup
 autovacuum_naptime                    | 60                            | sighup
 autovacuum_vacuum_cost_delay          | 2                             | sighup
 autovacuum_vacuum_cost_limit          | -1                            | sighup
 autovacuum_vacuum_insert_scale_factor | 0.2                           | sighup
 autovacuum_vacuum_insert_threshold    | 1000                          | sighup
 autovacuum_vacuum_scale_factor        | 0.2                           | sighup
 autovacuum_vacuum_threshold           | 50                            | sighup
 autovacuum_work_mem                   | -1                            | sighup
 bgwriter_delay                        | 200                           | sighup
(20 rows)

postgres=#

Count parameters per context

postgres=# SELECT context, count(*) AS num_params
FROM pg_settings
GROUP BY context
ORDER BY num_params DESC;
      context      | num_params
-------------------+------------
 user              |        145
 sighup            |         98
 postmaster        |         65
 superuser         |         46
 internal          |         19
 superuser-backend |          4
 backend           |          2
(7 rows)

postgres=#

Context cheat sheet:

  • postmaster → RESTART required. Cannot change on the fly.
  • sighup → RELOAD is enough (pg_reload_conf() or SIGHUP).
  • backend → Reload + only takes effect for new connections.
  • superuser / user → Can be changed live with SET, per session.

5. pg_file_settings — What’s Actually Written in the Files

pg_file_settings shows the raw contents of the conf files, independent of whether the value is currently applied.

postgres=# SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
ORDER BY sourcefile, sourceline
LIMIT 15;
              sourcefile              | sourceline |            name            |      setting      | applied | error
--------------------------------------+------------+----------------------------+-------------------+---------+-------
 /pgData/pgsql17/data/postgresql.conf |          1 | max_connections            | 100               | t       |
 /pgData/pgsql17/data/postgresql.conf |          2 | shared_buffers             | 128MB             | t       |
 /pgData/pgsql17/data/postgresql.conf |          3 | dynamic_shared_memory_type | posix             | t       |
 /pgData/pgsql17/data/postgresql.conf |          4 | max_wal_size               | 1GB               | t       |
 /pgData/pgsql17/data/postgresql.conf |          5 | min_wal_size               | 80MB              | t       |
 /pgData/pgsql17/data/postgresql.conf |          6 | log_destination            | stderr            | t       |
 /pgData/pgsql17/data/postgresql.conf |          7 | logging_collector          | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |          8 | log_directory              | log               | t       |
 /pgData/pgsql17/data/postgresql.conf |          9 | log_filename               | postgresql-%a.log | t       |
 /pgData/pgsql17/data/postgresql.conf |         10 | log_rotation_age           | 1d                | t       |
 /pgData/pgsql17/data/postgresql.conf |         11 | log_rotation_size          | 0                 | t       |
 /pgData/pgsql17/data/postgresql.conf |         12 | log_truncate_on_rotation   | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |         13 | log_line_prefix            | %m [%p]           | t       |
 /pgData/pgsql17/data/postgresql.conf |         14 | log_timezone               | Asia/Singapore    | t       |
 /pgData/pgsql17/data/postgresql.conf |         15 | datestyle                  | iso, dmy          | t       |
(15 rows)

postgres=#

Key columns:

  • applied = false — the setting was overridden by a later file. For example, postgresql.auto.conf is read after postgresql.conf, so it wins.
  • error — non-null if that specific line failed to parse or apply.

6. Dynamic Parameter Change — Reload Only, No Restart

log_min_duration_statement has context = 'sighup', so a reload is enough.

-- Change the value using ALTER SYSTEM
postgres=# ALTER SYSTEM SET log_min_duration_statement = 500;
ALTER SYSTEM
postgres=#

-- Apply with reload (no restart needed)
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Confirm it applied immediately
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |  context  |       source       |                sourcefile                 | pending_restart
----------------------------+---------+-----------+--------------------+-------------------------------------------+-----------------
 log_min_duration_statement | 500     | superuser | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f
(1 row)

postgres=#
postgres=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 500ms   <-------------------
(1 row)

postgres=#

Change is LIVE right now. setting = 500, source = 'override', sourcefile = postgresql.auto.conf, pending_restart = false. No restart was needed.

7. Static Parameter Change — Reload is NOT Enough

max_connections has context = 'postmaster'. A reload picks up the file change, but the running server cannot apply it without a restart.

-- Change the value
postgres=# ALTER SYSTEM SET max_connections = 250;
ALTER SYSTEM
postgres=#

-- Reload
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Check the setting — the OLD value is still active!
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |              sourcefile              | pending_restart
-----------------+---------+------------+--------------------+--------------------------------------+-----------------
 max_connections | 100     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf | t   <----------------
(1 row)

postgres=#

[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 13:43:50.273 +08 [3390] LOG:  redirecting log output to logging collector process
2026-09-25 13:43:50.273 +08 [3390] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |                sourcefile                 | pending_restart
-----------------+---------+------------+--------------------+-------------------------------------------+-----------------
 max_connections | 250     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f  <-------------
(1 row)

postgres=#

pending_restart = true. The file has the new value (250), but the running server still shows 100. A postmaster-context parameter requires a full restart to take effect. pg_reload_conf() alone is not enough.

8. How postgresql.auto.conf Overrides postgresql.conf

Every ALTER SYSTEM SET writes to postgresql.auto.conf, not to postgresql.conf. Since postgresql.auto.conf is parsed last, its values always win.

postgres=# SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('log_min_duration_statement','max_connections')
ORDER BY name;
            name            | setting |                sourcefile                 | sourceline
----------------------------+---------+-------------------------------------------+------------
 log_min_duration_statement | 500     | /pgData/pgsql17/data/postgresql.auto.conf |          3
 max_connections            | 250     | /pgData/pgsql17/data/postgresql.auto.conf |          4
(2 rows)

postgres=#

Verify from the OS shell:

-- postgresql.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf|grep max_connections
max_connections = 100                   # (change requires restart)
[postgres@pgdb01 ~]$

-- postgresql.auto.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep max_connections
max_connections = '250'
[postgres@pgdb01 ~]$

Both files mention max_connections with different values. The auto.conf value (250) is what PostgreSQL uses. This can cause confusion if you edit postgresql.conf and forget that auto.conf is overriding it.

9. Reset a Parameter Back to postgresql.conf’s Value

ALTER SYSTEM RESET removes the line from postgresql.auto.conf entirely, so PostgreSQL falls back to postgresql.conf (or the built-in default).

postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |       source       |                sourcefile
----------------------------+---------+--------------------+-------------------------------------------
 log_min_duration_statement | 500     | configuration file | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# ALTER SYSTEM RESET log_min_duration_statement;
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting | source  | sourcefile
----------------------------+---------+---------+------------
 log_min_duration_statement | -1      | default |
(1 row)

postgres=#

[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep log_min_duration_statement
[postgres@pgdb01 ~]$

Back to postgresql.conf. The auto.conf override is gone. source now reads configuration file and sourcefile points back to postgresql.conf.

10. Set a WRONG Value and See the Error

Scenario A: ALTER SYSTEM catches it immediately

postgres=# ALTER SYSTEM SET shared_buffers = 'not_a_number';
ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
postgres=#

ALTER SYSTEM validates most values up front. The bad value is rejected immediately — nothing is written to auto.conf, nothing to reload.

Scenario B: Hand-edit the file (the real-world “fat-finger” scenario)

# Someone accidentally edits postgresql.conf directly:

[postgres@pgdb01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp
[postgres@pgdb01 ~]$


[postgres@pgdb01 ~]$ echo "work_mem = 'banana'" >> /pgData/pgsql17/data/postgresql.conf
[postgres@pgdb01 ~]$


# Then reloads:
[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$

Check the PostgreSQL log:

[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied
[postgres@pgdb01 ~]$

# Manually edit and update right Value
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf | grep work_mem
work_mem = '4MB'
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$
[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied

-- New output

2026-09-25 14:03:56.537 +08 [3390] LOG:  received SIGHUP, reloading configuration files
[postgres@pgdb01 ~]$

For a sighup-context bad value: PostgreSQL logs the error but keeps the previous good value running. The server does not crash. Fix the typo and reload again.

For a postmaster-context bad value: If you restart with a malformed listen_addresses or shared_buffers, the server will fail to start entirely. The log will show a fatal parse error.

11. Find Every Parameter Pending a Restart

This is the single most useful query for a DBA before scheduling a restart window. It tells you exactly which staged changes are waiting.

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=# ALTER SYSTEM SET max_connections=300;  <---- static parameter
ALTER SYSTEM
postgres=# 

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
      name       | current_running_value |  context   |                sourcefile
-----------------+-----------------------+------------+-------------------------------------------
 max_connections | 250                   | postmaster | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;
 still_pending
---------------
             1  <----- means something require to restart 
(1 row)

postgres=#
postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 250     | postmaster | t  <----------------
(1 row)

postgres=#

DBA tip: Run this query before every planned restart. If it returns zero rows, there is nothing staged and the restart won’t change any configuration. This prevents unnecessary downtime.

12. Restart and Confirm pending_restart Clears

# Restart the server (OS shell)
[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 14:16:29.475 +08 [3600] LOG:  redirecting log output to logging collector process
2026-09-25 14:16:29.475 +08 [3600] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

After the restart, reconnect and verify:

postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 300     | postmaster | f   <-------------
(1 row)

postgres=#

-- Confirm nothing else is pending
postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;

 still_pending
---------------
             0  <--------- means nothing pending for restart
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=#

max_connections is now 300 and pending_restart = false. The restart applied the staged change.

13. Parameter Scope — Cluster / Database / User / Session

PostgreSQL lets you set most dynamic parameters at four different scopes, each narrower than the last. The narrowest scope always wins.

Precedence (narrowest wins):

SESSION  >  ROLE+DATABASE  >  ROLE  >  DATABASE  >  CLUSTER  >  built-in default

13a. CLUSTER Level (ALTER SYSTEM)

Affects the whole server — every database, every user, every session.

postgres=# SHOW work_mem;
 work_mem
----------
 4MB <------
(1 row)

postgres=# ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 8MB   <----------------------
(1 row)

postgres=#

13b. DATABASE Level (overrides cluster for one database)

Only sessions connecting to this specific database get this value.

postgres=# ALTER DATABASE postgres SET work_mem = '16MB';
ALTER DATABASE
postgres=#

-- Reconnect to "postgres" to see it take effect
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".
postgres=#

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 16MB  <-------------
(1 row)

postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname |    setconfig
----------+---------+-----------------
 postgres |         | {work_mem=16MB}
(1 row)

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <---------------
(1 row)

raj=#

13c. USER / ROLE Level (overrides cluster and database for one role)

postgres=# ALTER ROLE postgres SET work_mem = '32MB';
ALTER ROLE
postgres=#

-- Combine ROLE + DATABASE for maximum specificity
postgres=# ALTER ROLE postgres IN DATABASE postgres SET work_mem = '64MB';
ALTER ROLE
postgres=#

-- Check the catalog
postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname  |    setconfig
----------+----------+-----------------
 postgres | postgres | {work_mem=64MB}    <--------- ROLE(USER)+DB level
 postgres |          | {work_mem=16MB}    <--------- Database level (overrides cluster for one database)
          | postgres | {work_mem=32MB}    <--------- ROLE(USER LEVEL)  overrides cluster and database for one role
(3 rows)

postgres=#

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <----- CLUSTER LEVEL WE SET
(1 row)

raj=#

The postgres role connecting to the postgres database gets 64MB (the combined ROLE+DATABASE row wins over the role-only row, which wins over the database-only row, which wins over the cluster-wide 8MB).

PostgreSQL checks limits in this order (from most specific to least specific):

  1. ROLE + DATABASE together = 64MB 
  2. ROLE only = 32MB
  3. DATABASE only = 16MB
  4. Whole cluster = 8MB 

13d. SESSION Level (overrides everything, but only for this connection)

[postgres@lxceftsgvdb01 ~]$ psql -d postgres -U postgres
postgres=# SHOW work_mem;
 work_mem
----------
 64MB  <-----------------
(1 row)

postgres=#


-- SET lasts for this session only
postgres=# SET work_mem = '128MB';
SET
postgres=#

postgres=# SHOW work_mem;
 work_mem
----------
 128MB <----------------
(1 row)

postgres=#

-- SET LOCAL is even narrower — lasts only for the current transaction
postgres=# BEGIN;
BEGIN
postgres=*# SET LOCAL work_mem = '256MB';
SET
postgres=*# SHOW work_mem;
 work_mem
----------
 256MB   <---------------- Changed from 128MB to 256MB
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 128MB  <--------------- Came back to session setting 128MB
(1 row)

postgres=#

After COMMIT: work_mem reverted to 128MB (the session-level SET), not 256MB. SET LOCAL only lives inside the transaction. Disconnecting entirely drops the session-level SET and falls back to the role/database/cluster chain.

13e. See the Full Precedence Chain at a Glance

postgres=# SELECT name, setting AS effective_value, source, sourcefile
FROM pg_settings
WHERE name = 'work_mem';
   name   | effective_value | source  | sourcefile
----------+-----------------+---------+------------
 work_mem | 131072          | session |
(1 row)

postgres=#

The source column tells you which level actually won for the current session:

  • session → a SET / SET LOCAL you ran yourself
  • database → ALTER DATABASE ... SET
  • user → ALTER ROLE ... SET
  • override → ALTER SYSTEM (cluster level, via postgresql.auto.conf)
  • configuration file → plain postgresql.conf
  • default → built-in default, nothing overrode it

13f. Classify Every Parameter by Settable Scope

postgres=# SELECT name,
       context,
       CASE context
         WHEN 'internal'           THEN 'READ-ONLY (compile-time)'
         WHEN 'postmaster'         THEN 'CLUSTER only (restart required)'
         WHEN 'sighup'             THEN 'CLUSTER only (reload required)'
         WHEN 'backend'            THEN 'CLUSTER/DATABASE/ROLE (connect time only)'
         WHEN 'superuser-backend'  THEN 'CLUSTER/DATABASE/ROLE (connect time, superuser)'
         WHEN 'superuser'          THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (superuser)'
         WHEN 'user'               THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (any user)'
       END AS settable_scope
FROM pg_settings
ORDER BY context, name
LIMIT 15;
         name          | context  |              settable_scope
-----------------------+----------+-------------------------------------------
 ignore_system_indexes | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 post_auth_delay       | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 block_size            | internal | READ-ONLY (compile-time)
 data_checksums        | internal | READ-ONLY (compile-time)
 data_directory_mode   | internal | READ-ONLY (compile-time)
 debug_assertions      | internal | READ-ONLY (compile-time)
 huge_pages_status     | internal | READ-ONLY (compile-time)
 in_hot_standby        | internal | READ-ONLY (compile-time)
 integer_datetimes     | internal | READ-ONLY (compile-time)
 max_function_args     | internal | READ-ONLY (compile-time)
 max_identifier_length | internal | READ-ONLY (compile-time)
 max_index_keys        | internal | READ-ONLY (compile-time)
 segment_size          | internal | READ-ONLY (compile-time)
 server_encoding       | internal | READ-ONLY (compile-time)
 server_version        | internal | READ-ONLY (compile-time)
(15 rows)

postgres=#

Quick counts by scope:

-- Fully flexible (all 5 levels)
postgres=# SELECT count(*) AS fully_flexible
FROM pg_settings WHERE context IN ('user','superuser');
 fully_flexible
----------------
            191
(1 row)

postgres=#


-- Cluster-only (conf file or ALTER SYSTEM, never per-session)
postgres=# SELECT count(*) AS cluster_only
FROM pg_settings WHERE context IN ('postmaster','sighup');
 cluster_only
--------------
          163
(1 row)

postgres=#


-- Connect-time only (database/role, but not SET mid-session)
postgres=# SELECT count(*) AS connect_time_only
FROM pg_settings WHERE context IN ('backend','superuser-backend');
 connect_time_only
-------------------
                 6
(1 row)

postgres=#

14. Quick Cheatsheet

TaskCommand
Find config fileSHOW config_file;
View a settingSHOW work_mem; or SELECT * FROM pg_settings WHERE name = 'work_mem';
See non-default settingsSELECT * FROM pg_settings WHERE source != 'default';
See raw file contentsSELECT * FROM pg_file_settings;
Static vs DynamicSELECT name, context FROM pg_settings WHERE context = 'postmaster';
Change at cluster levelALTER SYSTEM SET work_mem = '64MB';
Change at database levelALTER DATABASE mydb SET work_mem = '64MB';
Change at role levelALTER ROLE myuser SET work_mem = '64MB';
Change at session levelSET work_mem = '128MB';
Change for one transactionSET LOCAL work_mem = '256MB';
Remove auto.conf overrideALTER SYSTEM RESET work_mem;
Apply without restartSELECT pg_reload_conf();
Find pending restartsSELECT * FROM pg_settings WHERE pending_restart = true;
Apply with restartpg_ctl restart -D $PGDATA
Check for config errorsSELECT * FROM pg_file_settings WHERE error IS NOT NULL;

15. Sample postgresql.conf

-- Server Config
# =============================================================================
# RAM   : 128 GB & CPU(s): 8  & OS: Redhat Linux 8
# =============================================================================

-- Sample parameters

# =============================================================================
# CONNECTION SETTINGS
# =============================================================================
listen_addresses = '*'                          
port = 6543                                     
max_connections = 1500
unix_socket_permissions = 0700

# =============================================================================
# SSL/TLS SECURITY SETTINGS
# =============================================================================
ssl = on
ssl_ca_file = '/pgData/pgsql17/certs/root.crt'
ssl_cert_file = '/pgData/pgsql17/certs/server.crt'
ssl_key_file = '/pgData/pgsql17/certs/server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'       
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# =============================================================================
# MEMORY SETTINGS
# =============================================================================
shared_buffers = 32GB                           # min 128kB
work_mem = 2796kB                               # min 64kB
maintenance_work_mem = 2GB                      # min 1MB
dynamic_shared_memory_type = posix              
effective_cache_size = 96GB
temp_tablespaces = 'pg_tstmp'

# =============================================================================
# PARALLEL EXECUTION & WORKER PROCESSES
# =============================================================================
effective_io_concurrency = 200                  # 1-1000; 0 disables prefetching
max_worker_processes = 8                        # (change requires restart)
max_parallel_workers_per_gather = 4             # taken from max_parallel_workers
max_parallel_maintenance_workers = 4            # taken from max_parallel_workers
max_parallel_workers = 8                        # maximum number of max_worker_processes that

# =============================================================================
# QUERY PLANNING & OPTIMIZATION
# =============================================================================
random_page_cost = 1.1                          # same scale as above
default_statistics_target = 1500                # range 1-10000

# =============================================================================
# WRITE-AHEAD LOGGING (WAL) SETTINGS
# =============================================================================
wal_level = replica                             
wal_buffers = 16MB                              # min 32kB, -1 sets based on shared_buffers
wal_log_hints = on                              
full_page_writes = on                           
max_wal_senders = 10                            # max number of walsender processes
wal_keep_size = 128000                          # in megabytes; 0 disables
hot_standby = on                                # 'off' disallows queries during recovery

# =============================================================================
# CHECKPOINT & WAL SIZE SETTINGS
# =============================================================================
checkpoint_completion_target = 0.9              
max_wal_size = 4GB
min_wal_size = 1GB

# =============================================================================
# ARCHIVING 
# =============================================================================
archive_mode = on                               
archive_command = 'rsync -a %p /pgArch/pgsql17/arch/%f'

#archive_command = '/pgBackup/pgsql17/backup/arch_copy.sh %p %f && pgbackup'
#archive_cleanup_command = 'pg_archivecleanup /pgArch/pgsql17/arch/ %r'

# =============================================================================
# LOGGING SETTINGS
# =============================================================================
logging_collector = on                          
log_destination = 'stderr'
log_directory = '/pgLog/pgsql17/logs'
log_filename = 'postgres-%Y-%m-%d_%HMS.log'
log_file_mode = 0600
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] user=%u,database=%d,app_name=%a client_host=%h '
log_lock_waits = on
log_statement = 'ddl'
log_min_duration_statement = 2000   # log statements taking longer than 2000ms
log_timezone = 'Asia/Singapore'

# Optional
#log_truncate_on_rotation = on       # truncate rather than append on rotation
#log_rotation_age = 1d               # Automatic rotation by time
#log_rotation_size = 0               # Automatic rotation by size (0=disabled)
#log_duration = off

# ========================================================================
# AUTOVACUUM SETTINGS
# ========================================================================
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 10000
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 600

# ========================================================================
# QUERY TRACKING SETTINGS
# ========================================================================
track_activities = on
track_counts = on
track_io_timing = off
track_activity_query_size = 10240

# ========================================================================
# QUERY PLANNING SETTINGS
# ========================================================================
seq_page_cost = 1.0                 # measured on an arbitrary scale
random_page_cost = 2.0              # same scale as above
cpu_tuple_cost = 0.03               # same scale as above
from_collapse_limit = 20
join_collapse_limit = 20            # 1 disables collapsing of explicit JOIN clauses
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

Visibility Map (VM) in PostgreSQL

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

Visibility Map (VM) in PostgreSQL

Table of Contents

1. What is the Visibility Map?

The Visibility Map (VM) is a tiny companion file stored alongside every PostgreSQL table. It tracks two boolean flags per 8 KB page:

  • all_visible: Are all rows on this page visible to all active transactions? If yes, PostgreSQL can skip visiting the heap page entirely during index-only scans.
  • all_frozen: Are all rows on this page frozen (their transaction IDs replaced with a special “FrozenXID”)? Frozen pages never need to be vacuumed for transaction ID wraparound prevention.

2. Prerequisites & Extension Setup

-- Enable extensions for VM and page-level inspection
CREATE EXTENSION IF NOT EXISTS pg_visibility;
CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

3. Create Demo Table & Insert Data

-- create a demo table
postgres=# DROP TABLE IF EXISTS demo_table;
DROP TABLE
postgres=# CREATE TABLE demo_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    status TEXT DEFAULT 'active'
);
CREATE TABLE
postgres=#


-- Insert 10,000 rows
postgres=# INSERT INTO demo_table (name, status)
SELECT
    'User_' || g,
    'active'
FROM generate_series(1, 10000) g;
INSERT 0 10000
postgres=#

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

postgres=#

[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65897*
-rw-------. 1 postgres postgres 524288 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897
-rw-------. 1 postgres postgres  24576 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897_fsm
-rw-------. 1 postgres postgres   8192 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897_vm
[postgres@pgdb01 ~]$

4. Check VM Before VACUUM

Right after inserting data, no pages have been “cleaned” by VACUUM yet. The VM should show everything as not visible.

-- Check VM status for the first 20 pages
postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'demo_table';
  relname   | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-------------+-----------------+--------------+------------------
 demo_table |             |                 |              |
(1 row)

postgres=# 
postgres=# SELECT
    blkno,
    all_visible,
    all_frozen
FROM pg_visibility('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
     5 | f           | f
     6 | f           | f
     7 | f           | f
     8 | f           | f
     9 | f           | f
    10 | f           | f
    11 | f           | f
    12 | f           | f
    13 | f           | f
    14 | f           | f
    15 | f           | f
    16 | f           | f
    17 | f           | f
    18 | f           | f
    19 | f           | f
(20 rows)

postgres=# 
postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages,
    count(*) FILTER (WHERE all_frozen) AS frozen_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
          64 |            0 |                 64 |            0
(1 row)

postgres=#

🔴 All pages are NOT visible. This means every index-only scan will be forced to visit the heap to verify row visibility. This is slow.

5. Run VACUUM & Watch VM Light Up

-- Run VACUUM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Check VM per page all pages visible now
postgres=# SELECT
    blkno,
    all_visible,
    all_frozen
FROM pg_visibility('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | f
     1 | t           | f
     2 | t           | f
     3 | t           | f
     4 | t           | f
     5 | t           | f
     6 | t           | f
     7 | t           | f
     8 | t           | f
     9 | t           | f
    10 | t           | f
    11 | t           | f
    12 | t           | f
    13 | t           | f
    14 | t           | f
    15 | t           | f
    16 | t           | f
    17 | t           | f
    18 | t           | f
    19 | t           | f
(20 rows)

postgres=#

-- Summary after VACUUM
postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages,
    count(*) FILTER (WHERE all_frozen) AS frozen_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
          64 |            64 |                 0 |            0
(1 row)

postgres=#

✅ All 64 pages are now all_visible = true! VACUUM confirmed that every row on every page is visible to all transactions, and recorded this in the VM.

6. VM Impact on Index-Only Scans

This is where the VM pays off. When all_visible = true, PostgreSQL can answer queries using only the index without ever touching the main table (heap).

-- Create an index on the name column
postgres=# CREATE INDEX idx_demo_name ON demo_table(name);
CREATE INDEX
postgres=#

-- Update planner statistics
postgres=# ANALYZE demo_table;
ANALYZE
postgres=#

-- Run an index-only scan
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..4.30 rows=1 width=9) (actual time=0.044..0.045 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 0
   Buffers: shared hit=1 read=2
 Planning:
   Buffers: shared hit=8 read=1
 Planning Time: 0.243 ms
 Execution Time: 0.067 ms
(8 rows)

postgres=#

Look for these two lines in the output:

 Index Only Scan using idx_demo_name on demo_table
   ...
   Heap Fetches: 0    <-- THIS IS THE VM IN ACTION!

What Heap Fetches: 0 means:

  • The query planner found the matching row in the index.
  • It checked the VM and saw the page is all_visible = true.
  • It skipped visiting the heap entirely because the VM guaranteed the row is visible.
  • Result: faster query, fewer disk reads.

7. Update Rows & Watch VM 

When you modify rows, the affected pages are no longer guaranteed to be all-visible. The VM flags flip back to false.

-- Update the first 1,000 rows

postgres=# UPDATE demo_table SET status = 'inactive' WHERE id BETWEEN 1 AND 1000;
UPDATE 1000
postgres=#

postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages
-------------+---------------+-------------------
          71 |            56 |                15
(1 row)

postgres=# 

-- Check index-only scan performance with dirty pages
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..8.30 rows=1 width=9) (actual time=0.106..0.117 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 2
   Buffers: shared hit=5 dirtied=3
 Planning Time: 0.124 ms
 Execution Time: 0.142 ms
(6 rows)

postgres=#

What changed:

 Index Only Scan using idx_demo_name on demo_table
   ...
   Heap Fetches: 2    <-- Now > 0! Had to visit the heap.

⚠️ Impact: Because User_500 lives on a page that is no longer all_visible, PostgreSQL was forced to fetch the actual heap page to verify the row’s visibility. This adds extra I/O.

8. VACUUM Again to Restore Visibility

-- VACUUM to clean dead tuples and restore VM flags
postgres=# VACUUM demo_table;
VACUUM
postgres=#

postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages
-------------+---------------+-------------------
          71 |            71 |                 0
(1 row)

postgres=#

-- Index-only scan should be efficient again
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..4.30 rows=1 width=9) (actual time=0.031..0.032 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.235 ms
 Execution Time: 0.057 ms
(8 rows)

postgres=#

 Heap Fetches: 0    <-- Back to zero! VM restored.

9. VACUUM FREEZE & the all_frozen Flag

The all_frozen flag is about transaction ID wraparound prevention. When a page is frozen, all its row transaction IDs are replaced with a special FrozenXID (value 2), meaning those rows will be visible to all future transactions forever. Frozen pages never need anti-wraparound vacuuming.


-- Create Demo Table and Insert Data 

postgres=# CREATE TABLE employees (id INT, name TEXT, salary DECIMAL);
CREATE TABLE
postgres=#
postgres=# INSERT INTO employees
SELECT i, 'Employee ' || i, 50000 + (i * 100) FROM generate_series(1, 100000) i;
INSERT 0 100000
postgres=#

-- VACUUM

postgres=# VACUUM employees;
VACUUM
postgres=#

-- Check BEFORE Freezing

postgres=# SELECT
    count(*) FILTER (WHERE all_frozen) AS frozen_pages,
    count(*) FILTER (WHERE NOT all_frozen) AS not_frozen_pages
FROM pg_visibility('employees');
 frozen_pages | not_frozen_pages
--------------+------------------
            0 |              735
(1 row)

postgres=#

-- Freeze all pages
postgres=# VACUUM FREEZE employees;
VACUUM
postgres=#

-- Check again
postgres=# SELECT
    count(*) FILTER (WHERE all_frozen) AS frozen_pages,
    count(*) FILTER (WHERE NOT all_frozen) AS not_frozen_pages
FROM pg_visibility('employees');
 frozen_pages | not_frozen_pages
--------------+------------------
          735 |                0
(1 row)

postgres=#

postgres=# SELECT count(*) AS total_pages, count(*) FILTER (WHERE all_visible) AS visible_pages, count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages, count(*) FILTER (WHERE all_frozen) AS frozen_pages FROM pg_visibility('employees');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
         735 |           735 |                 0 |          735
(1 row)

postgres=#
postgres=# SELECT blkno, all_visible, all_frozen
FROM pg_visibility('employees')
LIMIT 10;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | t
     1 | t           | t
     2 | t           | t
     3 | t           | t
     4 | t           | t
     5 | t           | t
     6 | t           | t
     7 | t           | t
     8 | t           | t
     9 | t           | t
(10 rows)

postgres=#

✅ All pages are now frozen. These pages will be skipped by future anti-wraparound autovacuum runs, saving significant I/O on large tables.

10. Quick Cheatsheet

ConceptDetail
What is VM?A bitmap tracking all_visible and all_frozen per 8 KB page.
all_visibleAll rows on the page are visible to all transactions. Enables Index-Only Scans with Heap Fetches: 0.
all_frozenAll rows are frozen (transaction IDs replaced). Page is exempt from anti-wraparound vacuum.
When does VM update?VACUUM sets flags to true. INSERT/UPDATE/DELETE set flags to false.
Biggest benefitIndex-Only Scans skip heap I/O entirely when pages are all_visible.
Inspect VMSELECT * FROM pg_visibility('table');
Disk fileStored as <relfilenode>_vm next to the main table file.
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

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

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 17 DBA Free Training Roadmap

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

PostgreSQL DBA Free Coaching

Free PostgreSQL DBA Training in Telugu for Beginners | Live Ongoing Batch | June 15 - July 31, 2026.
తెలుగులో నేర్చుకోండి · 5 Weeks · Online · Beginner Friendly · 100% Free

DBA Training Program
// 5-week structured training · 19 sessions · hands-on labs
5
Weeks
19
Sessions
100+
Topics
∞
Future Free
Week 01 Lab Setup, Linux & PostgreSQL Fundamentals
Session 01 Lab Setup & Storage Configuration ▾
Pre-requisites
  • Install VirtualBox on Windows (Do your self)
  • Install PgAdmin4 on Windows (Do your self)
  • Configure Oracle Linux 9.6 VM (will share link)
Linux VM Setup
  • Create Oracle Linux 9.6 VM
  • VM Hardware Sizing for PostgreSQL
  • Add Multiple Virtual Disks
Storage Configuration
  • Create Partitions
  • Create Filesystems
  • Mount Filesystems
  • Configure /etc/fstab
PostgreSQL Directory Layout
  • Create Mount Points: /pgData, /pgWal, /pgArch, /pgBackup, /pgLog
Session 02 Linux Administration for DBAs ▾
  • Basic Linux Commands
  • File and Directory Management
  • User and Group Management
  • Permissions and Ownership
  • Process Management
  • Service Management using systemctl
  • Network Commands
  • Disk and Memory Monitoring
Session 03 PostgreSQL 17 Installation ▾
Overview
  • PostgreSQL Overview and Versions
Installation Methods
  • RPM Installation
  • DNF/YUM Installation
  • Source Code Installation
Database Initialization
  • initdb
  • Custom WAL Location
  • PostgreSQL Service Configuration
Validation
  • Start PostgreSQL
  • Connect using psql
  • Connect using PgAdmin4
Session 04 PostgreSQL Architecture ▾
Memory Architecture
  • Shared Buffers
  • WAL Buffers
  • Work Memory
  • Maintenance Work Memory
Process Architecture
  • Postmaster
  • Backend Processes
  • Checkpointer
  • Background Writer
  • WAL Writer
  • Autovacuum
Physical Storage Layout
  • Base Directory
  • Global Directory
  • WAL Directory
  • Tablespaces
Session 05 PostgreSQL Configuration Files ▾
  • postgresql.conf
  • postgresql.auto.conf
  • pg_hba.conf
  • pg_ident.conf
  • Reload vs Restart
Week 02 Administration & Security
Session 06 Startup, Shutdown ▾
  • PostgreSQL Startup Process
  • Smart Shutdown
  • Fast Shutdown (default)
  • Immediate Shutdown
Session 07 Database Administration ▾
  • Creating Databases
  • Creating Schemas
  • Schema Search Path
  • Roles and Users
  • Access Control
Session 08 Tablespaces Management ▾
  • pg_default
  • pg_global
  • Custom Tablespaces
  • Move Objects Between Tablespaces
  • Tablespace Monitoring
Session 09 Security ▾
  • pg_hba.conf
  • Authentication Methods
  • pg_ident.conf
  • .pgpass
  • SSL/TLS Setup
Session 10 Vacuum and Analyze ▾
  • MVCC Concepts
  • Dead Tuples
  • VACUUM
  • VACUUM FULL
  • ANALYZE
  • Autovacuum
Week 03 Backup & Recovery
Session 11 WAL Archiving ▾
  • WAL Fundamentals
  • Archive Mode
  • Archive Command
Session 12 Logical Backup and Restore ▾
  • pg_dump
  • pg_dumpall
  • Backup Formats
  • pg_restore
  • Restore using psql
Session 13 Physical Backup and Restore ▾
  • pg_basebackup
  • Full Cluster Backup
  • Database Refresh on New Server
Session 14 PITR — Point-in-Time Recovery ▾
  • PITR Concepts and Architecture
  • PITR Demonstration (hands-on)
Session 15 Full Recovery & Disaster Recovery ▾
  • Full Database Recovery
  • Disaster Recovery Scenarios
Week 04 Replication, Failover
Session 16 Streaming Replication ▾
  • Primary Configuration
  • Replica Configuration
  • Base Backup for Replica
  • Verify Replication
Session 17 Replication Administration ▾
  • Replication Slots
  • Monitoring Replication
  • Async Replication
  • Sync Replication
  • Convert from ASYNC to SYNC
Session 18 Failover and pg_rewind ▾
  • Manual Failover
  • Promote Replica
  • pg_rewind
  • Rejoin Old Primary as Replica
Week 05 Upgrades
Session 19 PostgreSQL Upgrades & Final Lab ▾
  • Minor Version Upgrade
  • Major Version Upgrades
  • pg_upgrade
  • Upgrade using pg_dump/pg_restore
  • Upgrade Validation
  • Final End-to-End Lab
Future Batch Advanced Topics — Free Training
// Topics reserved for the next advanced batch · access is free
EXPLAIN / EXPLAIN ANALYZE Index Types & Tuning Table Partitioning Performance Tuning PostgreSQL Extensions PgBouncer pgmetrics pgcollector pgbadger Repmgr Patroni Maintenance Operations Oracle → PostgreSQL (Ora2Pg) Advanced Data Migration Logical Replication Multi-Node HA Architectures

PostgreSQL DBA Free Training Roadmap · Click any session card to expand topics

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/

Disable ARCHIVELOG Mode

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

How to Disable ARCHIVELOG Mode

Table of Contents

1. Verify Existing Archive Mode
2. Edit the archive settings
3. Restart PostgreSQL
4. Verify Current Mode
5. Verify WAL Archiving Behavior

1. Verify Existing Archive Mode

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

postgres=#

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <----- 
(1 row)

postgres=#

2. Edit the archive settings


[postgres@lxicbpgdsgv01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp_10sep2025
[postgres@lxicbpgdsgv01 ~]$ vi /pgData/pgsql17/data/postgresql.conf

#archive_mode = on
#archive_command = 'cp %p /pgArch/pgsql17/arch/%f'

3. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl start  postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Thu 2025-10-09 16:34:01 +08; 3s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 3492 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 3497 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 17.6M
        CPU: 92ms
     CGroup: /system.slice/postgresql-17.service
             ├─3497 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─3498 "postgres: logger "
             ├─3499 "postgres: checkpointer "
             ├─3500 "postgres: background writer "
             ├─3502 "postgres: walwriter "
             ├─3503 "postgres: autovacuum launcher "
             └─3504 "postgres: logical replication launcher "

Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] LOG:  redirecting log output to logging collector process
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] HINT:  Future log output will appear in directory "log".
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

4. Verify Current Mode

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off  <------ it's disabled
(1 row)

postgres=# SHOW archive_command;
 archive_command
-----------------
 (disabled) <-------
(1 row)

postgres=#

5. Verify WAL Archiving Behavior


postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# CHECKPOINT;
CHECKPOINT
postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# exit
postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=#
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgArch/pgsql17/arch/
total 0  <---- Archivelogs not generating
[postgres@lxicbpgdsgv01 ~]$

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/

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/