Azpg schema design

Schema design on Azure Database for PostgreSQL Flexible Server

Model data correctly the first time: pick the right types, define keys and constraints that make illegal states unrepresentable, and apply DDL safely. "PostgreSQL can be used for everything" — so lean on the type system, constraints, and JSONB before reaching for another store.

This skill is read-write. Inspecting an existing schema is safe and runs automatically; anything that mutates structure (CREATE/ALTER/DROP TABLE, adding constraints, type changes) is a guarded write — present the DDL as a plan, get explicit confirmation, and apply it transaction-wrapped via scripts/apply_migration.sh (--dry-run capable) rather than free-handing schema changes on a live database. See Safety protocol.

Core DDL here is standard PostgreSQL. Flexible-Server-specific concerns (ownership under azure_pg_admin, storage/IOPS impact of rewrites, connection limits that shape the model) are in azure-constraints.md. Deep type/constraint guidance is in reference.md.

Live instance context (dynamic injection)

Read the target database's real shape before proposing a model, so advice fits what exists. PGCONN is a libpq connection string. These are read-only.

• Postgres major version (governs available syntax — e.g. MERGE needs 15+): !psql "$PGCONN" -tAc "SHOW server_version;" 2>/dev/null || echo "(unknown — ask the user for connection details)"
• Existing tables and approximate row counts (top by volume): !psql "$PGCONN" -tAc "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 15;" 2>/dev/null || echo "(could not connect)"
• Extensions that unlock extra types (citext, hstore, uuid-ossp, postgis): !psql "$PGCONN" -tAc "SELECT extname FROM pg_extension ORDER BY extname;" 2>/dev/null || echo "(unknown)"

If injection is unavailable, run scripts/inspect_schema.sql and read its output. Never assume a table, column, or extension exists — verify.

When to use this skill

Trigger on: "design a database/schema for …", "how should I store …", "what column type for …", "should this be one table or many", "add a foreign key / constraint / unique index", "model a many-to-many", "should I use JSONB or columns", "review my schema", or any request that produces CREATE TABLE / ALTER TABLE. For partitioning a large table, hand off to partitioning-strategy; for index choice on an existing schema, hand off to azpg-index-selection; for vector columns, hand off to azpg-pgvector-rag.

Decision flow

Steps 1–2 are read/inspect (safe); steps 3–4 are write/mutate (guarded).

1. Inspect what exists (section above) and clarify the domain: entities, relationships, cardinalities, access patterns, expected volume/growth.
2. Design on paper first. Choose types (reference.md), define keys and constraints, decide normalised vs. JSONB per attribute (reference.md), and name things consistently (naming).
3. Generate DDL and review it as a plan — including the constraint and index implications and whether any change forces a table rewrite.
4. Apply (guarded write) via scripts/apply_migration.sh, transaction-wrapped, after a --dry-run.

Naming conventions

Consistency beats any particular style. Defaults that age well on Postgres:

• snake_case for every identifier; avoid quoted mixed-case names (they force quoting forever).
• Singular or plural table names — pick one and hold it. Plural (orders) is common.
• Primary key id (or <entity>_id if you prefer explicit FKs to read order_id = orders.order_id).
• Foreign keys <referenced_table_singular>_id (e.g. customer_id).
• Constraints named for grep-ability: pk_orders, fk_orders_customer, uq_users_email, ck_orders_total_nonneg. Named constraints make later ALTER TABLE ... DROP CONSTRAINT unambiguous.
• Reserve suffixes: _at for timestamptz, _on for date, is_/has_ for booleans.

Read / inspect steps (safe · auto)

These never mutate state.

• Full schema report — scripts/inspect_schema.sql lists tables, columns + types, primary/foreign keys, unique and check constraints, and flags common smells (un-indexed foreign keys, text PKs, timestamp without time zone).

• Check a would-be constraint holds before adding it (safe — read-only):

  -- Will "ALTER TABLE ... ADD CONSTRAINT ... CHECK (total >= 0)" succeed?
  SELECT count(*) AS violations FROM orders WHERE NOT (total >= 0);
  

• See whether a type change will rewrite the table — a rewrite locks and costs IOPS. ALTER TYPE that is binary-coercible (e.g. varchar(50)→text) avoids a rewrite; most others do not. Confirm on a copy first.

Write / mutate steps (explicit · guarded)

Do not apply DDL without presenting it as a plan and getting explicit confirmation. On Flexible Server there is no superuser; objects are owned by the connecting role and elevated actions use azure_pg_admin. Prefer scripts/apply_migration.sh — it wraps the DDL in a single transaction (so a failure rolls back cleanly) and prints the statements under --dry-run.

Design principles that go into the DDL

• Make illegal states unrepresentable. NOT NULL, CHECK, UNIQUE, and FOREIGN KEY are cheaper than application bugs. Add them at design time.
• Every foreign key needs its own index on the referencing column — Postgres does not create it automatically, and its absence makes the parent's DELETE/UPDATE scan the child.
• Prefer timestamptz over timestamp, text over varchar(n) unless a real length limit exists, and numeric for money (never float).
• Identity over serial. Use GENERATED ALWAYS AS IDENTITY for surrogate keys on new tables; serial is legacy.
• Natural vs surrogate keys: a surrogate id plus a UNIQUE on the natural key gives you stable FKs and enforced business identity.

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_customers_email UNIQUE (email)
);

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),
  total        numeric(12,2) NOT NULL,
  status       text NOT NULL DEFAULT 'pending',
  placed_at    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_orders_total_nonneg CHECK (total >= 0),
  CONSTRAINT ck_orders_status CHECK (status IN ('pending','paid','shipped','cancelled'))
);

-- FKs are NOT auto-indexed — add it or pay for it on every parent delete.
CREATE INDEX ix_orders_customer_id ON orders (customer_id);

Safer patterns for changes on populated tables

• Adding a NOT NULL column: add it nullable with a default, backfill, then SET NOT NULL — avoids a long exclusive lock on large tables.

• Adding a FK or CHECK to a big table: add it NOT VALID, then VALIDATE CONSTRAINT in a separate step (validation takes a weaker lock).

  ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
  ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
  

• New index on a live table: CREATE INDEX CONCURRENTLY (cannot run in a transaction block — the migration script has a --concurrently path for this).

More patterns and the full type-selection guide: reference.md. A worked model is in examples/ecommerce_schema.md.

Safety protocol

1. Inspect first. Read the live schema and volumes before proposing changes.
2. Plan → confirm → execute for every DDL statement. Call out any change that forces a table rewrite or takes an ACCESS EXCLUSIVE lock, and its blast radius (row count, estimated duration, IOPS).
3. Use apply_migration.sh, not free-hand DDL. Run --dry-run first and show the statements. It wraps everything in one transaction unless --concurrently is set (for online index builds, which cannot be transactional).
4. Precheck backups before destructive changes (DROP, type rewrite) — Flexible Server provides automated backups / PITR; confirm a recent restore point. See azure-constraints.md.
5. Prefer non-blocking sequences (NOT VALID + VALIDATE, add-nullable + backfill + SET NOT NULL) on large tables to avoid long locks.
6. Never guess ownership. If a change fails on privileges, check the object owner and azure_pg_admin membership rather than escalating blindly.

Bundled files

• reference.md — deep reference: type-selection table, JSONB-vs-columns, keys & constraints, generated/identity columns, enums vs check vs lookup table, normalization vs denormalization, safe-change recipes.
• azure-constraints.md — Flexible Server specifics: no-superuser ownership model, storage/IOPS and rewrites, connection limits and how they shape the model, extension-backed types on the allow-list.
• scripts/ — one read-only inspector (inspect_schema.sql) and one guarded, --dry-run-capable migration applier (apply_migration.sh).
• examples/ecommerce_schema.md — a worked, fully-constrained model with commentary.