TechPrufer
Get started
PlanetScale
PlanetScale logoPlanetScale · Feature trail

Neki Query Performance

32 posts · Jun 2022 – Oct 2026

What this trail covers

Neki, PlanetScale's sharded Postgres solution, continues to evolve its architecture. This post details optimizations in query processing by minimizing unnecessary data decoding and serialization. The router now selectively decodes only required columns and parameters, and preserves original PostgreSQL wire formats where possible. This lazy and composable approach reduces network hops and processing overhead, especially for point lookups, queries with LIMIT, and cross-shard queries where full row decoding is not required. For sorting operations, specific sort keys are decoded as needed. The system also leverages protocol-level optimizations for bind parameters and column frames, and maintains original formats for text/binary data to further reduce processing.

All PlanetScale trails
  1. Designing Neki for performanceThis post details performance optimizations in Neki's query router. Key contributions include: 1. Lazy decoding of PostgreSQL response messages, avoiding full row/column decoding when not necessary for routing or client presentation. 2. Preservation of original PostgreSQL wire formats for `DataRow` and `Bind` messages to minimize serialization/deserialization overhead. 3. Selective decoding of only the sharding key (`account_id`) for point lookups, leaving other columns as raw bytes. 4. Handling of `LIMIT` clauses by first counting rows and then creating a pointer slice to the relevant rows without moving data. 5. Decoding of sort keys (`created_at`) only when sorting is required for global ordering across shards. 6. Efficient handling of bind parameters by caching and reusing original protocol frames. 7. Optimization for cross-shard queries where full row decoding is not needed by streaming and combining `CommandComplete` and `DataRow` messages.
  2. Handling hot shardsThis post details how PlanetScale's Neki (built on Vitess) handles 'hot shards' by allowing users to adjust sharding strategies, scale individual shards, or isolate large tenants. It explains the concept of hot shards arising from uneven data distribution and the importance of a declarative data topology for reasoning about data placement. The post outlines three mitigation strategies: scaling up individual shards, isolating large tenants by adjusting key ranges, and resharding specific tables to a more appropriate shard key. It highlights the zero-downtime resharding capabilities of Neki, drawing parallels to Slack's experience with Vitess.
  3. The architecture of NekiThis post provides a deep dive into the architecture of Neki, PlanetScale's sharded Postgres system. It details the roles and interactions of key components: PostgresManager for managing Postgres instances, Sidecar for connection pooling and health reporting, Shards for data partitioning with authoritative OID management, Admin for high availability and durability policy enforcement, Operator for Kubernetes lifecycle management, Router for client interaction and distributed query execution, and Replicator for data movement. It also explains the use of etcd for the Data Topology.
  4. 118 million queries per second on NekiThis post details a benchmark run of Neki, achieving 118.5 million QPS across 512 shards and 1.22 PiB of data. It highlights linear scalability, with each shard sustaining over 200k QPS and reaching 231k QPS with headroom. The benchmark involved read-only, single-shard point selects with no writes, joins, or cross-shard queries. Key metrics include p99 latency of 6.06ms at the router and 13.95ms at the client, 67 errors per second (1 in 1.8 million queries), 15.8M read IOPS, and over 2 Tb/s network throughput. The run used primary-only shards without replicas and did not involve failovers during the measured window.
  5. Introducing NekiThis post introduces Neki, a sharded Postgres offering from PlanetScale. It details the architecture including Neki routers that speak the Postgres wire protocol, sharding and shard groups with real Postgres instances, sidecar connection pooling, and a control plane for managing operations like schema changes, upgrades, failovers, imports, and resharding. It also explains the data topology for mapping logical tables to physical shards and highlights that Neki can be run unsharded initially and resharded later. The post positions Neki as a solution to the scaling limitations of single-machine Postgres instances.
  7. The lifecycle of a sharded Postgres queryThis post details the query lifecycle through the Neki router, including authentication (SCRAM-SHA-256 in Go), protocol handling (Simple vs. Extended), parsing into an AST (18,000 lines of Go), and the sharded query planner's role in optimizing distributed queries across shards. It highlights the challenges of sharding, such as non-co-located related data, and the planner's 'laziness' in minimizing work by caching plans and understanding schema and data distribution rules.
  8. What is a Neki router?This post introduces the Neki router, a component that sits between applications and sharded Postgres instances. It explains how the router handles client connections using a fleet of stateless processes, overcoming Postgres's process-per-connection bottleneck. The post details the two-plan system: the Neki plan for shard selection and result coordination, and the Postgres plan for shard-local execution. It illustrates single-shard routing with `EXPLAIN (NEKI_PLAN)` and scatter-gather queries with `EXPLAIN (NEKI_PLAN, NEKI_PG_PLAN)`, highlighting operators like `Route [EqualUnique]`, `Route [Scatter]`, `Limit`, and `Collapse`. The interaction with Postgres sidecars via gRPC is also described.
  9. Problems with large tables in PostgresThis post details how sharding (horizontal scaling) resolves common problems associated with large tables in PostgreSQL. It explains how partitioning and vertical scaling offer partial solutions but sharding provides a more comprehensive fix by distributing data across smaller, isolated database clusters. Specific issues addressed include: slow and late vacuuming (due to per-table operation and trigger thresholds), wasted time on incomplete repacking (where vacuum can't reclaim space due to ongoing transactions), slow queries holding connections longer (due to sequential scans, sorts, and heavy joins on large tables), and slower backups and recovery (due to large data volumes and long-running transactions). The post contrasts sharding with partitioning and vertical scaling for each problem, highlighting how isolated resources and smaller data sets on each shard mitigate these issues.
  10. The history of Postgres shardingThis post provides a historical overview of database sharding, tracing its origins from video games to modern solutions like Vitess and Citus. It details early approaches such as application-level sharding (Facebook, YouTube), proxy-based solutions (PL/Proxy), and Postgres extensions (Citus). The post also discusses distributed SQL systems (Spanner, CockroachDB, Yugabyte) and their trade-offs compared to native Postgres sharding. It highlights the challenges and lessons learned throughout this evolution, which inform the development of new solutions like Neki.
  11. What is a data topology?This post introduces and details the concept of a 'data topology' within the Neki system. It explains that a data topology is a JSON configuration used to map logical PostgreSQL tables to physical shards, enabling query routing. The post breaks down the three core components of a data topology: shard indexes (specifying how to route rows based on shard keys using hash, modulo, or range strategies), shard groups (defining sets of physical shards and routing key ranges), and databases (binding tables to shard groups following PostgreSQL hierarchy). It provides an example of a data topology for a `store` database partitioning around `customer_id` and explains how these components work together to inform the Neki router.
  12. The dangers of Postgres subtransactionsThis post details how a large number of subtransactions within a single PostgreSQL transaction can lead to cluster-wide performance degradation and prevent read replicas from accepting queries. It explains PostgreSQL's WAL replay mechanism for replicas, how transaction states are tracked via RUNNING_XACTS records, and the limitations of the subtransaction cache (PGPROC_MAX_CACHED_SUBXIDS). When the cache overflows, PostgreSQL falls back to slower pg_subtrans lookups, impacting query performance. The post provides examples of how to trigger this overflow using explicit SAVEPOINTs or PL/pgSQL exception blocks and includes a benchmark demonstrating the TPS drop. It emphasizes that a single overflowing transaction can affect unrelated queries across the cluster.
  13. Concurrency vs. Throughput: why more parallelism can make databases slowerThis post details a critical incident where a production MySQL database experienced a 16-minute meltdown due to excessive concurrency and a lack of effective backpressure management. It explains how a long-running transaction, combined with a high number of concurrent read queries, overwhelmed the storage engine by forcing it to reconstruct increasingly long version histories for each read. The post contrasts the behavior of thread-pool-based connection management (Cloud SQL) with transaction-pool-based management (Vitess) and highlights how the migration to PlanetScale exacerbated the issue due to application stack unfamiliarity with pool-full errors. The solution involved reducing the Vitess transaction pool size to mimic the old thread pool's behavior and implementing a queuing mechanism with a longer timeout for requests arriving at a full pool. This change successfully absorbed high transaction volumes without performance degradation or errors, demonstrating the effectiveness of controlled backpressure in preventing system instability.
  14. Massively parallel Postgres backupsThis post details the engineering approach to achieving massively parallel Postgres backups for sharded databases (Neki). It describes spinning up dedicated backup nodes per shard, restoring from previous backups stored in object storage, and replaying WAL segments (hybrid approach using S3 and direct streaming from primary for recent changes) to catch up to the current state. The initial backup process using `pg_basebackup` is also outlined. The post quantifies the performance benefits of parallelism for large databases, demonstrating how it reduces backup times significantly and enables more frequent backups. It also explains how this backup system is leveraged for database resizing and node replacement operations.
  15. Making 768 servers look like 1This post explains the necessity of database sharding for scaling relational databases beyond single-node limitations, detailing bottlenecks like write limitations, data capacity, and backup times. It introduces the concept of a proxy layer (router) as the critical component that makes a sharded database appear as a single entity to applications. The post elaborates on how routers handle sharding strategies, query parsing, planning, and aggregation for multi-shard queries, using Neki (Postgres) and Vitess (MySQL) as examples. It also touches upon the use of JSON configuration files (VSchema/data topology) to define sharding rules and the role of Network Load Balancers in distributing traffic across multiple proxy instances at scale.
  16. Problem solving with PlanetScale InsightsThis post details how to use PlanetScale Insights to diagnose and resolve database performance issues, specifically focusing on query latency in PlanetScale Postgres. It explains latency percentiles (p50, p95, p99, p99.9, Max), how to interpret the query list and its various metrics (like rows read vs. returned), and advanced search syntaxes. It also covers using other graphs (Queries, Rows read, Rows written) for context. The post introduces the 'Summarize query' LLM feature and discusses actionable steps like adding indexes (including GIN trigram indexes for wildcard searches) and rethinking schema/application design. Finally, it introduces the MCP server and agentic workflows for automated slow query detection and resolution, providing a sample prompt and configuration details.
  17. AI-Powered Postgres index suggestionsThis post introduces AI-powered index suggestions for PostgreSQL databases within PlanetScale Insights. It details the process of using LLMs to identify queries that would benefit from new indexes, filtering candidate queries based on resource usage and schema relevance. The post also describes a validation process using the HypoPG extension to measure the estimated performance improvement of suggested indexes before recommending them to users.
  18. Announcing NekiThis post announces Neki, a new sharded Postgres system built by the Vitess team. It is architected from first principles to run demanding Postgres workloads and is not a fork of Vitess. It leverages Vitess's experience in running at extreme scale to make explicit sharding accessible for Postgres.
  19. Announcing PlanetScale for PostgresThis post announces the private preview of PlanetScale for Postgres, powered by a new architecture called Neki. Neki brings Vitess's sharding capabilities to Postgres, aiming to provide superior performance and reliability. Key technical details include: true high availability with automatic failovers, query buffering, connection pooling via a proprietary proxy layer (including PgBouncer), support for Postgres v17, online imports from Postgres v13+, automatic version updates without downtime, and leveraging PlanetScale Metal's locally-attached NVMe SSDs for performance and cost-effectiveness. The post explicitly states Neki is architected from first principles for Postgres, not a fork of Vitess.
  20. Database ShardingThis post introduces the fundamental concepts of database sharding as a solution for scaling databases to handle massive datasets and high query loads. It explains the architecture of sharded databases, including the role of proxy servers (VTGates) and the challenges of server overloading and proxy capacity. The post details two primary sharding strategies: range sharding, illustrating its drawbacks with examples of hot shards and uneven data distribution, and hash sharding, emphasizing its effectiveness in distributing data evenly, especially when using high-cardinality shard keys like user_id. It also briefly mentions other strategies like lookup sharding and custom functions supported by Vitess, and introduces the concept of cross-shard queries.
  21. Introducing sharding on PlanetScale with workflowsIntroduces a new 'Workflows' functionality that allows users to horizontally scale their databases by moving tables to a sharded keyspace directly from the PlanetScale dashboard. This workflow is similar to Vitess's `MoveTables` operation and enables zero-downtime traffic switching after data migration and replication. The post details the UI for configuring and executing these workflows, emphasizing the ease of transitioning from unsharded to sharded environments.
  22. Increase IOPS and throughput with shardingThis post explains how sharding can improve database IOPS and throughput by distributing data and compute across multiple primary instances. It details the concepts of IOPS and throughput, their measurement in AWS EBS, and how they relate to database workloads. The core contribution is a cost comparison between sharded and unsharded database configurations (PlanetScale, RDS, Aurora) for both small and large compute/storage requirements, demonstrating that sharding offers a more linear and cost-effective scaling path for high-demand workloads.
  23. Dealing with large tablesThis post details strategies for handling large tables in a Vitess-based MySQL environment. It outlines three options: vertical scaling (increasing compute/storage of a single server), vertical sharding (moving large tables to separate keyspaces/servers), and horizontal sharding (spreading rows of a single table across multiple servers based on a sharding key like user_id). It provides examples of Vitess commands for MoveTables and switchtraffic workflows, and discusses the trade-offs of different sharding keys for data locality and query performance.
  24. Sharding strategies: directory-based, range-based, and hash-basedThis post introduces and compares three sharding strategies: directory-based, range-based, and hash-based. It details the mechanics, pros, and cons of each, including potential issues like data distribution imbalance and hotspots. The post recommends hash-based sharding as the default due to its generally even distribution and ease of resharding, provided the shard key has high cardinality. It also mentions PlanetScale's built-in sharding options and the Vitess project.
  25. Achieving data consistency with the consistent lookup VindexThis post introduces the Consistent Lookup Vindex in Vitess, a mechanism to maintain data consistency between sharded user tables and their corresponding lookup Vindex tables without resorting to expensive 2PC for all DML operations. It details the three-connection transaction sequence (Pre, Main, Post) and the locking strategies employed during delete and insert operations to ensure that queries always return consistent results with respect to the user table, even if the Vindex tables temporarily hold stale data. The post also addresses update operations and the handling of duplicate key errors during inserts.
  26. Sharding for cost-effective database managementThis post introduces sharding as a technique to optimize database cluster performance and reduce infrastructure costs. It explains how sharding distributes data and query load across multiple database instances, leading to improved response times and more efficient resource utilization. The post emphasizes the cost-effectiveness of sharding by allowing for the use of smaller, less expensive database instances compared to a single large, monolithic database.
  27. Storing time series data in sharded MySQL to power Query InsightsThis post details the implementation of a scalable telemetry pipeline for PlanetScale's Query Insights tool. It describes how time series data, specifically query pattern-level aggregations and slow query events, is collected from VTGate instrumentation, sent to Kafka, and then processed by consumers to be stored in a sharded PlanetScale (MySQL/Vitess) database. Key technical contributions include: implementing instrumentation in Vitess to send aggregate summaries and slow query events to Kafka; using Kafka keys for deterministic sharding of data by database ID and query fingerprint; employing in-memory coalescing in Kafka consumers to reduce database writes; sharding the Insights database on customer database ID for scalability; using DDSketch for error-bounded quantile estimation of query latencies; and developing custom C++ MySQL functions to read/write DDSketch binary format and compute percentiles directly within MySQL.
  28. Query performance analysis with InsightsThis post introduces time-series metrics on a per-query pattern basis within PlanetScale Insights. It details how query literals are replaced with generic placeholders using Vitess's SQL parser to normalize queries for aggregation. The post provides a real-world example of identifying a slow `DELETE` query that became inefficient over time due to the lack of an index. It demonstrates how adding an index to the `minute` column significantly improved query latency, showcasing the effectiveness of the new Insights feature for performance troubleshooting.
  29. What is database sharding and how does it work?This post explains the concept of database sharding as a strategy for scaling out relational databases by partitioning data across multiple servers. It details the core components of sharding: choosing a sharding scheme (hash-based, range-based, directory-based), organizing infrastructure, creating a routing layer (often in the application layer), and planning/executing the migration. It also introduces Vitess as a mature open-source sharding framework for MySQL.
  30. Temporal workflows at scale: Part 2 — Sharding in productionThis post details how PlanetScale leverages Vitess for horizontal sharding of MySQL databases to achieve massive scalability, explaining the roles of VTGate, VSchema, and VTTablet. It provides a concrete example of configuring vSchema for a Temporal workload, demonstrating how to shard tables based on `shard_id` or `range_hash` using the `xxhash` Vindex and the `MoveTables` process for seamless traffic migration. The post highlights the importance of setting the History Shard Count in Temporal and showcases the performance benefits of this sharding strategy through production QPS and IOPS graphs during peak periods.
  31. Grouping and aggregations on VitessThis post details how Vitess's query planner was enhanced to push down grouping and aggregation operations under joins, inspired by the paper 'Orthogonal Optimization of Subqueries and Aggregation'. It explains the local and global aggregation strategy, the evalengine's role in mimicking MySQL expressions at the VTGate level, and provides a detailed example of planning and executing a query involving a join between sharded `order` and `order_line` tables. The contribution enables more complex queries to be efficiently processed across shards by optimizing the order of operations.