0

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.

2
  • How big is your database? What is the config of your system overall? How many HDDs? RAID? If RAID, then what sort? Is your SSD SATA or PCIe? Can you install more SSDs in place of your HDDs? Is your system OLTP or OLAP? 50/50?
    – Vérace
    Commented Apr 29, 2021 at 10:44
  • For daily volume of 100-200M I would probably use an SSD. Those are cheap nowadays. But if you have to make do with what you have, identify actual bottlenecks first. Writing logs is typically not the most performance-critical task, but all load on a drive can be competition. Commented Apr 29, 2021 at 12:16

2 Answers 2

2

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).

1

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.