August 2026 Summaries

A single uncommitted database transaction can cause widespread downtime when it holds a lock that blocks a schema migration, which then queues all subsequent queries behind it; this behavior can occur in both MySQL and PostgreSQL without a deadlock or automatic timeout. The example describes an application exception leaving a PostgreSQL transaction open after a read query, preventing an ALTER TABLE operation from obtaining its required exclusive lock and causing later queries against the table to stall. Setting PostgreSQL’s idle_in_transaction_session_timeout can help prevent such incidents by closing inactive transactions. To simplify diagnosis and recovery, PlanetScale has introduced dashboard and CLI tools for viewing active MySQL and PostgreSQL connections, identifying blocked queries, inspecting connection details, and canceling queries, terminating transactions, or dropping connections, including through reserved administrative capacity when normal connection slots are exhausted.

Large PostgreSQL tables, whether large by row count, width, or oversized values, can create escalating operational problems including delayed vacuuming, table bloat, slow queries, connection exhaustion, replica lag, expensive backups, excessive indexing costs, and outages such as cascade deletes generating enough WAL to overwhelm a primary and its replicas. The discussion compares partitioning, vertical scaling, and sharding as responses: partitioning can reduce vacuum thresholds, isolate TOAST storage, allow per-partition tuning, and make maintenance tasks more manageable, while additional hardware may improve throughput but generally does not address underlying architectural limits or data size. PostgreSQL’s cluster-wide transaction ID and snapshot behavior, fixed page and TOAST constraints, and connection-per-process model make very large single-cluster tables especially difficult to operate. Sharding distributes data, queries, WAL generation, caching, maintenance, backups, and hard limits across independent database clusters, containing failures and reducing the impact of long-running operations, though it requires careful selection of a shard key and may make cross-shard queries more costly.

Database sharding emerged as a way to distribute workloads once a single database server could no longer meet demand, with the term possibly originating in Ultima Online’s fictional “shards” of a world. MySQL gained an early advantage through the popularity of the LAMP stack and large-scale implementations at companies such as Facebook and YouTube, whose routing project evolved into Vitess. Postgres users instead initially relied on specialized in-house systems, including Skype’s function-based PL/Proxy and Instagram’s application-managed logical shards, before Citus introduced a more broadly available extension-based coordinator-and-worker model. Later proxy systems such as PgDog and managed distributed, Postgres-compatible databases including Spanner, CockroachDB, and Yugabyte offered differing approaches, but the account identifies trade-offs involving manual operations, routing bottlenecks, limited native Postgres behavior, unpredictable data placement, or reduced operational control. Neki is presented as a newer platform intended to combine explicit, operator-controlled sharding with actual Postgres clusters, horizontally scalable routers, a centralized topology definition, independent shard sizing, and managed functions such as backups, recovery, health monitoring, pooling, and schema changes.

Postgres connection pools can become “poisoned” when session-level settings or unfinished transaction state leak through PgBouncer’s transaction-mode connections and affect later clients that reuse them. A common example is setting a session to read-only with commands such as `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY` or `default_transaction_read_only`, which can cause subsequent write requests to fail with PostgreSQL error 25006, even though the database itself is not read-only. These failures should first be distinguished from writes mistakenly sent to replicas or clusters placed in read-only mode for operational reasons. Restoring affected connections generally requires resetting their state with `DISCARD ALL` or terminating suspect sessions, while the lasting solution is to remove session-scoped configuration from application code, ensure transactions reliably roll back after failures or timeouts, and route read traffic to replicas where possible. PlanetScale recommends using its query insights, CLI, and MCP server to identify problematic queries and code paths, with ORM replica-routing features potentially simplifying prevention efforts.

Neki, PlanetScale’s PostgreSQL-native sharding architecture inspired by lessons from Vitess, uses a data topology to map logical database tables onto physical shards and route queries without requiring applications to track data locations. Defined as JSON, a topology combines shard indexes, which convert shard-key values through hash, modulo, or range strategies; shard groups, which assign routing-key ranges to physical shards and support colocating related tables; and database bindings that apply shard-group defaults across PostgreSQL databases, schemas, and tables. Physical shards, each comprising a PostgreSQL primary and replicas, are provisioned separately and referenced through stable IDs, while an authoritative single-shard group stores database-wide metadata such as schemas and sequences. In an example store database, customer IDs are transformed into routing keys and directed to one of two shards according to configured ranges. The topology acts as a live routing configuration that can be updated during resharding, table moves, and imports while applications continue operating.

PostgreSQL read replicas replay the primary’s Write-Ahead Log (WAL) and depend on RUNNING_XACTS records to build consistent MVCC snapshots before accepting read-only connections. A transaction that creates more than the usual 64 cached active subtransactions, through excessive SAVEPOINT use or PL/pgSQL exception blocks, overflows its subtransaction cache, forcing visibility checks to consult the disk-backed pg_subtrans structure. Because any new snapshot can inherit this overflow state, one transaction can impose SLRU lock contention and expensive lookups on unrelated queries throughout the cluster, with a benchmark showing throughput falling from roughly 7,200 to about 160 TPS until the transaction ends. Overflow also affects newly created replicas because their RUNNING_XACTS records may omit active subtransaction IDs; since pg_subtrans changes are not WAL-logged, replicas cannot reconstruct the missing information and remain unable to enable hot standby until a complete record arrives, relevant transactions finish, or a checkpoint establishes a clean state. PostgreSQL 18 offers no built-in prevention mechanism, so recommended mitigations include keeping transactions short, applying transaction timeouts, monitoring backend subtransaction overflow and pg_subtrans activity, and, potentially, increasing the compiled cache limit or adopting future CSN-based snapshot designs.

A production MySQL outage illustrated how excessive database concurrency can reduce throughput: a batch transaction held locks for fifteen minutes, while thousands of retried snapshot reads accumulated, forcing InnoDB to traverse expanding row-version histories, exhaust buffer-pool capacity, and spread failures beyond the affected rows. The problem was amplified after migration from Cloud SQL’s thread-pool queuing model to Vitess configuration that allowed up to 10,000 open transactions and returned pool-full errors, encouraging application retries rather than applying backpressure. Drawing on Little’s Law and the Universal Scalability Law, the account explains that concurrency improves throughput only until contention and coordination costs make requests mutually slower, potentially causing “retrograde scaling.” Reducing the Vitess transaction-pool limit to roughly 1,000 and queueing requests with bounded waits restored controlled backpressure, allowing later traffic bursts to sustain about 60,000 queries per second with no disruption and fewer than 200 statements executing in MySQL. The approach is particularly relevant to workloads with hot rows, long transactions, locking, and shared state, and similar transaction-pooling strategies can also help PostgreSQL systems.