Design and review PostgreSQL database schemas by choosing suitable column types, keys, constraints, and indexes. It also guides decisions about large-table partitioning, flexible JSONB data, and schema changes on a live database without downtime.
Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
Review the instructions the agent will receive before you install.
The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload
patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,
and worked DDL examples are in references/details.md; open it when a section below points there.
BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.NUMERIC for exact decimal arithmetic).snake_case.UNIQUE NULLS NOT DISTINCT (...) (PG15+) to restrict to one NULL.NUMERIC(2,0) fails, unlike databases that silently truncate or round.CLUSTER is a one-off reorganization, not maintained on later inserts.BIGINT GENERATED ALWAYS AS IDENTITY; UUID for distributed or opaque IDs, generated with uuidv7() (PG18+) or gen_random_uuid().BIGINT unless storage is critical; DOUBLE PRECISION over REAL; NUMERIC(p,s) for money and exact decimals.TEXT, with CHECK (LENGTH(col) <= n) when a limit is needed; BYTEA for binary. Case-insensitive lookups: expression index on LOWER(col), or CITEXT when a constraint must be case-insensitive.TIMESTAMPTZ, DATE, INTERVAL. now() is transaction start; clock_timestamp() is wall clock.BOOLEAN NOT NULL unless tri-state is required.CREATE TYPE ... AS ENUM only for small, stable sets; evolving business values get TEXT + CHECK or a lookup table.references/details.md.| Avoid | Use instead |
|---|---|
timestamp (without time zone) | timestamptz |
char(n), varchar(n) | text (+ CHECK on length if needed) |
money | numeric |
timetz | timestamptz |
timestamptz(0) or any precision | timestamptz |
serial | generated always as identity |
ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Index the referencing column. Use DEFERRABLE INITIALLY DEFERRED for circular dependencies checked at commit.NULLS NOT DISTINCT (PG15+). Prefer NULLS NOT DISTINCT unless duplicate NULLs are wanted.NOT NULL: price NUMERIC NOT NULL CHECK (price > 0).EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) stops double-booking. Needs a GiST-capable type.=, <, >, BETWEEN, ORDER BY).WHERE a = ? AND b > ? uses (a,b); WHERE b = ? does not). Most selective columns first.CREATE INDEX ON tbl (id) INCLUDE (name, email) for index-only scans.CREATE INDEX ON tbl (user_id) WHERE status = 'active'.CREATE INDEX ON tbl (LOWER(email)); the query must use the same expression.PARTITION BY RANGE (created_at); TimescaleDB automates it with retention and compression), LIST for discrete values, HASH for even distribution without a natural key.CHECK constraints; declarative partitioning (PG10+) creates them for you.CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}',
theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
references/details.md holds the material this file only names:
TEMPORARY, UNLOGGED) and row-level security.pg_trgm, citext, timescaledb, postgis, pgvector, and more).jsonb_path_ops and extracted B-tree columns.No reviews yet. Tell others what you thought.
No login needed. Share how it worked for you, ask a question, or note where it did not run.
No comments yet. Be the first to write one.
Build and review interfaces against WCAG 2.2 accessibility guidelines. Covers keyboard navigation, screen readers that read interface content aloud, accessible forms, mobile support, and display preferences, helping identify and fix barriers to using a website or app.
Review AI-generated code for problems hidden behind successful runs. Check failure handling, resource cleanup, unusual inputs, nonexistent dependencies, and departures from project conventions to identify missing safeguards before merging or accepting the work as complete.
Build Apache Airflow workflows for data pipelines and scheduled jobs. The skill guides task dependencies, retries, waiting for conditions, testing, and deployment, with patterns for tasks that can be safely rerun.
Plan and carry out an AngularJS to Angular migration, from choosing a rewrite strategy to converting components, services, and routes. Includes a hybrid approach that runs both frameworks together while individual features are migrated.
Identify software protections that hinder inspection, such as debugger detection and obfuscation that makes code harder to understand. Support authorized binary analysis and CTF security challenges with protection findings, bypass strategies, reports, and analysis scripts.
Design and review REST and GraphQL APIs with consistent naming, data structures, error responses, pagination for large result sets, and versioning strategies. Helps teams define shared standards and plan changes that existing clients can adopt gradually.