Monday , January 30 2023

PostgreSQL Write Ahead Log

Write Ahead Log is PostgreSQL’s transaction log. Keeps records of transactions. Before it is written to the data files, it keeps the data here and writes the data to the data with the checkpoint operation.

Let’s explain a little more

You created a database and made a table in this database, added data, updated the data, made a wrong operation, deleted the wrong transactions and made a select to check, and you saw that your select query was slow and you created an index(For query performance, it will be explained in detail later.)

Transactions like this are written to the wal.

Let’s say your database server shuts down right after you update, you suddenly start to wonder if my data is gone?

The answer is no, after you perform the operation, your data is written to the log, that is WAL.

When you open the server and query your data, you will see that the data is there.

I would also like to point out that in some cases your data may be gone.

Let’s give a different example for the WAL concept to fit well.

You accidentally deleted the database and the application can no longer access the database … If you have a backup, you can restore from the backup.

You will also get help from WAL while restoring your backup . I just mentioned that everything is recorded in WAL.

While restoring the database, it has recorded the last transactions of the database before it was deleted, and you can restore the last records to the state before they were deleted by loading them with the help of WAL.

Let’s go into some detail after the example.

In Postgresql, the wal feature is automatically enabled. You cannot turn this feature off.

To examine the logs of Postgresql, we go to the pg_wal folder in the data file and we see the mixed numbers there.

Let’s talk about what they mean and why they are kept like this.

Filenames in pg_wal have 24 characters and values ​​are kept in hexadecimal. The first 8 characters give the timeline information. The next 16 characters give the WAL id and the Wals have a size of 16 MB.


Faruk Erdem
Author: Faruk Erdem

About Faruk Erdem

Leave a Reply

Your email address will not be published. Required fields are marked *