3

I have a PostgreSQL 11 database which hosts several databases, some of which are contain tables that are frequently inserted and updated. Recently the amount of data being written to WAL files has increased greatly (about 400%), though I believe that the number of inserts to the database has increased by only about 20%.

As a consequence not only are we using more disk, but our database performance now seems constrained by the writer performance of the disks the WALs are on.

The application is updated very frequently, and it may be that something in the application (which we control, and are able to change) has changed, which is causing less efficient inserts/updates to be made, but I don't know how we would identify that. There have probably been a many changes since this change In behaviour started, and several since it was first noticed.

Is there a way of determining which databases/tables/queries are writing to the WAL, and in what (approximate) quantity?

4
  • What changed in your landscape: only the number of inserts? Or did you roll out an application update too? Mar 5, 2021 at 14:53
  • @Colin'tHart I have updated the question to reflect that the app is constantly changing.
    – hmallett
    Mar 5, 2021 at 15:01
  • OK. Yes, I would suspect one of the updates. If you have 20% more database activity a rule of thumb would be to expect 20% more WAL. Any large database columns? JSONB? Mar 5, 2021 at 15:03
  • 1
    I'd run pg_waldump on some of the wal files and see if anything particularly stood out.
    – jjanes
    Mar 5, 2021 at 17:38

1 Answer 1

5

If you are using PostgreSQL v13, you can install pg_stat_statements, which logs the amount of WAL per statement in the wal_bytes column. So you could run

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT wal_bytes, calls, query
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 10;

The WAL itself has no connection to a certain SQL statement, but if you examine the entries with pg_waldump, you can connect them to certain files, which correspond to database objects. That route is cumbersome.

Upgrade to v13.

4
  • Unfortunately we're using an extension (TimescaleDB) which currently has version 12 as the latest supported version. I will update the question to include this.I like your answer though.
    – hmallett
    Mar 5, 2021 at 15:03
  • You can use pgBadger too. It might help to identify statements which are running excessively often. Mar 5, 2021 at 15:04
  • 1
    It is easy to identify the statements that take your time (also with pg_stat_statements). But they need not be the ones that generate most WAL. Still, it is a starting point. Mar 5, 2021 at 15:11
  • 1
    Hi @hmallett TimescaleDB 2.1 was released on Feb 22 with support for PG 13, if that helps. Cheers! Mar 6, 2021 at 7:01

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.