🇺🇸English
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)
| 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 |
|
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_ |
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 |
Data Type Recommendations
| Use | Instead Of |
|---|
text | char(n), varchar(n) |
numeric(p,s) | money, float |
timestamptz | timestamp |
boolean |
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 )
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 |
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
Weekly Installs
94
Repository
wimolivier/post…ractices
GitHub Stars
2
First Seen
Mar 2, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex93
gemini-cli92
amp92
cline92
github-copilot92
opencode92
