0

Recently I was informed that since enabling pg_stat_statements, the available disk space is dropping fast. I don't think it's caused by that, but I wonder: Does enabling pg_stat_statements reduce disk space? And by how much?

1

pg_stat_statements shouldn't use much disk space.

It uses two files:

  • pg_stat_statements.stat persists pg_stat_statement's data across server restarts.

    It is written upon shutdown and read on server startup. The file is deleted as soon as it has been read, so it should not be present when the server is up.

  • pg_stat_tmp/pgss_query_texts.stat contains the text of the queries being tracked.

    This file will grow as new statements are added, but if it becomes too big, pg_stat_statements will perform a “garbage collection” and rewrite it.

If you suspect that there is a disk space problem with pg_stat_statements, check pg_stat_tmp/pgss_query_texts.stat. But I don't think this file can be a problem unless you set pg_stat_statements.max to an astronomically high value and run lots of different statements.

  • Thanks for the answer! One more question: What about database size after enabling pg_stat_statements? Does the database increase significantly? – StandardNerd Aug 9 at 11:17
  • 2
    The database size (as measured with pg_database_size) shouldn't increase at all. – Laurenz Albe Aug 9 at 11:38
  • ok, good to know, thanks! – StandardNerd Aug 9 at 11:57
0

... since enabling pg_stat_statements, the available disk space is dropping.

So pg_stat_statements is consuming disk space.
This is to be expected, since it's purpose is to collect (and store) statistics about every single query that passes through your Postgres cluster. All that data has to be stored somewhere and that will take up space. Not a lot, compared to your database as a whole, I suspect, but some.

You should monitor it for a while and adjust your Capacity Plans accordingly.

  • pg_stat_statement only tracks a maximum number of statements (pg_stat_statements.max), and garbage is collected whenever the file size exceeds a limit based on that value. How can it consume much disk space? – Laurenz Albe Aug 9 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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