Interesting PostgreSQL Blog Articles

General

• Uber Engineering, 2016-05-26: Why Uber Engineering Switched from Postgres to MySQL PostgreSQL 8.3 (2008) - 9.3 (2013) vs MySQL 5.7 (2015 - 2023)
• Andreas ‘ads’ Scherbaum, 2024-06-14: PostgreSQL Development Conference 2024 - Review
• pgPedia
• Stefanie Janine Stölting, 2024-09-04: The Timescale PostgreSQL Survey 2024
• Hubert ‘depesz’ Lubaczewski, 2024-10-29: New way to search PostgreSQL documentation
• pgdoc ⚡ link
• Peter Eisentraut, EnterpriseDB, 2024-12-10: Where are all the PostgreSQL users?

Internals

• Ibrar Ahmed, Percona, 2019-10-17: Deep dive to PostgreSQL Indexes
• Laurenz Albe, Cybertec, 2020-04: PostgreSQL v13 new feature: tuning autovacuum on insert-only tables
• Craig Kerstiens, Crunchy Data, 2022-05-27: Postgres Databases and Schemas
• Andreas ‘ads’ Scherbaum, 2022-06-28: ctid and other PostgreSQL table internals
• Laurenz Albe, Cybertec, 2023-01: Transaction ID wraparound: a walk on the wild side
• Ants Aasma, Cybertec, 2023-06: Unexpected downsides of UUID keys in PostgreSQL
• Muhammad Ali, Stormatics, 2023-10-04: Vacuum in PostgreSQL
• Semab Tariq, Stormatics, 2024-04-18: PostgreSQL Internals Part 3: Understanding Processes in PostgreSQL
• Semab Tariq, Stormatics, 2024-05-16: PostgreSQL Internals Part 1: Understanding database cluster, database and tables
• Semab Tariq, Stormatics, 2024-05-22: PostgreSQL Internals Part 2: Understanding Page Structure
• Muhammad Ali, Stormatics, 2024-05-09: Understanding PostgreSQL Fill Factor: Benefits, Drawbacks, and Best Practices
• Muhammad Ali, Stormatics, 2024-05-15: Removing bloat with pg_repack-extension
• Laurenz Albe, Cybertec, 2024-05-28: Why do I have a slow COMMIT in PostgreSQL?
• Gilles Darold (CTO), HexaCluster, 2024-06-10: PGTT Extension for Global Temporary Tables in PostgreSQL
• Shane Borden, 2024-06-18: Reduce Vacuum by Using “ON CONFLICT” Directive
• Andreas ‘ads’ Scherbaum, 2024-06-19: Understanding VACUUM, VACUUM template0, and Transaction ID Wraparound
• Laurenz Albe, Cybertec, 2024-06-24: Making the PostgreSQL visibility map visible
• Laurenz Albe, Cybertec, 2024-07: What you should know about Linux memory overcommit in PostgreSQL
• Daniel Vérité, 2024-07-01: Looking at the new built-in collation provider in PostgreSQL 17
• Abhishek Deb, Percona, 2024-07-01: How to Remove an Orphan Large Object in PostgreSQL With Vacuumlo
• Hubert ‘depesz’ Lubaczewski, 2024-07-19: What tables were touched within given range of wal LSN?
• Hans-Jürgen Schönig, Cybertec, 2024-08: Speeding up things with hint bits
• Umair Shahid, Stormatics, 2024-08-08: Locks in PostgreSQL – Concurrency Benefits and Performance Challenges
• Andrei Lepikhov, 2024-08-19: Postgres query re-optimisation in practice - on PostgreSQL built-in reoptimisation
• Semab Tariq, Stormatics, 2024-08-20: PostgreSQL Internals Part 4: A Beginner’s Guide to Understanding WAL in PostgreSQL
• Shane Borden, 2024-09-04: Follow-Up: Reduce Vacuum by Using “ON CONFLICT” Directive
• Pavel Stehule, 2024-09-06: How to get info about relations between system tables?
• Hans-Jürgen Schönig, Cybertec, 2024-09-10: Why “SELECT * FROM table” is NOT a read
• Tomas Vondra, EnterpriseDB, 2024-10-14: Tuning the glibc memory allocator (for Postgres)
• Shiv Iyer, MinervaDB, 2024-10-18: Effective Methods for Tracking PostgreSQL Parameter Changes
• Semab Tariq, Stormatics, 2024-11-08: Scenarios That Trigger Autovacuum in PostgreSQL
• Radim Marek, boringSQL, 2024-11-23: DELETEs are difficult
• Peter Eisentraut, EnterpriseDB, 2024-11-26: Why PostgreSQL major version upgrades are hard
• Gulcin Yildirim Jelinek, Xata, 2025-01-13: Anatomy of Table-Level Locks in PostgreSQL - This blog explains locking mechanisms in PostgreSQL, focusing on table-level locks that are required by Data Definition Language (DDL) operations.
• Jobin Augustine, Percona, 2025-01-14: What Hurts in PostgreSQL Part One: Temporary Tables
• Laurenz Albe, Cybertec, 2025-01-14: The power of open source in PostgreSQL
• Gulcin Yildirim Jelinek, Xata, 2025-01-20: Anatomy of table-level locks: Reducing locking impact - Not all operations require the same level of locking, and PostgreSQL offers tools and techniques to minimize locking impact.

Architecture / Design / Topologies / High Availability

• Radim Marek, boringSQL, 2024-05-11: Deep Dive into PostgREST - Time Off Manager (Part 1) REST, JSON, BSON
• Tudor Golubenco, Xata, 2024-05-16: Geographically distributed Postgres for multi-tenant applications
• Semab Tariq, Stormatics, 2024-05-17: Understanding Synchronous and Asynchronous Replication in PostgreSQL – What is Best for You?
• Cary Huang, 2024-05-17: Understand PostgerSQL’s Portal – Executor vs Process Utility Optimizer
• Radim Marek, boringSQL, 2024-05-18: Deep Dive into PostgREST - Time Off Manager (Part 2) REST, JSON, BSON
• Samay Sharma (CTO), Jason Petersen (Senior Software Engineer), Tembo, 2024-05-20: Introducing pg_timeseries: Open-source time-series extension for PostgreSQL
• Semab Tariq, Stormatics, 2024-05-23: Deploying PostgreSQL Multi-Master Multi-Zone Architecture on AWS
• Radim Marek, boringSQL, 2024-05-24: Custom PostgreSQL extensions with Rust
• Semab Tariq, Stormatics, 2024-05-30: [Patroni on Github](https://stormatics.tech/blogs/setting-up-a-high-availability-3-node-postgresql-cluster-with-patroni-on-ubuntu-24-04" target="_blank">Setting-up a high availability-3-node-postgresql-cluster-with-patroni-on Ubuntu 24.04, <a href=“https://github.com/zalando/patroni)
• Radim Marek, boringSQL, 2024-06-06: Deep Dive into PostgREST - Time Off Manager (Part 3)
• Semab Tariq, Stormatics, 2024-06-12: Exploring the Power of Partitioning in PostgreSQL
• Radim Marek, boringSQL, 2024-06-15: The time keepers: pg_cron and pg_timetable
• Florian Nadler, Cybertec, 2024-06-17: From Microsoft SQL server to PostGIS
• Jobin Augustine, Percona, 2024-04-24: Upgrading to the New Etcd Version From 3.3 for Patroni
• Gabriele Bartolini, 2024-06-27: Making the world my single point of failure with PostgreSQL
• PostgreSQL Cloud Extension Comparator
• Shayon Mukherjee, 2024-07-13: Use pg_easy_replicate for setting up Logical Replication and Switchover in PostgreSQL
• Paul Laurence, 2024-07-24: Unleashing Postgres for Analytics With DuckDB Integration
• Hayato Kuroda, Fujitsu, 2024-08-22: New logical replication features in PostgreSQL 17
• Tudor Golubenco, Xata, 2024-08-27: Postgres webhooks with pgstream CDC
• Semab Tariq, Stormatics, 2024-08-29: A Beginner’s Guide to Sharding PostgreSQL with Citus Sharding
• Shayon Mukherjee, 2024-08-31: pg_easy_replicate Supports Schema Change Tracking During Logical Replication
• Gabriele Bartolini, 2024-09-10: Configuring PostgreSQL Synchronous Replication
• Umair Shahid, Stormatics, 2024-09-10: Understanding Factors Impacting Data Replication Latency in PostgreSQL Across Geographically Distributed Nodes
• PostGIS: Getting Started
• Jonathan Katz, 2024-09-16: Hybrid search with PostgreSQL and pgvector
• Peter Morgan, 2024-09-16: A Kafka Compatible Broker With A PostgreSQL Storage Engine
• Sven Klemm, Timescale, 2024-09-18: Bridging the Gap Between Compressed and Uncompressed Data in Postgres: Introducing Compression Tuple Filtering
• TimescaleDB
• Semab Tariq, Stormatics, 2024-10-10: Transform PostgreSQL into a Columnar Database Using Citus
• Doug Hunley, 2024-10-04, Automating Highly Available PostgreSQL Clusters
• Semab Tariq, Stormatics, 2024-10-17: Implementing Bi-Directional Replication in PostgreSQL
• Craig Kerstiens, Crunchy Data, 2024-10-18: The future of Postgres?</> About extensions.
• Pavlo Golub, Cybertec, 2024-11-28: vip-manager v2.8 meets Patroni REST API
• Paul Ramsey, Crunchy Data, 2024-11-27: PostGIS Day 2024 Summary
• Josef Machytka, NetApp/Credativ, 2024-12-04: Unique Constraint Violations During Inserts Cause Bloat in PostgreSQL
• Kaarel Moppel, 2024-12-09: Postgres Synchronous Replication - a 99.99% guarantee only
• Shaun Thomas, EDB, 2024-12-13: PG Phriday: Whats Our Vector Victor
• Marco Slot, Crunchy Data, 2024-12-17: pg_incremental: Incremental Data Processing in Postgres
• Shaun Thomas, EDB, 2024-12-20: PG Phriday: Kubernetes Killed the High Availability Star
• Jeremy Schneider, 2024-12-31: Challenges of Postgres Containers
• Kumar Babu P G, Rohit Kapoor, Amazon/AWS, 2025-01-02: Querying and writing to MySQL and MariaDB from Amazon Aurora and Amazon RDS for PostgreSQL using the mysql_fdw extension, Part 2: Handling foreign objects
• Paul Ramsey, Crunchy Data, 2025-01-06: Running an Async Web Query Queue with Procedures and pg_cron

Operations

• Laurenz Albe, Cybetec, 2021-02: Estimating connection pool size with PostgreSQL database statistics
• Paolo Melchiorre, 2024-05-20: Upgrade PostgreSQL from 15 to 16 on Ubuntu 24.04
• Greg Nokes, Crunchy Data, 2024-05-30: Data Encryption in Postgres: A Guidebook
• Brian Pace, Crunchy Data, 2024-05-31: Introducing pgcompare the ultimate multi database data comparison tool
  On GitHub
• Hans-Jürgen Schönig, Cybertec, 2024-07: Speeding up index creation in PostgreSQL
• Andrew Atkinson, 2024-07-23: Wait a minute! — PostgreSQL extension pg_wait_sampling
• Tomas Vondra, EnterpriseDB, 2024-07-15: Autovacuum Tuning Basics
• Carlos Pérez-Aradros Herce, Xata, 2024-07-18: Postgres major version upgrades with minimal downtime
• Jesse Soyland, Crunchy Data, 2024-08-09: Postgres Troubleshooting - DiskFull ERROR could not resize shared memory segment
• Gulcin Yildirim Jelinek, Xata, 2024-08-23: If pg_dump is not a backup tool, what is? - pg_dump was a utility for backing up a PostgreSQL database, not anymore
• Hans-Jürgen Schönig, Cybertec, 2024-09-01: VACUUM: Managing and detecting bloat
• Semab Tariq, Stormatics, 2024-09-03: Deploying Your First PostgreSQL Clusters on Kubernetes with Cloud Native Postgres
• Stefan Fercot, Data Egret, 2024-09-05: PITR and Streaming Replication environments
• Brian Pace, Crunchy Data, 2024-09-05: PostgreSQL Snapshots and Backups with pgBackRest in Kubernetes
• Andrew Atkinson, 2024-09-06: Shrinking Big PostgreSQL tables: Copy-Swap-Drop
• Grant Fritchey: 2024-09-07: PostgreSQL Backups to Support Point In Time Recovery: Learning PostgreSQL with Grant
• Stefan Fercot, Data Egret, 2024-09-23: pgBackRest backups from the standby server
• Amit Kapila, Fujitsu, 2024-09-25: Online Upgrading Logical and Physical Replication Nodes
• Stefan Fercot, Data Egret, 2024-09-25: pgBackRest dedicated backup host
• Amit Kapila, Fujitsu, 2024-10-03: Failover Slots in PostgreSQL-17: Ensuring High Availability with Logical Replication
• Nikolay Samokhvalov, 2024-10-03: AI-assisted Postgres experiment: number of partitions vs. planning time
• Damien Clochard, Dalibo, 2024-10-15: PostgreSQL Anonymizer 2.0 - Generating Fake Data
• Marat Bogatyrev, Dataegret, 2024-11-07: From Backup to Integrity: Leveraging WAL-G for PostgreSQL
• Shaun Thomas, Tembo, 2024-11-05: Plugging the Postgres Upgrade Hole
• Kaarel Moppel, 2024-11-05: Cloning Postgres user privileges vs ChatGPT
• Andrew Farries, Xata, 2024-11-13: Schema changes and the power of expand-contract with pgroll
  A pgconf EU talk recap covering how the expand-contract pattern and pgroll enable zero-downtime schema changes and rollbacks.
• Christophe Pettus, PostgreSQL Experts, 2024-11-15: The Doom That Came To PostgreSQL: When Collations Change
• Christophe Pettus, PostgreSQL Experts, 2024-11-20: Talk Fast: The Speed of Various Collations
• Christophe Pettus, PostgreSQL Experts, 2024-11-22: Locale Cooking: Common Scenarios and Suggestions
• Stefan Fercot, Data Egret, 2024-11-28: Combining cloud storage and dedicated backup host with pgBackRest
• Gabriele Bartolini, EnterpriseDB, 2024-12-11: CNPG Recipe 15 - PostgreSQL major online upgrades with logical replication - Unleashing the Power of Postgres in Kubernetes
• Andrei Lepikhov, 2024-12-15: Fractional Path Issue in Partitioned Postgres databases - Continuing discovery on Postgres planner weak points
• Umair Shahid, Stormatics, 2024-12-13: VACUUM FULL in PostgreSQL – What you need to be mindful of
• Hubert ‘depesz’ Lubaczewski, 2024-12-12: How to ALTER tables without breaking application?
• Shane Borden, 2024-12-17: Some of My Favorite Things – Postgres Queries - Top SQL, Vacuum
• Umair Shahid, Stormatics, 2025-01-09: Idle Transactions Cause Table Bloat? Wait, What?

Security

• Sagar Jadhav, Percona, 2024-08-06: Dispelling Myths About PostgreSQL Default Privileges
• Hans-Jürgen Schönig, Cybertec, 2024-08-06: Enhanced security: Logging PostgreSQL connections

Migration

• Florent Jardin, 2024-05-28: An assistant to copy data from a remote server
• Umair Shahid, Stormatics, 2024-10-04: Transitioning from Oracle to PostgreSQL: Tablespaces
• Deepak Mahto, 2024-11-10: Understanding Volatility in PL/pgSQL Functions: A Real-World Lesson

Performance, Optimizer, Query tuning, Statistics, Benchmarking

• Hans-Jürgen Schönig, Cybertec, 2021-03: How the PostgreSQL query optimizer works
• Deepak Mahto, 2024-05-23: Optimizing Performance in PostgreSQL: Join Column and ANY Filters
• Semab Tariq, Stormatics, 2024-06-08: Performance impact of using ORDER BY with LIMIT in PostgreSQL
• Hubert ‘depesz’ Lubaczewski, 2024-06-11: How much speed you’re leaving at the table if you use default locale? Character set, collation, locale
• Andrei Lepikhov, 2024-06-26: PostgreSQL Sort estimation instability
• Andrei Lepikhov and Alena Rybakina, 2024-07-01: Elaboration of the PostgreSQL sort cost model - Column order matters
• Semab Tariq, Stormatics, 2024-07-03: Key PostgreSQL Configuration Parameters for Enhanced Performance
• Andrei Lepikhov, 2024-07-14: How expensive is it to maintain extended statistics? - Some post-benchmarking insights
• Semab Tariq, Stormatics, 2024-07-22: A Follow up on Key PostgreSQL Configuration Parameters for Enhanced Performance – Part 2
• Andrei Lepikhov, 2024-07-29: Designing a Prototype: Postgres Plan Freezing - The story of one extension
• Deepak Mahto, 2024-08-02: Simplify PostgreSQL Execution Plan Analysis with StatsViaExplainAnalyze
• Grant Fritchey, ScaryDBA, 2024-08-05: PostgreSQL and Instrumentation
• Andrei Lepikhov, 2024-08-12: Probing indexes to survive data skew in Postgres - An attempt to respond the data skew issue in Postgres planner
• Jeremy Schneider, 2024-08-18: Good Benchmark Engineers and Postgres Benchmark Week
• Deepak Mahto, 2024-08-27: Exploring PostgreSQL 17: A Developer’s Guide – Part 5 : Correlated IN Clause Transformation
• Michael Banck, Credativ / NetApp: 2024-09-03: Quick Benchmark: ANALYZE vs. maintenance_io_concurrency
• Semab Tariq, Stormatics, 2024-09-12: Optimizing PostgreSQL with Composite and Partial Indexes: A Quick Comparison
• Mark Callaghan, 2024-09-24: Configuration options that make writes faster or slower with MySQL and Postgres
• Shaun Thomas, Tembo, 2024-10-10: Comparing Columnar to Heap Performance in Postgres with pg_timeseries. Hydra columnar storage
• Greg Smith, Crunchy Data, 2024-11-19: Loading the World! OpenStreetMap Import In Under 4 Hours
• Tomas Vondra, Microsoft, 2024-11-26: Performance archaeology: OLTP
• Tomas Vondra, Microsoft, 2024-12-03: Performance archaeology: OLAP
• Umair Shahid, Stormatics, 2024-12-10: Understanding Wait Events in PostgreSQL. See also: Wait Event Types. Caution: This feature seems to exist only in Redrock Postgres: Wait Events Overview
• David Rowley, 2024-12-11: Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default
• Shane Borden, 2024-12-17: Some of My Favorite Things – Postgres Queries - Top SQL, Vacuum
• Bertrand Drouvot, 2025-01-06: Postgres backend statistics (Part 1): I/O statistics
• Jeremy Schneider, 2025-01-08: Postgres Per-Connection Statistics
• Andrei Lepikhov, 2025-01-18: Whose optimisation is better? - How to compare the quality of SQL query plans in PostgreSQL
• Hans-Jürgen Schönig, Cybertec, 2025-01-20: # GROUP BY: Fixing optimizer estimates

Training / Education

• Alisdair Owens: PostgreSQL Exercises

Development / Tools

• Christoph Schiessl, 2024-07-01: Using Transaction Chaining to Reduce Server Round-Trips
• Paul Ramsey, Crunchy Data, 2024-08-05: Six Degrees of Kevin Bacon - Postgres Style Graph
• Shaun M. Thomas, 2024-08-09: PG Phriday: My Postgres is Rusty Rust
• Deepak Mahto, 2024-08-31: Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 6: Login Event Trigger
• Elizabeth Christensen, Crunchy Data, 2024-09-17: Window Functions for Data Analysis with Postgres
• Florent Jardin, 2024-09-19: Hierarchical data types
• Adam Hendel, Tembo, 2024-09-24: Building an image search engine on Postgres
• Hubert ‘depesz’ Lubaczewski, 2024-10-11: SQL/JSON is here! (kinda “Waiting for Pg 17”)
• Paul Ramsey, Crunchy Data, 2024-10-11: Convert JSON into Columns and Rows with JSON_TABLE
• David E. Wheeler: pgenv - PostgreSQL binary manager
• Christophe Pettus, PostgreSQL Experts, 2024-10-25: “Gentlemen, this is a 🏈”: Glyphs, Encodings, Collations, and Locales
• Christophe Pettus, PostgreSQL Experts, 2024-10-27: Speaking in Tongues: PostgreSQL and Character Encodings
• Umair Shahid, Stormatics, 2024-10-28: Transitioning from Oracle to PostgreSQL: PL/SQL vs PL/pgSQL
• Christopher Winslett, Crunchy Data, 2024-10-29: 4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char
• Shayon Mukherjee, 2024-10-29: Using CTID Based Pagination for Data Cleanups in PostgreSQL
• Christophe Pettus, PostgreSQL Experts, 2024-10-30: An Interlude: Why you do not want to use C/POSIX locale
• Radim Marek, 2024-11-09: Text identifiers in PostgreSQL database design
• Elizabeth Christensen, Crunchy Data, 2024-11-18: Easy Totals and Subtotals in Postgres with Rollup and Cube
• Leo Hsu, Regina Obe, Postgres OnLine Journal, 2024-11-25: Some of my favorite PostgreSQLisms
• Hubert ‘depesz’ Lubaczewski, 2024-11-25: How can I send mail or HTTP request from database?
• Andrei Lepikhov, 2024-11-25: Could GROUP-BY clause reordering improve performance? - Utilising statistics to optimise GROUP-BY clause order
• Umair Shahid, Stormatics, 2024-11-27: Operator Classes: Fine-Tuning Index Performance in PostgreSQL
• Leo Hsu, Regina Obe, Postgres OnLine Journal, 2024-12-01: Substring function Regex style
• Keith Fiske, Crunchy Data, 2024-12-06: Postgres Partitioning with a Default Partition
• Stefanie Janine Stölting, ProOpenSource OÜ, 2024-12-09: Implementing Time Travel In PostgreSQL

Business

• Umair Shahid, Stormatics, 2025-01-15: Cut Cloud Costs with Smarter PostgreSQL CPU Core Allocation