Neki

# Pre-Sharding PostgreSQL Best Practices

This guide helps prepare a Postgres schema for future horizontal sharding with minimal rework.

## Shard Key Design

Choose a **shard key** now, even if you're not sharding yet. It should be present on every tenant/user-scoped table, included in every frequent query's WHERE clause, high cardinality, and evenly distributed. Prefer an immutable key — changing it later requires data migration. Common choices: `tenant_id`, `org_id`, `user_id`, `account_id`.

Use real workload data to choose: favor a key that keeps your hottest queries single-shard.

**IDs:** UUIDs (or UUIDv7) work well for globally unique IDs without coordination; per-shard sequences are fine for the secondary column in composite primary keys.

## Primary Keys

A single-column PK is fine when it functions as the natural shard key (e.g., `user_id` on a `users` table). For other tables, use a composite PK with the shard key leading so lookups stay shard-local. Avoid globally-coordinated sequences across shards.

```sql
-- good: single-column PK that is the shard key
CREATE TABLE users (user_id BIGINT PRIMARY KEY, ...);

-- good: composite PK with shard key leading on a child table
CREATE TABLE orders (
  user_id BIGINT NOT NULL,
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  PRIMARY KEY (user_id, id)
);

-- incorrect: shard key not leading in composite PK
PRIMARY KEY (id, user_id)
```

## Co-located Data

Tables frequently joined must share the same shard key so joins stay shard-local. Always include the shard key in join conditions. Use consistent column types for the shard key across co-located tables (e.g., don't mix `int` and `bigint` for the same logical key).

```sql
-- correct: shard-local join
SELECT o.id, oi.product_id FROM orders o
JOIN order_items oi ON oi.tenant_id = o.tenant_id AND oi.order_id = o.id
WHERE o.tenant_id = $1;
```

## Reference Tables

Small, rarely-changing lookup tables (countries, currencies, feature flags) don't need a shard key — they get replicated across shards. Characteristics: typically small (e.g., well under 100K rows), rarely written, no tenant scoping, broadly joined.

## Query Patterns

Every query on sharded tables must include the shard key. Without it, the query becomes a scatter-gather across all shards.

```sql
-- correct: routed to single shard
SELECT * FROM orders WHERE tenant_id = $1 AND status = 'pending';

-- incorrect: hits all shards
SELECT * FROM orders WHERE status = 'pending';
```

For lookups by a non-shard column, maintain a mapping table. Ensure mapping consistency with backfill/repair jobs and miss-rate monitoring.

## Indexes

Lead indexes with the shard key. Scope unique constraints to include it.

```sql
-- correct
CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status, created_at);
ALTER TABLE orders ADD CONSTRAINT uq_order_number UNIQUE (tenant_id, order_number);

-- incorrect: index or unique constraint without shard key
CREATE INDEX idx_orders_status ON orders (status, created_at);
ALTER TABLE orders ADD CONSTRAINT uq_order_number UNIQUE (order_number);
```

## Foreign Keys

Cross-shard FKs are challenging to support in sharded systems. FKs within the same shard key (co-located data) may be supported depending on the sharding implementation. Cross-shard-key FKs must move to application-level enforcement before sharding. Some systems require all FKs to be disabled before sharding.

## Transactions

Keep transactions within a single shard key value. Cross-shard transactions typically require 2PC or similar distributed coordination and are significantly slower.

## Aggregations

Global aggregations (`COUNT(*)`, `SUM()` across all shards) become expensive. Scope aggregations to the shard key, or ma