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
126posts
PostgreSQL ecosystem, internals, extensions, and production tuning.
AI Agent Explorer
97posts
Practical engineering with coding agents, LLMs, AI4DB, and DB4AI.
Travel Notes
12posts
Trips, hikes, landscapes, and field notes across mountains and seas.
Miscellaneous
40posts
Essays, reflections, and personal writing inside and outside of tech.
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.
Everest East Face: Gamma Gou Trekking
13-day journey with 8 days of trekking, completing the legendary hardcore route - Gamma Gou. Finally witnessed the most beautiful sunrise on Everest's east face.
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.
Several Levels of Learning Knowledge
Through observation and contemplation, we can divide the process of deepening understanding in the mind from accepting knowledge to the highest level of "intuition" into four stages: knowledge, …
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)
Understanding Character Encoding Principles
Without understanding the basic principles of character encoding, even simple string operations like comparison, sorting, and random access can easily lead you into pitfalls. This article attempts to …
Understanding Character Encoding
Code is literally “encoding,” and everything collapses if you mishandle text. Here’s a practical tour of characters, glyphs, Unicode, and UTF encodings.
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.
Concurrency Anomalies Explained
Concurrent programs are hard to write correctly and even harder to write well. Many programmers simply throw these problems at the database... But even the most sophisticated databases won't help if …
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.
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.
Everest East Face: Gamma Gou Trekking
13-day journey with 8 days of trekking, completing the legendary hardcore route - Gamma Gou. Finally witnessed the most beautiful sunrise on Everest's east face.
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.
Several Levels of Learning Knowledge
Through observation and contemplation, we can divide the process of deepening understanding in the mind from accepting knowledge to the highest level of "intuition" into four stages: knowledge, …
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)
Understanding Character Encoding Principles
Without understanding the basic principles of character encoding, even simple string operations like comparison, sorting, and random access can easily lead you into pitfalls. This article attempts to …
Understanding Character Encoding
Code is literally “encoding,” and everything collapses if you mishandle text. Here’s a practical tour of characters, glyphs, Unicode, and UTF encodings.
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.
Concurrency Anomalies Explained
Concurrent programs are hard to write correctly and even harder to write well. Many programmers simply throw these problems at the database... But even the most sophisticated databases won't help if …
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.
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 …
Everest East Face: Gamma Gou Trekking
This year’s National Day holiday was perfect - taking six days off could connect Mid-Autumn Festival with National Day for a 16-day consecutive break. I’ve visited most of China’s provinces, but Tibet remained unexplored. Seeing a Gamma Gou/Everest …
This year’s National Day holiday was perfect - taking six days off could connect Mid-Autumn Festival with National Day for a 16-day consecutive break. I’ve visited most of China’s provinces, but Tibet remained unexplored. Seeing a Gamma Gou/Everest …
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. …
Several Levels of Learning Knowledge
Original WeChat Article Levels of Knowledge When we say “learning knowledge,” what exactly are we talking about? When we say someone is “smart,” what exactly do we mean? Through observation and contemplation, we can divide the process of deepening …
Original WeChat Article Levels of Knowledge When we say “learning knowledge,” what exactly are we talking about? When we say someone is “smart,” what exactly do we mean? Through observation and contemplation, we can divide the process of deepening …
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 …
Understanding Character Encoding Principles
Programmers deal with Code (both programming code and encoding), and character encoding is the most fundamental type of encoding. The problem of how to represent characters using binary numbers - this character encoding problem is not as simple as it …
Programmers deal with Code (both programming code and encoding), and character encoding is the most fundamental type of encoding. The problem of how to represent characters using binary numbers - this character encoding problem is not as simple as it …
Understanding Character Encoding
WeChat original Programmers spend their lives with code—both source code and encodings. Representing text with bits is harder than it looks: character sets, comparators, normalization, locale rules, variable-length encodings, BOMs, surrogate pairs, …
WeChat original Programmers spend their lives with code—both source code and encodings. Representing text with bits is harder than it looks: character sets, comparators, normalization, locale rules, variable-length encodings, BOMs, surrogate pairs, …
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 …
Concurrency Anomalies Explained
Concurrent programs are hard to write correctly and even harder to write well. Many programmers haven’t truly figured out these problems - they just dump them all on the database. Concurrency anomalies aren’t just theoretical problems: these …
Concurrent programs are hard to write correctly and even harder to write well. Many programmers haven’t truly figured out these problems - they just dump them all on the database. Concurrency anomalies aren’t just theoretical problems: these …
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 …