Postgres – should I keep the log file on the fastest drive

All we need is an easy explanation of the problem, so here it is.

I wonder your recommendations / experience re database log file (pg_log) placement

We are debating internally if it is worth to keep that on fastest (and expensive) SSD volume, or can be kept on magnetic drives without hitting performance.
Have separate volume dedicated only for this – Id be more than happy to reallocate that to slower HDDs and use this space for DB volume

We have verbose logging (writing almost every single query into log) on and producing 100-200M log file daily
Engine is PG 12.6 working on Linux.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

With the logging_collector enabled, writes are sequential so you can keep log_directory on almost any media.

Assuming average size of a log message is 5 KiB, with a HDD that can do sequential writes at 100 MiB/s you can log 20k messages/second.

So in theory – if you need to you emit tons of logs – you could put them on a separate, cheaper storage.

At the same time, remember that when the log volume fails or is overloaded, it can effectively stop the server (as noted in documentation linked above).

Method 2

The log can be on a slow disk as long as you don’t log insane volumes of data, e.g. all statements on a busy database.

Logging too much is not good anyway: you want to keep it readable.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply