skills.rest
ivanshamaev
ivanshamaevCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

postgresql-data-engineering

Design PostgreSQL partitioning, indexing, bulk load, and query diagnostics for data engineering.

14|1|Updated May 5, 2026
Search Tags:#indexing#postgresql#partitioning#jsonb#autovacuum#explain analyze#bulk load
One-click install
npx skills add https://github.com/ivanshamaev/de-agent-skills --skill postgresql-data-engineering
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-data-engineering
Source: https://github.com/ivanshamaev/de-agent-skills/tree/main/skills/postgresql_de
Command: npx skills add https://github.com/ivanshamaev/de-agent-skills --skill postgresql-data-engineering

SYSTEM DOCUMENTATION & REQUIREMENTS

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.

Frequently Asked Questions about postgresql-data-engineering

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I choose between RANGE, LIST, and HASH partitioning strategies in PostgreSQL?▼

PostgreSQL indexing strategies depend on data shape: B-Tree handles general access, BRIN optimizes time-correlated data, and GIN accelerates JSONB containment queries. Partial and covering indexes further reduce I/O by targeting specific subsets and avoiding table lookups.

What is the best way to perform a bulk load in PostgreSQL without causing bloat?▼

Bulk loading in PostgreSQL is best achieved using COPY with UNLOGGED staging tables to bypass WAL overhead. After loading, tune autovacuum settings to manage dead tuples and control bloat, ensuring sustained write health for analytical pipelines.

How do I read EXPLAIN ANALYZE output to diagnose slow PostgreSQL query latency?▼

Reading EXPLAIN ANALYZE involves checking the query plan for sequential scans, high cost estimates, and actual execution times. Pinpointing scan and index issues allows you to apply missing indexes or rewrite advanced SQL patterns like LATERAL joins to resolve latency.

Can I use JSONB operations and window functions together for pipeline transformations?▼

JSONB operations and window functions can be combined in PostgreSQL to build robust in-place transformations. This approach leverages GIN indexes for JSONB containment while using window functions to compute analytical aggregations across partitioned result sets.

When should I use BRIN indexes instead of B-Tree for large fact tables?▼

BRIN indexes should be used instead of B-Tree for large fact tables when data is naturally time-correlated, as BRIN stores only block range summaries. This drastically reduces index size and I/O for time-series events, whereas B-Tree is better for general point lookups.

Related Skills

Explore More Software Engineering Skills→
syanixsyanix

database-guidelines

Provide PostgreSQL schema design, indexing, and performance guidelines.

Community
Advanced
sumeetonline90sumeetonline90

clickhouse-io

Optimize ClickHouse schemas, queries, and data ingestion for OLAP workloads.

Community
Intermediate
Mercurium-AnalyticsMercurium-Analytics

pg-partman-partitioning

Automate declarative partitioning for large PostgreSQL tables with pg_partman.

Official
Advanced
aaioneaaione
25

clickhouse-io

Optimize ClickHouse OLAP workloads with MergeTree table design and query patterns.

Community
Advanced