MySQL QA

Cold Comfort: When PostgreSQL Protects Your Data by Locking You Out PostgreSQL’s MVCC architecture is designed to preserve data integrity at all costs. But when autovacuum falls behind, that protection can come at a steep operational price: your data is still there — but you can’t write to it, and in some cases, you can’t even access it. Over the past three years, PostgreSQL has experienced multiple production-halting outages across major cloud providers and enterprise deployments. The root cause in every case? Vacuum lag. Three Confirmed Outages (2024–2025) AWS RDS PostgreSQL Wraparound Incident Date: December 2024 (updated February 2025) Impact: PostgreSQL entered read-only mode Cause: Transaction ID wraparound protection triggered due to autovacuum lag Recovery: Required emergency vacuuming and downtime Source: AWS Blog Google Cloud SQL PostgreSQL Lockouts Date: Throughout 2024 Impact: PostgreSQL refused new t...

If you're benchmarking PostgreSQL against MariaDB, you're not comparing apples to apples unless you introduce PgBouncer . Here's why. Connection Architecture PostgreSQL spawns a full process per client connection. That means every incoming connection forks a new backend, each with its own memory map, file descriptors, and kernel overhead. On a high-core host, this model hits system limits fast—either process count or scheduler overhead. To scale efficiently, PostgreSQL requires PgBouncer , a separate connection pooling proxy. It’s an external add-on—you have to install it, configure it, and monitor it independently. MariaDB , by contrast, uses a thread-per-connection model natively . No add-ons, no proxies. Thread pooling is built into the server and enabled out of the box. Threads are lightweight, share memory space, and scale efficiently. With proper stack tuning, MariaDB can handle tens of thousands of concurrent connections without breaking a sweat. PgBouncer...

Solving the Backlog Problem in MySQL Benchmarking In database scalability testing, Autobench3 (AB3) steps through thread counts to evaluate performance under increasing load—typically using values like 4, 8, 16, 32, 64, 128, 256...4096 and beyond. To improve stability and reproducibility, each thread count is executed multiple times (usually 3 iterations, but up to 7 if needed). Final results are averaged across iterations. The Backlog Problem One challenge with thread stepping is that MySQL may still be processing work after the client stops sending traffic . This backlog is especially common in write-heavy workloads, but even read tests can leave MySQL active briefly after disconnect. Initially, AB3 tried to address this by inserting sleep intervals between phases—during MySQLD startup, database load, and between iterations. But sleep was a guessing game. As thread counts increased, so did the time needed to clear the backlog, and fixed sleep durations weren’t reliable. ...

In the MariaDB Foundation’s deep dive, Manoj Vakeel (who leads database migration at MariaDB) breaks down the architectural DNA of PostgreSQL and MariaDB with clarity and precision. The discussion touches on MVCC housekeeping, including PostgreSQL’s vacuuming model versus MariaDB’s auto-purge strategy—critical for understanding how each handles transaction visibility and long-term performance. Key takeaways from the video: PostgreSQL uses a process-based model, offering strong isolation but requiring external pooling for scale. MariaDB uses a threaded architecture, enabling efficient high-concurrency handling out of the box. MVCC strategies differ: PostgreSQL relies on VACUUM and freezing, while MariaDB uses auto-purge. Replication models diverge: PostgreSQL favors logical decoding and WAL shipping; MariaDB supports GTID and Galera Cluster for synchronous multi-master setups. Licensing and ecosystem support vary: PostgreSQL under BSD, MariaDB under GPL. Th...