AgentSkillsCN

pg

针对Gleam + Squirrel + POG + Cigogne堆栈，提供PostgreSQL性能优化与最佳实践。在编写、审查或优化PostgreSQL查询、架构设计，或数据库配置时使用此技能。

SKILL.md
--- frontmatter
name: pg
description: Postgres performance optimization and best practices for a Gleam + Squirrel + POG + Cigogne stack. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
license: MIT
metadata:
  version: "2.0.0"
  date: February 2026
  abstract: Postgres best practices aligned with a Gleam backend stack (Squirrel for SQL codegen, POG for connections, Cigogne for migrations). Covers schema design, query performance, connection management, security, and patterns specific to our conventions (UUIDv7, bigint money, session-variable RLS, prefixed enums, soft deletes, audit fields).

Postgres Best Practices (Gleam Stack)

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

Stack Context

LayerToolRole
LanguageGleamBackend application code
SQL codegenSquirrelGenerates type-safe Gleam from .sql files
DB driverPOGConnection pooling, binary protocol
MigrationsCigogneSchema migrations
Extensionspg_uuidv7UUIDv7 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 TypeGleam TypeNotes
uuidStringUUIDv7 for PKs
textStringPrefer over varchar(n)
booleanBool
integerInt
bigintIntUse for money (exact)
numericFloatLossy! Avoid for money
timestamppog.TimestampNo timestamptz support
datepog.Date
jsonbString (raw JSON)Decode in Gleam
byteaBitArray
enumGenerated variant typeSee 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 .sql query files
  • •Writing Cigogne migrations

Rule Categories by Priority

PriorityCategoryImpactPrefix
1Query PerformanceCRITICALquery-
2Connection ManagementCRITICALconn-
3Security & RLSCRITICALsecurity-
4Schema DesignHIGHschema-
5Concurrency & LockingMEDIUM-HIGHlock-
6Data Access PatternsMEDIUMdata-
7Monitoring & DiagnosticsLOW-MEDIUMmonitor-
8Advanced FeaturesLOWadvanced-

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

Available References

Advanced Features (advanced-):

  • •references/advanced-deferred-constraints.md
  • •references/advanced-full-text-search.md
  • •references/advanced-generated-columns.md
  • •references/advanced-jsonb-indexing.md

Connection Management (conn-):

  • •references/conn-idle-timeout.md
  • •references/conn-limits.md
  • •references/conn-pooling.md
  • •references/conn-prepared-statements.md

Data Access Patterns (data-):

  • •references/data-batch-inserts.md
  • •references/data-n-plus-one.md
  • •references/data-pagination.md
  • •references/data-upsert.md

Concurrency & Locking (lock-):

  • •references/lock-advisory.md
  • •references/lock-deadlock-prevention.md
  • •references/lock-short-transactions.md
  • •references/lock-skip-locked.md

Monitoring & Diagnostics (monitor-):

  • •references/monitor-explain-analyze.md
  • •references/monitor-pg-stat-statements.md
  • •references/monitor-vacuum-analyze.md

Query Performance (query-):

  • •references/query-composite-indexes.md
  • •references/query-covering-indexes.md
  • •references/query-index-types.md
  • •references/query-missing-indexes.md
  • •references/query-partial-indexes.md

Schema Design (schema-):

  • •references/schema-audit-fields.md
  • •references/schema-data-types.md
  • •references/schema-enums.md
  • •references/schema-foreign-key-indexes.md
  • •references/schema-lowercase-identifiers.md
  • •references/schema-naming-conventions.md
  • •references/schema-partitioning.md
  • •references/schema-primary-keys.md
  • •references/schema-soft-deletes.md

Security & RLS (security-):

  • •references/security-privileges.md
  • •references/security-rls-basics.md
  • •references/security-rls-performance.md

36 reference files across 8 categories

References