postgresql-data-engineering

What problem does it solve?

This Skill helps you design and operate PostgreSQL for data engineering workloads so partitioning, indexing, bulk loads, and query diagnostics work together instead of becoming performance bottlenecks and maintenance pain.

Core Features & Use Cases

• Declarative partitioning strategies: Choose RANGE, LIST, or HASH partition keys, manage partitions with DEFAULT plus ATTACH/DETACH, and ensure pruning works for predictable query performance (including pg_partman automation).
• Indexing that matches workload: Select B-Tree for general access patterns, BRIN for time-correlated data, GIN for JSONB containment, and use partial and covering indexes to reduce I/O and improve selectivity.
• Operational bulk-load and performance diagnosis: Load and export efficiently with COPY (including UNLOGGED staging and pg_bulkload patterns), then interpret EXPLAIN/EXPLAIN ANALYZE to pinpoint scan/index issues.
• Sustained write health: Tune autovacuum for bloat control, monitor dead tuples, and apply safe vacuum/analyze practices.
• Advanced SQL patterns for pipelines: Use window functions, JSONB operators, CTEs/recursive queries, and LATERAL joins to build robust transformations in-place.

Quick Start

Ask an AI to generate a PostgreSQL plan for a partitioned events table (RANGE by event_date), propose index choices for JSONB queries and time filters, and provide an EXPLAIN ANALYZE interpretation checklist for resolving the slowest query path.