Blog Columns
Six thematic columns, each with its own dedicated RSS feed.
Database Guru
77posts
Database news, industry surveys, architecture, and best practices.
Cloud-Exiter
67posts
Cloud economics, repatriation, and building sovereign infrastructure.
Postgres Mage
130posts
PostgreSQL ecosystem, internals, extensions, and production tuning.
AI Agent Explorer
100posts
Practical engineering with coding agents, LLMs, AI4DB, and DB4AI.
Travel Notes
12posts
Trips, hikes, landscapes, and field notes across mountains and seas.
Miscellaneous
41posts
Essays, reflections, and personal writing inside and outside of tech.

Pigsty v0.7: Monitor-Only Deployments
Monitor-only deployments unlock hybrid fleets, while DB/user provisioning APIs get a serious cleanup.

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.

Pigsty v0.6: Provisioning Upgrades
v0.6 reworks the provisioning flow, adds exporter toggles, and makes the monitoring stack portable across environments.

Urban Wandering: Guangzhou Observations
Taking advantage of attending the 2020 PostgreSQL China Conference, I toured Guangzhou for a day. A quite interesting place.

Online Primary Key Column Type Change
How to change column types online, such as upgrading from INT to BIGINT?

New Year Pilgrimage: Yubeng Mountain Circuit
The disaster-filled year 2020 required proper blessings. Completed the Meili inner circuit in 3 days - Sacred Waterfall, Sacred Lake, Ice Lake.

Farewell to Grandfather
Grandfather has passed away. I am beyond grief, but I should still write something in his memory.

Pigsty v0.5: Declarative DB Templates
Pigsty v0.5 introduces declarative database templates so roles, schemas, extensions, and ACLs can be described entirely in YAML.

Pigsty v0.4: PG13 and Better Docs
Pigsty v0.4 ships PG13 support, a Grafana 7.3 refresh, and a cleaned-up docs site for the second public beta.

Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Understanding the golden monitoring metrics in PostgreSQL

Pigsty v0.3: First Public Beta
Pigsty v0.3.0, the first public beta, lands with eight battle-tested dashboards and an offline bundle.

Paradise Found: Wusun Ancient Trail
This National Day I trekked the Wusun Ancient Trail, crossing over the Tianshan Mountains to reach that Ili. Though over a week has passed, the memories linger - writing to commemorate this journey.

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 …

Practical Cryptography Made Simple
A classic problem in cryptography is how to transmit data securely and reliably through insecure channels. Protecting your chats and communications from surveillance and monitoring - easily achievable …

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.

City Upon a Hill: California Road Trip
One week road trip: Cupertino, Los Angeles, San Francisco, Yosemite

Pigsty v0.7: Monitor-Only Deployments
Monitor-only deployments unlock hybrid fleets, while DB/user provisioning APIs get a serious cleanup.

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.

Pigsty v0.6: Provisioning Upgrades
v0.6 reworks the provisioning flow, adds exporter toggles, and makes the monitoring stack portable across environments.

Urban Wandering: Guangzhou Observations
Taking advantage of attending the 2020 PostgreSQL China Conference, I toured Guangzhou for a day. A quite interesting place.

Online Primary Key Column Type Change
How to change column types online, such as upgrading from INT to BIGINT?

New Year Pilgrimage: Yubeng Mountain Circuit
The disaster-filled year 2020 required proper blessings. Completed the Meili inner circuit in 3 days - Sacred Waterfall, Sacred Lake, Ice Lake.

Farewell to Grandfather
Grandfather has passed away. I am beyond grief, but I should still write something in his memory.

Pigsty v0.5: Declarative DB Templates
Pigsty v0.5 introduces declarative database templates so roles, schemas, extensions, and ACLs can be described entirely in YAML.

Pigsty v0.4: PG13 and Better Docs
Pigsty v0.4 ships PG13 support, a Grafana 7.3 refresh, and a cleaned-up docs site for the second public beta.

Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Understanding the golden monitoring metrics in PostgreSQL

Pigsty v0.3: First Public Beta
Pigsty v0.3.0, the first public beta, lands with eight battle-tested dashboards and an offline bundle.

Paradise Found: Wusun Ancient Trail
This National Day I trekked the Wusun Ancient Trail, crossing over the Tianshan Mountains to reach that Ili. Though over a week has passed, the memories linger - writing to commemorate this journey.

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 …

Practical Cryptography Made Simple
A classic problem in cryptography is how to transmit data securely and reliably through insecure channels. Protecting your chats and communications from surveillance and monitoring - easily achievable …

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.

City Upon a Hill: California Road Trip
One week road trip: Cupertino, Los Angeles, San Francisco, Yosemite
Pigsty v0.7: Monitor-Only Deployments

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.7.0 Pigsty v0.7 focuses on plugging existing fleets into Pigsty’s observability stack. The new monitor-only flow lets you drop Pigsty dashboards onto databases that were provisioned …
GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.7.0 Pigsty v0.7 focuses on plugging existing fleets into Pigsty’s observability stack. The new monitor-only flow lets you drop Pigsty dashboards onto databases that were provisioned …
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 …
Pigsty v0.6: Provisioning Upgrades

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.6.0 Pigsty v0.6 responds to user feedback with a redesigned provisioning path plus a monitoring stack that can sit beside any managed PG fleet—even a MyBase cluster built elsewhere. Bug …
GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.6.0 Pigsty v0.6 responds to user feedback with a redesigned provisioning path plus a monitoring stack that can sit beside any managed PG fleet—even a MyBase cluster built elsewhere. Bug …
Urban Wandering: Guangzhou Observations

Taking advantage of attending the 2020 PostgreSQL China Conference, I toured Guangzhou for a day. A quite interesting place. Among the four first-tier cities, only Guangzhou remained unvisited. This time, leveraging the PG conference opportunity, I …
Taking advantage of attending the 2020 PostgreSQL China Conference, I toured Guangzhou for a day. A quite interesting place. Among the four first-tier cities, only Guangzhou remained unvisited. This time, leveraging the PG conference opportunity, I …
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 …
New Year Pilgrimage: Yubeng Mountain Circuit

The disaster-filled year 2020 required proper blessings. Completed the Meili inner circuit in 3 days - Sacred Waterfall, Sacred Lake, Ice Lake. Preface: As the saying goes: “If not going to paradise, go to Yubeng.” Going to Yubeng usually means …
The disaster-filled year 2020 required proper blessings. Completed the Meili inner circuit in 3 days - Sacred Waterfall, Sacred Lake, Ice Lake. Preface: As the saying goes: “If not going to paradise, go to Yubeng.” Going to Yubeng usually means …
Farewell to Grandfather

Although 2020 was a year of many disasters, until grandfather’s death, I always thought we could somehow get by. The death of a close relative—this is the second time for me. First my father, then my grandfather. Humans appear so powerless before …
Although 2020 was a year of many disasters, until grandfather’s death, I always thought we could somehow get by. The death of a close relative—this is the second time for me. First my father, then my grandfather. Humans appear so powerless before …
Pigsty v0.5: Declarative DB Templates

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.5.0 v0.5.0 Outline The official docs site (http://pigsty.cc/) is live. Database templating becomes fully declarative: define users, roles, databases, ACLs, extensions, and schemas in …
GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.5.0 v0.5.0 Outline The official docs site (http://pigsty.cc/) is live. Database templating becomes fully declarative: define users, roles, databases, ACLs, extensions, and schemas in …
Pigsty v0.4: PG13 and Better Docs

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.4.0 Pigsty v0.4 is our second public beta. The observability stack was rebuilt around Grafana 7.3, and ten curated dashboards became the default open-source payload. pg_exporter 0.3.1 …
GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.4.0 Pigsty v0.4 is our second public beta. The observability stack was rebuilt around Grafana 7.3, and ten curated dashboards became the default open-source payload. pg_exporter 0.3.1 …
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 …
Pigsty v0.3: First Public Beta

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.3.0 Pigsty v0.3.0 is the very first public preview. It packages a lean observability stack plus a reproducible offline bundle so you can spin up a real PostgreSQL lab without touching …
GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.3.0 Pigsty v0.3.0 is the very first public preview. It packages a lean observability stack plus a reproducible offline bundle so you can spin up a real PostgreSQL lab without touching …
Paradise Found: Wusun Ancient Trail

This National Day I trekked the Wusun Ancient Trail, crossing over the Tianshan Mountains to reach that Ili. Though over a week has passed, the memories linger - writing to commemorate this journey. TL;DR Too Long; Didn’t Read For videos, please …
This National Day I trekked the Wusun Ancient Trail, crossing over the Tianshan Mountains to reach that Ili. Though over a week has passed, the memories linger - writing to commemorate this journey. TL;DR Too Long; Didn’t Read For videos, please …
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 …
Practical Cryptography Made Simple

Original WeChat Article A classic problem in cryptography is how to transmit data securely and reliably through insecure channels. Protecting your chats and communications from eavesdropping, surveillance, and censorship. With just a computer at …
Original WeChat Article A classic problem in cryptography is how to transmit data securely and reliably through insecure channels. Protecting your chats and communications from eavesdropping, surveillance, and censorship. With just a computer at …
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 …
City Upon a Hill: California Road Trip

Went on a business trip to America, worked one week and played one week. Road trip along Highway 1, Bay Area, Los Angeles, San Francisco, Yosemite. Thanks to my company, I went on a business trip to America for two weeks - one week working, one week …
Went on a business trip to America, worked one week and played one week. Road trip along Highway 1, Bay Area, Los Angeles, San Francisco, Yosemite. Thanks to my company, I went on a business trip to America for two weeks - one week working, one week …