PostgreSQL Schema and Indexing Fundamentals

What it is

Schema design defines valid durable states; indexing defines additional data structures the optimizer may use to reach those states efficiently. Constraints are correctness controls. Indexes are workload-dependent performance and access-path tools.

Why it matters

Application validation can be bypassed by races, scripts, imports, or another service. Database constraints close that gap. Poor indexes can waste storage and write throughput while still missing the actual filter and ordering pattern.

How it works

For a multicolumn B-tree, leading equality constraints and then the first range constraint usually determine how much of the index can bound the scan. PostgreSQL can apply later-column conditions, but they may not reduce the scanned portion in the same way.

Simple example

CREATE TABLE task (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id     bigint NOT NULL REFERENCES app_user(id),
    title        varchar(160) NOT NULL CHECK (length(trim(title)) > 0),
    status       varchar(20) NOT NULL CHECK (status IN ('OPEN', 'DONE', 'CANCELLED')),
    due_at       timestamptz,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX task_owner_status_created_idx
    ON task (owner_id, status, created_at DESC, id DESC);

Backend example

SELECT id, owner_id, title, status, created_at
FROM task
WHERE owner_id = :ownerId
  AND status = :status
  AND (created_at, id) < (:cursorCreatedAt, :cursorId)
ORDER BY created_at DESC, id DESC
LIMIT :pageSize;

The index begins with the equality predicates, then matches the descending pagination order and unique id tie-breaker. Verify with representative data and EXPLAIN (ANALYZE, BUFFERS) in a safe non-production environment; ANALYZE executes the statement.

Production example

1. 1

   Start from queries

   Collect the exact predicates, sort order, selected columns, frequency, and latency objective.

2. 2

   Inspect estimates

   Compare estimated and actual rows, scan type, loops, buffers, sorts, and join strategy.

3. 3

   Change one thing

   Add or revise a constraint/index/query and re-measure with representative distribution.

4. 4

   Account for writes

   Every index consumes space and must be updated by INSERT, UPDATE, DELETE, vacuum, backup, and replication work.

Common mistakes

• Relying only on application validation for uniqueness
• Adding an index for every column
• Using SELECT * in stable mapper contracts
• Assuming an index will always be chosen
• Running EXPLAIN ANALYZE for a write on production without a rollback plan
• Using offset pagination for unbounded deep pages

Best practices

• Name constraints and indexes clearly
• Prefer narrow stable keys and explicit foreign-key actions
• Keep statistics current and inspect actual row-count errors
• Use representative data volume and distribution
• Design online/backward-compatible migrations
• Test backup restore, not only backup creation

Trade-offs

Interview questions

• Why does a UNIQUE constraint solve a race that a pre-check cannot?
• How does column order affect a composite B-tree index?
• What evidence do you read in EXPLAIN ANALYZE?
• When would a sequential scan be correct?
• How does keyset pagination work with duplicate timestamps?

Hands-on task

1. 1

   Model

   Create users, roles, tasks, orders, and order items with named constraints and audit columns.

2. 2

   Load

   Generate data with realistic skew across owners and statuses.

3. 3

   Measure

   Compare no index, a poorly ordered index, and a query-aligned composite index using plans and timings.

4. 4

   Race

   Run two concurrent inserts for the same business key and confirm the constraint is the final arbiter.

Related topics

• MyBatis Mapper Workflow
• Spring Transactions
• Keyset Pagination
• Connection Pooling
• Concurrent Balance Updates

References

• PostgreSQL Global Development Group: PostgreSQL Constraints — https://www.postgresql.org/docs/18/ddl-constraints.html
• PostgreSQL Global Development Group: PostgreSQL Multicolumn Indexes — https://www.postgresql.org/docs/18/indexes-multicolumn.html
• PostgreSQL Global Development Group: Using EXPLAIN — https://www.postgresql.org/docs/18/using-explain.html
• PostgreSQL Global Development Group: Transaction Isolation — https://www.postgresql.org/docs/18/transaction-iso.html
• Oracle: Oracle Database SQL Language Reference 19c — https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/