Postgres FM

Nikolay and Michael discuss data types in PostgreSQL — including system types, choosing between types, types provided by extensions, and more.    Here are some links to some things they mentioned: Data Types (docs) https://www.postgresql.org/docs/current/datatype.html   10 tips for beginners https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners   Tid Scan (explain glossary) https://www....

Nikolay and Michael discuss HA (high availability) — what it means, tools and techniques for maximising it, while going through some of the more common causes of downtime.    Here are some links to some things they mentioned: https://en.wikipedia.org/wiki/High_availability https://postgres.fm/episodes/upgrades     https://github.com/shayonj/pg_easy_replicate/   pg_easy_replicate discussion on Hack...

Nikolay and Michael discuss 10 beginner tips Nikolay recently shared — they go into a bit more detail on each, and even disagree a little on one or two!    Here are some links to some things they mentioned: Nikolay’s tweet with all 10 tips: https://twitter.com/samokhvalov/status/1679953049899642880   Tip 1: tuples are physical versions of rows Related episodes: https://postgres.fm/episodes/how-to-...

Nikolay and Michael discuss Postgres connection poolers — when and why we need them, the king that is PgBouncer, and the many new pretenders to the throne.   Here are links to a few things they mentioned:  max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS   Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) htt...

Nikolay and Michael celebrate the podcast's 1 year anniversary by going through several questions and suggestions received over the year.   Here are the questions and some links to things we mentioned:  Question 1: Effect of wal_log_hints=on after bulk Deletes: Why next select runs slow and generated tons of WAL? https://twitter.com/dmx551/status/1598253188926570496   wal_log_hints https://www.pos...

Nikolay (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime. Links: - "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades - recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/ - recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/ - pg_easy_replicate htt...

Lonely Nikolay discusses the performance aspects of using UUID for primary keys.  Here are links to a few things I mentioned:  "postgresql" posts on HN, most popular last week (Algolia search): https://hn.algolia.com/?dateRange=pastWeek&page=0&prefix=true&query=postgresql&sort=byPopularity&type=story Unexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cyberte...

Nikolay and Michael discuss memory in PostgreSQL — how it works, important settings, and how to go about tuning them.   Here are links to a few things we mentioned:  Resource Consumption (PostgreSQL docs) https://www.postgresql.org/docs/current/runtime-config-resource.html Andres Freud tweet about shared_buffers https://twitter.com/AndresFreundTec/status/1438912583554113537   Henrietta (Hettie) Do...

Nikolay and Michael discuss Postgres extensions — what they are, how they affect your decisions around Postgres, and some things to keep in mind when using them.   Here are links to a few things we mentioned:  Extensions (docs) https://www.postgresql.org/docs/current/external-extensions.html   Extension (pgPedia) https://pgpedia.info/e/extension.html   pgvector https://github.com/pgvector/pgvector...

Nikolay and Michael discuss zero-downtime schema migrations — why they're a challenge, a variety of different cases, and some things you can do to achieve them.   Here are links to a few things we mentioned:  Comparison of JOINS: MongoDB vs. PostgreSQL (blog post by Michael Stonebraker and Álvaro Hernández) Common DB schema change mistakes (blog post by Nikolay) lock_timeout and retries (blog post...

Nikolay and Michael discuss parallelism — both parallel query execution and other parallel operations.   Here are links to a few things we mentioned:  Parallel query (docs) Parallelism in PostgreSQL 11 (talk by Thomas Munro) Parallelism in PostgreSQL 15 (talk by Thomas Munro) Towards Millions TPS (blog post by Alexander Korotkov) Memory resource consumption (docs) Our episode about index maintenan...

Nikolay and Michael discuss database corruption — various types, how they can come about, and what to do (and not do) if you come across it.    Here are links to a few things we mentioned:  The dangers of streaming across versions of glibc (TripAdvisor mailing list thread) The floor is Java meme Data Corruption talk by Sebastian Webber (on Postgres TV)  Data corruption monitoring & troubleshoo...

Nikolay and Michael discuss using ChatGPT for Postgres tasks — should you, if so what for, and some things to be mindful of!   Here are links to a few things we mentioned:  ChatGPT Nikolay’s polls on Twitter and on LinkedIn   The Art of PostgreSQL (book by Dimitri Fontaine) SQL Performance Explained (book by Markus Winand) Nikolay’s YouTube correction about deletes and index amplification Don’t us...

Nikolay and Michael discuss pg_stat_statements — why everyone should use it, but also some downsides!   Here are links to a few things we mentioned:  pg_stat_statements (docs) pg_stat_statements (PgPedia) PGSQL Phriday Observer effect in pg_stat_statements and pg_stat_kcache (Postgres Hacking session on Postgres TV)  track_io_timing (docs) Overhead comment (by Tom Kate, via Jeremy Schneider)  pg_s...

Nikolay and Michael discuss auto_explain — what it is, how it can help, and how to check it's overhead.   Here are links to a few things we mentioned:  auto_explain (docs) ScaleGrid guide to auto_explain   Can auto_explain, with timing, have low overhead? (Blog post by Michael) pgBadger   pg_stat_monitor EXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres) pg_test_timing Our episo...

Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling.   A couple of apologies-in-advance:  Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS We also had a couple of audio issues, sorry! Here are links to a few things we mentioned:  Recent discussion on Hacker News PgQ What...

Nikolay and Michael discuss a listener request — special considerations for databases that are used in a read-only mode all day, and get an update at night with additional data. Here are links to a few things we mentioned:  Index-only scans Vacuum UK Covid-19 dashboard pg_repack Partitioning Our episode on BRIN indexes Always load sorted data (blog post by Haki Benita) GIN indexes: the good and th...

Nikolay and Michael discuss table partitioning — what it is, why and when it's helpful, and some considerations for your partition key.   Here are links to a few things we mentioned:  Partitioning docs pg_partman Index maintenance episode   Timescale partitioning pg_cron Xtreme PostgreSQL (talk by Christophe Pettus) Database Antipatterns (also by Christophe, slides 46-49) Understanding an outage (...

This week we're sharing an edited version of Nikolay's recent interview with Peter Zaitsev from Percona — they discuss MySQL vs Postgres, Percona’s success, open source licenses, FerretDB, and databases on Kubernetes… phew!   And here are some links to a few things mentioned:  Percona pgCloudHacker browser extension   PMM Percona Distribution for PostgreSQL FerretDB Peter's Twitter profile Peter's...

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer.   Here are links to a few things we mentioned:  psql (docs) psql is awesome! (talk by Lætitia Avrot) psql tips (site by Lætitia Avrot) pgAdmin Postico   DBeaver DataGrip PgManage (new Command Prompt fork of OmniDB)  PopSQL postgres...

Nikolay and Michael discuss major and minor version Postgres upgrades — what they are, how often they come out, and how regularly we should be upgrading.   Here are links to a few things we mentioned:  Postgres versioning policy   why-upgrade (by depesz) postgresqlco.nf (by Ongres) postgresql.conf comparison (by Rustproof Labs)  pg_upgrade Logical replication   CHECKPOINT amcheck Locale data chang...

Nikolay and Michael discuss wait events — what they are, why we have them, and how to use them to help diagnose performance issues.   Here are links to a few things we mentioned:  Wait events table (docs) 9.6 release notes PostgreSQL Scalability (blog post by Alexander Korotkov) Wait event analysis in pganalyze auto_explain Database Lab Engine track_io_timing pg_test_timing pgBadger Coroot Okmeter...

Nikolay and Michael discuss TOAST (The Oversized-Attribute Storage Technique) — what it is, how it works, and some general things to be aware of.   Here are links to a few things we mentioned:  TOAST docs TOAST wiki Hussein Nasser on rows per page (Twitter) Toasting in action (dbi services blog) Interview with Peter Zaitsev (Postgres TV) Building columnar compression in a row-oriented database (Ti...

Nikolay and Michael discuss JSON — our options for storing it in Postgres, whether or when we should, as well as a brief intro and some tips for JSON functions available.   Here are links to a few things we mentioned:  hstore XML type XML functions JSON types JSON functions JSONB indexing NULLS episode Why Postgres is popular episode PostgreSQL 12 release notes What’s New in SQL:2016 (blog post by...

Nikolay and Michael discuss real-time analytics — what it means, what the options are, and some tips if you're trying to implement it within Postgres.   Here are links to a few things we mentioned:  Loose index scan / skip scan with recursive CTE (wiki) Zheap (wiki) cstore_fdw (now part of Citus) Timescale compression docs Hydra founders interview (on Postgres TV) Materialised views episode   pg_i...