aboio-labs 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:
uuidviauuid_generate_v7()(pg_uuidv7 extension) - Timestamps:
timestamp(nottimestamptz); timezone stored incore.tenant.timezone - Money:
bigintwith scale factor 10,000 (maps to GleamInt, exact arithmetic) - Enums:
CREATE TYPEwith prefixed values (os_pending,ps_paid) for Gleam uniqueness - Constraints: Named with
pk_,fk_,uq_,chk_,idx_prefixes - Schemas:
core,tenant,shared,extensionsseparation - Tables: Singular names (
ordernotorders) - Soft deletes:
deleted_at timestamp+deleted_by uuid+ partial indexes - Audit fields:
created_at,updated_at,created_by,updated_byvia 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
.sqlquery 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:
- Grep for specific SQL patterns (e.g.,
pog.transaction,set_config,FORCE ROW LEVEL) instead of reading all migration files - Use
git diff --name-onlyto scope reviews to changed.sqlfiles only - 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 |