PostgreSQL WAL: The Backbone of Reliable and Scalable Databases

Ever wondered how PostgreSQL survives crashes without losing a byte of data?

Databases can crash, servers can fail, but losing data is not an option. PostgreSQL achieves this reliability with its WAL(write-ahead log) mechanism.

WAL records every changes before it's applied to the database, ensuring consistency and enabling quick recovery. It is the log of changes made to the database cluster which is replayed either as part of the database recovery process when a database isn’t shutdown correctly (such as when a crash occurs), or is used by standbys to replay the changes to replicate the database.

Working of WAL in 4 Steps-

• Step 1: When a change (INSERT, UPDATE, DELETE) occurs, PostgreSQL writes it to the WAL first.
• Step 2: Only after the WAL is safely written, the change is applied to the main database files.
• Step 3: If the database crashes, PostgreSQL can replay the WAL to recover all committed changes.
• Step 4: WAL ensures data consistency, durability, and quick crash recovery.

Experimenting with WAL

1. Check your PostgreSQL data directory
   SHOW data_directory;
   I installed it using brew so its /opt/homebrew/var/postgresql@14

2. Navigate to the WAL directory
   cd /opt/homebrew/var/postgresql@14/pg_wal

In pg_wal, files like 000000010000000000000001 are binary WAL segments that record all database changes before they are applied. The archive_status folder shows which WAL files are ready to be saved or backed up.

1. Get connected to your postgresql and run simple queries to create table and insert some data in it.

CREATE TABLE wal_demo (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO wal_demo(name) VALUES ('Shivam'), ('PostgreSQL'), ('WAL TEST');

Watch live WAL logs in a separate terminal:
watch -n 1 "pg_waldump 000000010000000000000001 | tail -n 20"

The content is binary and not human-readable, but every query you run is logged in WAL, ensuring no data is lost if the database crashes.

Make WAL human-readable (Logical WAL)

1. To see the changes in WAL into Readable format we would need to make some changes. From your path /opt/homebrew/var/postgresql@14 we will change some content by nano postgresql.conf and then uncomment wal_level and make it to logical, restart your postgresql.

2. Now we will create a logical replication slot, its way to stream database modifications in a readable format instead of the raw binary WAL. Run the below query-
   SELECT * FROM pg_create_logical_replication_slot('live_slot', 'test_decoding');

• pg_create_logical_replication_slot is a PostgreSQL function that creates a logical replication slot.
• live_slot is the name of replication.
• test_decoding is built-in and outputs changes in a simple human-readable format.

1. Lets insert some data in our table created before.

INSERT INTO wal_demo(name) VALUES ('Shivam Live Test5');
INSERT INTO wal_demo(name) VALUES ('Shivam Live Test6');

1. We will now verify if we can see our changes in readable form or not using the command psql -U shivamkumar -d postgres -c "SELECT * FROM pg_logical_slot_get_changes('live_slot', NULL, NULL);"

You should now see the inserted rows in a readable format, confirming that WAL changes are being tracked and streamed successfully.

With WAL in action, PostgreSQL never misses a beat—your data is always safe and sound.