
PostgreSQL Posts
Column: PostgreSQL Mage
·473 words·3 mins
Navigation of articles about PostgreSQL development, administration, principles, ecosystem, tools, architecture design, performance optimization, troubleshooting, and more.
PostgreSQL Dominates Database World, but Who Will Devour PG?
·2717 words·13 mins
The same forces that once led MongoDB and MySQL toward closure are now at work in the PostgreSQL ecosystem. The PG world needs a distribution that represents “software freedom” values.
PostgreSQL Has Dominated the Database World
·1443 words·7 mins
The 2025 SO global developer survey results are fresh out, and PostgreSQL has become the most popular, most loved, and most wanted database for the third consecutive year. Nothing can stop PostgreSQL from consolidating the entire database world!
Supply Chain Strangled: PGDG Cuts Off Mirror Sync Channel
·743 words·4 mins
PGDG cuts off FTP rsync sync channels, global mirror sites universally disconnected - this time they really strangled global users’ supply chain.
Postgres Extension Day - See You There!
·1907 words·9 mins
The annual PostgreSQL developer conference will be held in Montreal in May. Like the first PG Con.Dev, there’s also an additional dedicated event - Postgres Extensions Day
OrioleDB is Coming! 4x Performance, Eliminates Pain Points, Storage-Compute Separation
·1434 words·3 mins
A PG kernel fork acquired by Supabase, claiming to solve PG’s XID wraparound problem, eliminate table bloat issues, improve performance by 4x, and support cloud-native storage. Now part of the Pigsty family.
OpenHalo: MySQL Wire-Compatible PostgreSQL is Here!
·713 words·4 mins
What? PostgreSQL can now be accessed using MySQL clients? That’s right, openHalo, which was open-sourced on April Fool’s Day, provides exactly this capability and has now joined the Pigsty kernel family.
PGFS: Using Database as a Filesystem
·1540 words·8 mins
Leverage JuiceFS to turn PostgreSQL into a filesystem with PITR capabilities!
PostgreSQL Ecosystem Frontier Developments
·1853 words·9 mins
Sharing some interesting recent developments in the PG ecosystem.
Pig, The Postgres Extension Wizard
·541 words·3 mins
Why would we need yet another package manager for PostgreSQL & extensions?
Don't Upgrade! Released and Immediately Pulled - Even PostgreSQL Isn't Immune to Epic Fails
·1849 words·9 mins
Never deploy on Friday, or you’ll be working all weekend! PostgreSQL minor releases were pulled on the day of release, requiring emergency rollback.
PostgreSQL 12 End-of-Life, PG 17 Takes the Throne
·1609 words·8 mins
PG17 achieved extension ecosystem adaptation in half the time of PG16, with 300 available extensions ready for production use. PG 12 officially exits support lifecycle.
The ideal way to deliver PostgreSQL Extensions
·2219 words·11 mins
PostgreSQL Is Eating the Database World through the power of extensibility. With 390 extensions powering PG, we may not say it’s invincible, but it’s definitely getting much closer.
PostgreSQL 17 Released: No More Pretending!
·2704 words·13 mins
PostgreSQL is now the world’s most advanced open-source database and has become the preferred open-source database for organizations of all sizes, matching or exceeding top commercial databases.
Can PostgreSQL Replace Microsoft SQL Server?
·755 words·4 mins
PostgreSQL can directly replace Oracle, SQL Server, and MongoDB at the kernel level. Of course, the most thorough replacement is SQL Server - AWS’s Babelfish provides wire-protocol-level compatibility.
Whoever Integrates DuckDB Best Wins the OLAP World
·1322 words·7 mins
Just like the vector database extension race two years ago, the current PostgreSQL ecosystem extension competition has begun revolving around DuckDB. MotherDuck’s official entry into the PostgreSQL extension space undoubtedly signals that competition has entered white-hot territory.
StackOverflow 2024 Survey: PostgreSQL Has Gone Completely Berserk
·659 words·4 mins
The 2024 StackOverflow Global Developer Survey results are fresh out, and PostgreSQL has become the most popular, most loved, and most wanted database globally for the second consecutive year. Nothing can stop PostgreSQL from devouring the entire database world anymore!
Self-Hosting Dify with PG, PGVector, and Pigsty
·1241 words·6 mins
Dify is an open-source LLM app development platform. This article explains how to self-host Dify using Pigsty.
PGCon.Dev 2024, The conf that shutdown PG for a week
·3415 words·17 mins
Experience & Feeling on the PGCon.Dev 2024
PostgreSQL 17 Beta1 Released!
·1358 words·7 mins
The PostgreSQL Global Development Group announces PostgreSQL 17’s first Beta version is now available. This time, PostgreSQL has truly burst the toothpaste tube!
Why PostgreSQL is the Bedrock for the Future of Data
·2719 words·13 mins
Today, one of the biggest trends in software development is PostgreSQL becoming the de facto database standard. There have already been blogs about using PostgreSQL for everything, but until now, there haven’t been many articles explaining the reasons behind this phenomenon (and more importantly, why it matters).
Will PostgreSQL Change Its Open Source License?
·2090 words·10 mins
PostgreSQL will not change its license, Since the license is literally named after the project, and the project has a long-standing commitment to open source community collaboration.
Postgres is eating the database world
·2645 words·13 mins
PostgreSQL isn’t just a simple relational database; it’s a data management framework with the potential to engulf the entire database realm.
Technical Minimalism: Just Use PostgreSQL for Everything
·988 words·5 mins
Whether production databases should be containerized remains a controversial topic. From a DBA’s perspective, I believe that currently, putting production databases in Docker is still a bad idea.
New PostgreSQL Ecosystem Player: ParadeDB
·877 words·5 mins
ParadeDB aims to be an Elasticsearch alternative: “Modern Elasticsearch Alternative built on Postgres” — PostgreSQL for search and analytics.
PostgreSQL's Impressive Scalability
·2177 words·11 mins
This article describes how Cloudflare scaled to support 55 million requests per second using 15 PostgreSQL clusters, and PostgreSQL’s scalability performance.
PostgreSQL Wins 2024 Database of the Year Award! (Fifth Time)
·724 words·4 mins
DB-Engines officially announced today that PostgreSQL has once again been crowned “Database of the Year.” This is the fifth time PG has received this honor in the past seven years. If not for Snowflake stealing the spotlight for two years, the database world would have almost become a PostgreSQL solo show.
PostgreSQL Convention 2024
·7925 words·38 mins
No rules, no standards. Some developer conventions for PostgreSQL 16.
PostgreSQL Macro Query Optimization with pg_stat_statements
·4341 words·21 mins
Query optimization is one of the core responsibilities of DBAs. This article introduces how to use metrics provided by pg_stat_statements for macro-level PostgreSQL query optimization.
FerretDB: PostgreSQL Disguised as MongoDB
·1242 words·6 mins
FerretDB aims to provide a truly open-source MongoDB alternative based on PostgreSQL.
How to Use pg_filedump for Data Recovery?
·2806 words·14 mins
Backups are a DBA’s lifeline — but what if your PostgreSQL database has already exploded and you have no backups? Maybe pg_filedump can help you!
Vectors are the New JSON
·2583 words·13 mins
Vector-based features will become key elements in building applications, just like JSON in history. PostgreSQL once again stands at the forefront of the times leading database trends, securing high growth in the AI era with vector extension support.
PostgreSQL, The most successful database
·3079 words·7 mins
StackOverflow 2023 Survey shows PostgreSQL is the most popular, loved, and wanted database, solidifying its status as the ‘Linux of Database’.
AI Large Models and Vector Database PGVector
·1941 words·10 mins
This article focuses on vector databases hyped by AI, introduces the basic principles of AI embeddings and vector storage/retrieval, and demonstrates the functionality, performance, acquisition, and application of the vector database extension PGVECTOR through a concrete knowledge base retrieval case study.
How Powerful is PostgreSQL Really?
·2319 words·11 mins
Let performance data speak: Why PostgreSQL is the world’s most advanced open-source relational database, aka the world’s most successful database. MySQL vs PostgreSQL performance showdown and distributed database reality check.
Why PostgreSQL is the Most Successful Database?
·3102 words·15 mins
Database users are developers, but what about developers’ preferences, likes, and choices? Looking at StackOverflow survey results over the past six years, it’s clear that in 2022, PostgreSQL has won all three categories, becoming literally the “most successful database”
Ready-to-Use PostgreSQL Distribution: Pigsty
·2055 words·10 mins
Yesterday I gave a live presentation in the PostgreSQL Chinese community, introducing the open-source PostgreSQL full-stack solution — Pigsty
Why Does PostgreSQL Have a Bright Future?
·4920 words·24 mins
Databases are the core component of information systems, relational databases are the absolute backbone of databases, and PostgreSQL is the world’s most advanced open source relational database. With such favorable timing and positioning, how can it not achieve great success?
Implementing Advanced Fuzzy Search
·3253 words·7 mins
How to implement relatively complex fuzzy search logic in PostgreSQL?
Localization and Collation Rules in PostgreSQL
·2720 words·6 mins
What? Don’t know what COLLATION is? Remember one thing: using C COLLATE is always the right choice!
PG Replica Identity Explained
·2214 words·11 mins
Replica identity is important - it determines the success or failure of logical replication
PostgreSQL Logical Replication Deep Dive
·6564 words·31 mins
This article introduces the principles and best practices of logical replication in PostgreSQL 13.
Incident Report: Patroni Failure Due to Time Travel
·145 words·1 min
Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start.
Online Primary Key Column Type Change
·1175 words·6 mins
How to change column types online, such as upgrading from INT to BIGINT?
Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
·1250 words·6 mins
Understanding the golden monitoring metrics in PostgreSQL
Database Cluster Management Concepts and Entity Naming Conventions
·1509 words·8 mins
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 names create unexpected additional burden. Therefore, they need careful design.
PostgreSQL's KPI
·3053 words·15 mins
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 independent of workload type and machine type, called PG Load.
Online PostgreSQL Column Type Migration
·790 words·4 mins
How to modify PostgreSQL column types online? A general approach
Frontend-Backend Communication Wire Protocol
·872 words·5 mins
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
Transaction Isolation Level Considerations
·2705 words·13 mins
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable
Incident: PostgreSQL Extension Installation Causes Connection Failure
·437 words·3 mins
Today encountered an interesting case where a customer reported database connection issues caused by extensions.
CDC Change Data Capture Mechanisms
·4573 words·22 mins
Change Data Capture is an interesting ETL alternative solution.
O(n2) Complexity of GIN Search
·674 words·4 mins
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.
PostgreSQL Common Replication Topology Plans
·578 words·3 mins
Replication is one of the core issues in system architecture.
Warm Standby: Using pg_receivewal
·766 words·4 mins
There are various backup strategies. Physical backups can usually be divided into four types.
Incident Report: Connection Pool Contamination Caused by pg_dump
·1237 words·6 mins
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.
PostgreSQL Data Page Corruption Repair
·2729 words·13 mins
Using binary editing to repair PostgreSQL data pages, and how to make a primary key query return two records.
Relation Bloat Monitoring and Management
·3182 words·15 mins
PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat.
TimescaleDB Quick Start
·4021 words·19 mins
TimescaleDB is a PostgreSQL extension plugin that provides time-series database functionality.
Incident Report: Integer Overflow from Rapid Sequence Number Consumption
·651 words·4 mins
If you use Integer sequences on tables, you should consider potential overflow scenarios.
Incident Report: PostgreSQL Transaction ID Wraparound
·968 words·5 mins
XID WrapAround is perhaps a unique type of failure specific to PostgreSQL
GeoIP Geographic Reverse Lookup Optimization
·1551 words·8 mins
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)
PostgreSQL Trigger Usage Considerations
·1069 words·6 mins
Detailed understanding of trigger management and usage in PostgreSQL
PostgreSQL Development Convention (2018 Edition)
·3438 words·17 mins
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.
What Are PostgreSQL's Advantages?
·2153 words·11 mins
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 them all.
Efficient Administrative Region Lookup with PostGIS
·2180 words·11 mins
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
KNN Ultimate Optimization: From RDS to PostGIS
·4114 words·20 mins
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Monitoring Table Size in PostgreSQL
·908 words·5 mins
Tables in PostgreSQL correspond to many physical files. This article explains how to calculate the actual size of a table in PostgreSQL.
PgAdmin Installation and Configuration
·220 words·2 mins
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
·1342 words·7 mins
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.
Distinct On: Remove Duplicate Data
·557 words·3 mins
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
Function Volatility Classification Levels
·644 words·4 mins
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
Implementing Mutual Exclusion Constraints with Exclude
·854 words·5 mins
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
PostgreSQL Routine Maintenance
·130 words·1 min
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum
Backup and Recovery Methods Overview
·4009 words·19 mins
Backup is the foundation of a DBA’s livelihood. With backups, there’s no need to panic.
PgBackRest2 Documentation
·3129 words·15 mins
PgBackRest is a set of PostgreSQL backup tools written in Perl
Pgbouncer Quick Start
·2906 words·14 mins
Pgbouncer is a lightweight database connection pool. This guide covers basic Pgbouncer configuration, management, and usage.
PostgreSQL Server Log Regular Configuration
·660 words·4 mins
It’s recommended to configure PostgreSQL’s log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables.
Testing Disk Performance with FIO
·335 words·2 mins
FIO is a convenient tool for testing disk I/O performance
Using sysbench to Test PostgreSQL Performance
·301 words·2 mins
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.
Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
·681 words·4 mins
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.
Finding Unused Indexes
·376 words·2 mins
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
·297 words·2 mins
Quick configuration for passwordless login to all machines
Wireshark Packet Capture Protocol Analysis
·982 words·5 mins
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
·846 words·4 mins
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.
Common Linux Statistics CLI Tools
·2379 words·12 mins
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
Installing PostGIS from Source
·630 words·3 mins
PostGIS is PostgreSQL’s killer extension, but compiling and installing it isn’t easy.
Go Database Tutorial: database/sql
·4249 words·20 mins
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
·1234 words·6 mins
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
·477 words·3 mins
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
UUID Properties, Principles and Applications
·1683 words·8 mins
UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.