Digging Deeper on Partitioning Part 2: Key-Based Partitions

Last week, we covered time-based partitioning. This week, we're looking at key-based partitioning, which splits tables by a column like customer_id, tenant_id, or region. It's particularly effective in multi-tenant SaaS and geographically distributed architectures.

• Choose the right key: Partition on columns that appear most often in query filters. In SaaS apps, tenant_id is typical. For regional data, use region or country.
• Balance partition sizes: A good key evenly distributes rows across partitions. Avoid skew. If one customer holds 80% of the data, partitioning loses its value.
• Keep partition count reasonable: Dozens to low hundreds is fine. Thousands create metadata overhead and slow query planning.
• Align with access patterns: If most queries filter on a single tenant or region, key-based partitioning allows the planner to prune partitions efficiently.
• Index locally: Create indexes within each partition, for example (tenant_id, created_at) for tenant-scoped queries with time filters.
• Handle cross-partition queries: Global reporting often spans multiple tenants or regions. Use summary tables or caching to avoid scanning all partitions.

Key-based partitioning works best when your workload naturally shards by tenant or geography. Combined with caching, it keeps single-partition queries fast and scales reads and writes predictably. If caching is part of your approach, Readyset integrates seamlessly to accelerate repetitive, tenant-scoped queries without code or schema changes.

🔍 What We're Watching in the Database Ecosystem

Real-time Visualization for how LLMs work

Transformers, the self-attention-based architecture behind GPT, Llama, and Gemini, have become the foundation of modern AI, driving next-token prediction across text, vision, audio, and even scientific domains. This interactive explainer breaks down how they work at a fundamental level, providing useful context as database systems increasingly integrate with vector retrieval and model-driven workloads.

PostgreSQL: Parallel Apply Proposal

Amit Kapila has proposed extending PostgreSQL's parallel apply to support not only large streaming transactions but also frequent smaller ones. The design adds dependency tracking on replica identity, unique keys, and foreign keys, allowing independent transactions to be applied in parallel without causing deadlocks or constraint violations. This could significantly improve replication throughput in high-concurrency environments.

PostgreSQL 18 Released!

PostgreSQL 18 introduces a new asynchronous I/O subsystem with up to 3x faster reads, faster and less disruptive upgrades, and new query optimizations like skip scans and OR-indexing. It also adds features like virtual generated columns, UUIDv7, OAuth 2.0 authentication, and improvements to replication and observability.

In Case You Missed It

It's not broken, it's just that fast: Readyset QueryPilot Demo

QueryPilot automatically identifies and optimizes your most expensive queries with no manual tuning or configuration, so results are ready before you even notice the query ran. In this demo, Tanmay and Mike show how quickly WordPress queries scale with QueryPilot and how easy it is to get started.

Watch the short clip on LinkedIn. See the full demo on YouTube.