Blog Columns
Six thematic columns, each with its own dedicated RSS feed.
Database Guru
75posts
Database news, industry surveys, architecture, and best practices.
Cloud-Exiter
66posts
Cloud economics, repatriation, and building sovereign infrastructure.
Postgres Mage
128posts
PostgreSQL ecosystem, internals, extensions, and production tuning.
AI Agent Explorer
98posts
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.
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
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.
Is running postgres in docker a good idea?
Thou shalt not run a prod database inside a container
The Sorrow of the Internet
The future may not necessarily be bright, but the path will certainly be tortuous.
Understanding Time - Leap Years, Leap Seconds, Time and Time Zones
A proper understanding of time is very helpful for correctly handling time-related issues in work and life. For example, time representation and processing in computers, as well as time handling in …
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.
New Year Reflections
The future is not necessarily bright, but the path is definitely winding. During this rare leisure time alone in the New Year, I write these random thoughts, writing wherever my mind wanders.
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
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.
Is running postgres in docker a good idea?
Thou shalt not run a prod database inside a container
The Sorrow of the Internet
The future may not necessarily be bright, but the path will certainly be tortuous.
Understanding Time - Leap Years, Leap Seconds, Time and Time Zones
A proper understanding of time is very helpful for correctly handling time-related issues in work and life. For example, time representation and processing in computers, as well as time handling in …
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.
New Year Reflections
The future is not necessarily bright, but the path is definitely winding. During this rare leisure time alone in the New Year, I write these random thoughts, writing wherever my mind wanders.
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 …
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 …
Is running postgres in docker a good idea?
For stateless app services, containers are an almost perfect devops solution. However, for stateful services like databases, it’s not so straightforward. Whether production databases should be containerized remains controversial. From a developer’s …
For stateless app services, containers are an almost perfect devops solution. However, for stateful services like databases, it’s not so straightforward. Whether production databases should be containerized remains controversial. From a developer’s …
The Sorrow of the Internet
In Understanding the Internet, I discussed my views on the internet. Today, let’s talk about the part I deliberately omitted: the setbacks the internet will face. Destiny It was the best of times, it was the worst of times; It was the age of wisdom, …
In Understanding the Internet, I discussed my views on the internet. Today, let’s talk about the part I deliberately omitted: the setbacks the internet will face. Destiny It was the best of times, it was the worst of times; It was the age of wisdom, …
Understanding Time - Leap Years, Leap Seconds, Time and Time Zones
A few days ago, we encountered the quadrennial leap year February 29th. Every time this day comes around, some poorly written software experiences major failures. If you’re unlucky, this type of problem might take four years to surface. For example, …
A few days ago, we encountered the quadrennial leap year February 29th. Every time this day comes around, some poorly written software experiences major failures. If you’re unlucky, this type of problem might take four years to surface. For example, …
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, …
New Year Reflections
This article may cause discomfort. Please read with caution. During this rare leisure time alone in the New Year, I write these random thoughts, writing wherever my mind wanders. I’ve always believed that choice is more important than effort: a …
This article may cause discomfort. Please read with caution. During this rare leisure time alone in the New Year, I write these random thoughts, writing wherever my mind wanders. I’ve always believed that choice is more important than effort: a …