postgres-patterns

PostgreSQL Patterns

Constraints

• Apply @rules/sql/optimalize.mdc — it already owns indexing fundamentals, SARGable WHERE, seek/keyset pagination, EXPLAIN, transactions/locking basics, and batch-over-per-row. Do not re-explain those; defer to it. Note it is written for MySQL — translate engine-specific syntax (EXPLAIN ANALYZE, plan flags) to Postgres equivalents here.
• This skill is the Postgres counterpart to @skills/mysql-patterns/SKILL.md. It is for designing features. For diagnosing an existing slow query, the closest fit is @skills/mysql-problem-solver/SKILL.md (apply its method; substitute Postgres EXPLAIN/pg_stat_statements).
• If the project uses Laravel, also apply @rules/laravel/laravel.mdc and @rules/laravel/architecture.mdc.
• Apply @rules/security/backend.md — parameterized queries / Eloquent only, least-privilege DB users, never hardcode credentials.
• final classes, declare(strict_types=1), Pest tests for any data-access code added.
• Confirm the major version (SELECT version();) before using version-specific features — MERGE (15+), covering INCLUDE indexes (11+), and NULLS NOT DISTINCT (15+) are not in older servers.

Use when

• Adding jsonb columns, GIN/BRIN/partial/covering indexes, or full-text search to a Laravel app on Postgres.
• Building a queue/worker that pulls jobs with FOR UPDATE SKIP LOCKED, or paginating large result sets by cursor.
• Adding ON CONFLICT upserts, Row-Level Security for multi-tenancy, or correct timestamptz/numeric typing.
• Reviewing a migration that introduces any of the above on a large table.

Set DB_CONNECTION=pgsql. These topics complement the query-tuning rules; they are not covered there.

Data Type Discipline

Pick the right type once — wrong choices are expensive to migrate later.

Schema::create('orders', function (Blueprint $table): void {
    $table->id();                                  // bigint identity
    $table->decimal('amount', 12, 2);              // numeric — never float/double for money
    $table->timestampTz('placed_at');              // timestamptz — stores UTC instant
    $table->jsonb('meta')->default('{}');          // jsonb (binary, indexable), not json
    $table->boolean('is_paid')->default(false);
});