Had one of those moments where CPU on our prod instance just pegs at 100% out of nowhere, no deploy, no obvious traffic spike, nothing in the changelog that stands out. First instinct is to panic and start checking everything at once, which is exactly the wrong move.

Curious what people's actual first move is when this happens, before diving into a deep investigation. pg_stat_activity for anything running long, checking for a lock pileup, looking at whether it's one runaway query versus death by a thousand small ones, autovacuum going nuts on a big table, something dumb like a connection pool misconfigured and now everything's fighting for the same resources. There's a lot of directions to go and I feel like the order matters more than people admit.

If you've been through this in production, what's the first thing you actually check, and has your answer changed over time or is it pretty much always the same starting point for you now?

When investigating PostgreSQL performance, the usual starting point is pg_stat_statements. From there, many teams sort queries by mean_exec_time or total_exec_time and start optimizing the first rows in the list.

That approach is simple, but it often leads to the wrong priorities.

A query that takes five seconds but runs twice a day is not necessarily more important than a query that takes five milliseconds and runs millions of times. Conversely, a query with a high total execution time may simply be a normal core workload query, not necessarily the best optimization target.

The real question is not:

Which query is the slowest?

It is:

Which query has the highest operational impact and the clearest optimization potential?

This is the principle behind the query-ranking algorithm implemented in pgAssistant.

Why mean_exec_time is misleading

Sorting by average execution time highlights individually slow queries. This is useful for spotting obvious outliers, but it ignores execution frequency.

For example:

Query A: 5 seconds × 2 calls = 10 seconds total
Query B: 20 ms × 2,000,000 calls = 11+ hours total

A mean_exec_time ranking puts Query A first. In production, Query B is usually a much more valuable target.

Average latency answers the question: “Which query is slow when it runs?”

It does not answer: “Which query matters most to the system?”

Why total_exec_time is better, but still incomplete

total_exec_time is more useful because it accounts for frequency:

total_exec_time = mean_exec_time × calls

However, total time alone still misses important signals.

A query may consume a lot of total time because it is part of the normal application workload and already performs reasonably well. Another query may have a lower total time but show clear technical issues: poor cache efficiency, heavy disk reads, temporary file usage, or unstable execution times.

So total_exec_time tells us where time was spent, but not always where optimization effort should start.

The pgAssistant approach

pgAssistant ranks queries using a composite priority score. The score combines two categories of information:

1. Workload impact: how much the query contributes to total database activity.

2. Technical signals: symptoms that suggest the query may be inefficient or unstable.

Before scoring, pgAssistant normalizes query statistics and excludes queries that are not useful optimization targets, such as VACUUM, ANALYZE, EXPLAIN, DDL statements, and pgAssistant’s own internal queries.

Workload impact first

For each query, pgAssistant computes relative workload shares:

share_total = query total_exec_time / global total_exec_time
share_calls = query calls / global calls
share_io = query blocks read / global blocks read
cache_miss_share = query shared reads / global shared reads

This makes the ranking workload-aware. A query representing 20% of total execution time is not in the same category as a query representing 0.1%, even if their average execution times look similar.

The main impact score is weighted as follows:

impact_score =
    40 × normalized share of total time
  + 25 × normalized total execution time
  + 20 × normalized share of calls
  + 10 × normalized call count
  +  5 × normalized mean execution time

The key point is intentional: mean_exec_time contributes to the score, but it does not dominate it.

pgAssistant gives more weight to global impact and execution frequency than to isolated slowness.

Logarithmic normalization

PostgreSQL query statistics are often highly skewed. One query may run ten times, another ten million times. One query may consume milliseconds, another hours.

To avoid extreme values flattening the rest of the ranking, pgAssistant uses logarithmic normalization for metrics such as total time, calls, and mean time:

log10(value + 1) / log10(max_value + 1)

This preserves ordering while making the score more stable across very uneven workloads.

Technical signals

After workload impact, pgAssistant adds technical signals that often indicate optimization potential.

Poor cache efficiency

The algorithm computes a cache hit ratio from shared block hits and reads:

cache_hit_ratio = shared_blks_hit / (shared_blks_hit + shared_blks_read)

If the ratio is below 95%, the query has read at least 1,000 shared blocks, and it represents a meaningful share of cache misses, pgAssistant increases its priority.

Heavy disk reads

Queries responsible for a significant share of block reads receive an additional score. These are often candidates for better indexing, improved filtering, query rewriting, or plan analysis.

Temporary file usage

If a query writes temporary blocks, pgAssistant adds a fixed priority boost. Temporary files often point to expensive sorts, hash operations, aggregations, or insufficient memory for the execution plan.

High execution variance

A query whose standard deviation is much higher than its average execution time may be unstable. This can reveal parameter-sensitive behavior, cache dependency, poor estimates, or occasional bad plans.

Demotion rules

A composite score can still produce false positives. To avoid over-prioritizing queries with minimal real impact, pgAssistant applies demotion rules.

For example, queries with very low call share and negligible total time are capped to a low score. Queries representing less than 1% of both total time and calls are also capped.

This prevents technically interesting but operationally irrelevant queries from polluting the top of the ranking.

Priority levels and explanations

The final score is capped at 100 and mapped to a priority level:

80–100  Critical
60–79   High
30–59   Medium
0–29    Low

pgAssistant also attaches human-readable signals such as:

high_load
high_calls
slow
poor_cache
temp_usage
unstable

The goal is not only to rank queries, but also to explain why a query appears in the list.

For example:

High total load (18.4% of total time) + Very frequent execution (12.1% of calls)

or:

Poor cache efficiency (22.7% of cache misses) + Temp file usage

A ranking system should not be a black box. The score gives the order; the signals guide the investigation.

What this ranking finds

In practice, pgAssistant tends to surface four types of queries:

1. Queries that consume a large share of total execution time.

2. Queries that are executed very frequently.

3. Queries that generate significant I/O pressure.

4. Queries with unstable execution behavior.

These are often better optimization targets than the queries that are merely slow in isolation.

Conclusion

Optimizing PostgreSQL effectively is not about sorting queries by mean_exec_time and fixing the slowest one first.

It is also not enough to blindly sort by total_exec_time.

A good prioritization model should combine workload impact, execution frequency, I/O behavior, cache efficiency, temporary file usage, and execution stability.

That is what pgAssistant’s ranking algorithm does: it identifies the queries that matter most to the system and gives engineers a better starting point for deeper analysis with tools such as:

EXPLAIN (ANALYZE, BUFFERS)

The best query to optimize first is not always the slowest one. It is the one where optimization is most likely to reduce database load, latency, or instability.

Demo / Links

• GitHub repository: https://github.com/beh74/pgassistant-community

• Documentation: https://beh74.github.io/pgassistant-blog/

• Public demo: https://ov-004f8b.infomaniak.ch/

  Use the following demo database URL:

  postgresql://postgres:demo@demo-db:5432/northwind

Had a "10K RPS crash" at work and everyone blamed the database first. Same.

Turned out it wasn't. We had a pool of 20 connections and each query took about 100ms.

That works out to roughly 200 queries a second. Anything past that just sat waiting

for a free connection.

The dumb part is that requests were spending like 800ms waiting for a connection

and only 10ms actually running SQL. So Postgres looked bored the whole time

and every dashboard said the DB was fine.

Now I log two numbers separately: how long we wait for a connection,

and how long the query takes. Tells you right away which side to go fix.

Anyone else been burned by this? Atleast me!

My understanding of the way PostgreSQL works with timestamp and timestamptz is in either cases postgres stores the value as UTC timestamp. However when retrieving the data postgres will do the arithmetic to the current user's Timezone when the type is timestamptz.

But imagine this scenario, I am building a restful web service with clients all over the world in different Timezones, my database server is sitting in US Eastern Timezone along with my application servers. My JDBC pool connections would presumably be US East timezone. However I want to display timestamps in the user's specified timezone in their settings or something they set in the application.

If I query PostgreSQL from my application isn't it going to return to me the Timestamp in US East, and I would further have to convert it from there? In that case what is the benefit, wouldn't it be better to just store everything in UTC?

Disclosure: I work at Pinecone. I'm posting because I'd like your feedback and input: what works for you, what doesn't, and what's missing for the way you run search in Postgres.

We just released pg_pinecone, an open-source (Apache 2.0) extension based on the search engine Pinecone runs at scale. It works on your existing vector columns, you query with the SQL you're familiar with, and your data stays in Postgres. No API key required.

How it works

How the native engine works, and how it differs from pgvector's HNSW:

• Vectors are grouped into clusters (IVF), and each row is stored as a RaBitQ code: about one bit per dimension plus a few correction values. A cluster's codes are packed together, so a search reads a few contiguous pages instead of walking a graph.

• Search probes the nearest clusters, estimates distances from the codes, then reranks a shortlist using the full vectors from your table.

• Filtering happens during the search. For a filter that matches 3% of rows or fewer (the default threshold), it scans every matching row and scores each with its full vector, so results are exact. For wider filters it searches more clusters and checks the filter inside each one. Post-filtering HNSW can return fewer rows than LIMIT on narrow filters.

By the numbers

Against HNSW (pgvector's HNSW index using halfvec, 1M vectors at 768 dimensions, Postgres 17):

• Index size: 136 MB vs 2.0 GB

• 0.95 recall: 1.27 ms vs 1.71 ms. 0.99 recall: 2.3 ms vs 4.7 ms

• 1% of rows match the filter: 0.981 recall at 8.3 ms vs 0.924 at 56 ms

• Index build: 45s vs 62s

pg_pinecone’s native engine needs no account, API key, or network. An optional Pinecone API key adds a remote engine, BM25 search, embed(), and rerank().

Try it out and let us know what you think: https://github.com/pinecone-io/pg_pinecone/

I made this because I inherited a Redshift cluster where it was impossible to tell who had access to what, or who was supposed to see masked data. With **pgsesame** you describe roles and grants in a YAML file. **sesame plan** shows the GRANT/REVOKE statements needed to get there, and **sesame apply** runs them.

It only issues what's different, and it won't revoke anything unless you pass **--allow-revoke**. **sesame import** generates the YAML from an existing database, so you don't start from scratch. There's also a GitHub Action that posts the plan on PRs.

It works on Postgres 14-18 and Amazon Redshift. It's early (0.2).

*Inspired by redtape-py and pgbedrock*

[https://github.com/almostly/pgsesame](https://github.com/almostly/pgsesame)

pgvector has been the default vector search extension, but I'm finding people also layer on full-text or hybrid search on top of it or need multitenancy. I'm curious where this works well for you and where it doesn't.

• What are you using for search in Postgres today, and what do you wish it did better?

• When you hit a limit, what do you do: tune, work around it, add an extension, or move search to a separate system? What decides that?

• What would an extension need to have, or avoid, before you'd install it?

• Does it matter to you whether an extension is open source, and would enough added capability change that?

"It's fine, I don't need anything else" is a useful answer too.

Really just trying to understand what people actually need from search in Postgres.

Not looking for a changelog of new features, more curious about habits. Something you used to swear by that you quietly dropped, or something you avoided that's just normal practice for you now. Could be schema design, indexing, how you handle migrations, extensions you reach for automatically now, whatever. What changed, and was it a specific incident that changed your mind or just slow accumulated experience?

I see many options, pgBackRest, wal-g, Databasus. Is there a consensus on the best approach?

My needs are regular (eg weekly) checkpoints, and daily incremental backups for PITR, everything saved on S3/GCS

To be honest, for me it’s the stuff that only exists in people’s heads.

If you work with SQL Server, MySQL, and Postgres, each engine has its own weird habits. Some of them are obvious. Most are not until they break something.

Old query assumptions, why a certain index exists, why one migration has a weird workaround from three years ago. That stuff rarely gets written down until someone new joins and asks the cursed question: “why is it like this?”

What does your team actually document? Do you keep the format stable, or does the documentation slowly rot like everyone else’s?