1

I'm currently confronted with an issue of rapidly incresing used diskspace on a server that hosts a postgres database. When looking deeper into it I found out the space of the WAL directory has doubled in size and thus caused the rapid increase in used diskspace. So it was no gradual increase of the WAL size but a rapid spike happening in under a minute.

The min_wal_size and max_wal_size parameters of postgres are currently set to 4GB and 8GB respectively. The WAL size used to be 4GB for some time and then suddenly increased to 8GB. During this increase (and also for some hours before) I ran a python script. This script acts on a table and copies over all the values that are not NULL from an older column to the respective rows in a newer column which are currently still NULL. This happens for several columns pairs. The scrip uses Afterwards it drops the old column. The script uses sqlalchemy to interact with the postgres database.

As I understand it even though the actual data in the WAL log may not take up the size indicated by min_wal_size the space occupied by the WAL files will not decrease below this value after the checkpoints when the data from the shared buffers gets written do disk.

What I do not understand is: What happens if at some point the data in the WAL files takes up more space than min_wal_size? Will new WAL files get created one by one, or is a batch of new WAL files created at once and then populated with data incrementally afterwards?

I don't understand why this doublind in the WAL size happens at some random point in time when the script was running for a while.

Improve this question

2 Answers 2

Reset to default
1

"This script acts on a table and copies over all the values that are not NULL from an older column to the respective rows in a newer column which are currently still NULL. This happens for several columns pairs. The scrip uses Afterwards it drops the old column."

So there is twice the values in the transaction log :

  1. One for the UPDATEs to be able to undo the INSERTs
  2. One for the DELETEs to be able to undo the DELETEs

This explain why the amount of transaction logged can be wide because, also, you repeat it...

Also you use a ORM (The script uses sqlalchemy) which does some extra works, and perhaps a mega transaction that requires to keep all the undo data values until the transaction is clearly achieved...

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the explanation. Do you see a way of reducing the UPDATE and DELETE into one single statement? Overall there are almost 3 million rows in the table with many NULL values for the individual signals.
PostGreSQL does not have some fine setitngs to reduce the amount of data stored in the transaction log like Microsoft SQL Server where you can reduce to near zero the amount of data when copiying from a table to one anoter in BULK LOGED recovery model of the transaction log... Sometime "free"ware if expensive!
1

WAL files are created one by one as more are needed. If a bunch are created in a very short time, it is because a bunch were needed in a very short time. WAL can be generated very quickly by bulk operations. Especially just after a checkpoint, when it needs to do "full page writes".

If the files are still around, You could use pg_waldump to investigate what was happening within them. But you told it that it could use 8GB when it was busy, and it used 8GB when it was busy; so this hardly seems like something worthwhile to investigate.

Improve this answer

1 Comment

Thanks, the thing I am probably most concerned about. How large will the WAL file grow beyond 8GB? Let's say after one iteration with an UPDATE and DELETE for a signal pair it grows to 8.5GB, will the next UPDATE and DELETE be postponed until enough WAL files are recycled in order to be filled again or will it just grow the WAL file another time? So depening on the speed of the checkpoints and the script the WAL size will either increase or decrease?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.