Comprehensive PostgreSQL-specific table design reference covering data types, indexing, constraints, performance patterns, and advanced features. Use when designing database schemas, creating tables, or reviewing database structure.
Provides PostgreSQL-specific schema design guidance covering data types, indexing, constraints, and performance patterns. Use when creating tables, designing schemas, or reviewing database structure for PostgreSQL databases.
/plugin marketplace add francanete/fran-marketplace/plugin install database-expert@fran-marketplaceThis skill inherits all available tools. When active, it can use any tool Claude has access to.
BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.NUMERIC for exact decimal arithmetic).snake_case for table/column names.UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL.NUMERIC(2,0) fails with error, unlike some databases that silently truncate or round.CLUSTER is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.BIGINT GENERATED ALWAYS AS IDENTITY preferred (GENERATED BY DEFAULT also fine); UUID when merging/federating/used in a distributed system or for opaque IDs. Generate with uuidv7() (preferred if using PG18+) or gen_random_uuid() (if using an older PG version).BIGINT unless storage space is critical; INTEGER for smaller ranges; avoid SMALLINT unless constrained.DOUBLE PRECISION over REAL unless storage space is critical. Use NUMERIC for exact decimal arithmetic.TEXT; if length limits needed, use CHECK (LENGTH(col) <= n) instead of VARCHAR(n); avoid CHAR(n). Use BYTEA for binary data.NUMERIC(p,s) (never float).TIMESTAMPTZ for timestamps; DATE for date-only; INTERVAL for durations. Avoid TIMESTAMP (without timezone). Use now() for transaction start time, clock_timestamp() for current wall-clock time.BOOLEAN with NOT NULL constraint unless tri-state values are required.CREATE TYPE ... AS ENUM for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.TEXT[], INTEGER[], etc. Use for ordered lists where you query elements. Index with GIN for containment (@>, <@) and overlap (&&) queries. Access: arr[1] (1-indexed), arr[1:3] (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: '{val1,val2}' or ARRAY[val1,val2].daterange, numrange, tstzrange for intervals. Support overlap (&&), containment (@>), operators. Index with GiST. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer [) (inclusive/exclusive) by default.INET for IP addresses, CIDR for network ranges, MACADDR for MAC addresses. Support network operators (<<, >>, &&).POINT, LINE, POLYGON, CIRCLE for 2D spatial data. Index with GiST. Consider PostGIS for advanced spatial features.TSVECTOR for full-text search documents, TSQUERY for search queries. Index tsvector with GIN. Always specify language: to_tsvector('english', col) and to_tsquery('english', 'query'). Never use single-argument versions.CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$') for reusable custom types with validation. Enforces constraints across tables.CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) for structured data within columns. Access with (col).field syntax.vector type by pgvector for vector similarity search for embeddings.timestamp (without time zone); DO use timestamptz instead.char(n) or varchar(n); DO use text instead.money type; DO use numeric instead.timetz type; DO use timestamptz instead.timestamptz(0) or any other precision specification; DO use timestamptz instead.serial type; DO use generated always as identity instead.Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression:
PLAIN (no TOAST)EXTENDED (compress + out-of-line) - default, usually optimalEXTERNAL (out-of-line, no compress)MAIN (compress, keep in-line if possible)Control with ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy and ALTER TABLE tbl SET (toast_tuple_target = 4096) for threshold.
For locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on LOWER(col) (preferred unless column needs case-insensitive PK/FK/UNIQUE) or CITEXT.
Enable with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Create policies:
CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id());
Built-in user-based access control at the row level.
ON DELETE/UPDATE action (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use DEFERRABLE INITIALLY DEFERRED for circular FK dependencies checked at transaction end.NULLS NOT DISTINCT (PG15+). Standard behavior: (1, NULL) and (1, NULL) are allowed. With NULLS NOT DISTINCT: only one (1, NULL) allowed. Prefer NULLS NOT DISTINCT unless you specifically need duplicate NULLs.CHECK (price > 0) allows NULL prices. Combine with NOT NULL to enforce: price NUMERIC NOT NULL CHECK (price > 0).EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) prevents double-booking rooms. Requires appropriate index type (often GiST).=, <, >, BETWEEN, ORDER BY)WHERE a = ? AND b > ? uses index on (a,b), but WHERE b = ? does not). Put most selective/frequently filtered columns first.CREATE INDEX ON tbl (id) INCLUDE (name, email) - includes non-key columns for index-only scans without visiting table.WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Any query with status = 'active' can use this index.CREATE INDEX ON tbl (LOWER(email))). Expression must match exactly in WHERE clause: WHERE LOWER(email) = 'user@example.com'.@>, ?), full-text search (@@)CLUSTER).Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date). Alternate use: data maintenance where data is pruned or bulk replaced periodically.
PARTITION BY RANGE (created_at)). Create partitions: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automates time-based or ID-based partitioning with retention policies and compression.PARTITION BY LIST (region)). Example: FOR VALUES IN ('us-east', 'us-west').PARTITION BY HASH (user_id)). Creates N partitions with modulus.CHECK constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
Limitations: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.
fillfactor=90 to leave space for HOT updates that avoid index maintenance.COPY or multi-row INSERT instead of single-row inserts.BIGINT GENERATED ALWAYS AS IDENTITY over UUID.ON CONFLICT (col1, col2) needs exact matching unique index (partial indexes don't work).EXCLUDED.column to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.DO NOTHING faster than DO UPDATE when no actual update needed.BEGIN; ALTER TABLE...; ROLLBACK; for safe testing.CREATE INDEX CONCURRENTLY avoids blocking writes but can't run in transactions.NOT NULL columns with volatile defaults (e.g., now(), gen_random_uuid()) rewrites entire table. Non-volatile defaults are fast.ALTER TABLE DROP CONSTRAINT then DROP COLUMN to avoid dependency issues.CREATE OR REPLACE with different arguments creates overloads, not replacements. DROP old version if no overload desired.... GENERATED ALWAYS AS (<expr>) STORED for computed, indexable fields. PG18+ adds VIRTUAL columns (computed on read, not stored).
pgcrypto: crypt() for password hashing.uuid-ossp: alternative UUID functions; prefer pgcrypto for new projects.pg_trgm: fuzzy text search with % operator, similarity() function. Index with GIN for LIKE '%pattern%' acceleration.citext: case-insensitive text type. Prefer expression indexes on LOWER(col) unless you need case-insensitive constraints.btree_gin/btree_gist: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).hstore: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.timescaledb: essential for time-series—automated partitioning, retention, compression, continuous aggregates.postgis: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.pgvector: vector similarity search for embeddings.pgaudit: audit logging for all database activity.Prefer JSONB with GIN index.
CREATE INDEX ON tbl USING GIN (jsonb_col);
Accelerates:
jsonb_col @> '{"k":"v"}'jsonb_col ? 'k', any/all keys ?|, ?&jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])Consider opclass jsonb_path_ops for smaller/faster containment-only indexes:
CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
Trade-off: loses support for key existence (?, ?|, ?&) queries—only supports containment (@>)
Extract and index with B-tree (generated column or expression):
ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
CREATE INDEX ON tbl (price);
Prefer queries like WHERE price BETWEEN 100 AND 500 (uses B-tree) over WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 without index.
@> for containment (e.g., tags). Consider jsonb_path_ops if only doing containment.config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')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);
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);
-- Customers with validation
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
encrypted_password TEXT NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
phone TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
is_active BOOLEAN NOT NULL DEFAULT true,
CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT valid_phone CHECK (phone IS NULL OR phone ~* '^\+?[1-9]\d{1,14}$')
);
CREATE UNIQUE INDEX ON customers (LOWER(email));
CREATE INDEX ON customers (created_at);
-- Products with inventory
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
description TEXT,
category_id BIGINT REFERENCES categories(id),
base_price NUMERIC(10,2) NOT NULL CHECK (base_price >= 0),
inventory_count INTEGER NOT NULL DEFAULT 0 CHECK (inventory_count >= 0),
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON products (category_id);
CREATE INDEX ON products (is_active) WHERE is_active = true;
-- Orders with state machine
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_number TEXT NOT NULL UNIQUE,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')),
subtotal NUMERIC(10,2) NOT NULL CHECK (subtotal >= 0),
tax_amount NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
shipping_amount NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (shipping_amount >= 0),
total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT valid_total CHECK (total_amount = subtotal + tax_amount + shipping_amount)
);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (status);
CREATE INDEX ON orders (created_at);
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Applies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.