PostgreSQL

Articles and Blog Posts

• Postgres Indexes for Newbies
• Debugging the Postgres query planner
• Query Optimization in Postgres with pg_stat_statements
• Trace Query Processing Internals with Debugger
• PostgreSQL Connection Pooling: Part 1 – Pros & Cons
• Your Guide to Connection Management in Postgres
• How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04
• Postgres In Production with Patroni & TimescaleDB— Our Experiences
• Postgres 14: It's The Little Things
• A look at Postgres 14: Performance and Monitoring Improvements
• How to interpret PostgreSQL EXPLAIN ANALYZE output
• Zero downtime Postgres migration, done right - A step by step guide to migrate your Postgres databases in production environments with zero downtime.
• Do You Really Need Redis? How to Get Away with Just PostgreSQL
• Hierarchical Structures in PostgreSQL
• Globally Distributed Postgres
• PostgreSQL, memory and the cloud
• How pg_sleep works in PostgreSQL
• Exploring the Postgres GIN index
• Index bloat reduced in PostgreSQL v14
• Herding elephants: Lessons learned from sharding Postgres at Notion
• How Postgres Audit Tables Saved Us From Taking Down Production
• How we deconstructed the Postgres planner to find indexing opportunities
• How We Went All In on sqlc/pgx for Postgres + Go
• What is the new LZ4 TOAST compression in PostgreSQL 14, and how fast is it?
• Lesser Known PostgreSQL Features
• Ephemeral Postgres Databases
• Back to basics: Writing an application using Go and PostgreSQL
• How Postgres Stores Rows
• When Postgres blocks: 7 tips for dealing with locks
• PostgreSQL Schema Change Guide
• pg-osc: Zero downtime schema changes in PostgreSQL - Non blocking and zero downtime ALTER statements in PostgreSQL with pg-osc.
• Postgres Auditing in 150 lines of SQL
• Let's build a distributed Postgres proof of concept
• Neon - Serverless Postgres built for the cloud
• How Retool upgraded our 4 TB main application PostgreSQL database
• How to Find and Stop Running Queries on PostgreSQL
• Postgres Full-Text Search: A Search Engine in a Database
• Postgres 15 improves UNIQUE and NULL
• Learn Postgres at the Playground - Postgres compiled to WASM running in browser.
• Writing your own PostgreSQL driver in Go - Because Postgres protocol is well documented, the complexity to write your own driver (or modify an existing one) is massively reduced. And this article provides a great starting point.
• Partitioning in Postgres, 2022 edition
• Postgres Full Text Search vs the rest
• Debugging PostgreSQL performance, the hard way
• Index Merges vs Composite Indexes in Postgres and MySQL
• pg_CRDT - an experimental CRDT extension for Postgres
• Just use Postgres for everything
• I Migrated from a Postgres Cluster to Distributed SQLite with LiteFS
• Features I'd like in PostgreSQL
• Storing OpenAI embeddings in Postgres with pgvector
• Five Tips For a Healthier Postgres Database in the New Year

Projects

• pg-mem - An in memory postgres DB instance for your unit tests.
• pg_hint_plan - Controls execution plan with hinting phrases in comment of special form.
• Patroni - A template for PostgreSQL High Availability with Etcd, Consul, ZooKeeper, or Kubernetes.

  Note to Citus users: Starting from 3.0 Patroni nicely integrates with the Citus database extension to Postgres. Please check the Citus support page in the Patroni documentation for more info about how to use Patroni high availability together with a Citus distributed cluster. Note to Kubernetes users: Patroni can run natively on top of Kubernetes. Take a look at the Kubernetes chapter of the Patroni documentation.

• High Availability Postgres on Fly.io - Postgres + Stolon for HA clusters as Fly apps.
• pgmetrics - Collect and display information and stats from a running PostgreSQL server.
• yandex/odyssey - Scalable PostgreSQL connection pooler.
• jackc/pgx - PostgreSQL driver and toolkit for Go.
• Reshape - An easy-to-use, zero-downtime schema migration tool for Postgres.
• Hydra - The open source Snowflake alternative. OLAP Postgres. Query any database via Postgres.
• OrioleDB - Building a modern cloud-native storage engine (... and solving some PostgreSQL wicked problems).
• Vectorized Executor - Speeding up PostgreSQL through vectorized execution.
• migra - Like diff but for PostgreSQL schemas.
• pgcli - Postgres CLI with autocompletion and syntax highlighting.
• WaterbugDB - Raft + Postgres parser + Postgres wire protocol + Boltdb + ? = Distributed PostgreSQL.
• HypoPG - Hypothetical Indexes for PostgreSQL.
• RUM - RUM access method - inverted index with additional information in posting lists.
• pgvm - PostgreSQL Version Manager.
• pgBackRest - Reliable PostgreSQL backup & restore.
• repmgr - A lightweight replication manager for PostgreSQL.

Books

• PostgreSQL 14 Internals - A deep dive into the server mechanics.

Lists

• Awesome Postgres - A curated list of awesome PostgreSQL software, libraries, tools and resources, inspired by awesome-mysql.