Postgres FM

Nikolay 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   pgbench sysbench Improving Postgres Connection Scalability (blog post by Andres Freund) pgreplay pgreplay-go JMeter pg_qualstats pg_query...

Nikolay 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_buffers Andres Freund tweets about shared_buffers   PGTune Leopard   Cybertec Configurator pg_stat_statements JIT configuration postgresqlco.nf annotated.conf OtterTune work_mem...

Here are links to a few things we mentioned:  The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz) OVH Hetzner postgresql_cluster Why we're leaving the cloud (blog post by DHH from Basecamp) Managed services vs. DIY episode ec2instances.info Vantage Postgres TV episode with Everett Berry from Vantage Migrating to Aurora: easy except the bill (blog post by Kimberly Nich...

Here are links to a few things we mentioned:  Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) pgAudit (site) pgAudit (repo) noset (extension) Splunk Kibana Timescale CREATE TRIGGER docs Transition 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)...

Here 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   -------------------...

Here 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 1...

Here are links to a few things we mentioned:  1. Startups building momentum Aiven raised $210m   Timescale raised $110m   Hasura raised $100m   Supabase raised $80m   Neon raised $30m   Hydra   OrioleDB   2. Educational resources Postgres FM started 👋 Postgres TV became more active (including topic playlists)   Tobias Petry tips on Twitter and SQL for Devs   Hussein Nasser YouTube channel (backen...

Here 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...

Here are links to a few things we mentioned:  Replication (docs)  9.0 release notes (including built-in replication)  Slony Londiste pg_is_in_recovery (pgPedia page)  Patroni   Multiple Databases with Active Record hot_standby_feedback (docs)  max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively) synchronous_commit Synchronous replicas Database...

Here 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...

Here are links to a few things we mentioned:  PostgREST documentation Supabase No offset (blog post by Markus Winand) JSON functions and operators (PostgreSQL docs) Stored procedures (a previous Postgres FM episode) PostGraphile   Hasura   Parse, Inc.   Firebase PostgresML PgQ   ------------------------ What did you like or not like? What should we discuss next time? Let us know by tweeting us on...

Apologies, 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 post PlanetScale Boost (content warning: MySQL)  Incremental Materialized Views with pg_ivm (video by Lukas Fittl)  Articles on how to do your own incremental updates(?) Materialize (com...

Here 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...

Here are links to a few things we mentioned:  Database Lab Engine Overview of Aurora cloning   PlanetScale branching Common DB schema change mistakes (blog post by Nikolay) Supabase vision Neon branching Shift-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 comm...

Here 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 migrati...

Here 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 lists GitHub PostgreSQL mirror GitLab PostgreSQL mirror...

Here are links to a few things we mentioned:  Extending SQL   PostgREST   PL/pgSQL   EdgeDB   pgTAP   Sqitch   Flyway   Liquibase   client_min_messages   log_min_messages RAISE DEBUG Simplify: 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...

Here are links to a few things we mentioned:  PostgreSQL 15 release notes Speeding up sort performance in Postgres 15 (blog post by David Rowley) Past, Present, and Future of Logical Replication (talk by Amit Kapila)  Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)  Do you vacuum everyday? (talk by Hannu Krosing)  Why upgrade PostgreSQL? (by depesz) What did you like or not like?...

Here 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 advisor Nancy bot (project is not active) pgreplay pgreplay go   Real Application Testing on YugabyteDB with pgreplay (blog post by Franck Pachot)  pg_query   Database Lab thin...

This episode was badly affected by internet issues. Hopefully the edit came out ok, but the quality should be back to a better level from next week. Here are links to a few things we mentioned:  Monthly blog event, PGSQL Phriday (blog post from Ryan Booz)  Who or what made Postgres cool? (tweet from Kenneth Cassel)  PostGIS   Acquisition of Sun by Oracle  DB-Engines trend Hacker News hiring trends...

Important correction from this episode: amcheck promises no false positives, not no false negatives, sorry! Here are links to a few things we mentioned:  pgstattuple pg_repack postgres-checkup   Workload Analysis for GitLab.com Bloat Analysis (also from GitLab) Bloat, pg_repack, and deferred constraints (blog post by Miro)  amcheck Peter Geoghegan interview (on Postgres TV)  ----------------------...

Here are links to a few things we mentioned:  pg_stat_statements pgFouine pgBadger pg_query pg_stat_activity auto_explain Can auto_explain (with timing) have low overhead? (blog post by Michael) track_io_timing pgbench PgHero pgCenter pgwatch2 (Postgres AI edition) pg_stat_kcache PASH Viewer ------------------------ What did you like or not like? What should we discuss next time? Let us know by tw...

Here 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 com...

Here are links to a few things we mentioned:  Using EXPLAIN (PostgreSQL documentation) explain.depesz.com explain.dalibo.com pgMustard   EverSQL pganalyze pg_stat_monitor (Extension by Percona) Recent thread on hackers mailing list about plan_id in pg_stat_activity auto_explain EXPLAIN observer effect (Ongres blog post by Álvaro Hernández) auto_explain overhead (blog post by Michael)   pg_test_tim...

And 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 blog modern-sql.com (by Markus Winand) use-the-index-luke.com (by Marku...