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.

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 …

Blockchain and Distributed Databases
The technical essence, functionality, and evolution of blockchain is distributed databases. Specifically, it's a Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless …

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.

Consistency: An Overloaded Term
The term "consistency" is heavily overloaded, representing different concepts in different contexts. For example, the C in ACID and the C in CAP actually refer to different concepts.

Why Study Database Principles
Those who only know how to code are just programmers; learn databases well, and you can at least make a living; but for excellent engineers, merely using databases is far from enough.

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

PostgreSQL Routine Maintenance
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum

Backup and Recovery Methods Overview
Backup is the foundation of a DBA's livelihood. With backups, there's no need to panic.

Pgbouncer Quick Start
Pgbouncer is a lightweight database connection pool. This guide covers basic Pgbouncer configuration, management, and usage.

PgBackRest2 Documentation
PgBackRest is a set of PostgreSQL backup tools written in Perl

Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.

Using sysbench to Test PostgreSQL Performance
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.

Testing Disk Performance with FIO
FIO is a convenient tool for testing disk I/O performance

PostgreSQL Server Log Regular Configuration
It's recommended to configure PostgreSQL's log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables.

Finding Unused Indexes
Indexes are useful, but they're not free. Unused indexes are a waste. Use these methods to identify unused indexes.

Batch Configure SSH Passwordless Login
Quick configuration for passwordless login to all machines

Wireshark Packet Capture Protocol Analysis
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here's a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.

The Versatile file_fdw — Reading System Information from Your Database
With file_fdw, you can easily view operating system information, fetch network data, and feed various data sources into your database for unified viewing and management.

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 …

Blockchain and Distributed Databases
The technical essence, functionality, and evolution of blockchain is distributed databases. Specifically, it's a Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless …

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.

Consistency: An Overloaded Term
The term "consistency" is heavily overloaded, representing different concepts in different contexts. For example, the C in ACID and the C in CAP actually refer to different concepts.

Why Study Database Principles
Those who only know how to code are just programmers; learn databases well, and you can at least make a living; but for excellent engineers, merely using databases is far from enough.

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

PostgreSQL Routine Maintenance
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum

Backup and Recovery Methods Overview
Backup is the foundation of a DBA's livelihood. With backups, there's no need to panic.

Pgbouncer Quick Start
Pgbouncer is a lightweight database connection pool. This guide covers basic Pgbouncer configuration, management, and usage.

PgBackRest2 Documentation
PgBackRest is a set of PostgreSQL backup tools written in Perl

Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.

Using sysbench to Test PostgreSQL Performance
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.

Testing Disk Performance with FIO
FIO is a convenient tool for testing disk I/O performance

PostgreSQL Server Log Regular Configuration
It's recommended to configure PostgreSQL's log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables.

Finding Unused Indexes
Indexes are useful, but they're not free. Unused indexes are a waste. Use these methods to identify unused indexes.

Batch Configure SSH Passwordless Login
Quick configuration for passwordless login to all machines

Wireshark Packet Capture Protocol Analysis
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here's a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.

The Versatile file_fdw — Reading System Information from Your Database
With file_fdw, you can easily view operating system information, fetch network data, and feed various data sources into your database for unified viewing and management.
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 …
Blockchain and Distributed Databases

The essence, intended functionality, and evolutionary direction of blockchain is distributed databases. To be precise, it’s a Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless replication) database. If this …
The essence, intended functionality, and evolutionary direction of blockchain is distributed databases. To be precise, it’s a Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless replication) database. If this …
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, …
Consistency: An Overloaded Term

The term consistency is heavily overloaded, representing different things in different contexts and situations: In the context of transactions, such as the C in ACID, it refers to the usual Consistency In the context of distributed systems, such as …
The term consistency is heavily overloaded, representing different things in different contexts and situations: In the context of transactions, such as the C in ACID, it refers to the usual Consistency In the context of distributed systems, such as …
Why Study Database Principles

Our school offers a database systems principles course. But I’m still confused. From the first few classes, the teacher started with a bunch of mind-numbing terms and concepts. I thought knowing “how to design and build tables” and “how to perform …
Our school offers a database systems principles course. But I’m still confused. From the first few classes, the teacher started with a bunch of mind-numbing terms and concepts. I thought knowing “how to design and build tables” and “how to perform …
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 …
PostgreSQL Routine Maintenance

Cars need oil changes, databases need maintenance. Maintenance Tasks in PG For PG, there are three important maintenance tasks: backup, repack, vacuum Backup: The most important routine work, a lifeline. Create base backups Archive incremental WAL …
Cars need oil changes, databases need maintenance. Maintenance Tasks in PG For PG, there are three important maintenance tasks: backup, repack, vacuum Backup: The most important routine work, a lifeline. Create base backups Archive incremental WAL …
Backup and Recovery Methods Overview

Author: Vonng (@Vonng) Backup is the foundation of a DBA’s livelihood. With backups, there’s no need to panic. There are three forms of backup: SQL dumps, file system backups, and continuous archiving. 1. SQL Dumps The idea behind the SQL dump …
Author: Vonng (@Vonng) Backup is the foundation of a DBA’s livelihood. With backups, there’s no need to panic. There are three forms of backup: SQL dumps, file system backups, and continuous archiving. 1. SQL Dumps The idea behind the SQL dump …
Pgbouncer Quick Start

Pgbouncer is a lightweight database connection pool. Synopsis pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini> pgbouncer -V|-h Description pgbouncer is a PostgreSQL connection pooler. Any target application can connect to pgbouncer as if it were a …
Pgbouncer is a lightweight database connection pool. Synopsis pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini> pgbouncer -V|-h Description pgbouncer is a PostgreSQL connection pooler. Any target application can connect to pgbouncer as if it were a …
PgBackRest2 Documentation

pgBackRest homepage: http://pgbackrest.org pgBackRest Github homepage: https://github.com/pgbackrest/pgbackrest Preface pgBackRest aims to provide a simple, reliable, easily scalable PostgreSQL backup and recovery system. pgBackRest doesn’t depend on …
pgBackRest homepage: http://pgbackrest.org pgBackRest Github homepage: https://github.com/pgbackrest/pgbackrest Preface pgBackRest aims to provide a simple, reliable, easily scalable PostgreSQL backup and recovery system. pgBackRest doesn’t depend on …
Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration

Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation. Zero-downtime data migration can essentially be viewed as consisting of three operations: Replication: Logical …
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation. Zero-downtime data migration can essentially be viewed as consisting of three operations: Replication: Logical …
Using sysbench to Test PostgreSQL Performance

sysbench homepage: https://github.com/akopytov/sysbench Installation Binary installation - on Mac, use brew to install sysbench: brew install sysbench --with-postgresql Source compilation (CentOS): yum -y install make automake libtool pkgconfig …
sysbench homepage: https://github.com/akopytov/sysbench Installation Binary installation - on Mac, use brew to install sysbench: brew install sysbench --with-postgresql Source compilation (CentOS): yum -y install make automake libtool pkgconfig …
Testing Disk Performance with FIO

Author: Vonng FIO is an excellent disk performance testing tool. You can test disk read/write performance using the following commands. fio --filename=/tmp/fio.data \ -direct=1 \ -iodepth=32 \ -rw=randrw \ --rwmixread=80 \ -bs=4k \ -size=1G \ …
Author: Vonng FIO is an excellent disk performance testing tool. You can test disk read/write performance using the following commands. fio --filename=/tmp/fio.data \ -direct=1 \ -iodepth=32 \ -rw=randrw \ --rwmixread=80 \ -bs=4k \ -size=1G \ …
PostgreSQL Server Log Regular Configuration

It’s recommended to configure PostgreSQL’s log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables. Log-Related Configuration Items log_destination ='csvlog' logging_collector =on log_directory ='log' …
It’s recommended to configure PostgreSQL’s log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables. Log-Related Configuration Items log_destination ='csvlog' logging_collector =on log_directory ='log' …
Finding Unused Indexes

Author: Vonng Indexes are useful, but they’re not free. Unused indexes are a waste. Use the following SQL to identify unused indexes: First, exclude indexes used to implement constraints (can’t be dropped) Expression indexes (containing field 0 in …
Author: Vonng Indexes are useful, but they’re not free. Unused indexes are a waste. Use the following SQL to identify unused indexes: First, exclude indexes used to implement constraints (can’t be dropped) Expression indexes (containing field 0 in …
Batch Configure SSH Passwordless Login

Configuring SSH is fundamental operations work - sometimes the basics need revisiting. Generate Public-Private Key Pairs Ideally, everything should use public-private key authentication for passwordless direct connection from local to all database …
Configuring SSH is fundamental operations work - sometimes the basics need revisiting. Generate Public-Private Key Pairs Ideally, everything should use public-private key authentication for passwordless direct connection from local to all database …
Wireshark Packet Capture Protocol Analysis

Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis. Assuming debugging local PostgreSQL instance: 127.0.0.1:5432 …
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis. Assuming debugging local PostgreSQL instance: 127.0.0.1:5432 …
The Versatile file_fdw — Reading System Information from Your Database

Author: Vonng PostgreSQL is the most advanced open-source database, and one of its killer features is FDW: Foreign Data Wrapper. Through FDW, users can access various external data sources from Postgres in a unified manner. file_fdw is one of the …
Author: Vonng PostgreSQL is the most advanced open-source database, and one of its killer features is FDW: Foreign Data Wrapper. Through FDW, users can access various external data sources from Postgres in a unified manner. file_fdw is one of the …