Postgresql

PostgreSQL Database Design

Database design guidance specific to PostgreSQL.

CRITICAL: Verify Before Writing SQL

NEVER guess object names. Before writing any DDL or DML:

-- List all tables
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Search for tables by pattern
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name LIKE '%user%';

-- Get columns for a table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users'
ORDER BY ordinal_position;

-- Get foreign keys for a table
SELECT
  kcu.constraint_name,
  kcu.column_name,
  ccu.table_name AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.key_column_usage kcu
JOIN information_schema.constraint_column_usage ccu
  ON kcu.constraint_name = ccu.constraint_name
JOIN information_schema.table_constraints tc
  ON kcu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND kcu.table_name = 'posts';

-- Get indexes for a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Get constraints for a table
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

---

PostgreSQL Strengths

What makes PostgreSQL unique among relational databases:

| Feature | Description | |---------|-------------| | JSONB | Binary JSON with indexing, containment operators, and path queries | | Native UUID | First-class UUID type with gen_random_uuid() | | Native BOOLEAN | True boolean type (not 0/1 integers) | | Arrays | Native array types: TEXT[], INTEGER[], etc. | | TIMESTAMPTZ | Timezone-aware timestamps (stores UTC, converts on display) | | Partial Indexes | Index only rows matching a WHERE clause | | ENUM Types | Type-safe enumerations at the database level | | Full ALTER TABLE | Add/drop/modify columns and constraints on existing tables | | MVCC | Multi-Version Concurrency Control for concurrent writes without locking | | Generated Columns | Computed columns stored or virtual | | Row Level Security | Fine-grained access control at the row level | | Table Partitioning | Native range, list, and hash partitioning |

---

Data Types

Preferred Types

| Use Case | Type | Notes | |----------|------|-------| | Text (any length) | TEXT | No performance difference vs VARCHAR in PostgreSQL | | Text (with limit) | VARCHAR(n) | Only when you need to enforce max length | | Timestamps | TIMESTAMPTZ | Always use this, never TIMESTAMP | | Boolean | BOOLEAN | Native true/false | | Integer | INTEGER | -2B to +2B | | Large integer | BIGINT | For IDs, counts that may exceed 2B | | Money/currency | NUMERIC(precision, scale) | Never use FLOAT for money | | JSON data | JSONB | Always use this, never JSON | | Unique identifier | UUID | Use with gen_random_uuid() | | Arrays | TEXT[], INTEGER[] | Native array support |

Primary Keys

-- UUID (recommended for most tables)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- Auto-increment (modern syntax)
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- NEVER use SERIAL (deprecated)
-- id SERIAL PRIMARY KEY  -- DON'T DO THIS

UUID benefits:

• No sequential ID enumeration attacks
• Safe for distributed systems
• Can generate client-side

BIGINT IDENTITY benefits:

• Smaller storage (8 bytes vs 16)
• Better index locality for time-ordered data
• Simpler debugging

Timestamps

-- ALWAYS use TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
deleted_at TIMESTAMPTZ  -- nullable for soft deletes

-- NEVER use TIMESTAMP (loses timezone info)
-- created_at TIMESTAMP  -- DON'T DO THIS

---

Naming Conventions

Based on Simon Holywell's SQL Style Guide.

Tables

• Plural, snake_case
• Describes the collection of entities

users
blog_posts
order_line_items

-- Junction tables: both entity names (alphabetical or logical order)
user_roles
post_tags

Columns

• Singular, snake_case
• No table name prefix (avoid user_name in users table)
• Descriptive and unambiguous

-- Primary key
id

-- Foreign keys: singular_entity_id
user_id
organization_id

-- Booleans: is_, has_, can_, should_
is_active
has_verified_email
can_publish
should_notify

-- Timestamps: _at suffix
created_at
updated_at
published_at
expires_at

-- Counts: _count suffix
view_count
comment_count

-- Status/state: _status suffix
order_status
payment_status

Indexes

-- Pattern: {table}_{columns}_idx
users_email_idx
posts_user_id_idx
orders_customer_id_status_idx

-- Unique indexes
users_email_unique_idx

-- Partial indexes (include condition hint)
posts_published_at_active_idx

Constraints

-- Primary key: {table}_pkey
users_pkey

-- Foreign key: {table}_{column}_fkey
posts_user_id_fkey

-- Unique: {table}_{columns}_key
users_email_key

-- Check: {table}_{column}_check or {table}_{description}_check
orders_quantity_check
orders_dates_valid_check

-- Exclusion: {table}_{description}_excl
reservations_no_overlap_excl

---

Standard Table Template

-- Create ENUM type if needed
CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

CREATE TABLE posts (
    -- Primary key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign keys (always indexed separately)
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,

    -- Business columns
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    content TEXT,

    -- Enum column
    status post_status NOT NULL DEFAULT 'draft',

    -- Boolean flags
    is_featured BOOLEAN NOT NULL DEFAULT false,

    -- JSONB for flexible data
    metadata JSONB NOT NULL DEFAULT '{}',

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at TIMESTAMPTZ,

    -- Named constraints
    CONSTRAINT posts_title_check CHECK (char_length(title) >= 1),
    CONSTRAINT posts_slug_key UNIQUE (slug)
);

-- Always index foreign keys
CREATE INDEX posts_user_id_idx ON posts(user_id);
CREATE INDEX posts_category_id_idx ON posts(category_id);

-- Index commonly filtered columns
CREATE INDEX posts_status_idx ON posts(status);
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);

-- Partial index for active records only
CREATE INDEX posts_published_at_active_idx ON posts(published_at)
    WHERE status = 'published';

-- GIN index for JSONB queries
CREATE INDEX posts_metadata_idx ON posts USING GIN (metadata);

---

Indexing Strategies

Index Types

| Type | Use Case | Example | |------|----------|---------| | B-tree (default) | Equality, range, sorting | CREATE INDEX ... ON table(column) | | GIN | JSONB, arrays, full-text | CREATE INDEX ... USING GIN (jsonb_col) | | GiST | Geometric, range types, full-text | CREATE INDEX ... USING GiST (geo_col) | | Hash | Equality only (rarely better than B-tree) | CREATE INDEX ... USING HASH (col) | | BRIN | Very large tables with natural ordering | CREATE INDEX ... USING BRIN (created_at) |

B-tree Index Examples

-- Single column
CREATE INDEX users_email_idx ON users(email);

-- Composite (order matters for queries)
CREATE INDEX orders_customer_status_idx ON orders(customer_id, status);

-- Descending (for ORDER BY col DESC)
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);

-- Unique
CREATE UNIQUE INDEX users_email_unique_idx ON users(email);

Partial Indexes

Index only rows matching a condition - smaller and faster:

-- Only index active users
CREATE INDEX users_email_active_idx ON users(email)
    WHERE is_active = true;

-- Only index published posts
CREATE INDEX posts_published_idx ON posts(published_at)
    WHERE status = 'published';

-- Only index non-null values
CREATE INDEX orders_shipped_at_idx ON orders(shipped_at)
    WHERE shipped_at IS NOT NULL;

Expression Indexes

Index computed values:

-- Case-insensitive email lookup
CREATE INDEX users_email_lower_idx ON users(LOWER(email));

-- Extract year from timestamp
CREATE INDEX posts_year_idx ON posts(EXTRACT(YEAR FROM created_at));

-- JSONB field extraction
CREATE INDEX posts_author_idx ON posts((metadata->>'author'));

GIN Indexes for JSONB

-- Index entire JSONB document (supports @>, ?, ?&, ?|)
CREATE INDEX posts_metadata_idx ON posts USING GIN (metadata);

-- Index specific JSONB path (smaller, faster for specific queries)
CREATE INDEX posts_tags_idx ON posts USING GIN ((metadata->'tags'));

What to Index

Always index:

• Foreign key columns (PostgreSQL doesn't auto-index these!)
• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY (consider DESC if needed)

Consider indexing:

• Columns with high selectivity (many unique values)
• JSONB columns you query with @>, ?, etc.

Avoid indexing:

• Low-selectivity columns (boolean with 50/50 distribution)
• Frequently updated columns (index maintenance overhead)
• Small tables (sequential scan may be faster)

---

JSONB Patterns

When to Use JSONB vs Normalized Tables

Use JSONB for:

• Truly dynamic/schemaless data
• Configuration/settings
• Metadata/tags that vary per record
• Third-party API responses you need to store
• Audit logs with varying payloads

Use normalized tables for:

• Data you query/filter frequently
• Data with relationships to other tables
• Data that needs referential integrity
• Reports/analytics

JSONB Operators

-- Get JSON object field (returns JSONB)
SELECT metadata->'author' FROM posts;

-- Get JSON field as text (returns TEXT)
SELECT metadata->>'author' FROM posts;

-- Get nested field
SELECT metadata->'author'->>'name' FROM posts;
SELECT metadata#>>'{author,name}' FROM posts;  -- path syntax

-- Check if key exists
SELECT * FROM posts WHERE metadata ? 'featured';

-- Check if any key exists
SELECT * FROM posts WHERE metadata ?| array['featured', 'promoted'];

-- Check if all keys exist
SELECT * FROM posts WHERE metadata ?& array['author', 'tags'];

-- Containment (JSONB contains this structure)
SELECT * FROM posts WHERE metadata @> '{"status": "featured"}';

-- Contained by (this structure contains JSONB)
SELECT * FROM posts WHERE '{"a":1, "b":2}' >'author' = 'John';

-- Filter by nested JSON value
SELECT * FROM posts
WHERE metadata->'settings'->>'theme' = 'dark';

-- Filter by JSON array containment
SELECT * FROM posts
WHERE metadata->'tags' @> '["featured"]';

-- Check if array contains value
SELECT * FROM posts
WHERE metadata->'tags' ? 'featured';

-- Cast JSON value to proper type for comparison
SELECT * FROM posts
WHERE (metadata->>'view_count')::int > 100;

JSONB Modification

-- Set/update a key (jsonb_set)
UPDATE posts
SET metadata = jsonb_set(metadata, '{view_count}', '100')
WHERE id = '...';

-- Merge objects (|| operator)
UPDATE posts
SET metadata = metadata || '{"featured": true, "priority": 1}'
WHERE id = '...';

-- Remove a key (- operator)
UPDATE posts
SET metadata = metadata - 'deprecated_field'
WHERE id = '...';

-- Remove nested key
UPDATE posts
SET metadata = metadata #- '{author,temp_data}'
WHERE id = '...';

-- Append to array
UPDATE posts
SET metadata = jsonb_set(
    metadata,
    '{tags}',
    (metadata->'tags') || '"new-tag"'
)
WHERE id = '...';

---

ENUM Types

Creating and Using ENUMs

-- Create ENUM type
CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

-- Use in table
CREATE TABLE posts (
    status post_status NOT NULL DEFAULT 'draft'
);

-- Insert with ENUM
INSERT INTO posts (status) VALUES ('published');

-- Query with ENUM
SELECT * FROM posts WHERE status = 'published';

Modifying ENUMs

-- Add new value (PostgreSQL 9.1+)
ALTER TYPE post_status ADD VALUE 'scheduled';

-- Add value at specific position
ALTER TYPE post_status ADD VALUE 'scheduled' AFTER 'draft';
ALTER TYPE post_status ADD VALUE 'pending' BEFORE 'published';

-- Rename value (PostgreSQL 10+)
ALTER TYPE post_status RENAME VALUE 'draft' TO 'unpublished';

-- NOTE: You CANNOT remove or reorder ENUM values easily
-- For that, you need to recreate the type

ENUM vs CHECK Constraint

-- ENUM: Type-safe, shows in schema, harder to modify
status post_status NOT NULL DEFAULT 'draft'

-- CHECK: More flexible, easier to modify, less discoverable
status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'published', 'archived'))

---

Advanced Features (Overview)

Row Level Security (RLS)

Control row-level access based on user context:

-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own posts
CREATE POLICY posts_user_isolation ON posts
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Policy: published posts visible to all
CREATE POLICY posts_public_read ON posts
    FOR SELECT
    USING (status = 'published');

Table Partitioning

Split large tables for better performance:

-- Range partitioning by date
CREATE TABLE events (
    id UUID DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL,
    data JSONB
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Generated Columns

Computed columns stored or virtual:

-- Stored generated column (computed on write)
CREATE TABLE products (
    price NUMERIC(10,2) NOT NULL,
    quantity INTEGER NOT NULL,
    total NUMERIC(10,2) GENERATED ALWAYS AS (price * quantity) STORED
);

-- Use in expressions
ALTER TABLE users
ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Triggers for updated_at

Auto-update timestamp on row changes:

-- Create trigger function (once per database)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for each table
CREATE TRIGGER posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

---

Anti-Patterns

| Anti-Pattern | Problem | Solution | |--------------|---------|----------| | TIMESTAMP instead of TIMESTAMPTZ | Loses timezone, causes bugs | Always use TIMESTAMPTZ | | JSON instead of JSONB | Can't index, slower queries, no operators | Always use JSONB | | SERIAL instead of IDENTITY | Deprecated, permission issues | Use GENERATED ALWAYS AS IDENTITY | | VARCHAR(255) everywhere | Cargo cult from MySQL | Use TEXT or appropriate length | | Missing FK indexes | Slow JOINs and CASCADE deletes | Always index foreign key columns | | FLOAT/DOUBLE for money | Precision errors | Use NUMERIC(precision, scale) | | Unnamed constraints | Hard to modify/reference | Always name constraints | | SELECT * in production | Schema changes break code | List columns explicitly | | No NOT NULL on required fields | Data integrity issues | Default to NOT NULL, allow NULL explicitly | | Soft deletes without partial index | Queries scan deleted rows | Add partial index WHERE deleted_at IS NULL |

---

Quick Reference

TABLES:      plural, snake_case           users, blog_posts, order_items
COLUMNS:     singular, snake_case         user_id, created_at, is_active
PRIMARY KEY: id (UUID or BIGINT)          id UUID DEFAULT gen_random_uuid()
FOREIGN KEY: singular_entity_id

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [stonegiantstudio](https://github.com/stonegiantstudio)
- **Source:** [stonegiantstudio/skills](https://github.com/stonegiantstudio/skills)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.