This article walks MySQL developers through the essential architectural, schema, data‑type, SQL‑dialect, transaction, vacuum, indexing, and extension differences when moving to PostgreSQL, providing concrete examples, code snippets, and best‑practice recommendations to avoid common pitfalls.
The article reveals seven common PostgreSQL architectural oversights—such as neglecting vacuum, misusing UUID primary keys, treating the database as a queue, missing indexes, over‑relying on ORMs, ignoring write‑side scaling, and failing to partition large tables—that silently degrade performance and reliability, and provides concrete fixes and best‑practice configurations.
Discover the ten most frequent causes of sluggish PostgreSQL queries—from missing or inefficient indexes and poor query design to lack of vacuuming, table bloat, wrong data types, excessive sequential scans, deadlocks, missing connection pooling, overused ORDER BY/DISTINCT, and misconfigured settings—and learn concrete steps to resolve each issue.
The article examines why PostgreSQL tables often retain large disk footprints after massive inserts, updates, and deletions, showing experiments on PG 13/PG 14, analyzing factors such as long‑running transactions, replication slots, uncommitted transactions, fillfactor settings, and the old_snapshot_threshold bug, and offering practical diagnostics and mitigation steps.
The article examines why openGauss 2.0/3.0 cannot use Index‑Only Scan for covering indexes, explores the role of the visibility map and VACUUM, compares behavior with PostgreSQL 11, and shows how the USTORE storage engine can finally enable true covering indexes.
This article explains the purpose and mechanics of PostgreSQL's VACUUM and AUTOVACUUM processes, demonstrates practical examples of table bloat, transaction ID handling, and provides step‑by‑step guidance for precise triggering and performance optimization of vacuum operations.
This article explains the cause of PostgreSQL "database is not accepting commands to avoid wraparound data loss" errors, details the XID allocation and wraparound mechanisms, and provides manual and automated vacuum‑freeze solutions with concrete SQL commands and scripts.
This article explains how enterprises can dramatically reduce cloud‑native data‑warehouse expenses by understanding ADB PG/Greenplum architecture, applying disk‑reservation and lock‑write safeguards, and implementing practical optimizations such as table compression, hot‑cold tiering, vacuuming, redundant‑index cleanup, replication conversion, and isolated temporary‑table spaces.
PostgreSQL uses Multi-Version Concurrency Control (MVCC) to ensure reads never block writes and vice versa, assigning transaction IDs to rows, managing visibility with xmin/xmax, supporting isolation levels, and requiring periodic VACUUM to clean dead rows and handle XID wraparound.
