Scribd
Upload
10 views5 pages

PostgreSQL WAL Levels Explained

Uploaded by

vignesh murugan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views5 pages

PostgreSQL WAL Levels Explained

Uploaded by

vignesh murugan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

wal_level = replica

• Purpose: The replica level is used for physical replication (streaming replication)
and point-in-time recovery (PITR).
• Use case: If you're setting up a physical standby server (for high availability) or
performing backup and restore operations.
• What is logged:
o Enough WAL data to ensure that a standby server can exactly replicate the
primary server.
o It logs all changes to the database but in a format designed for exact block-
level replication.
• Impact on performance: This level logs more information than minimal but is still
optimized for performance since it only writes the data needed for physical
replication.

Best suited for:

• Physical streaming replication.


• Point-in-time recovery (PITR).
• Disaster recovery solutions that rely on exact block replication.

2. wal_level = logical

• Purpose: The logical level is used for logical replication, which allows replicating
subsets of data (specific tables, rows, or even specific changes) to another server, and
for using logical decoding (e.g., decoding WAL logs for change data capture (CDC)).
• Use case: This level is necessary when using logical replication or features like
logical decoding, which are often used in data integration, replication of partial data
(e.g., specific tables), or in cases where you want to capture data changes in real time.
• What is logged:
o All the information needed for logical replication, which includes changes to
individual rows in a format that can be used by external systems to replay
these changes.
o More detailed logging than replica, including changes at the row level with
additional metadata needed for logical decoding.
• Impact on performance: Since it logs more detailed information than replica, there
can be a higher performance overhead. However, it enables powerful features like
selectively replicating specific changes to a downstream system.

Best suited for:

• Logical replication.
• Streaming real-time data changes to an external system (change data capture).
• Database migrations or replication of subsets of data.
• Integration with external systems (e.g., event-driven architectures).

Key Differences:
Feature replica logical
Type of Physical replication (entire Logical replication (partial or entire
Replication database) database)
Logs enough information for Logs detailed row-level changes
Level of Detail
exact replication with extra metadata
Logical replication, change data
Use Case Standby servers, PITR
capture
Performance Lower than logical due to less Slightly higher due to detailed logs
Impact detailed logging for logical decoding
Replication Specific tables, rows, or even
Whole database replication
Granularity individual changes

Summary:

• Use wal_level = replica if you are only interested in physical replication or PITR.
• Use wal_level = logical if you need logical replication, logical decoding, or
change data capture.

Logical replication provides more flexibility but comes at a higher performance cost
compared to physical replication.

VACUUM
-----------
Purpose: Removes dead rows and marks space available for reuse.
Space Handling: Does not return space to the operating system.
Space Reclamation: Space is reclaimed only if obsolete rows are at the end of a table.

VACUUM FULL
------------------
Purpose: More aggressive than regular VACUUM.
Compaction: Compacts tables by creating a completely new version of the table without dead space.
Time and Resources: Takes more time and requires extra disk space for the new copy of the table
until the operation completes.

Creating vacuum_test table with data

postgres=# CREATE TABLE vacuum_test (id INT PRIMARY KEY, name


VARCHAR NOT NULL);
CREATE TABLE
postgres=# insert into vacuum_test select
generate_series(1,1000000),md5(generate_series(1,1000000)::text);
INSERT 0 1000000
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size |
Description
--------+-------------+-------+----------+-------------+--------+---
----------
public | color | table | postgres | permanent | 16 kB |
public | employee | table | postgres | permanent | 104 kB |
public | student | table | postgres | permanent | 16 kB |
public | test | table | postgres | permanent | 75 MB |
public | vacuum_test | table | postgres | permanent | 65 MB |
(5 rows)

vacuum_test Table size is 65MB

Deleting the half of the data


postgres=# delete from vacuum_test where id < 500000;
DELETE 499999

After deleting also space is same 65MB , it has not reduced, Space is not release from the table

postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size |
Description
--------+-------------+-------+----------+-------------+--------+---
----------
public | color | table | postgres | permanent | 16 kB |
public | employee | table | postgres | permanent | 104 kB |
public | student | table | postgres | permanent | 16 kB |
public | test | table | postgres | permanent | 75 MB |
public | vacuum_test | table | postgres | permanent | 65 MB |
(5 rows)
postgres=# select schemaname,relname,n_dead_tup,last_autovacuum from
pg_stat_user_tables where relname='vacuum_test';
schemaname | relname | n_dead_tup | last_autovacuum
------------+-------------+------------+----------------------------
-----
public | vacuum_test | 0 | 2024-09-21
15:08:46.55958+05:30
(1 row)

Before Full Vacuum file path


postgres=# select pg_relation_filepath('vacuum_test'::regclass);
pg_relation_filepath
----------------------
base/14448/16445
(1 row)

postgres=# \dt+ vacuum_test


List of relations
Schema | Name | Type | Owner | Persistence | Size |
Description
--------+-------------+-------+----------+-------------+-------+----
---------
public | vacuum_test | table | postgres | permanent | 65 MB |
(1 row)
`

postgres=# vacuum FULL vacuum_test;


Table size and path has changed after vacuum FULL:

postgres=# \dt+ vacuum_test


List of relations
Schema | Name | Type | Owner | Persistence | Size |
Description
--------+-------------+-------+----------+-------------+-------+----
---------
public | vacuum_test | table | postgres | permanent | 33 MB |
(1 row)
postgres=# select pg_relation_filepath('vacuum_test'::regclass);
pg_relation_filepath
----------------------
base/14448/16455
(1 row)

Common questions

Powered by AI

'wal_level=replica' supports strategies that require exact block-level replication, critical for maintaining a standby server that mirrors the primary server exactly, which is vital for disaster recovery. In contrast, 'wal_level=logical' allows selective data replication, which might complicate a full data restoration but provides the flexibility to maintain a lean replication environment focused on critical data slices for rapid recovery of specific components .

VACUUM removes dead rows and marks space for reuse but does not return it to the operating system; space is reclaimed only if obsolete rows are at the end of the table. Meanwhile, VACUUM FULL aggressively compacts the table by creating a new version without dead space, reducing the database size significantly, but it requires extra disk space and takes more time due to the complete table rebuild .

A database administrator should prioritize using physical replication over logical replication when the primary goal is to maintain an exact copy of the entire database for high availability, disaster recovery, or to support a read-only standby server, as it offers more straightforward, full-database copying with less performance overhead compared to logical replication, which is more selective and resource-intensive .

After running VACUUM FULL on the 'vacuum_test' table, its size reduces significantly from 65 MB to 33 MB. This is because VACUUM FULL compacts the table, removing dead rows and rebuilding the table to occupy only the space needed by the remaining live data, thus reclaiming unused space .

WAL logs play a crucial role in maintaining data integrity during replication by ensuring all changes are logged and can be replayed to reconstruct accurate database states on standby systems. In physical replication, WAL logs capture all necessary data to exactly reproduce the primary server, while in logical replication, they provide detailed change data with metadata to support selective replication strategies, thereby ensuring data accuracy and consistency across different database systems .

Not performing a VACUUM FULL after significant row deletions can result in inefficient use of database space, as the table size remains the same and the deleted space is not reclaimed. This can lead to a larger-than-necessary database footprint and potential performance degradation because of unused dead space, impacting storage costs and future query performance .

'wal_level' set to 'logical' is preferred when logical replication or real-time data change streaming is needed, such as selectively replicating subsets of data or capturing changes in real-time for integration with external systems. It offers more flexibility compared to 'replica', which is suited for whole database replication including physical standby servers and disaster recovery .

The implementation of VACUUM FULL affects the file path of a PostgreSQL table by moving the file to a new location, as indicated by the change in its file path identifier. This occurs because VACUUM FULL creates a new version of the table, necessitating a file path update to reflect this complete rebuilding of the data structure .

The 'wal_level' set to 'replica' logs enough information for exact block-level replication, mainly focusing on efficient physical replication and point-in-time recovery, thereby optimizing performance. However, 'wal_level' set to 'logical' logs detailed row-level changes and includes additional metadata necessary for logical decoding, which results in a higher performance overhead. This level is essential for tasks like selectively replicating specific changes or streaming real-time data changes to external systems .

Additional metadata is logged during 'wal_level=logical' settings to facilitate detailed change data capture and replication of specific subsets of data. This enables external systems to decode these logs accurately and apply changes selectively, essential for tasks such as real-time integration and logical replication where precise control over data streaming is necessary .

You might also like