postgresql
/postgresql
Stosuje dobre praktyki PostgreSQL: wielodostępność z RLS, projekt schematu, migracje Alembic, asynchroniczny SQLAlchemy i optymalizacja zapytań, w tym debugowanie izolacji tenantów.
- Autor
- itechmeat ↗
- Repozytorium
- llm-code ↗
- Licencja
- MIT
- Ostatnia zmiana repozytorium
- 24.10.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 14
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 3
Brak sygnałów. Repozytorium zmienione 24.10.2026, bez odwołań do wycofanych modeli i API.
MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.
Co robi i kiedy
Stosuje dobre praktyki PostgreSQL: wielodostępność z RLS, projekt schematu, migracje Alembic, asynchroniczny SQLAlchemy i optymalizacja zapytań, w tym debugowanie izolacji tenantów.
Oryginalny opis (EN)
PostgreSQL best practices: multi-tenancy with RLS, schema design, Alembic migrations, async SQLAlchemy, and query optimization. Use when designing multi-tenant tables with Row-Level Security, debugging tenant isolation, creating/changing Alembic migrations, or optimizing PostgreSQL queries. Keywords: PostgreSQL, RLS, Alembic, SQLAlchemy, multi-tenancy.
Jak działa
- RLS Multi-tenancy Pattern
- Multi-tenant Table Checklist
- Alembic Migrations Checklist
- Version Notes
- RLS Isolation Testing Recipe
- Destructive Operations Safety
- References
- Links
- See Also
Podgląd SKILL.md
---
name: postgresql
description: "PostgreSQL best practices: multi-tenancy with RLS, schema design, Alembic migrations, async SQLAlchemy, and query optimization. Use when designing multi-tenant tables with Row-Level Security, debugging tenant isolation, creating/changing Alembic migrations, or optimizing PostgreSQL queries. Keywords: PostgreSQL, RLS, Alembic, SQLAlchemy, multi-tenancy."
metadata:
version: "19rc1"
release_date: "2026-10-15"
---
# PostgreSQL
## 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
1. Add/modify schema (columns, constraints, FKs)
2. Create/update indexes
3. Enable RLS and create/adjust policies
4. Add verification (tests) for isolation
5. Provide a real downgrade (no stubs)
## Version Notes
### PostgreSQL 19 (RC 1)
- 19 is at **RC 1 (2026-10-15)**: the feature set is frozen and matches the upcoming general availability, planned for **2026-11-19**. RC 2 ships on **2026-11-12** together with the November update release, so that release's fixes land in 19 before GA. Latest stable line is `18.6`.
- Headline changes: `REPACK` / `REPACK CONCURRENTLY` replacing `VACUUM FULL` and `CLUSTER`, parallel autovacuum with a scoring system, logical replication of sequences, SQL/PGQ property graphs, `GROUP BY ALL`, `FOR PORTION OF`, and online checksum enable/disable.
- 18 incompatible changes to plan for, including forced `standard_conforming_strings`, RADIUS removal, `jit` off by default, `default_toast_compression` switching to `lz4`, and `max_locks_per_transaction` defaulting to 128 with changed sizing.
- Full detail and the upgrade checklist: [postgresql-19.md](references/postgresql-19.md)
### PostgreSQL 18.4
- `18.4` is a security/robustness patch release; no dump/restore is required for existing `18.x` clusters.
- The patch line hardens startup packet parsing, backup tools (`pg_basebackup`, `pg_rewind`, `pg_verifybackup`), and several logical replication code paths.
- Planner/executor fixes also land for `MERGE`, nondeterministic collations, generated columns, and assorted aggregate/window edge cases.
## 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:
1. Setup data through an **admin session** (RLS bypass) for tenant A and B
2. 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:
1. Confirm exact target (tables / IDs / date range)
2. Run a `SELECT`/row count first and show results
3. Ask for final confirmation, then execute
## References
### Versions
- [postgresql-19.md](references/postgresql-19.md) — PostgreSQL 19 (beta): new features by area, full incompatible-changes list, upgrade checklist
### Schema & Design
- [table-design.md](references/table-design.md) — Data types, constraints, indexing, partitioning, JSONB, safe schema evolution
- [charset-encoding.md](references/charset-encoding.md) — Character sets, encoding, collation, ICU, locale settings
### Authentication
- [authentication.md](references/authentication.md) — pg_hba.conf, SCRAM-SHA-256, md5, peer, cert, LDAP, GSSAPI
- [authentication-oauth.md](references/authentication-oauth.md) — OAuth 2.0 (PostgreSQL 18+), SASL OAUTHBEARER, validators
- [user-management.md](references/user-management.md) — CREATE/ALTER/DROP ROLE, membership, GRANT/REVOKE, predefined roles
### Runtime Configuration
- [connection-settings.md](references/connection-settings.md) — listen_addresses, max_connections, SSL, TCP keepalives
- [query-tuning.md](references/query-tuning.md) — Planner settings, work_mem, parallel query, cost constants
- [replication.md](references/replication.md) — Streaming replication, WAL, synchronous commit, logical replication
- [vacuum.md](references/vacuum.md) — Autovacuum, vacuum cost model, freeze ages, per-table tuning
- [error-handling.md](references/error-handling.md) — exit_on_error, restart_after_crash, data_sync_retry
### Internals
- [internals.md](references/internals.md) — Query processing pipeline, parser/rewriter/planner/executor, system catalogs, wire protocol, access methods
- [protocol.md](references/protocol.md) — Wire protocol v3.2: message format, startup, auth, query, COPY, replication
## Links
- [Documentation](https://www.postgresql.org/docs/current/)
- [Releases](https://www.postgresql.org/about/newsarchive/pgsql/)
- [GitHub](https://github.com/postgres/postgres)
## See Also
- [sql-expert](../sql-expert/SKILL.md) — Query patterns, EXPLAIN workflow, optimization
Pliki w paczce (14)
- SKILL.md 6 KB
- references/authentication-oauth.md 23 KB
- references/authentication.md 7 KB
- references/charset-encoding.md 15 KB
- references/connection-settings.md 4 KB
- references/error-handling.md 3 KB
- references/internals.md 3 KB
- references/postgresql-19.md 19 KB
- references/protocol.md 5 KB
- references/query-tuning.md 6 KB
- references/replication.md 5 KB
- references/table-design.md 6 KB
- references/user-management.md 9 KB
- references/vacuum.md 5 KB
Wersje
| Repozytorium | Wynik | Licencja |
|---|---|---|
| Ta strona · itechmeat/llm-code | 100 | MIT |
| speakeasy-api/gram | 100 | AGPL-3.0 |
| TerminalSkills/skills | 100 | Apache-2.0 |
| iliaal/whetstone | 100 | MIT |
| claude-dev-suite/claude-dev-suite | 100 | MIT |
| yvssdheeraj/opencode-engineering-harness | 100 | MIT |
| G1Joshi/Agent-Skills | 100 | MIT |
| bcastelino/agent-skills-kit | 100 | MIT |
| MARUCIE/openclaw-foundry | 100 | MIT |
Migawka bazy z 13.11.2026 · wersja paczki 2ccabf664e02 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 100alembic-migrations
Konfiguruje migracje Alembic dla asynchronicznego SQLAlchemy: async engine z run_sync w env.py, autogenerate i migracje RLS dla wielu najemców. - 100sqlalchemy
Pomaga pisać asynchroniczny kod SQLAlchemy 2.0, czyli sesje, relacje ładowane bez N+1 i migracje Alembic. - 100sqlalchemy
Pomaga w asynchronicznym SQLAlchemy 2.0: modele, zapytania, sesje i migracje Alembic bez zapytań N+1. - 100alembic
Zarządza migracjami bazy danych dla SQLAlchemy przez Alembic: wersjonowanie schematu, autogenerowanie skryptów z modeli, uruchamianie i wycofywanie migracji oraz asynchroniczne migracje. - 100python-data-engineer
Wspiera pracę z SQLAlchemy 2.0, migracjami Alembic, administracją bazami PostgreSQL, MySQL i SQL Server, optymalizacją zapytań, pulami połączeń oraz wizualizacją pochodzenia danych. - 90sqlalchemy-code-review
Przegląda kod SQLAlchemy pod kątem zarządzania sesją, relacji, zapytań N+1 i wzorców migracji z Alembic.