SQL and data systems: schemas, indexing and partitioning in Postgres

Generated by Claude through GigAI 0.1.11.10 on 2026-10-11.

Concept: SQL · also covers Data systems (schemas, indexing, partitioning), Storage systems · Step 8 of 34 in AI forward deployed engineer · version 1

Practice this

On this page

Almost every deployment ends up reading from or writing to a relational database, and Postgres is the one most often named. The work has two halves: designing schemas, indexes and partitions for the systems you build, and reading a customer's existing, often undocumented, schema well enough to map it to business concepts and query it without hurting production.

Read more

How it is done in practice

On a new engagement the first database task is usually discovery: a read-only role, a pass through the catalog, column profiling, and a mapping document reviewed with the customer's domain expert. For systems the team builds, schemas are managed with a migration tool in the application repository, constraints carry the invariants, and indexes are added after looking at real query plans from slow query logs or pg_stat_statements. Large event or log tables are range partitioned by time from the start so retention is a partition drop. Changes to big production tables are rehearsed on a copy and applied with concurrent index builds and short lock timeouts.

Technologies

PostgreSQLpsqlpg_stat_statementspgvectorAlembicFlywayPrismaDrizzle ORMPgBouncerAmazon RDS

Diagram

flowchart TB
  Q[Query filtered by tenant and time range] --> PL[Planner]
  PL -->|partition pruning| P2[events_2026_02]
  PL -.->|skipped| P1[events_2026_01]
  PL -.->|skipped| P3[events_2026_03]
  P2 --> IX[B-tree index on tenant_id, occurred_at]
  IX --> R[Matching rows]
flowchart TB
  Q[Query filtered by tenant and time range] --> PL[Planner]
  PL -->|partition pruning| P2[events_2026_02]
  PL -.->|skipped| P1[events_2026_01]
  PL -.->|skipped| P3[events_2026_03]
  P2 --> IX[B-tree index on tenant_id, occurred_at]
  IX --> R[Matching rows]

Code

sql
CREATE TABLE events (
  tenant_id   bigint      NOT NULL,
  occurred_at timestamptz NOT NULL,
  kind        text        NOT NULL CHECK (kind <> ''),
  payload     jsonb       NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE INDEX ON events (tenant_id, occurred_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT kind, count(*)
FROM events
WHERE tenant_id = 42
  AND occurred_at >= '2026-02-10' AND occurred_at < '2026-02-11'
GROUP BY kind;
sql
-- Discover declared relationships in an unfamiliar schema
SELECT tc.table_name, kcu.column_name,
       ccu.table_name AS references_table,
       ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;

Subtopics

Schema design and constraints in Postgres

Let the database enforce the rules

A schema is the most durable documentation a system has. Model entities as tables with a primary key, express relationships with foreign keys, and encode invariants as NOT NULL, UNIQUE and CHECK constraints so bad data is rejected at write time instead of discovered in a report. Choose types precisely: timestamptz for instants, numeric for money, uuid or bigint identity columns for keys, and jsonb for semi-structured payloads that do not justify columns yet. Normalize by default and denormalize deliberately for read paths you have measured. Schema changes go through versioned migrations, and on large tables you need to know which changes take locks that block writes.

Indexing: types, column order and trade-offs

How an index helps

An index is a separate ordered structure that lets the database find rows without scanning the table. The default in Postgres is the B-tree, which serves equality and range conditions and can return rows already sorted. In a multicolumn B-tree the column order matters: the index is most useful when the query constrains the leading columns, so put equality columns first and the range or sort column after them. Other index types fit other data: GIN for jsonb containment, arrays and full-text search, GiST for geometric and range types, and BRIN for very large tables whose values correlate with physical order, such as append-only timestamps.

Refinements and costs

A partial index covers only rows matching a predicate, which keeps it small when queries always filter the same way, for example on unprocessed rows. An expression index stores the result of a function such as lower of an email, and is used only when the query uses the same expression. Covering indexes add non-key columns with INCLUDE so a query can be answered by an index-only scan. Every index slows writes and takes space, so add them in response to real queries. On a busy production table, build with CREATE INDEX CONCURRENTLY to avoid blocking writes.

Reading query plans with EXPLAIN

From slow query to cause

EXPLAIN shows the plan the planner chose as a tree of nodes, each with an estimated cost and row count. EXPLAIN ANALYZE actually runs the query and adds real timings and row counts, and the BUFFERS option shows how many pages were read. Read from the innermost nodes outward. The key signals are a sequential scan on a large table where few rows are needed, a large gap between estimated and actual rows, which points to stale or missing statistics, and a nested loop executed many thousands of times. Remember that ANALYZE executes the statement, so wrap data-modifying queries in a transaction and roll back.

Table partitioning

Declarative partitioning and when it pays off

Postgres can split one logical table into child tables by range, list or hash on a partition key. The planner uses partition pruning to skip children that cannot match the query's conditions, so queries must filter on the key to benefit. The larger operational win is data lifecycle: dropping or detaching a month-old partition is a fast metadata change, while deleting the same rows is slow and leaves bloat. The constraints are real: a primary key or unique constraint must include the partition key columns, too many partitions increases planning time, and a poor key choice gives no pruning at all. Partition when a table is large and access is naturally bounded by time or tenant.

Mapping an undocumented customer schema to business concepts

A repeatable discovery method

Start with the catalog: information_schema and the pg_catalog tables list every table, column, type, constraint and index, and table statistics show which tables are large and actively written. Declared foreign keys give the reliable relationships; for the rest, infer candidates from column names and confirm by checking that values in one column actually exist in the other. Profile each important column for null rate, distinct count and value distribution, since a status column with eleven values tells you more than its name. Then sit with someone who uses the system, trace one real business record across tables, and write down the mapping as a glossary the customer signs off on.

Working safely on someone else's database

Ask for a read-only role scoped to the schemas you need, and prefer a replica or a snapshot over the primary. Set a statement timeout for your session so an accidental cross join cannot run for an hour. Sample with LIMIT while exploring, and check the plan before running anything over a large table. Record every assumption about meaning, such as which timestamp is the business date and how soft deletes are flagged, because those assumptions are where integrations and AI features silently go wrong.

Sources

Related lessons

Practice this
Full-stack customer applications with React and Next.jsShipping production code with Claude Code and other AI coding tools