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)
| Document | Purpose |
|---|
| testing-patterns.md | pgTAP unit testing, test factories |
| performance-tuning.md | EXPLAIN ANALYZE, query optimization, JIT |
| row-level-security.md | RLS patterns, multi-tenant isolation |
| jsonb-patterns.md | JSONB indexing, queries, validation |
| audit-logging.md | Generic audit triggers, change tracking |
| bulk-operations.md | COPY, batch inserts, upserts |
| session-management.md | Session variables, connection pooling |
| transaction-patterns.md | Isolation levels, locking, deadlock prevention |
| full-text-search.md | tsvector, tsquery, ranking, multi-language |
| partitioning.md | Range, list, hash partitioning strategies |
| window-functions.md | Frames, ranking, running calculations |
| time-series.md | Time-series data patterns, BRIN indexes |
| event-sourcing.md | Event store, projections, CQRS |
| queue-patterns.md | Job queues, SKIP LOCKED, LISTEN/NOTIFY |
| encryption.md | pgcrypto, column encryption, TLS |
| vector-search.md | pgvector, embeddings, similarity search |
| postgis-patterns.md | Spatial data, geographic queries |
🚀 DevOps & Migration
📊 Data Warehousing
Executable Scripts
Core Architecture
Schema Separation Pattern
Application → api schema → data schema
↓
private schema (triggers, helpers)
| Schema | Contains | Access | Purpose |
|---|
data | Tables, indexes | None | Data storage |
private | Triggers, helpers | None | Internal logic |
api | Functions, procedures | Applications | External interface |
app_audit | Audit tables | Admins | Change tracking |
app_migration | Migration tracking | Admins | Schema versioning |
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
| Prefix | Type | Example |
|---|
l_ | Local variable | l_customer_count |
g_ | Session/global variable | g_current_user_id |
co_ | Constant | co_max_retries |
in_ | IN parameter | in_customer_id |
out_ | OUT parameter (functions only) | out_total |
io_ | INOUT parameter (procedures) | io_id |
c_ | Cursor | c_active_orders |
r_ | Record | r_customer |
t_ | Array/table | t_order_ids |
e_ | Exception | e_not_found |
Note: PostgreSQL procedures only support INOUT parameters, not OUT. Use io_ prefix for all procedure output parameters.
Database Objects
| Object | Pattern | Example |
|---|
| Table | snake_case, plural | orders, order_items |
| Column | snake_case | customer_id, created_at |
| Primary Key | id | id |
| Foreign Key | {table_singular}_id | customer_id |
| Index | {table}_{cols}_idx | orders_customer_id_idx |
| Unique | {table}_{cols}_key | users_email_key |
| Function | {action}_{entity} | get_customer, select_orders |
| Procedure | {action}_{entity} | insert_order, update_status |
| Trigger | {table}_{timing}{event}_trg | orders_bu_trg |
Data Type Recommendations
| Use | Instead Of |
|---|
text | char(n), varchar(n) |
numeric(p,s) | money, float |
timestamptz | timestamp |
boolean | integer flags |
uuidv7() | serial, uuid_generate_v4() |
GENERATED ALWAYS AS IDENTITY | serial, bigserial |
jsonb | json, EAV pattern |
Critical Anti-Patterns
- ❌ Direct table access from applications
- ❌
RETURNS SETOF table (exposes all columns)
- ❌ Missing
SET search_path with SECURITY DEFINER
- ❌
timestamp without timezone
- ❌
NOT IN with subqueries (use NOT EXISTS)
- ❌
BETWEEN with timestamps (use >= AND <)
- ❌ Missing indexes on foreign keys
- ❌
serial/bigserial (use IDENTITY)
- ❌
varchar(n) arbitrary limits (use text)
- ❌
SELECT FOR UPDATE without NOWAIT/SKIP LOCKED
PostgreSQL 18+ Features
| Feature | Usage |
|---|
uuidv7() | id uuid DEFAULT uuidv7() - timestamp-ordered UUIDs |
| Virtual generated columns | col type GENERATED ALWAYS AS (expr) - computed at query time |
OLD/NEW in RETURNING | UPDATE ... RETURNING OLD.col, NEW.col |
| Temporal constraints | PRIMARY KEY (id) WITHOUT OVERLAPS |
NOT VALID constraints | Add constraints without full table scan |
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
