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

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
• 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 <a href=“https://stormatics.tech/alis-planet-postgresql/vacuum-in-postgresql
• 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 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

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: 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

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 <a href=“https://github.com/CrunchyData/pgCompare
• 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, 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, 2024-09-23: pgBackRest backups from the standby server
• Amit Kapila, Fujitsu, 2024-09-25: Online Upgrading Logical and Physical Replication Nodes
• Stefan Fercot, 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

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

Performance, Optimizer, Query tuning, Statistics, Benchmarking

• 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

Training / Education

• Alisdair Owens: PostgreSQL Exercises

Development

• 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 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