postgresql-optimization

Optimizes PostgreSQL queries, indexes, and schemas using JSONB, arrays, and window functions.

Updated Jul 25, 2026

Search Tags:#query optimization #indexing #postgresql #sql #performance tuning #full-text search #jsonb

Ready to run

npx skills add https://github.com/kaannakiin/turborepo_template --skill postgresql-optimization-kaannakiin

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgresql-optimization
Source: https://github.com/kaannakiin/turborepo_template/tree/main/.agents/skills/postgresql-optimization
Command: npx skills add https://github.com/kaannakiin/turborepo_template --skill postgresql-optimization-kaannakiin

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing and tuning PostgreSQL queries that leverage the database's unique features is difficult, and developers often miss performance gains from proper indexing, JSONB operators, and PostgreSQL-specific data types. ## Core Features & Use Cases - Query Performance Tuning: Analyze queries with EXPLAIN ANALYZE, pg_stat_statements, and targeted index strategies including partial, covering, and GIN indexes. - PostgreSQL-Specific Features: Guidance on JSONB operations, array types, range and geometric types, full-text search, window functions, and custom domains. - Extensions & Maintenance: Covers useful extensions like pg_trgm and pgcrypto, plus VACUUM, index bloat monitoring, and connection management. - Use Case: A developer notices a slow endpoint querying a JSONB column with LIKE. Use this Skill to rewrite it with the @> containment operator and a GIN index, cutting execution time dramatically. ## Quick Start Ask the assistant to review and optimize your slow PostgreSQL query or schema using PostgreSQL-specific features and indexing strategies.

Frequently Asked Questions about postgresql-optimization

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

FAQPage Schema

How do I optimize a slow PostgreSQL query?▼

Start by running EXPLAIN (ANALYZE, BUFFERS) on the query to identify sequential scans and expensive joins. Then add appropriate indexes such as composite, partial, or covering indexes, and check pg_stat_statements for the highest total_time queries.

How to query JSONB data efficiently in PostgreSQL?▼

Use JSONB operators like @> for containment and #>> for path extraction instead of casting to text with LIKE. Create a GIN index on the JSONB column to make containment and key-existence queries fast.

What index type should I use in PostgreSQL?▼

Use B-tree for standard equality and range queries, GIN for JSONB, arrays, and full-text search, and GiST for geometric and range exclusion constraints. Partial and covering indexes help for filtered or read-heavy workloads.

Does PostgreSQL support full-text search natively?▼

Yes, PostgreSQL includes built-in full-text search using tsvector and tsquery. Store a generated search vector, index it with GIN, and rank results with ts_rank for relevance ordering.

Why is OFFSET pagination slow on large tables?▼

OFFSET forces PostgreSQL to scan and discard all skipped rows, so cost grows linearly with page depth. Cursor-based pagination using WHERE id > last_id with an index keeps performance constant regardless of position.