-
Query Optimization: The Macro Approach with pg_stat_statements
2023-10-26 in PG Admin
In production databases, slow queries not only impact end-user experience but also waste system resources, increase resource saturation, cause deadlocks and transaction conflicts, add pressure to database connections, and lead to replication lag. …
-
Rescue Data with pg_filedump?
2023-09-27 in PG Admin
Backups are a DBA’s lifeline — but what if your PostgreSQL database has crashed without any backups? Maybe pg_filedump can help! Recently, I encountered a rather challenging case. Here’s the situation: A user’s PostgreSQL database …
-
Collation in PostgreSQL
2021-03-05 in PG Admin
Why does Pigsty default to locale=C and encoding=UTF8 when initializing PostgreSQL databases? The answer is simple: Unless you explicitly need LOCALE-specific features, you should never configure anything other than C.UTF8 for character encoding and …
-
PostgreSQL Replica Identity Explained
2021-03-03 in PG Admin
Introduction: DIY Logical Replication The concept of Replica Identity serves logical replication. Logical replication fundamentally works by decoding row-level changes (INSERT/UPDATE/DELETE) on published tables and applying them to subscribers. The …
-
PG Logical Replication Explained
2021-03-03 in PG Admin
Logical Replication Logical Replication is a method of replicating data objects and their changes based on the Replica Identity (typically primary keys) of data objects. The term Logical Replication contrasts with Physical Replication, where …
-
Slow Query Diagnosis
2021-02-23 in PG Admin
You can’t optimize what you can’t measure Slow queries are the arch-enemy of online business databases. Knowing how to diagnose and locate slow queries is an essential skill for DBAs. This article introduces a general methodology for …