Nikolay and Michael discuss logging in Postgres — mostly what to log, and why changing quite a few settings can pay off big time in the long term.

Here are some links to things they mentioned:

• What to log https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
• Our episode about Auditing https://postgres.fm/episodes/auditing
• Our episode on auto_explain https://postgres.fm/episodes/auto_explain

Here are the parameters they mentioned changing:

• log_checkpoints
• log_autovacuum_min_duration
• log_statement
• log_connections and log_disconnections
• log_lock_waits
• log_temp_files
• log_min_duration_statement
• log_min_duration_sample and log_statement_sample_rate

And finally, some very useful tools they meant to mention but forgot to!

• https://pgpedia.info
• https://postgresqlco.nf
• https://why-upgrade.depesz.com/show?from=16.9&to=17.5

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

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

With credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss moving off managed services — when and why you might want to, and some tips on how for very large databases.

Here are some links to things they mentioned:

• Patroni https://github.com/patroni/patroni
• pgBackRest https://github.com/pgbackrest/pgbackrest
• WAL-G https://github.com/wal-g/wal-g
• Hetzner Cloud https://www.hetzner.com/cloud
• Postgres Extensions Day https://pgext.day
• pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
• pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
• auto_explain https://www.postgresql.org/docs/current/auto-explain.html
• Fivetran https://www.fivetran.com
• pgcopydb https://github.com/dimitri/pgcopydb
• Kafka https://kafka.apache.org
• Debezium https://debezium.io
• max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
• log_statement DDL https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT
• PgBouncer pause/resume https://www.pgbouncer.org/usage.html#pause-db

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

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

With credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss heavyweight locks in Postgres — how to think about them, why you can't avoid them, and some tips for minimising issues.

Here are some links to things they mentioned:

• Locking (docs) https://www.postgresql.org/docs/current/explicit-locking.html
• Postgres rocks, except when it blocks (blog post by Marco Slot) https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
• Lock Conflicts (tool by Hussein Nasser) https://pglocks.org/
• log_lock_waits (docs) https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITS
• How to analyze heavyweight lock trees (guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0042_how_to_analyze_heavyweight_locks_part_2.md
• Lock management (docs) https://www.postgresql.org/docs/current/runtime-config-locks.html
• Our episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

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

With credit to:

• Jessie Draws for the elephant artwork