PostgreSQL Advanced Best Practices (PostgreSQL 18+)

Architecture at a Glance

                        ┌─── PostgreSQL Database ──────────────────────────────┐
                        │                                                      │
                        │  ┌──────────────────┐    ┌───────────────────────┐   │
                        │  │   api schema      │    │   private schema      │   │
  ┌─────────────┐       │  │──────────────────│    │───────────────────────│   │
  │ Application │─EXECUTE─▶│ get_customer()   │───▶│ set_updated_at()     │   │
  └─────────────┘       │  │ insert_order()   │    │ hash_password()      │   │
        │               │  └────────┬─────────┘    └──────────┬────────────┘   │
        │               │           │                         │                │
        │               │           │ SECURITY DEFINER        │ triggers       │
        │               │           ▼                         ▼                │
        │               │  ┌──────────────────────────────────────────────┐    │
        │               │  │              data schema                     │    │
     BLOCKED            │  │──────────────────────────────────────────────│    │
        │               │  │  customers    orders    ...                  │    │
        └ ─ ─ ─ ✕       │  └──────────────────────────────────────────────┘    │
                        │                                                      │
                        └──────────────────────────────────────────────────────┘

Skill Contents

🚀 Getting Started (Read These First)

📚 Core Reference (Use Daily)

🔧 Advanced Topics (When Needed)

🚀 DevOps & Migration

📊 Data Warehousing

Executable Scripts

Core Architecture

Schema Separation Pattern

Application → api schema → data schema
                ↓
            private schema (triggers, helpers)

Security Model

All api functions MUST have:

SECURITY DEFINER
SET search_path = data, private, pg_temp

Quick Reference

Create Table Pattern

CREATE TABLE data.{table_name} (
    id              uuid PRIMARY KEY DEFAULT uuidv7(),
    -- columns...
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER {table}_bu_updated_trg
    BEFORE UPDATE ON data.{table_name}
    FOR EACH ROW EXECUTE FUNCTION private.set_updated_at();

API Function Pattern

CREATE FUNCTION api.{action}_{entity}(in_param type)
RETURNS TABLE (col1 type, col2 type)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
    SELECT col1, col2 FROM data.{table} WHERE ...;
$$;

API Procedure Pattern

CREATE PROCEDURE api.{action}_{entity}(
    in_param type,
    INOUT io_id uuid DEFAULT NULL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
BEGIN
    INSERT INTO data.{table} (...) VALUES (...) RETURNING id INTO io_id;
END;
$$;

Migration Pattern

SELECT app_migration.acquire_lock();

CALL app_migration.run_versioned(
    in_version := '001',
    in_description := 'Description',
    in_sql := $mig$ ... $mig$,
    in_rollback_sql := '...'
);

SELECT app_migration.release_lock();

Naming Conventions

Trivadis-Style Variable Prefixes

Note: PostgreSQL procedures only support INOUT parameters, not OUT. Use io_ prefix for all procedure output parameters.

Database Objects

Data Type Recommendations

Critical Anti-Patterns

1. ❌ Direct table access from applications
2. ❌ RETURNS SETOF table (exposes all columns)
3. ❌ Missing SET search_path with SECURITY DEFINER
4. ❌ timestamp without timezone
5. ❌ NOT IN with subqueries (use NOT EXISTS)
6. ❌ BETWEEN with timestamps (use >= AND <)
7. ❌ Missing indexes on foreign keys
8. ❌ serial/bigserial (use IDENTITY)
9. ❌ varchar(n) arbitrary limits (use text)
10. ❌ SELECT FOR UPDATE without NOWAIT/SKIP LOCKED

PostgreSQL 18+ Features

File Organization

db/
├── migrations/
│   ├── V001__create_schemas.sql
│   ├── V002__create_tables.sql
│   └── repeatable/
│       ├── R__private_triggers.sql
│       └── R__api_functions.sql
├── schemas/
│   ├── data/           # Table definitions
│   ├── private/        # Internal functions
│   └── api/            # External interface
└── seeds/              # Reference data