This article presents a collection of practical PostgreSQL DBA techniques—including selective updates, bulk‑load optimizations, CTE‑based deduplication, partial and BRIN indexes, correlation tuning, and transactional DDL tricks—to help developers reduce database bottlenecks, improve query speed, and avoid common performance pitfalls.
The article summarizes Digoal's 2017 Gdevops talk, detailing PostgreSQL's advanced features such as block-level BRIN indexes, high‑efficiency range queries, asynchronous messaging, data pumps, real sharding, MADlib machine‑learning integration, FDW data federation, and large‑scale use cases in IoT, finance, and geospatial analytics.
This article demonstrates how PostgreSQL 9.5's BRIN index leverages the allnulls and hasnulls flags in its regular pages to filter out unnecessary heap page scans, dramatically improving query performance while also discussing the impact of MVCC bloat and maintenance.
This article demonstrates how PostgreSQL's Partial Index feature can be used together with BTREE and BRIN indexes to dramatically reduce storage while maintaining high query performance on both low‑frequency and high‑frequency data in a heavily skewed dataset.
This article experimentally compares PostgreSQL 9.5's BRIN and BTREE indexes across seven data distributions, revealing that BRIN uses up to 6,848 times less storage while BTREE consistently outperforms BRIN in query speed, with the gap narrowing as data repetition increases.
