PostgreSQL Mage
Ruohang Feng @Vonng / Pigsty
PostgreSQL ecosystem news, plus development, operations, internals and tuning.
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 Trigger Usage Considerations
Detailed understanding of trigger management and usage in PostgreSQL
GeoIP Geographic Reverse Lookup Optimization
A common requirement in application development is GeoIP conversion - converting source IP addresses to geographic coordinates or administrative divisions (country-state-city-county-town-village)
PostgreSQL Development Convention (2018 Edition)
Without rules, there can be no order. This article compiles a development specification for PostgreSQL database principles and features, which can reduce confusion encountered when using PostgreSQL.
What Are PostgreSQL's Advantages?
PostgreSQL's slogan is "The World's Most Advanced Open-Source Relational Database," but I think the most vivid characterization should be: The Full-Stack Database That Does It All - one tool to rule …
KNN Ultimate Optimization: From RDS to PostGIS
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Efficient Administrative Region Lookup with PostGIS
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
Monitoring Table Size in PostgreSQL
Tables in PostgreSQL correspond to many physical files. This article explains how to calculate the actual size of a table in PostgreSQL.
PgAdmin Installation and Configuration
PgAdmin is a GUI program for managing PostgreSQL, written in Python, but it's quite dated and requires some additional configuration.
Incident-Report: Uneven Load Avalanche
Recently there was a perplexing incident where a database had half its data volume and load migrated away, but ended up being overwhelmed due to increased load.
Implementing Mutual Exclusion Constraints with Exclude
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Function Volatility Classification Levels
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
Distinct On: Remove Duplicate Data
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
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 …
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 …
PostgreSQL Trigger Usage Considerations
Overview Trigger behavior overview Trigger classification Trigger functionality Trigger types Trigger firing Trigger creation Trigger modification Trigger queries Trigger performance Trigger Overview Trigger behavior overview: English, Chinese …
Overview Trigger behavior overview Trigger classification Trigger functionality Trigger types Trigger firing Trigger creation Trigger modification Trigger queries Trigger performance Trigger Overview Trigger behavior overview: English, Chinese …
GeoIP Geographic Reverse Lookup Optimization
Efficient implementation of IP geolocation lookups In application development, a ‘very common’ requirement is GeoIP conversion - converting source IP addresses from requests into corresponding geographic coordinates or administrative divisions …
Efficient implementation of IP geolocation lookups In application development, a ‘very common’ requirement is GeoIP conversion - converting source IP addresses from requests into corresponding geographic coordinates or administrative divisions …
PostgreSQL Development Convention (2018 Edition)
0x00 Background Without rules, there can be no order. PostgreSQL is extremely powerful, but to use PostgreSQL well requires coordinated effort from backend developers, operations teams, and DBAs. This article compiles a development specification …
0x00 Background Without rules, there can be no order. PostgreSQL is extremely powerful, but to use PostgreSQL well requires coordinated effort from backend developers, operations teams, and DBAs. This article compiles a development specification …
What Are PostgreSQL's Advantages?
PostgreSQL’s slogan is “The world’s most advanced open source relational database”, but I think this slogan isn’t catchy enough, and it looks like it’s targeting MySQL’s “The world’s most popular open source relational database” slogan, which seems …
PostgreSQL’s slogan is “The world’s most advanced open source relational database”, but I think this slogan isn’t catchy enough, and it looks like it’s targeting MySQL’s “The world’s most popular open source relational database” slogan, which seems …
KNN Ultimate Optimization: From RDS to PostGIS
Flexibly applying database functionality can easily achieve a 30,000-fold performance improvement in GIS selection scenarios. Level Method Performance/Time(ms) Maintainability/Reliability Notes 1 Brute Force 30,000 - Simple form 2 Coordinate Index 35 …
Flexibly applying database functionality can easily achieve a 30,000-fold performance improvement in GIS selection scenarios. Level Method Performance/Time(ms) Maintainability/Reliability Notes 1 Brute Force 30,000 - Simple form 2 Coordinate Index 35 …
Efficient Administrative Region Lookup with PostGIS
Author: Vonng (@Vonng) Original WeChat article In application development, we often need to solve this problem: determining administrative regions based on user coordinates. We collect coordinates like 28°00'00"N 100°00'00.000"E, but what we …
Author: Vonng (@Vonng) Original WeChat article In application development, we often need to solve this problem: determining administrative regions based on user coordinates. We collect coordinates like 28°00'00"N 100°00'00.000"E, but what we …
Monitoring Table Size in PostgreSQL
Table Space Layout In the broad sense, a Table includes two parts: the main table and TOAST table: Main table: stores the relation’s own data, i.e., the narrow sense relation, relkind='r'. TOAST table: corresponds one-to-one with the main table, …
Table Space Layout In the broad sense, a Table includes two parts: the main table and TOAST table: Main table: stores the relation’s own data, i.e., the narrow sense relation, relkind='r'. TOAST table: corresponds one-to-one with the main table, …
PgAdmin Installation and Configuration
PgAdmin4 Installation and Configuration PgAdmin is a GUI designed specifically for PostgreSQL. It works very well. It can run as either a local GUI program or a web service. Since PgAdmin’s GUI components have display issues on Retina screens, this …
PgAdmin4 Installation and Configuration PgAdmin is a GUI designed specifically for PostgreSQL. It works very well. It can run as either a local GUI program or a web service. Since PgAdmin’s GUI components have display issues on Retina screens, this …
Incident-Report: Uneven Load Avalanche
Author: Vonng (@Vonng) Recently there was a perplexing incident where a database had half its data volume and load migrated away. Everything else remained unchanged, and it was fine before. The pressure decreased, yet it fell into a near-death state …
Author: Vonng (@Vonng) Recently there was a perplexing incident where a database had half its data volume and load migrated away. Everything else remained unchanged, and it was fine before. The pressure decreased, yet it fell into a near-death state …
Implementing Mutual Exclusion Constraints with Exclude
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints. Introduction Data integrity is extremely important, but data integrity guaranteed by applications isn’t always reliable: humans make …
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints. Introduction Data integrity is extremely important, but data integrity guaranteed by applications isn’t always reliable: humans make …
Function Volatility Classification Levels
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance. Core Differences VOLATILE: Has side effects, cannot be optimized. STABLE: Executes database queries. IMMUTABLE: Pure function, execution …
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance. Core Differences VOLATILE: Has side effects, cannot be optimized. STABLE: Executes database queries. IMMUTABLE: Pure function, execution …
Distinct On: Remove Duplicate Data
Distinct On is a unique syntax provided by PostgreSQL that can efficiently solve typical query problems, for example, quickly finding records with maximum/minimum values within groups. Introduction Finding records with maximum/minimum values within …
Distinct On is a unique syntax provided by PostgreSQL that can efficiently solve typical query problems, for example, quickly finding records with maximum/minimum values within groups. Introduction Finding records with maximum/minimum values within …