Postgres FM19 Elo 2022

Monitoring checklist

Monitoring checklist (dashboard 1):

TPS and (optional but also desired) QPS
Latency (query duration) — at least average. Better: histogram, percentiles
Connections (sessions) — stacked graph of session counts by state (first of all: active and idle-in-transaction; also interesting: idle, others) and how far the sum is from max_connection (+pool size for PgBouncer).
Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activity
Commits vs rollbacks — how many transactions are rolled back
Transactions left till transaction ID wraparound
Replication lags / bytes in replication slot / unused replication slots
Count of WALs waiting to be archived (archiving lag)
WAL generation rates
Locks and deadlocks
Basic query analysis graph (top-n by total_time or by mean_time?)
Basic wait event analysis (a.k.a. “active session analysis” or “performance insights”)

And links to a few things we mentioned:

Postgres monitoring review checklist (community document)
pgstats.dev
Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund)
Transaction ID Wraparound in Postgres (blog post by David Cramer)
Subtransactions Considered Harmful (blog post by Nikolay)
datadoghq.com
pgwatch2 (Postgres.ai Edition)

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the elephant artwork

Tämä jakso on lisätty Podme-palveluun avoimen RSS-syötteen kautta eikä se ole Podmen omaa tuotantoa. Siksi jakso saattaa sisältää mainontaa.

Jaksot(182)

pgBackRest

Nik and Michael are joined by David Steele to talk all things pgBackRest. Here are some links to things they mentioned: David Steele https://postgres.fm/people/david-steelepgBackRest https://pgbackres...

3 Heinä 1h 13min

autovacuum

Nik and Michael discuss autovacuum, including what it does, and the basics of why and how to tune it. Here are some links to things they mentioned: autovacuum https://www.postgresql.org/docs/current/...

29 Touko 46min

pg_flight_recorder

Nik and Michael are joined by David Ventimiglia to discuss pg_flight_recorder, a new tool he created for monitoring a Postgres database from within. Here are some links to things they mentioned: David...

15 Touko 43min

PgQue

Nik and Michael discuss Nik's new project PgQue, a descendent of Skype's PgQ, for running queue-like workloads in Postgres. Here are some links to things they mentioned: Our first episode on Queues in...

8 Touko 47min

pg_wait_tracer

Nik and Michael are joined by Dmitry Fomin to discuss his new tool pg_wait_tracer, as well as changes that could be made to core to allow wait event tracing with lower overhead, and on managed service...

24 Huhti 44min

Schema design checklist

Nik and Michael discuss a list of things to check when designing new schema in Postgres. Here are some links to things they mentioned:Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.ru...

17 Huhti 45min

What’s Missing in Postgres?

Nik and Michael are joined by Bruce Momjian to discuss his new talk "What’s Missing in Postgres?" Here are some links to things they mentioned: Bruce Momjian https://postgres.fm/people/bruce-momjianED...

3 Huhti 43min

Long-running transactions

Nik and Michael discuss long-running transactions, including when they're harmless, when they cause issues, and how to mitigate those issues. Here are some links to things they mentioned: transaction_...

27 Maalis 48min