postgresql

Original：🇺🇸 English

Translated

PostgreSQL best practices: multi-tenancy with RLS, schema design, Alembic migrations, async SQLAlchemy, and query optimization.

1installs

Sourceitechmeat/llm-code

Added on2026-02-08

NPX Install

npx skill4agent add itechmeat/llm-code postgresql

SKILL.md Content

View Translation Comparison →

PostgreSQL

When to use

Designing or changing multi-tenant tables with Row-Level Security (RLS)
Debugging tenant isolation issues
Adding/changing Alembic migrations for schema, RLS policies, or indexes
Writing tests that validate RLS isolation
Configuring PostgreSQL authentication, replication, or tuning

RLS Multi-tenancy Pattern

Non-negotiables

RLS context is mandatory for any tenant-scoped query
Context must be set inside the same transaction as the queries
No fallbacks for tenant ID (fail fast if missing)
Async-only DB access when using async frameworks

Setting RLS Context

RLS works only if the current transaction has the context set:

sql

SET LOCAL app.current_tenant_id = '<tenant_uuid>';

Must run before the first tenant-scoped query in that transaction.

Common Failure Modes

Setting
```
SET LOCAL ...
```
after the first
```
select()
```
Setting the context in one session, then querying in another
Running queries outside the expected transaction scope

Typical RLS Policy

sql

ALTER TABLE some_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY some_table_tenant_isolation
ON some_table
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

Multi-tenant Table Checklist

Tenant ID column is UUID
FK to tenants table with
```
ON DELETE CASCADE
```
Indexes aligned with access patterns (usually tenant_id first)
- PostgreSQL does not auto-index FK columns — add explicit indexes
- UNIQUE allows multiple NULLs unless using
```
NULLS NOT DISTINCT
```
  (PG15+)
RLS is enabled and policies exist
Application code sets RLS context at transaction start

Alembic Migrations Checklist

Add/modify schema (columns, constraints, FKs)
Create/update indexes
Enable RLS and create/adjust policies
Add verification (tests) for isolation
Provide a real downgrade (no stubs)

RLS Isolation Testing Recipe

Goal:

Data for tenant A is visible to tenant A
Data for tenant A is NOT visible to tenant B

Canonical flow:

Setup data through an admin session (RLS bypass) for tenant A and B
Assert via an RLS session:
- set context to tenant A → sees only tenant A data
- set context to tenant B → does not see tenant A data

Destructive Operations Safety

Hard rules:

Never run
```
DELETE
```
without a narrow
```
WHERE
```
targeting specific data
Never run
```
TRUNCATE
```
/
```
DROP
```
without explicit confirmation

Pre-flight before destructive actions:

Confirm exact target (tables / IDs / date range)
Run a
```
SELECT
```
/row count first and show results
Ask for final confirmation, then execute

References

Schema & Design

table-design.md — Data types, constraints, indexing, partitioning, JSONB, safe schema evolution
charset-encoding.md — Character sets, encoding, collation, ICU, locale settings

Authentication

authentication.md — pg_hba.conf, SCRAM-SHA-256, md5, peer, cert, LDAP, GSSAPI
authentication-oauth.md — OAuth 2.0 (PostgreSQL 18+), SASL OAUTHBEARER, validators
user-management.md — CREATE/ALTER/DROP ROLE, membership, GRANT/REVOKE, predefined roles

Runtime Configuration

connection-settings.md — listen_addresses, max_connections, SSL, TCP keepalives
query-tuning.md — Planner settings, work_mem, parallel query, cost constants
replication.md — Streaming replication, WAL, synchronous commit, logical replication
vacuum.md — Autovacuum, vacuum cost model, freeze ages, per-table tuning
error-handling.md — exit_on_error, restart_after_crash, data_sync_retry

Internals

internals.md — Query processing pipeline, parser/rewriter/planner/executor, system catalogs, wire protocol, access methods
protocol.md — Wire protocol v3.2: message format, startup, auth, query, COPY, replication