PostgreSQL Mage
Ruohang Feng @Vonng / Pigsty
PostgreSQL ecosystem news, plus development, operations, internals and tuning.
PostgreSQL Logical Replication Deep Dive
This article introduces the principles and best practices of logical replication in PostgreSQL 13.
PG Replica Identity Explained
Replica identity is important - it determines the success or failure of logical replication
A Methodology for Diagnosing PostgreSQL Slow Queries
Slow queries are the sworn enemy of OLTP databases. Here’s how to identify, analyze, and fix them using metrics (Pigsty dashboards), pg_stat_statements, and logs.
Incident-Report: Patroni Failure Due to Time Travel
Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start.
Online Primary Key Column Type Change
How to change column types online, such as upgrading from INT to BIGINT?
Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Understanding the golden monitoring metrics in PostgreSQL
Database Cluster Management Concepts and Entity Naming Conventions
Concepts and their naming are very important. Naming style reflects an engineer's understanding of system architecture. Poorly defined concepts lead to communication confusion, while carelessly set …
PostgreSQL's KPI
Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single …
Online PostgreSQL Column Type Migration
How to modify PostgreSQL column types online? A general approach
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable.
Frontend-Backend Communication Wire Protocol
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
Incident: PostgreSQL Extension Installation Causes Connection Failure
Today encountered an interesting case where a customer reported database connection issues caused by extensions.
CDC Change Data Capture Mechanisms
Change Data Capture is an interesting ETL alternative solution.
Locks in PostgreSQL
Snapshot isolation does most of the heavy lifting in PG, but locks still matter. Here’s a practical guide to table locks, row locks, intention locks, and pg_locks.
O(n2) Complexity of GIN Search
When GIN indexes are used to search with very long keyword lists, performance degrades significantly. This article explains why GIN index keyword search has O(n^2) time complexity.
PostgreSQL Common Replication Topology Plans
Replication is one of the core issues in system architecture.
Warm Standby: Using pg_receivewal
There are various backup strategies. Physical backups can usually be divided into four types.
Incident-Report: Connection-Pool Contamination Caused by pg_dump
Sometimes, interactions between components manifest in subtle ways. For example, using pg_dump to export data from a connection pool can cause connection pool contamination issues.
PostgreSQL Data Page Corruption Repair
Using binary editing to repair PostgreSQL data pages, and how to make a primary key query return two records.
Relation Bloat Monitoring and Management
PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat.
TimescaleDB Quick Start
TimescaleDB is a PostgreSQL extension plugin that provides time-series database functionality.
Getting Started with PipelineDB
PipelineDB is a PostgreSQL extension for streaming analytics. Here’s how to install it and build continuous views over live data.
Incident-Report: PostgreSQL Transaction ID Wraparound
XID WrapAround is perhaps a unique type of failure specific to PostgreSQL
Incident-Report: Integer Overflow from Rapid Sequence Number Consumption
If you use Integer sequences on tables, you should consider potential overflow scenarios.
PostgreSQL Logical Replication Deep Dive
This article introduces the principles and best practices of logical replication in PostgreSQL 13.
PG Replica Identity Explained
Replica identity is important - it determines the success or failure of logical replication
A Methodology for Diagnosing PostgreSQL Slow Queries
Slow queries are the sworn enemy of OLTP databases. Here’s how to identify, analyze, and fix them using metrics (Pigsty dashboards), pg_stat_statements, and logs.
Incident-Report: Patroni Failure Due to Time Travel
Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start.
Online Primary Key Column Type Change
How to change column types online, such as upgrading from INT to BIGINT?
Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Understanding the golden monitoring metrics in PostgreSQL
Database Cluster Management Concepts and Entity Naming Conventions
Concepts and their naming are very important. Naming style reflects an engineer's understanding of system architecture. Poorly defined concepts lead to communication confusion, while carelessly set …
PostgreSQL's KPI
Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single …
Online PostgreSQL Column Type Migration
How to modify PostgreSQL column types online? A general approach
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable.
Frontend-Backend Communication Wire Protocol
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
Incident: PostgreSQL Extension Installation Causes Connection Failure
Today encountered an interesting case where a customer reported database connection issues caused by extensions.
CDC Change Data Capture Mechanisms
Change Data Capture is an interesting ETL alternative solution.
Locks in PostgreSQL
Snapshot isolation does most of the heavy lifting in PG, but locks still matter. Here’s a practical guide to table locks, row locks, intention locks, and pg_locks.
O(n2) Complexity of GIN Search
When GIN indexes are used to search with very long keyword lists, performance degrades significantly. This article explains why GIN index keyword search has O(n^2) time complexity.
PostgreSQL Common Replication Topology Plans
Replication is one of the core issues in system architecture.
Warm Standby: Using pg_receivewal
There are various backup strategies. Physical backups can usually be divided into four types.
Incident-Report: Connection-Pool Contamination Caused by pg_dump
Sometimes, interactions between components manifest in subtle ways. For example, using pg_dump to export data from a connection pool can cause connection pool contamination issues.
PostgreSQL Data Page Corruption Repair
Using binary editing to repair PostgreSQL data pages, and how to make a primary key query return two records.
Relation Bloat Monitoring and Management
PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat.
TimescaleDB Quick Start
TimescaleDB is a PostgreSQL extension plugin that provides time-series database functionality.
Getting Started with PipelineDB
PipelineDB is a PostgreSQL extension for streaming analytics. Here’s how to install it and build continuous views over live data.
Incident-Report: PostgreSQL Transaction ID Wraparound
XID WrapAround is perhaps a unique type of failure specific to PostgreSQL
Incident-Report: Integer Overflow from Rapid Sequence Number Consumption
If you use Integer sequences on tables, you should consider potential overflow scenarios.
PostgreSQL Logical Replication Deep Dive
Logical Replication Logical Replication is a method of replicating data objects and their changes based on their replica identity (usually the primary key). The term logical replication contrasts with physical replication. Physical replication uses …
Logical Replication Logical Replication is a method of replicating data objects and their changes based on their replica identity (usually the primary key). The term logical replication contrasts with physical replication. Physical replication uses …
PG Replica Identity Explained
Introduction: DIY Logical Replication The concept of replica identity serves logical replication. The basic working principle of logical replication is to decode row-level INSERT/UPDATE/DELETE events from logical publication-related tables and …
Introduction: DIY Logical Replication The concept of replica identity serves logical replication. The basic working principle of logical replication is to decode row-level INSERT/UPDATE/DELETE events from logical publication-related tables and …
A Methodology for Diagnosing PostgreSQL Slow Queries
“You can’t optimize what you can’t measure.” Slow queries hog connections, hold locks, block replication, trigger deadlocks, and waste resources. Every DBA must know how to find and fix them quickly. Traditional tools pg_stat_statements – essential …
“You can’t optimize what you can’t measure.” Slow queries hog connections, hold locks, block replication, trigger deadlocks, and waste resources. Every DBA must know how to find and fix them quickly. Traditional tools pg_stat_statements – essential …
Incident-Report: Patroni Failure Due to Time Travel
Summary: Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start. The failure information in Patroni is shown as follows: Process %s is not postmaster, too much difference between PID file …
Summary: Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start. The failure information in Patroni is shown as follows: Process %s is not postmaster, too much difference between PID file …
Online Primary Key Column Type Change
Author: Vonng (@Vonng) How to change primary key column types online, such as upgrading from INT to BIGINT, without affecting business operations? Suppose you have a table in PostgreSQL where you initially chose an INT primary key without much …
Author: Vonng (@Vonng) How to change primary key column types online, such as upgrading from INT to BIGINT, without affecting business operations? Suppose you have a table in PostgreSQL where you initially chose an INT primary key without much …
Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Preface Playing with databases and playing with cars have something in common - they both require frequently checking the dashboard. What are you doing staring at the dashboard? Looking at metrics. Why look at metrics? You need to understand the …
Preface Playing with databases and playing with cars have something in common - they both require frequently checking the dashboard. What are you doing staring at the dashboard? Looking at metrics. Why look at metrics? You need to understand the …
Database Cluster Management Concepts and Entity Naming Conventions
Author: Vonng (@Vonng) “Once named, it can be spoken; once spoken, it can be acted upon.” Concepts and their naming are very important. Naming style reflects an engineer’s understanding of system architecture. Poorly defined concepts lead to …
Author: Vonng (@Vonng) “Once named, it can be spoken; once spoken, it can be acted upon.” Concepts and their naming are very important. Naming style reflects an engineer’s understanding of system architecture. Poorly defined concepts lead to …
PostgreSQL's KPI
Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single horizontally comparable metric that is basically …
Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single horizontally comparable metric that is basically …
Online PostgreSQL Column Type Migration
Scenario In the lifecycle of a database, there’s a common type of requirement: modifying column types. For example: Using INT as a primary key, only to discover that business is booming and the 2.1 billion limit of INT32 isn’t enough, wanting to …
Scenario In the lifecycle of a database, there’s a common type of requirement: modifying column types. For example: Using INT as a primary key, only to discover that business is booming and the 2.1 billion limit of INT32 isn’t enough, wanting to …
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable Basics The SQL standard defines four isolation levels, but PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable …
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable Basics The SQL standard defines four isolation levels, but PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable …
Frontend-Backend Communication Wire Protocol
Understanding the TCP protocol used for communication between PostgreSQL server and client Startup Phase The basic flow of the startup phase is as follows: Client sends a StartupMessage (F) to initiate connection request to server Payload includes …
Understanding the TCP protocol used for communication between PostgreSQL server and client Startup Phase The basic flow of the startup phase is as follows: Client sends a StartupMessage (F) to initiate connection request to server Payload includes …
Incident: PostgreSQL Extension Installation Causes Connection Failure
Author: Vonng (@Vonng) Today encountered an interesting case where a customer reported database connection issues. The error was: psql: FATAL: could not load library "/export/servers/pgsql/lib/pg_hint_plan.so": …
Author: Vonng (@Vonng) Today encountered an interesting case where a customer reported database connection issues. The error was: psql: FATAL: could not load library "/export/servers/pgsql/lib/pg_hint_plan.so": …
CDC Change Data Capture Mechanisms
In actual production, we often need to synchronize database states to other places, such as synchronizing to data warehouses for analysis, to message queues for downstream consumption, or to caches to accelerate queries. Generally speaking, there are …
In actual production, we often need to synchronize database states to other places, such as synchronizing to data warehouses for analysis, to message queues for downstream consumption, or to caches to accelerate queries. Generally speaking, there are …
Locks in PostgreSQL
PostgreSQL relies on snapshot isolation (SI) for concurrency and two-phase locking (2PL) as a supporting act. DML (SELECT/INSERT/UPDATE/DELETE) uses SSI; DDL (CREATE TABLE etc.) still uses 2PL. Understanding locks is essential when diagnosing …
PostgreSQL relies on snapshot isolation (SI) for concurrency and two-phase locking (2PL) as a supporting act. DML (SELECT/INSERT/UPDATE/DELETE) uses SSI; DDL (CREATE TABLE etc.) still uses 2PL. Understanding locks is essential when diagnosing …
O(n2) Complexity of GIN Search
When GIN indexes are used to search with very long keyword lists, performance degrades significantly. This article explains why GIN index keyword search has O(n^2) time complexity. Here is the detail of why that query have O(N^2) inside GIN …
When GIN indexes are used to search with very long keyword lists, performance degrades significantly. This article explains why GIN index keyword search has O(n^2) time complexity. Here is the detail of why that query have O(N^2) inside GIN …
PostgreSQL Common Replication Topology Plans
Replication is one of the core issues in system architecture. Cluster Topology Suppose we use a standard 4-unit configuration: primary, synchronous replica, delayed backup, and remote replica, identified by letters M, S, O, R respectively. M: Master, …
Replication is one of the core issues in system architecture. Cluster Topology Suppose we use a standard 4-unit configuration: primary, synchronous replica, delayed backup, and remote replica, identified by letters M, S, O, R respectively. M: Master, …
Warm Standby: Using pg_receivewal
Author: Vonng (@Vonng) Backup is the foundation of a DBA’s livelihood and one of the most critical tasks in database management. There are various types of backups, but the backups discussed here are all physical backups. Physical backups can …
Author: Vonng (@Vonng) Backup is the foundation of a DBA’s livelihood and one of the most critical tasks in database management. There are various types of backups, but the backups discussed here are all physical backups. Physical backups can …
Incident-Report: Connection-Pool Contamination Caused by pg_dump
PostgreSQL is great, but that doesn’t mean it’s Bug-Free. This time in the production environment, I encountered another very interesting case: a production incident caused by pg_dump. This is a very subtle bug triggered by Pgbouncer, search_path, …
PostgreSQL is great, but that doesn’t mean it’s Bug-Free. This time in the production environment, I encountered another very interesting case: a production incident caused by pg_dump. This is a very subtle bug triggered by Pgbouncer, search_path, …
PostgreSQL Data Page Corruption Repair
PostgreSQL is a very reliable database, but even the most reliable database will struggle when faced with unreliable hardware. This article introduces methods for dealing with data page corruption in PostgreSQL. The Initial Problem A statistics …
PostgreSQL is a very reliable database, but even the most reliable database will struggle when faced with unreliable hardware. This article introduces methods for dealing with data page corruption in PostgreSQL. The Initial Problem A statistics …
Relation Bloat Monitoring and Management
Author: Vonng (@Vonng) PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat. Relation bloat (table and index) negatively impacts database performance and …
Author: Vonng (@Vonng) PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat. Relation bloat (table and index) negatively impacts database performance and …
TimescaleDB Quick Start
Official website: https://www.timescale.com Official documentation: https://docs.timescale.com/v0.9/main Github: https://github.com/timescale/timescaledb Why Use TimescaleDB What is Time-Series Data? We keep talking about what “time-series data” is, …
Official website: https://www.timescale.com Official documentation: https://docs.timescale.com/v0.9/main Github: https://github.com/timescale/timescaledb Why Use TimescaleDB What is Time-Series Data? We keep talking about what “time-series data” is, …
Getting Started with PipelineDB
PipelineDB extends PostgreSQL with streaming primitives—continuous views over unbounded input. Although the upstream project was discontinued, the concepts remain useful. Install & configure PipelineDB ships as an extension. Install the RPM/DEB, then …
PipelineDB extends PostgreSQL with streaming primitives—continuous views over unbounded input. Although the upstream project was discontinued, the concepts remain useful. Install & configure PipelineDB ships as an extension. Install the RPM/DEB, then …
Incident-Report: PostgreSQL Transaction ID Wraparound
Encountered a transaction wraparound failure caused by disk bad blocks: Primary database (PostgreSQL 9.3) disk bad blocks caused VACUUM FREEZE execution failure on several tables. Unable to reclaim old transaction IDs, causing database transaction …
Encountered a transaction wraparound failure caused by disk bad blocks: Primary database (PostgreSQL 9.3) disk bad blocks caused VACUUM FREEZE execution failure on several tables. Unable to reclaim old transaction IDs, causing database transaction …
Incident-Report: Integer Overflow from Rapid Sequence Number Consumption
0x01 Overview Incident symptoms: A table using auto-increment columns had sequence numbers reach the integer limit, preventing writes. Discovered large gaps in auto-increment columns, with many sequence numbers consumed without corresponding records. …
0x01 Overview Incident symptoms: A table using auto-increment columns had sequence numbers reach the integer limit, preventing writes. Discovered large gaps in auto-increment columns, with many sequence numbers consumed without corresponding records. …