1

I consider Query Store to be the best idea that Microsoft's SQL Server has had in the past ten years. I worry that I will miss it very dearly when I move to Postgres. What equivalents does Postgres have to SQL Server's Query Store?

2
  • 3
    Postgres experts might be able to help, but they probably don't know what Query Store does, perhaps indicate the specific features you are looking for? Keep in mind that the Postgres' query planner behaves very differently from that of SQL Server, so some things may not even make sense in Postgres.
    – mustaccio
    Commented Feb 14 at 19:41
  • There are various extensions that can keep track of plans - you'll need to do some research and perhaps come back with a more specific question - i.e. "I want to do this, is this functionality available by default or is there an extension which might do the job?" or just modify this question - personally, if I were you, I'd delete this one and come back with something more specific.
    – Vérace
    Commented Feb 28 at 9:26

3 Answers 3

3

The most common approach here would be "pg_stat_statements", which records all queries that have run on the Postgres database since the last reset:

https://www.postgresql.org/docs/current/pgstatstatements.html

Note that this does not have any tracking of activity over time (i.e. "how many queries ran in the last hour") - for that you need an external process that snapshots the pg_stat_statements data, and does a diff to the previously recorded snapshot, matching up queries on (dbid, userid, queryid, toplevel).

You could either script this yourself, or use an existing third-party tool that can do this for you. See https://wiki.postgresql.org/wiki/Monitoring#Postgres-centric_monitoring_solutions for an overview of some solutions that do this.

2
  • Does such data tend to be useful in Postgres? In SQL Server, Query Store is amazing.
    – J. Mini
    Commented Feb 16 at 8:11
  • Speaking as someone who develops a solution in this space (pganalyze, also listed on the above wiki page), yes its super useful in my experience, both as a direct user of this data, and seeing others benefit from it. But you do really gain most benefits when you look at the data over time, vs just as a one time snapshot when querying pg_stat_statements directly. Commented Feb 28 at 22:50
2

Since you mentioned PostgreSQL I believe this is a self managed instance, for cloud managed instances like Azure Managed PostgreSQL Service they have their own custom built query store.

For self managed PostgreSQL there is nothing like Query store built-in. The most widely used view for getting query stats is pg_stat_statements which you need to enable in the config file.

While pg_stat_statements records query execution metrics there is no execution date/time recording to filter the queries which ran during a specific period of time as pg_stat_statements is cumulative and aggregated based on user and query. If you looking for such a filtering mechanism will need to setup a time-series monitoring on pg_stat_statements using tools like pg_watch2 extension to put this data into another DB for analysis which is most of other monitoring tools also doing.

1

That would be QueryStore

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-query-store

Assuming you're moving to Azure that is. Part of the problem with Postgres is that it is all called Postgres but different suppliers ship very different features & getting the various extensions to play together is also "interesting"

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.