I'm maintaining a postgresql database and am considering the use of clustering so that update queries have to touch fewer pages.

With clustering based on indices I'm seeing a significant speedup for the actual queries, but is there a way to profile WAL files to see which tables contribute most to the log size when they are modified? I'm experiencing a number of issues related to transaction sizes and operations on write ahead logs by other teams maintaining replicae of the database.

1 Answer 1


If rows which cluster together get updated together, that could result in fewer full-page-images, so it would be expected to decrease WAL size. But moving checkpoints further apart would also do that, and wouldn't generate the massive spike in WAL that the CLUSTER operation itself would do.

You can use pg_waldump to inspect some WAL files. It doesn't have a mode to generate per-object stats, so you would have to use something like Perl to parse and summarize. The records only indicate objects by their relfilenodes, so you would also need to do something to convert them back to names.

But you should probably tackle the problem more directly. What are the problems you are having? Raw size of WAL? Transmission over network? Random reads needed to fetch the pages which need to get updated during replay?

  • A bunch of those, including a team in our sister company maintaining a replica in their cloud instance and syncing over an unreliable vpn, which means wal files build up whenever the connection shuts down. Just to be clear, in benchmarking clustering significantly improve basically all common workloads on two very frequently accessed large tables, which is the main reason why I am doing it. I just want to figure out to what extent it also helps with the wall file issue.
    – saolof
    May 21, 2022 at 5:41

Your Answer

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

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