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.
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.
Shangri-La: Luoke Line Trekking Journal
Solo heavy trekking on the Luoke Line, completing a 6-day route in three and a half days - nearly died on the mountain.
Installing PostGIS from Source
PostGIS is PostgreSQL's killer extension, but compiling and installing it isn't easy.
Common Linux Statistics CLI Tools
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
Go Database Tutorial: database/sql
Similar to JDBC, Go also has a standard database access interface. This article details how to use database/sql in Go and important considerations.
Implementing Cache Synchronization with Go and PostgreSQL
Cleverly utilizing PostgreSQL's Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.
Auditing Data Changes with Triggers
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
Basic Principles of Neural Networks
Neural networks are inspired by how the brain works and can be used to solve general learning problems. This article introduces the basic principles and practice of neural networks.
Statistics Fundamentals: Descriptive Statistics
Statistical analysis is divided into two fields: descriptive statistics and inferential statistics. Descriptive Statistics is the technology for describing or characterizing existing data and is the …
Inferential Statistics: The Past and Present of p-values
The core of inferential statistics lies in hypothesis testing. The basic logic is based on an important argument from philosophy of science: universal propositions can only be falsified, not proven. …
Building an ItemCF Recommender in Pure SQL
Five minutes, PostgreSQL, and the MovieLens dataset—that’s all you need to implement a classic item-based collaborative filtering recommender.
Basic Concepts of Probability Theory
Basic knowledge notes on probability theory: axiomatic foundations, probability calculus, counting, conditional probability, random variables and distribution functions
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.
Shangri-La: Luoke Line Trekking Journal
Solo heavy trekking on the Luoke Line, completing a 6-day route in three and a half days - nearly died on the mountain.
Installing PostGIS from Source
PostGIS is PostgreSQL's killer extension, but compiling and installing it isn't easy.
Common Linux Statistics CLI Tools
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
Go Database Tutorial: database/sql
Similar to JDBC, Go also has a standard database access interface. This article details how to use database/sql in Go and important considerations.
Implementing Cache Synchronization with Go and PostgreSQL
Cleverly utilizing PostgreSQL's Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.
Auditing Data Changes with Triggers
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
Basic Principles of Neural Networks
Neural networks are inspired by how the brain works and can be used to solve general learning problems. This article introduces the basic principles and practice of neural networks.
Statistics Fundamentals: Descriptive Statistics
Statistical analysis is divided into two fields: descriptive statistics and inferential statistics. Descriptive Statistics is the technology for describing or characterizing existing data and is the …
Inferential Statistics: The Past and Present of p-values
The core of inferential statistics lies in hypothesis testing. The basic logic is based on an important argument from philosophy of science: universal propositions can only be falsified, not proven. …
Building an ItemCF Recommender in Pure SQL
Five minutes, PostgreSQL, and the MovieLens dataset—that’s all you need to implement a classic item-based collaborative filtering recommender.
Basic Concepts of Probability Theory
Basic knowledge notes on probability theory: axiomatic foundations, probability calculus, counting, conditional probability, random variables and distribution functions
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 …
Shangri-La: Luoke Line Trekking Journal
Solo heavy trekking on the Luoke Line, completing a 6-day route in three and a half days - nearly died on the mountain. After anticipating this for half a year, the Luoke Line trek is finally complete. Still feeling a bit surreal. On the last day in …
Solo heavy trekking on the Luoke Line, completing a 6-day route in three and a half days - nearly died on the mountain. After anticipating this for half a year, the Luoke Line trek is finally complete. Still feeling a bit surreal. On the last day in …
Installing PostGIS from Source
Strongly recommend using yum / apt commands to install PostGIS from official PostgreSQL binary repositories. Reference: …
Strongly recommend using yum / apt commands to install PostGIS from official PostgreSQL binary repositories. Reference: …
Common Linux Statistics CLI Tools
top free vmstat iostat top Display Linux tasks Summary Press space or enter to force refresh Use h to open help Use l,t,m to collapse summary sections Use d to modify refresh interval Use z to enable color highlighting Use u to list processes for …
top free vmstat iostat top Display Linux tasks Summary Press space or enter to force refresh Use h to open help Use l,t,m to collapse summary sections Use d to modify refresh interval Use z to enable color highlighting Use u to list processes for …
Go Database Tutorial: database/sql
The conventional way Go uses SQL and SQL-like databases is through the standard library database/sql. This is a generic abstraction for relational databases that provides a standard, lightweight, row-oriented interface. However, the documentation for …
The conventional way Go uses SQL and SQL-like databases is through the standard library database/sql. This is a generic abstraction for relational databases that provides a standard, lightweight, row-oriented interface. However, the documentation for …
Implementing Cache Synchronization with Go and PostgreSQL
Parallel and Hierarchy are the two great principles of architectural design, and caching is the embodiment of Hierarchy in the IO domain. Implementing caching mechanisms in single-threaded scenarios can be surprisingly simple, but it’s hard to …
Parallel and Hierarchy are the two great principles of architectural design, and caching is the embodiment of Hierarchy in the IO domain. Implementing caching mechanisms in single-threaded scenarios can be surprisingly simple, but it’s hard to …
Auditing Data Changes with Triggers
Author: Vonng (@Vonng) Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically. -- Create an audit-specific schema and revoke all non-superuser privileges DROP …
Author: Vonng (@Vonng) Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically. -- Create an audit-specific schema and revoke all non-superuser privileges DROP …
Basic Principles of Neural Networks
Neural networks are inspired by how the brain works and can be used to solve general learning problems. This article introduces the basic principles and practice of neural networks. Neural Network Representation Neuron Model Neural networks are …
Neural networks are inspired by how the brain works and can be used to solve general learning problems. This article introduces the basic principles and practice of neural networks. Neural Network Representation Neuron Model Neural networks are …
Statistics Fundamentals: Descriptive Statistics
Statistical analysis is divided into two fields: descriptive statistics and inferential statistics. Descriptive Statistics is the technology for describing or characterizing existing data and is the most fundamental part of statistics. 1. Statistics …
Statistical analysis is divided into two fields: descriptive statistics and inferential statistics. Descriptive Statistics is the technology for describing or characterizing existing data and is the most fundamental part of statistics. 1. Statistics …
Inferential Statistics: The Past and Present of p-values
The core of inferential statistics lies in hypothesis testing. The basic logic is based on an important argument from philosophy of science: universal propositions can only be falsified, not proven. The reasoning is simple: individual cases cannot …
The core of inferential statistics lies in hypothesis testing. The basic logic is based on an important argument from philosophy of science: universal propositions can only be falsified, not proven. The reasoning is simple: individual cases cannot …
Building an ItemCF Recommender in Pure SQL
Everyone knows “item-based collaborative filtering” (ItemCF): Amazon recommendations, YouTube watch-next, etc. Here’s how to implement it in PostgreSQL using the MovieLens dataset. No Python, just SQL. Theory in one minute ItemCF recommends items …
Everyone knows “item-based collaborative filtering” (ItemCF): Amazon recommendations, YouTube watch-next, etc. Here’s how to implement it in PostgreSQL using the MovieLens dataset. No Python, just SQL. Theory in one minute ItemCF recommends items …
Basic Concepts of Probability Theory
1. Set Theory Sample space and sample points are undefined basic concepts in probability theory, like the concepts of points and lines in geometry. Definition: Event Event: An event is a set of sample points. $A=0$ means event A contains no sample …
1. Set Theory Sample space and sample points are undefined basic concepts in probability theory, like the concepts of points and lines in geometry. Definition: Event Event: An event is a set of sample points. $A=0$ means event A contains no sample …