Nikolay Samokhvalov - Podcast Details

Shows

Postgres FMLocksNikolay 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.htmlPostgres 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-WAITSHow to analyze heavyweight lock trees (guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/mai...2025-05-2338 min

Postgres FMTime-series considerationsNikolay and Michael discuss time-series considerations for Postgres — including when it matters, some tips for avoiding issues, performance considerations, and more. Here are some links to things they mentioned:Time series data https://en.wikipedia.org/wiki/Time_seriesTimescaleDB https://github.com/timescale/timescaledb13 Tips to Improve PostgreSQL Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performanceWhy we're leaving the cloud (37 Signals / Basecamp / David Heinemeier Hansson) https://world.hey.com/dhh/why-we-re-leaving-the-cloud-654b47e0UUID v7 and partitioning (“how to” by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.mdpg_cron ht...2025-04-1142 min

Postgres FMSnapshotsNikolay talks Michael through using cloud snapshots — how they can be used to reduce RTO for huge Postgres setups, also to improve provisioning time, and some major catches to be aware of. Here are some links to things they mentioned:Snapshots on RDS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.htmlpgBackRest https://pgbackrest.orgWAL-G https://github.com/wal-g/wal-gpg_backup_start and pg_backup_stop (docs) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP How to troubleshoot long Postgres startup (by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0003_how_to_t...2025-03-2144 min

Postgres FMSQL vs NoSQLNikolay and Michael are joined by Franck Pachot to discuss SQL vs NoSQL — did Franck change teams by joining MongoDB, normalisation vs denormalisation, developer experience, NULLs, and more! Here are some links to things they mentioned:Franck Pachot https://postgres.fm/people/franck-pachotFranck's workshop at PGConf India https://pgconf.in/conferences/pgconfin2025/program/proposals/958 PostgreSQL Conference Germany https://2025.pgconf.de"Schema Later" Considered Harmful by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/schema-later-considered-harmfulComparison of JOINS by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/comparison-joins-mongodb-vs-postgresql Franck’s post about why he join...2025-02-1450 min

Postgres FMReads causing writesNikolay and Michael discuss a couple of surprising ways read queries (selects) can cause writes (shared buffers dirtied) in Postgres. Here are some links to things they mentioned:Reads causing writes in Postgres (post by Alex Jesipow) https://jesipow.com/blog/postgres-reads-cause-writes/Exploring how SELECT queries can produce disk writes (post by Nikolay Sivko) https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f Hint Bits (wiki) https://wiki.postgresql.org/wiki/Hint_Bitspg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html pg_stat_kcache https://github.com/powa-team/pg_stat_kcache pg_wait_sampling http...2025-01-2426 min

Postgres FMIs pg_dump a backup tool?Michael and Nikolay are joined by Gülçin Yıldırım Jelínek and Robert Haas to discuss both the technical question of whether or not pg_dump is a backup tool, as well as the tone and intent behind the statement "pg_dump is not a backup tool". Here are some links to things they mentioned:Gülçin Yıldırım Jelínek https://postgres.fm/people/gulcin-yildirim-jelinekRobert Haas https://postgres.fm/people/robert-haasWhy you should upgrade PostgreSQL today (blog post by Gülçin) https://xata.io/blog/cve-2024-7348-postgres-upgra...2024-11-2248 min

Postgres FMAppend-only tablesNikolay and Michael discuss append-only tables in Postgres — what they are, some unique challenges they bring, and some options for compressing / removing / offloading the data eventually. Here are some links to things they mentioned:Append-only https://en.wikipedia.org/wiki/Append-onlyOur episode on BRIN indexes https://postgres.fm/episodes/brin-indexesTips to Improve Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/ Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningautovacuum_vacuum_insert_scale_factor https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR Our episode about compression https://postgres.fm/episodes/compressionNew project announced recen...2024-11-1544 min

Postgres FMDenormalizationNikolay and Michael discuss denormalization in Postgres — when and why to denormalize things, and a couple of specific cases Nikolay came across recently. Here are some links to things they mentioned:Denormalization https://en.wikipedia.org/wiki/DenormalizationOur episode on materialized views https://postgres.fm/episodes/materialized-viewsOur episode on data model trade-offs https://postgres.fm/episodes/data-model-trade-offsOur episode with Markus Winand https://postgres.fm/episodes/modern-sqlUniversal Relation Data Modelling Considered Harmful (blog post by Michael Stonebraker and Álvaro Hernández) https://www.enterprisedb.com/blog/universal-relation-data-modelling-considered-harmfulBoyce–Codd normal form https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_nor...2024-11-0835 min

Postgres FMPostgres GotchasNikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way. Here are some links to things they mentioned:Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownPostgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.htmlOur episode on slow count https://postgres.fm/episodes/slow-countDiscussion on X about major version upgrades and statistics https://x.com/samokhvalov/status/1844593601638260850Our episode on upgrades https://postgres.fm/episodes/upgradesStatistics Import and Export (commitfest entry which link to email thread) https://commitfest.postgresql.org/50/4538/vacuumdb https://www.p...2024-10-1840 min

Postgres FMPostgres Emergency RoomNikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too. Here are some links to things they mentioned:Site Reliability Engineering resources from Google https://sre.googleGitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineerKeeping Customers Streaming — The Centralized Site Reliability Practice at Netflix https://netflixtechblog.com/keeping-customers-streaming-the-centralized-site-reliability-practice-at-netflix-205cc37aa9fbOur monitoring checklist episode https://postgres.fm/episodes/monitoring-checklistHannu Krosing talk on Postgres TV — Do you vacuum everyday? https://www.youtube.com/watch?v=JcRi8Z7rkPgOur episode on corruption https://postgres.fm/episodes/corruptionNikolay’s episode o...2024-08-3045 min

Postgres FMGet or CreateMichael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat. Here are some links to things they mentioned:Haki Benita https://hakibenita.com How to Get or Create in PostgreSQL (blog post by Haki) https://hakibenita.com/postgresql-get-or-create "Find-or-insert" using a single query (how-to guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0036_find-or-insert_using_a_s...2024-08-2350 min

Postgres FMIndex-Only ScansNikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice. Here are some links to things they mentioned:Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.htmlDiscussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scansHeap Fetches https://www.pgmustard.com/docs/explain/heap-fetchesRows Removed By Filter https://www.pgmustard.com/docs/explain/rows-removed-by-filterrandom_page_cost https://postgresqlco.nf/doc/en/par...2024-08-0939 min

Postgres FMFour million TPSNikolay talks Michael through a recent experiment to find the current maximum transactions per second single-node Postgres can achieve — why he was looking into it, what bottlenecks occurred along the way, and ideas for follow up experiments. Here are some links to things they mentioned:How many TPS can we get from a single Postgres node? (Article by Nikolay) https://www.linkedin.com/pulse/how-many-tps-can-we-get-from-single-postgres-node-nikolay-samokhvalov-yu0rcChat history with Postgres AI bot https://postgres.ai/chats/01905a83-4573-7dca-b47a-bb60ce30fe6cOur episode on the overhead of pg_stat_statements and pg_stat_kcache https://pos...2024-07-0545 min

Postgres FMShould we use foreign keys?Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them. Here are some links to things they mentioned:Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FKOur episode about constraints: https://postgres.fm/episodes/constraintsGitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rbAdding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188Blo...2024-06-2151 min

Postgres FMFull text searchNikolay and Michael discuss full text search in Postgres — some of the history, some of the features, and whether it now makes sense to try to replace or combine it with semantic search. Here are some links to things they mentioned:Full Text Search https://www.postgresql.org/docs/current/textsearch.htmltsearch2 https://www.postgresql.org/docs/9.6/tsearch2.htmlDictionaries https://www.postgresql.org/docs/current/textsearch-dictionaries.html RUM index https://github.com/postgrespro/rum Okapi BM25 https://en.wikipedia.org/wiki/Okapi_BM25 tf–idf https://en.wikipedia.org/wiki/Tf%E2%80%93idf unaccent https://www.postgresq...2024-05-2450 min

Postgres FMMinor releasesNikolay and Michael discuss Postgres minor releases — how the schedule works, options for upgrading to them, and the importance of reading the release notes. Here are some links to things they mentioned:PostgreSQL 16.3, 15.7, 14.12, 13.15, and 12.19 released (announcement) https://www.postgresql.org/about/news/postgresql-163-157-1412-1315-and-1219-released-2858/ PostgreSQL versioning policy https://www.postgresql.org/support/versioning/ PostgreSQL 14.4 release notes (most recent minor release not on the usual schedule) https://www.postgresql.org/docs/release/14.4/ Minor release roadmap https://www.postgresql.org/developer/roadmap/ Our last episode on upgrades (major and minor) https://postgres.fm/episodes/upgrade...2024-05-1739 min

Postgres FMCustom vs generic planNikolay and Michael discuss custom and generic planning in prepared statements — how it works, how issues can present themselves, some ways to view the generic plan, and some benefits of avoiding planning (not just time). Here are some links to things they mentioned:PREPARE https://www.postgresql.org/docs/current/sql-prepare.html track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE plan_cache_mode https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE EXPLAIN (GENERIC_PLAN) https://www.postgresql.org/docs/current/sql-explain.html#id-1.9.3.148.8 EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laure...2024-05-1029 min

Postgres FMLIMIT vs PerformanceNikolay and Michael discuss LIMIT in Postgres — what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!) Here are some links to things they mentioned:LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987 LIMIT and OFFSET (docs) https://www.postgresql.org/docs/current/queries-limit.html No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT ~~~What did you like or not like? What sh...2024-05-0345 min

Postgres FMBuffers II (the sequel)Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17. Here are some links to things they mentioned:BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default Lightning talk by Michael at pgDay Paris (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/ ~~~What did you like or not like? What should we dis...2024-04-2636 min

Postgres FMMassive DELETEsNikolay and Michael discuss doing massive DELETE operations in Postgres — what can go wrong, how to prevent major issues, and some ideas to minimise their impact. Here are some links to things they mentioned:Article based on Nikolay’s talk, including batching implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=true Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningEgor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf full_page_writes https://w...2024-04-1944 min

Postgres FMLogical replication common issuesNikolay and Michael are joined by Sai Srirampur, CEO and Co-founder of PeerDB, to discuss how to overcome several logical replication issues. They discuss the protocol versions, minimising slot growth, minimising lag, and some tips and tricks for scaling things well. Here are some links to things they mentioned:PeerDB https://www.peerdb.io/Our episode on logical replication https://postgres.fm/episodes/logical-replicationExploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.htmlREPLICA IDENTITY FULL https://www.po...2024-04-1238 min

Postgres FMDon't do thisNikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included. Here are some links to things they mentioned:Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_ThisHow to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.mdDon’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-thisDon’t use NOT IN (j...2024-04-0544 min

Postgres FMHealth checkNikolay and Michael discuss Postgres health checks — what they are, things to include, how often makes sense, and whether improvements to Postgres would increase or decrease the need for them. Here are some links to things they mentioned:MOT (car test in the UK) https://en.wikipedia.org/wiki/MOT_test Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup Why upgrade https://why-upgrade.depesz.com/ Related episodes: Default configuration https://postgres.fm/episodes/default...2024-03-2240 min

Postgres FMsuperuserNikolay and Michael discuss the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing). Here are some links to things they mentioned:superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-availableSupabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuserCrunchy Data docs https://docs.crunchybridge.com/concepts/usersRDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserG...2024-03-1530 min

Postgres FMtransaction_timeoutNikolay and Michael discuss transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future. Here are some links to things they mentioned:transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUTCommit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 Mailing list thread: https://www.postgresql.org/message-id/flat/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com Hacking Postg...2024-03-0826 min

Postgres FMRails + PostgresMichael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better. Here are some links to things they mentioned:Planet Argon survey https://rails-hosting.com/2022/#databasesActive Record https://guides.rubyonrails.org/active_record_basics.htmlPostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.htmlMultiple Databases with Active Record https://guides.rubyonrails.org/act...2024-03-0145 min

Postgres FMWhy isn't Postgres using my index?Nikolay and Michael discuss a common question — why Postgres isn't using an index, and what you can do about it! Here are some links to things they mentioned:Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index enable_seqscan (and similar parameters) https://www.postgresql.org/docs/current/runtime-config-query.html Crunchy Bridge changed random_page_cost to 1.1 https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1 Make indexes invisible...2024-02-2335 min

Postgres FMOverhead of pg_stat_statements and pg_stat_kcacheNikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!) Here are some links to things they mentioned:pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache https://github.com/powa-team/pg_stat_kcachepg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faqWhat is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statementsTowards Millions TPS (blog...2024-02-1634 min

Postgres FMPartitioning by ULIDNikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time). Here are some links to things they mentioned:Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8UUIDv7 patch https://commitfest.postgresql.org/43/4388/ Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems Nikolay’s howto post on UUID v7...2023-12-2238 min

Postgres FMHash indexesNikolay and Michael discuss hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable). Here are some links to things they mentioned:Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) https://hakibenita.com/postgresql-hash-index Hash indexes intro (docs) https://www.postgresql.org/docs/current...2023-12-1530 min

Postgres FMConstraintsNikolay and Michael discuss PostgreSQL constraints — the six different types we have, how to add and modify them, including on large tables with high TPS. Here are some links to things they mentioned:Constraints (docs) https://www.postgresql.org/docs/current/ddl-constraints.htmlUnique index vs unique constraint issue (2017) https://www.postgresql.org/message-id/flat/CANNMO%2B%2B7%2BuVMj%2BZPGKB_zuRpKE0sNthy2Z-_oKSS%3DoE3_rN7QA%40mail.gmail.com Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownAdvanced Int to Bigint Conversions (talk by Robert Treat) https://www.youtube.com/watch?v=_IieyXzdmcM Use BIGINT in Postgr...2023-12-0839 min

Postgres FMEventsNikolay and Michael discuss PostgreSQL events — whether in-person or online, large conferences or small meet-ups, as well as some strong opinions based on their experiences attending, speaking, and organising them. Here are some links to things they mentioned:PGSQL Phriday #014: PostgreSQL Events https://www.cybertec-postgresql.com/en/pgsql-phriday-014-postgresql-events/ PGCon https://www.pgcon.org/ Highload https://highload.rs/ The San Francisco Bay Area PostgreSQL Meetup Group https://www.meetup.com/postgresql-1/ Our episode on “Why is Postgres popular?” https://postgres.fm/episodes/why-is-postgres-popular PGConf EU https://pgconf.eu/ Open talks series on Postgres TV https://www.youtube.com/pl...2023-12-0152 min

Postgres FMSubtransactionsNikolay and Michael discuss PostgreSQL subtransactions — what they are, and four issues they can cause at scale. Here are some links to things they mentioned:Subtransactions (docs) https://www.postgresql.org/docs/current/subxacts.html SAVEPOINT (docs) https://www.postgresql.org/docs/current/sql-savepoint.html PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful Subtransactions and performance in PostgreSQL (blog post by Laurenz at Cybertec) https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ Notes on some PostgreSQL implementation details (blog post by Nelson Elhage) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/ Why we spent the last mont...2023-11-2424 min

Postgres FMCompanion databasesNikolay and Michael discuss companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors. Here are some links to things they mentioned:Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture Heap recently moved their core analytics to SingleStore (we only spotted this after recording 🤦‍♂️) https://www.heap.io/blog/heaps-next-generation-data-platform Posthog moved their analytics from Postgres to Clickhouse https://posthog.com/blog/clickhouse-vs-postgres Timescale https://www.timescale.com/ Citus https://www.citusdata.com/ Hy...2023-11-1745 min

Postgres FMBlue-green deploymentsNikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally. Here are some links to things they mentioned:Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL https://aws.amazon.com/blogs/database/new-fully-managed-blue-green-deployment-in-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/ Blue-green deployment (blog post by Martin Fowler) https://martinfowler.com/bliki/BlueGreenDeployment.html Our episode on logical replication https://postgres.fm/episodes/logical-replication pgroll https://github.com/xataio/pgroll 2023-11-1043 min

Postgres FMData model trade-offsNikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play. Here are some links to things they mentioned:Our episode on JSON https://postgres.fm/episodes/json PostgreSQL limits https://www.postgresql.org/docs/current/limits.htmlBoyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form Our episode on over-indexing https://postgres.fm/episodes/over-indexing Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance Query planner...2023-11-0338 min

Postgres FMUnder-indexingNikolay and Michael discuss under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones. Here are some links to things they mentioned:Indexes (docs) https://www.postgresql.org/docs/current/indexes.html random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlauto_explain https://www.postgresql.org/docs/current/auto-explain.html Our epis...2023-10-2734 min

Postgres FMOver-indexingNikolay and Michael discuss over-indexing — what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently. Here are some links to things they mentioned:Nikolay’s tweet on over-indexing https://twitter.com/samokhvalov/status/1713101666629927112 Heap-Only Tuples (HOT) optimization https://www.postgresql.org/docs/current/storage-hot.html Our episode on index maintenance https://postgres.fm/episodes/index-maintenance PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0 Our episode on connection poolers https://postgres.fm/episodes/connection-poolers Configurable FP_LOCK_SLOTS_PER_B...2023-10-2042 min

Postgres FMQuery hintsNikolay and Michael discuss query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of. Here are some links to some extra things they mentioned:Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion An example mailing list thread from 2006 https://www.postgresql.org/messa...2023-10-1335 min

Postgres FMStop and start Postgres fasterIn this episode (recorded live on YouTube), Nikolay discusses Postgres shutdown and startup times – how to troubleshoot them and, when needed, optimize. Some extra things mentioned in this episode:#PostgresMarathon series – every day, Nikolay posts a new howto-style article https://twitter.com/hashtag/PostgresMarathonDay 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtosEpisode 010 "WAL and checkpoint tuning": https://postgres.fm/episodes/wal-and-checkpoint-tuning~~~What did you like or not like...2023-10-0729 min

Postgres FMBackupsNikolay and Michael discuss Postgres backups — why we need them, what the options are, whether a dump is a backup or not, and some considerations for lowering RPO and RTO at scale. Here are some links to some extra things they mentioned:pg_dump https://www.postgresql.org/docs/current/app-pgdump.html pg_basebackup https://www.postgresql.org/docs/current/app-pgbasebackup.htmlpgBackRest https://github.com/pgbackrest/pgbackrest WAL-G https://github.com/wal-g/wal-g Barman https://github.com/EnterpriseDB/barman Data loss at GitLab (2017) https://about.gitlab.com/blog/2017/02/01/gitlab-dot-com-database-incident/ Dev Deletes Entire Production Database, Chaos Ensues (YouTube v...2023-09-2943 min

Postgres FMPostgres 16Nikolay and Michael discuss the release of PostgreSQL 16 — the most important new features, what they mean for us as users, whether and when to upgrade, and more. Here are some links to some extra things they mentioned:Release notes https://www.postgresql.org/docs/current/release-16.htmlNew Features With Examples (PDF from Noriyoshi Shinoda of Hewlett Packard Enterprise Japan) https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL16Beta1_New_Features_en_20230528_1.pdf Why Upgrade? (site by depesz) https://why-upgrade.depesz.com/Waiting for PostgreSQL 16 (blog post series from Depesz) https://www...2023-09-2239 min

Postgres FMLogical replicationNikolay and Michael discuss logical replication — some history, initialization, change data capture, how to scale it, some limitiations, and ways that it is getting better. Here are some links to some things they mentioned:Logical replication https://www.postgresql.org/docs/current/logical-replication.html GitLab upgraded multi-terabyte, heavily-loaded clusters with zero-downtime https://twitter.com/samokhvalov/status/1700574156222505276 pg_waldump https://www.postgresql.org/docs/current/pgwaldump.html pg_dump and external snapshots (blog post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/ Failover of logical replication slots in Patroni (talk by Alexander Kukushkin) https://www.youtube...2023-09-1543 min

Postgres FMOur favourite v16 featureNikolay and Michael discuss their favourite feature each from the upcoming PostgreSQL 16 release. Here are some links to some things they mentioned:v16 draft release notes https://www.postgresql.org/docs/16/release-16.htmlPGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/ Subscribe options for the podcast https://postgres.fm/subscribeA recent closed source ClickHouse feature https://github.com/ClickHouse/ClickHouse/issues/44767#issuecomment-1683293218 Postgres TV hacking session with Andrey Borodin on \watch with limited number of loops (v16) https://www.youtube.com/watch?v=vTV8XhWf3mo Allow \watch queries to stop on minimum rows retu...2023-09-0827 min

Postgres FMConnectionsNikolay and Michael discuss connections — the options, security and performance tradeoffs, and a few other things to be aware of. Here are some links to some things they mentioned:Episode on connection poolers https://postgres.fm/episodes/connection-poolers listen_addresses https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-LISTEN-ADDRESSES SSLMODE Explained (blog post by Andrew Kane) https://ankane.org/postgres-sslmode-explained pg_hba.conf https://www.postgresql.org/docs/current/auth-pg-hba-conf.htmlTiming a query (blog post by Bruce Momjian) https://momjian.us/main/blogs/pgblog/2012.html#June_6_2012 How to connect (blog post by Lætitia Avrot) https://mydbanot...2023-09-0128 min

Postgres FMDecoupled storage and computeNikolay and Michael discuss a listener question — about products that take Postgres and transform it to something that decouples compute from storage (RDS Aurora, GC AlloyDB, Neon etc.) and whether they see something like this landing upstream in the medium term.Here are some links to some things they mentioned:Amazon Aurora https://aws.amazon.com/rds/aurora/Google Cloud AlloyDB for PostgreSQL https://cloud.google.com/alloydbNeon https://neon.tech/ Google Cloud Spanner https://cloud.google.com/spannerIs Aurora PostgreSQL really faster and cheaper than RDS PostgreSQL? (blog post by Avinash Vallarapu from MigOps) https://www...2023-08-2542 min

Postgres FMSelf-managingNikolay and Michael discuss self-managing Postgres — both the practicalities of doing so, as well as some managed-service style tooling. Here are some links to some things they mentioned:Our episode on Managed services vs. DIY https://postgres.fm/episodes/managed-services-vs-diy WAL-G https://github.com/wal-g/wal-g pgBackRest https://pgbackrest.org/ Barman https://github.com/EnterpriseDB/barman Dead Man’s Snitch https://deadmanssnitch.com/ Netdata https://www.netdata.cloud/ Upgrades https://postgres.fm/episodes/upgrades High availability https://postgres.fm/episodes/high-availability Configuration https://postgres.fm/episodes/default-configuration Corruption https://postgres.fm/episodes/corruption Connection poolers https://postgres.fm/epis...2023-08-1837 min

Postgres FMShardingNikolay and Michael discuss sharding Postgres — what it means, why and when it's needed, and the available options right now. Here are some links to some things they mentioned:PGSQL Friday monthly blogging event https://www.pgsqlphriday.com/Did “sharding” come from Ultima Online? https://news.ycombinator.com/item?id=23438399 Our episode on partitioning: https://postgres.fm/episodes/partitioningVitess https://vitess.io/Citus https://www.citusdata.com/ Lessons learned from sharding Postgres (Notion 2021) https://www.notion.so/blog/sharding-postgres-at-notion The Great Re-shard (Notion 2023) https://www.notion.so/blog/the-great-re-shard The growing pains of database architecture (Figma 2023) https://www...2023-08-1139 min

Postgres FMData typesNikolay 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.pgmustard.com/docs/explain/tid-scan Don’t do this (wiki) https://wiki.postgresql.org/wiki/Don't_Do_This Boundless `text` and back again https://brandur.org/text UUID episode https://postgres.fm/episodes/uuid I use ENUM (30min talk by Boriss Mejías) http...2023-08-0437 min

Postgres FMHigh availabilityNikolay 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 Hacker News https://news.ycombinator.com/item?id=36405761 https://postgres.fm/episodes/connection-poolers https://www.postgresql.org/docs/current/libpq.html Support load balancing in libpq (new feature in Postgres 16) https://commitfest.postgresql.org/42/3679/ target_session_att...2023-07-2840 min

Postgres FMBeginner tipsNikolay 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 rowsRelated episodes: https://postgres.fm/episodes/how-to-become-a-dba and https://postgres.fm/episodes/hot-updates Tip 2: always use EXPLAIN (ANALYZE, BUFFERS)Related episode: https://postgres.fm/episodes/buffers-by-default Tip 3: throw away pgAdminRelated episode: https://postgres.fm/episodes/psql-vs-guis Tip 4: enable as much logging as you can affordRe...2023-07-2140 min

Postgres FMConnection poolersNikolay 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) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462 PgBouncer https://github.com/pgbouncer/pgbouncerOdyssey https://github.com/yandex/odysseyPgCat https://github.com/postgresml/pgcat Adopting PgCat: A Nextgen Postgres Proxy https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy/ Supavisor https://github.com/supa...2023-07-1431 min

Postgres FMpg_upgrade: the tricky and dangerous partsNikolay (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 https://github.com/shayonj/pg_easy_replicate/- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding 2023-06-3025 min

Postgres FMUUIDLonely 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=storyUnexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cybertec): https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/HN discussion of that post: https://news.ycombinator.com/item?id=36429986Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bisStatus of...2023-06-2322 min

Postgres FMExtensionsNikolay 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 PL/Rust https://github.com/tcdi/plrustZomboDB https://github.com/zombodb/zombodb Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular Citus https://github.com/citusdata/citusTimescaleDB https://github.com/timescale/timescaledb OrioleDB https://github.com/orioledb/o...2023-06-0930 min

Postgres FMZero-downtime migrationsNikolay 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 by Nikolay)lock_timeoutFast Column Creation with Defaults (blog post by Brandur)Database Schema Changes Without Downtime (new version of blog post by Braintree)Zero-downtime Postgres migrations - the hard parts (blog post f...2023-06-0228 min

Postgres FMParallelismNikolay 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 maintenanceOur episode about partitioning Patch to make postgres_fdw parallel-safe (by Swarm64) PostgreSQL Parallelism Do’s and Don’ts (talk by Sebastian Dressler)Increasing max_parallel_workers_per_gather (blog post by Michael)~~~What did you like or no...2023-05-2644 min

Postgres FMCorruptionNikolay 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 memeData Corruption talk by Sebastian Webber (on Postgres TV) Data corruption monitoring & troubleshooting talk by Andrey Borodin (on Postgres TV)Postgres data corruption (document from Nikolay) Data Corruption and Bugs Runbook (document from Nikolay)Corruption (Postgres wiki)Checksumspg_checksumsOriginal pg_checksums (by Credativ)amcheckOur episode on index m...2023-05-1942 min

Postgres FMpg_stat_statementsNikolay 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 PhridayObserver 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_stat_monitorPGConOur episode on query analysisMarginalia ~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by comm...2023-05-0545 min

Postgres FMauto_explainNikolay 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_monitorEXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres)pg_test_timingOur episode on benchmarkingDatabase Lab Engine~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social...2023-04-2837 min

Postgres FMQueues in PostgresNikolay 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 TPSWe also had a couple of audio issues, sorry!Here are links to a few things we mentioned: Recent discussion on Hacker NewsPgQWhat is SKIP LOCKED (blog post by Craig Ringer) autovacuumPostgres queues (blog post by Brandur)pg_repackOur episode on partitioningNikolay’s Twitter pollSubtransactions Considered Harmful (blog post by Nikolay)~~~2023-04-2139 min

Postgres FMRead-only considerationsNikolay 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 scansVacuumUK Covid-19 dashboardpg_repackPartitioningOur episode on BRIN indexesAlways load sorted data (blog post by Haki Benita)GIN indexes: the good and the bad (blog post by Lukas Fittl)Our episode on materialised viewspg_buffercacheTowards Millions TPS (blog post by Alexander Korotkov)Postgres WASM (by Snaplet and Supabase)YugabyteAWS Aurora Continuous Archiving and Point-in-Time Recovery (docs)Our episode on c...2023-04-1439 min

Postgres FMPartitioningNikolay 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 docspg_partmanIndex maintenance episode Timescale partitioningpg_cronXtreme PostgreSQL (talk by Christophe Pettus)Database Antipatterns (also by Christophe, slides 46-49)Understanding an outage (blog post by Duffel)~~~What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to sh...2023-04-0734 min

Postgres FMPeter ZaitsevThis 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: PerconapgCloudHacker browser extension PMMPercona Distribution for PostgreSQLFerretDBPeter's Twitter profilePeter's LinkedIn profile------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this epis...2023-03-3143 min

Postgres FMpsql vs GUIsNikolay 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)pgAdminPostico DBeaverDataGripPgManage (new Command Prompt fork of OmniDB) PopSQLpostgres_dbapspgMaterialized views episodepgcli------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our G...2023-03-2431 min

Postgres FMUpgradesNikolay 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_upgradeLogical replication CHECKPOINTamcheckLocale data changes (e.g. glibc upgrades)ANALYZEUpgrades are hard (summary of panel discussion by Andreas 'ads' Scherbaum)spiloRecent pgsql hackers discussion about using logical and pg_upgrade together------------------------What did you like or not like? W...2023-03-1740 min

Postgres FMWait eventsNikolay 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 notesPostgreSQL Scalability (blog post by Alexander Korotkov)Wait event analysis in pganalyzeauto_explainDatabase Lab Enginetrack_io_timingpg_test_timingpgBadgerCorootOkmeterpgwatch2 Postgres.ai Editionpg_wait_samplingpgsentinelDatadogAWS RDS docs for PostgreSQL wait eventspgMustard newsletterPASH ViewerpgCenterIntro to query optimisation episodeMonitoring checklist episode------------------------What did you like or not like? What should we discuss next time? Let...2023-03-1031 min

Postgres FMTOASTNikolay 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 docsTOAST wikiHussein 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 (Timescale blog post)The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita)PostgreSQL at Scale: Saving Space Basically for Free (blog post by Braintree on column Tetris)postgres_dba alignment padding quer...2023-03-0328 min

Postgres FMJSONNikolay 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: hstoreXML typeXML functionsJSON typesJSON functionsJSONB indexingNULLS episodeWhy Postgres is popular episodePostgreSQL 12 release notesWhat’s New in SQL:2016 (blog post by Markus Winand)SQL/JSON is postponed (blog post by depesz) JSON[b] Roadmap (talk by Oleg Bartunov)Slides, with benchmarksRUM access methodJSON in PostgreSQL: how to use it right (blog post by Laurenz Albe from Cybertec)pg_jsonsch...2023-02-2431 min

Postgres FMReal-time analyticsNikolay 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 docsHydra founders interview (on Postgres TV)Materialised views episode pg_ivmTimescale continuous aggregates docsClickhouseSnowflakeReplication episodeTimescale bottomless storage on S3 (blog post)pg_partmanQuerying Postgres from DuckDB (blog post)Heap blog (filter by “Engineering”)Incremental View Maintenance (wiki)PostgreSQL HyperLogLog Faster counting (by Joe Nelson on the Cit...2023-02-1734 min

Postgres FMBenchmarkingNikolay and Michael discuss benchmarking — reasons to do it, and some approaches, tools, and resources that can help. Here are links to a few things we mentioned: Towards Millions TPS (blog post by Alexander Korotkov)Episode on testing Episode on buffers pgbenchsysbenchImproving Postgres Connection Scalability (blog post by Andres Freund)pgreplaypgreplay-goJMeterpg_qualstatspg_queryDatabase experimenting/benchmarking (talk by Nikolay, 2018)Database testing (talk by Nikolay at PGCon, 2022)Systems Performance (Brendan Gregg’s book, chapter 12)fioNetdataSubtransactions Considered Harmful (blog post by Nikolay including Netdata exports)WAL compression benchmarks (by Vitaly from Postgres.ai)Dumping/restoring a 1 TiB database benchmarks (by Vital...2023-02-1036 min

Postgres FMDefault configurationNikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.Here are links to a few things we mentioned: shared_buffersAndres Freund tweets about shared_buffers PGTune Leopard Cybertec Configuratorpg_stat_statementsJIT configurationpostgresqlco.nfannotated.confOtterTunework_memrandom_page_costmax_connectionsWhat to logmax_wal_sizeWAL and checkpoint tuning episode effective_cache_sizeIntro to Performance Tuning and Optimization (EDB guide)max_parallel_workers_per_gather ------------------------What did you like or not like? What should...2023-02-0332 min

Postgres FMInfra cost optimizationHere are links to a few things we mentioned: The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz)OVHHetznerpostgresql_clusterWhy we're leaving the cloud (blog post by DHH from Basecamp)Managed services vs. DIY episodeec2instances.infoVantagePostgres TV episode with Everett Berry from VantageMigrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium)Database Lab EnginePostgres.ai consultingNetdatasysbenchfioQuery macro analysis episodeTop queries by buffers (Gist from Michael)------------------------What did you like or not like? What should we discuss next time? Let us k...2023-01-2728 min

Postgres FMAuditingHere are links to a few things we mentioned: Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG)pgAudit (site)pgAudit (repo)noset (extension)SplunkKibanaTimescaleCREATE TRIGGER docsTransition table triggers (blog post by David Fetter)Table Audit (blog post by Lorenzo Alberton)Row change auditing options (blog post by CYBERTEC)Hydra founders interview (on Postgres TV)max_slot_wal_keep_sizeeBPFBuilding a perf-like tool for PostgreSQL (talk by Ronan Dunklau)Party tricks for PostgreSQL: perf, ftrace and bpftrace (talk by Dmitry Dolgov)------------------------What did you like or not like? What should we d...2023-01-2035 min

Postgres FMCopying a databaseHere are links to a few things we mentioned: pg_dump pg_restore pgcopydb (tool by Dimitri Fontaine) pg_dump and external snapshots (blog post by Michael Paquier) Systems Performance (book by Brendan Gregg) Performance troubleshooting methodologies (two part talk by Brendan Gregg) pg_basebackup wal-g pgBackRest Database Lab Engine Database branching episode ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this e...2023-01-1339 min

Postgres FMTransaction ID wraparoundHere are links to a few things we mentioned: The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI) PostgreSQL 14 internals chapter 7 (book by Egor Rogov) Transaction ID Wraparound (blog post from Sentry) What We Learned from the Recent Mandrill Outage (blog post from Mailchimp) How to simulate transaction ID wraparound (blog post by Prashant Dixit)Add 64-bit XIDs into PostgreSQL 16 (commitfest entry) Partitioning (docs) Consider using VACUUM’s INDEX_CLEANUP option (tweet by Peter Geoghan) Add wraparound failsafe to VACUUM (commit) Do you vacuum everyday? (talk by Hannu Krosing) Multixacts wraparound monitoring (mailing list thread) Subtransactions Considered Harmful (blog post by...2023-01-0635 min

Postgres FMPostgres year in review 2022Here are links to a few things we mentioned: 1. Startups building momentumAiven raised $210m Timescale raised $110m Hasura raised $100m Supabase raised $80m Neon raised $30m Hydra OrioleDB 2. Educational resourcesPostgres FM started 👋Postgres TV became more active (including topic playlists) Tobias Petry tips on Twitter and SQL for Devs Hussein Nasser YouTube channel (backend engineering) Postgres Weekly (newsletter) 3. Sharding progressCitus goes fully open source SPQR pgcat Sharding Postgres at Notion (blog post) PlanetScale (MySQL) 4. Database branching is comingDatabase Lab Engine Neon branching OrioleDB branching Crunchy Bridge Database branching episo...2022-12-3033 min

Postgres FMRow estimatesHere are links to a few things we mentioned: ANALYZE (docs)Autovacuum config (docs)Statistics used by the planner (docs) CREATE STATISTICS (docs) Row count estimates (pgMustard blog post) pg_hint_plan Optimizer methodology (talk by Robert Haas) Tomáš Vondra on statistics and hints (an excellent interview we forgot to mention, sorry!) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this epis...2022-12-2331 min

Postgres FMReplicationHere are links to a few things we mentioned: Replication (docs) 9.0 release notes (including built-in replication) SlonyLondistepg_is_in_recovery (pgPedia page) Patroni Multiple Databases with Active Recordhot_standby_feedback (docs) max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively)synchronous_commitSynchronous replicasDatabase Lab Engine Neon Branching Past, Present, and Future of Logical Replication (Postgres TV with Amit Kapila) Failover of logical replication slots in Patroni (Postgres TV with Alexander Kukushkin) PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) Why we spent the last month eliminating PostgreSQL subtransactions (blog post by GitLab) Fivetran ...2022-12-1637 min

Postgres FMTimestampsHere are links to a few things we mentioned: Date/Time Types (docs) Don’t use timestamp without time zone (wiki) Date/Time functions and operators (docs) Postgres AT TIME ZONE explained (blog post by Bruce Momjian) Our episode on BRIN indexes RUM indexes allballs (mailing list thread) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link...2022-12-0926 min

Postgres FMPostgRESTHere are links to a few things we mentioned: PostgREST documentationSupabaseNo offset (blog post by Markus Winand)JSON functions and operators (PostgreSQL docs)Stored procedures (a previous Postgres FM episode)PostGraphile Hasura Parse, Inc. FirebasePostgresMLPgQ ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:2022-12-0229 min

Postgres FMMaterialized viewsApologies, Michael's audio is not great in this, we'll hopefully be back to normal next week!Here are links to a few things we mentioned: Materialized views (docs)Refresh materialized view (docs)Timescale blog postPlanetScale Boost (content warning: MySQL) Incremental Materialized Views with pg_ivm (video by Lukas Fittl) Articles on how to do your own incremental updates(?)Materialize (company) Materialize talkIncremental View Maintenance (Postgres wiki) Implementing Incremental View Maintenance (mailing list thread) ------------------------What did you like or not like? What should we discuss next time? Let us know b...2022-11-2541 min

Postgres FMHOT updatesHere are links to a few things we mentioned: Heap-only tuple updates (docs)README file (source code)Heap Only Tuple chapter (Internals of PostgreSQL by Hironobu SUZUKI) How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay) Why Uber switched from Postgres to MySQL (blog post) Fighting write amplification with HOT updates (Adyen blog post) HOT Updates vs Bottom-Up Index Deletion (video by Lukas Fittl from pganalyze) Reduced Bloat with Bottom-Up Deletion (blog post by Hamid Akthar from Percona) What is fillfactor and how does it affect performance? (blog post by Kaarel Moppel from Cyberte...2022-11-1830 min

Postgres FMDatabase branchingHere are links to a few things we mentioned: Database Lab EngineOverview of Aurora cloning PlanetScale branchingCommon DB schema change mistakes (blog post by Nikolay)Supabase visionNeon branchingShift-left testing------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.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 Po...2022-11-1131 min

Postgres FMVersion control for databasesHere are links to a few things we mentioned: A brief history of Version Control Systems (blog post by Mehran Hajirajabi)Redgate Source Control for Oracle (and SQL Server)Flyway Sqitch Liquibase pgAdmin Schema Diff Migra PostgresCompare Schema based versioning and deployment (blog post by Valentine Gogichashvili)Change management tools and techniques (PostgreSQL Wiki)GitLab migration_helpers.rb Database schema changes without downtime (blog post by James Coleman from Braintree/PayPal) Zero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay) ------------------------What did you like or not like? What...2022-11-0431 min

Postgres FMContributing to PostgresHere are links to a few things we mentioned: PostgreSQL 14 coin (upside down!)Intro to Planner Hacking (talk by Melanie Plageman)Demystifying contributing to PostgreSQL (talk by Lætitia Avrot)How to become a PostgreSQL contributor (blog post by Aleksander Alekseev)Compile and install from source code (PostgreSQL Wiki)PostgreSQL mailing listsGitHub PostgreSQL mirrorGitLab PostgreSQL mirrorCommitfestsSo, you want to be a developer? (PostgreSQL Wiki)Resolving the search engine issue (mailing list thread)Planet PostgreSQLpg_stat_monitor (extension by Percona)ZomboDBpgx (framework for developing extensions)Awesome PostgresDepesz blog ------------------------What did you lik...2022-10-2833 min

Postgres FMStored proceduresHere are links to a few things we mentioned: Extending SQL PostgREST PL/pgSQL EdgeDB pgTAP Sqitch Flyway Liquibase client_min_messages log_min_messagesRAISE DEBUGSimplify: move code into database functions (blog post by Derek Sivers) PL/sh Supabase auto_explain.log_nested_statements Hasura ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)...2022-10-2135 min

Postgres FM102 Query optimizationHere are links to a few things we mentioned: pg_plan_advsr How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay)Why Uber Switched from Postgres to MySQL (blog post)pganalyze index advisorNancy bot (project is not active)pgreplaypgreplay go Real Application Testing on YugabyteDB with pgreplay (blog post by Franck Pachot) pg_query Database Lab thin clones Migrating to Aurora: easy except the bill (blog post by Kimberley Nicholls) ------------------------What did you like or not like? What should we discuss next time? Let us know by...2022-10-0731 min

Postgres FMIndex maintenanceImportant correction from this episode: amcheck promises no false positives, not no false negatives, sorry!Here are links to a few things we mentioned: pgstattuplepg_repackpostgres-checkup Workload Analysis for GitLab.comBloat Analysis (also from GitLab)Bloat, pg_repack, and deferred constraints (blog post by Miro) amcheckPeter Geoghegan interview (on Postgres TV) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas Google doc.If you would like...2022-09-2332 min

Postgres FMQuery macro analysis introHere are links to a few things we mentioned: pg_stat_statementspgFouinepgBadgerpg_querypg_stat_activityauto_explainCan auto_explain (with timing) have low overhead? (blog post by Michael)track_io_timingpgbenchPgHeropgCenterpgwatch2 (Postgres AI edition)pg_stat_kcachePASH Viewer------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres F...2022-09-1633 min

Postgres FMWAL and checkpoint tuningHere are links to a few things we mentioned: LSN (log sequence number)On the impact of full-page writes (blog post by Tomas Vondra)Deep dive on Aurora Postgres (talk by Grant McAlister) Netdata monitoring PostgresqlCO.NF pgPedia ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is broug...2022-09-0937 min

Postgres FMIntro to query optimizationHere are links to a few things we mentioned: Using EXPLAIN (PostgreSQL documentation)explain.depesz.comexplain.dalibo.compgMustard EverSQLpganalyzepg_stat_monitor (Extension by Percona)Recent thread on hackers mailing list about plan_id in pg_stat_activityauto_explainEXPLAIN observer effect (Ongres blog post by Álvaro Hernández)auto_explain overhead (blog post by Michael) pg_test_timingDatabase Lab Engine (for thin clones)Our previous episode on BUFFERSEXPLAIN Explained (talk by Josh Berkus)A beginner's guide to EXPLAIN (talk by Michael)A deeper dive into EXPLAIN (talk by Michael)EXPLAIN glossary (pgMustard docs)Topic suggestions document...2022-09-0233 min

Postgres FMHow to become a DBAAnd few things we mentioned: Topic request on Reddit — thanks HerbyHoover!Haki Benita's blog PostgreSQL documentation (table of contents) Planet PostgreSQL (blog aggregator) MVCC Unmasked (by Bruce Momjian) The Internals of PostgreSQL (by Hironobu SUZUKI)PostgreSQL 14 Internals — parts I and II (by Egor Rogov)Cybertec blogmodern-sql.com (by Markus Winand)use-the-index-luke.com (by Markus Winand)The Art of PostgreSQL (by Dimitri Fontaine) explain.depesz.comexplain.dalibo.com ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would l...2022-08-2632 min

Postgres FMMonitoring checklistMonitoring checklist (dashboard 1):TPS and (optional but also desired) QPSLatency (query duration) — at least average. Better: histogram, percentilesConnections (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 activityCommits vs rollbacks — how many transactions are rolled backTransactions left till transaction ID wraparoundReplication lags / bytes in replication slot / unused replication slotsCount of WALs waiting to be archived (archiving lag)WAL generation ratesLocks and deadlocksBasic query analysis graph...2022-08-1927 min

Postgres FMVacuumHere are links to a few things we mentioned: Do you vacuum everyday? (talk by Hannu Krosing)Autovacuum tuning (EDB guide) When autovacuum does not vacuum (2ndQuadrant blog post by Tomas Vondra) Autovacuum tuning basics (old 2ndQuadrant blog post)Discussion with Anastasia Lubennikova (on RuPostgres, in Russian) B-tree indexes (talk by Anastasia Lubennikova, in English) Discussion with Peter Geoghegan (on Postgres TV)pg_repack pg_squeeze ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf yo...2022-08-1232 min

Postgres FMNULLs: the good, the bad, the ugly, and the unknownHere are links to a few things we mentioned: Three-valued_logic (Wikipedia)Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert) Practical SQL for Data Analysis — Interpolation (blog post by Haki Benita) What is the deal with NULLs? (blog post by Jeff Davis) NULL in SQL: Indicating the Absence of Data (Markus Winand on Modern SQL) The Art of PostgreSQL (book by Dimitri Fontaine) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you w...2022-08-0527 min

Postgres FMBUFFERS by defaultHere are links to a few things we mentioned: EXPLAIN parameters (PostgreSQL documentation)EXPLAIN (ANALYZE) needs BUFFERS (blog post by Nikolay)Using BUFFERS for query optimization (blog post by Michael) H3 indexes on PostGIS data (blog post by Ryan Lambert)Turning BUFFERS on by default (latest patch)pgMustard explain.depesz.comexplain.dalibo.comDatabase Lab Engine------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a goo...2022-07-2933 min

Postgres FMBRIN indexesHere are links to the two main resources we mentioned: Paul Ramsey's recent blog post on BRIN indexesTomas Vondra's slides on BRIN index improvementsA few other things we mentioned:B-tree Wikipedia page pg_repack pg_squeeze ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:Ni...2022-07-2136 min

Postgres FMManaged services vs. DIYA well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅Here are links to most of them, roughly in the order they came up: How Auto Trader migrated its on-prem databases to Cloud SQLPostgreSQL Community Panel: UpgradabilityPostgres TV Open TalksPostgreSQL Conference EuropeHannu Krosing — excellent vacuum talkpg_docs_bot — browser extension for getting to the current docsAmazon RDS for PostgreSQLGoogle Cloud SQL for PostgreSQLHeroku PostgresCrunchy BridgeSpilo: HA PostgreSQL Clusters with DockerAiven for PostgreSQLAlloyDB for PostgreSQLNeonYugabyteScaleGrid PostgreSQL HostingStackGresTimescaleOrioleDBCitusSupabasePlanetScalepg_stat_kcachepg_wait_samplingEDB BigAnimalAzure Database for PostgreS...2022-07-1432 min

Postgres FMSlow queries and slow transactionsWhat did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)We also have an uncut video version on YouTube.Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 2022-07-0526 min