skills.rest
PremModhaOfficial
PremModhaOfficialCOMMUNITY
Explore all skills by this author →
GitHub

pg-analytics-duckdb

Route GROUP BY and time-series aggregation queries to DuckDB via pg_duckdb in PostgreSQL 18.

1|Updated Apr 17, 2026
Search Tags:#analytics#duckdb#group_by#pg_duckdb#force_duckdb_execution#acquired-connection#postgresql-18
One-click install
npx skills add https://github.com/PremModhaOfficial/motadata-ai-pipeline --skill pg-analytics-duckdb
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: pg-analytics-duckdb
Source: https://github.com/PremModhaOfficial/motadata-ai-pipeline/tree/main/.claude/skills/pg-analytics-duckdb
Command: npx skills add https://github.com/PremModhaOfficial/motadata-ai-pipeline --skill pg-analytics-duckdb

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

DAL workloads with GROUP BY and time-series analytics can be slow on PostgreSQL; this skill routes such queries to DuckDB via the pg_duckdb extension in PostgreSQL 18 to accelerate analytics while preserving transactional session isolation.

Core Features & Use Cases

  • DuckDB-enabled analytics routing: automatically sends aggregation queries to DuckDB for faster compute.
  • Acquired connection pattern: uses a dedicated connection per tenant to avoid polluting the shared pool's session state.
  • Dockerized setup guidance: includes steps to enable pg_duckdb in PostgreSQL 18 and run with a DuckDB-backed database.
  • Use Case: dashboards performing daily aggregates on large datasets can be served with reduced latency.

Quick Start

Configure the data-access-layer to route analytics queries to DuckDB behind pg_duckdb and run a sample aggregation.

Frequently Asked Questions about pg-analytics-duckdb

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

FAQPage Schema
How do I route PostgreSQL GROUP BY queries to DuckDB for faster analytics?▼

Route PostgreSQL GROUP BY queries to DuckDB by applying an acquired-connection pattern and session-scoped force_duckdb_execution via the pg_duckdb extension in PostgreSQL 18.

What is the acquired-connection pattern for pg_duckdb execution?▼

The acquired-connection pattern uses a dedicated connection per tenant to execute DuckDB queries, avoiding shared pool session state pollution while maintaining transactional isolation in PostgreSQL 18.

Why are time-series aggregations slow on PostgreSQL and how does DuckDB help?▼

Time-series aggregations are slow on PostgreSQL due to row-based processing; routing them to DuckDB via pg_duckdb leverages columnar MsgPack encoding and faster compute for reduced latency.

Does pg_duckdb work with PostgreSQL 18 to accelerate dashboard aggregations?▼

Yes, pg_duckdb works with PostgreSQL 18 by enforcing session-scoped extension setup to route daily dashboard aggregations on large datasets to DuckDB for accelerated columnar analytics.

How do I enable the pg_duckdb extension in a PostgreSQL 18 Docker container?▼

Enable pg_duckdb in PostgreSQL 18 by following Dockerized setup guidance to configure the extension and run a DuckDB-backed database for analytics query routing.

Can I use session-scoped force_duckdb_execution without polluting the shared connection pool?▼

Yes, the skill enforces session-scoped force_duckdb_execution using an acquired-connection pattern that isolates DuckDB execution state from the shared PostgreSQL pool sessions.

Related Skills

Explore More Data & Analytics Skills→
trusted-americantrusted-american

neon-postgres

Sync Firestore clients, deals, and invoices to Neon PostgreSQL with pgvector.

Official
Advanced
xukrutdonutxukrutdonut

duckdb-en

Run SQL queries on CSV, Parquet, and JSON files using the DuckDB CLI.

Community
Advanced
CJHarmathCJHarmath

postgres-performance

Optimize PostgreSQL queries and data access with indexing, partitioning, and EXPLAIN ANALYZE.

Community
Advanced
sumeetonline90sumeetonline90

clickhouse-io

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

Community
Intermediate