Pg gleam

Postgres Best Practices (Gleam Stack)

Performance optimization and schema design guide for Postgres, aligned with our Gleam backend stack.

Stack Context

Layer	Tool	Role
Language	Gleam	Backend application code
SQL codegen	Squirrel / Parrot	Generates type-safe Gleam from .sql files
DB driver	POG	Connection pooling, binary protocol
Migrations	Cigogne	Schema migrations
Extensions	pg_uuidv7	UUIDv7 primary key generation

Our Conventions

• Primary keys: uuid via uuid_generate_v7() (pg_uuidv7 extension)
• Timestamps: timestamp (not timestamptz); timezone stored in core.tenant.timezone
• Money: bigint with scale factor 10,000 (maps to Gleam Int, exact arithmetic)
• Enums: CREATE TYPE with prefixed values (os_pending, ps_paid) for Gleam uniqueness
• Constraints: Named with pk_, fk_, uq_, chk_, idx_ prefixes
• Schemas: core, tenant, shared, extensions separation
• Tables: Singular names (order not orders)
• Soft deletes: deleted_at timestamp + deleted_by uuid + partial indexes
• Audit fields: created_at, updated_at, created_by, updated_by via triggers
• RLS: Session variables via core.current_tenant_id() / core.current_user_id()

Gleam Type Mapping (via Squirrel/POG)

Postgres Type	Gleam Type	Notes
uuid	String	UUIDv7 for PKs
text	String	Prefer over varchar(n)
boolean	Bool
integer	Int
bigint	Int	Use for money (exact)
numeric	Float	Lossy! Avoid for money
timestamp	pog.Timestamp	No timestamptz support
date	pog.Date
jsonb	String (raw JSON)	Decode in Gleam
bytea	BitArray
enum	Generated variant type	See schema-enums.md

When to Apply

Reference these guidelines when:

• Writing SQL queries or designing schemas
• Implementing indexes or query optimization
• Reviewing database performance issues
• Configuring POG connection pooling
• Working with Row-Level Security (RLS)
• Creating Squirrel or Parrot .sql query files
• Writing Cigogne migrations

Rule Categories by Priority

Priority	Category	Impact	Prefix
1	Query Performance	CRITICAL	query-
2	Connection Management	CRITICAL	conn-
3	Security & RLS	CRITICAL	security-
4	Schema Design	HIGH	schema-
5	Concurrency & Locking	MEDIUM-HIGH	lock-
6	Data Access Patterns	MEDIUM	data-
7	Monitoring & Diagnostics	LOW-MEDIUM	monitor-
8	Advanced Features	LOW	advanced-

Token Efficiency

Follow references/token-efficiency.md rules. When reviewing or writing database code:

1. Grep for specific SQL patterns (e.g., pog.transaction, set_config, FORCE ROW LEVEL) instead of reading all migration files
2. Use git diff --name-only to scope reviews to changed .sql files only
3. Read targeted line ranges around Grep matches, not entire migrations

How to Use

Read individual rule files in references/ for detailed explanations and SQL examples. Each rule file contains:

• Brief explanation of why it matters
• Incorrect SQL example with explanation
• Correct SQL example with explanation
• Optional EXPLAIN output or metrics
• Gleam/POG-specific notes where applicable

Reference routing — by task

Task	References to read
Designing a new table	schema-primary-keys.md + schema-data-types.md + schema-naming-conventions.md
Adding Enum columns	schema-enums.md
Adding Audit / Soft Deletes	schema-audit-fields.md + schema-soft-deletes.md
Implementing RLS (Tenant Isolation)	security-rls-basics.md + security-session-variables.md
RLS on Child Tables	security-rls-child-tables.md + security-rls-performance.md
Optimizing slow queries	query-missing-indexes.md + monitor-explain-analyze.md
Creating indexes	schema-foreign-key-indexes.md + query-composite-indexes.md
Writing batch inserts	data-batch-inserts.md
Implementing pagination	data-pagination.md
Handling concurrent updates	lock-short-transactions.md + lock-skip-locked.md
Configuring POG connections	conn-pooling.md + conn-limits.md

Reference routing — by topic

Topic	Reference
Postgres Data Types to Gleam mapping	schema-data-types.md
UUIDv7 generation	schema-primary-keys.md
Row Level Security (RLS) performance	security-rls-performance.md
Partial & Covering Indexes	query-partial-indexes.md + query-covering-indexes.md
JSONB Indexing	advanced-jsonb-indexing.md
Full Text Search	advanced-full-text-search.md
Deadlock prevention	lock-deadlock-prevention.md
N+1 Query prevention	data-n-plus-one.md
Upserts (ON CONFLICT)	data-upsert.md
Connection Pooling & Idle timeouts	conn-pooling.md + conn-idle-timeout.md
Prepared Statements	conn-prepared-statements.md

References

• https://www.postgresql.org/docs/current/
• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://hexdocs.pm/pog/
• https://hexdocs.pm/squirrel/
• https://hexdocs.pm/parrot/