- checkpoint tuning was performed in favor of fewer overheads of bulk random writes and fewer full-page writes (usually meaning that`max_wal_size` and`checkpoint_timeout` are increased)

- the latest checkpoint happened quite long ago (can be seen in PG logs in`log_checkpoint = on`, which is recommended to have in most cases).

The amount of dirty buffers is quite easy to observe, using extension pg_buffercache (standard contribmodele) and this query (may take significant time; see[the docs](https://postgresql.org/docs/current/pgbuffercache.html)):

The amount of dirty buffers is quite easy to observe, using extension pg_buffercache (standard contribmodule) and this query (may take significant time; see[the docs](https://postgresql.org/docs/current/pgbuffercache.html)):

selectcount(*), pg_size_pretty(count(*)*8*1024)

from pg_buffercache

That's it. Note that we didn't cover various timeouts (e.g., pg_ctl's option`--timeout` and wait behavior`-w`,`-W`, see[Postgres docs](https://postgresql.org/docs/current/app-pg-ctl.html)) here and just discussed what can cause delays in shutdown/restart attempts.

Hope it was helpful - as usual,[subscribe](https://twitter.com/samokhvalov/), like, share, and comment! 💙

Hope it was helpful - as usual,[subscribe](https://twitter.com/samokhvalov/), like, share, and comment! 💙

- Index maintenance: bloat control as well + regular reindexing, because index health declines over time even if autovacuum is well-tuned (btree health degradation rates improved in PG14, but those optimization does not eliminate the need to reindex on regular basis in heavily loaded systems).

- Partitioning: one of benefits of partitioning is improved data locality.

**Option 2.** Use index-only scans instead of index scans. This can be achieved by usingmutli-column indexes or covering indexes, to include all the columns needed for our query. For our example:

**Option 2.** Use index-only scans instead of index scans. This can be achieved by usingmulti-column indexes or covering indexes, to include all the columns needed for our query. For our example:

In this case, the planning time is really low, sub-millisecond – but I encountered with cases, when planning happened to be extremely slow, many seconds or even dozens of seconds. And it turned out (thanks to flamegraphs!) thatanalysing the Merge Join paths was the reason, so with "set enable_mergejoin = off" the planning time dropped to very low, sane values. But this is another story.

In this case, the planning time is really low, sub-millisecond – but I encountered with cases, when planning happened to be extremely slow, many seconds or even dozens of seconds. And it turned out (thanks to flamegraphs!) thatanalyzing the Merge Join paths was the reason, so with "set enable_mergejoin = off" the planning time dropped to very low, sane values. But this is another story.

- Brendan Gregg's books: "Systems Performance" and "BPF Performance Tools"

-Brendant Gregg's talks – for example,["eBPF: Fueling New Flame Graphs & more • Brendan Gregg"](https://youtube.com/watch?v=HKQR7wVapgk) (video, 67 min)

-Brendan Gregg's talks – for example,["eBPF: Fueling New Flame Graphs & more • Brendan Gregg"](https://youtube.com/watch?v=HKQR7wVapgk) (video, 67 min)

-[Profiling with perf](https://wiki.postgresql.org/wiki/Profiling_with_perf) (Postgres wiki)

- tricks for versions <16

- In PG14+, use`compute_query_id` to havequer`y_id values both in Postgres logs and`pg_stat_activity`

- In PG14+, use`compute_query_id` to havequery_id values both in Postgres logs and`pg_stat_activity`

- Increase`track_activity_query_size` (requires restart) to be able to track larger queries in`pg_stat_activity`

- Organize workflow to combine records from`pg_stat_statements` and query examples from logs and`pg_stat_activity`, so when it comes to query optimization, you have good examples ready to be used with`EXPLAIN (ANALYZE, BUFFERS)`.

- For comparison, instead of`=` or`<>`:`IS [NOT] DISTINCT FROM` (check out the`EXPLAIN` plan though).

- Instead of concatenation, use:`format('%s %s', var1, var2)`.

- Don't use`WHERE NOT IN (SELECT ...)` – use`NOT EXISTS` instead (

seethia[JOOQ blog post](https://jooq.org/doc/latest/manual/reference/dont-do-this/dont-do-this-sql-not-in/)).

seethis[JOOQ blog post](https://jooq.org/doc/latest/manual/reference/dont-do-this/dont-do-this-sql-not-in/)).

- Just be careful.`NULL`s are treacherous.

This method should be used when we need to query the CSV file data via SQL "live" without loading a snapshot. To achieve

this, we'll be using[file_fwd](https://postgresql.org/docs/current/file-fdw.html).

this, we'll be using[file_fdw](https://postgresql.org/docs/current/file-fdw.html).

Having a great advantage (live data!), this method has its obvious disadvantages:

The concept here is straightforward:

1. First, we call OpenAI API to "vectorize" the text of our request.

2. Then, we use`pgvector`'s similarity search to find K nearestneighbours.

2. Then, we use`pgvector`'s similarity search to find K nearestneighbors.

We will use the`HNSW` index, considered one of the best approaches today (although originally described in

[2016](https://arxiv.org/abs/1603.09320)); added in by many DBMSes. In`pgvector`, it was added in version 0.5.0.

risks. And it should be included in the snippets showing how much of the "capacity" is used.

To install:

- on macOS/homebrew:`brew install pspg`

- on macOS/Homebrew:`brew install pspg`

- Ubuntu/Debian:`sudo apt update && sudo apt install -y pspg`

Then:

Also read the following comment by[@jamessewell](https://twitter.com/jamessewell),originaly posted

Also read the following comment by[@jamessewell](https://twitter.com/jamessewell),originally posted

[here](https://twitter.com/jamessewell/status/1730125437903450129):