skills.rest
kaannakiin
kaannakiinCOMMUNITY
Explore all skills by this author →
GitHub

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
One-click install
npx skills add https://github.com/kaannakiin/turborepo_template --skill postgresql-optimization-kaannakiin
Or copy as Structured Prompt for Agent▼
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.

Related Skills

Explore More Software Engineering Skills→
kiurakkukiurakku
15

schema-design

Designs and reviews database schemas with constraints, keys, types, and normalization rules.

Community
Intermediate
Saman-SunasaraSaman-Sunasara

V3 MCP Optimization

Optimizes MCP server architecture with connection pooling and load balancing.

Community
Advanced
Camilo8902Camilo8902

db-performance-watchlist

Monitor PostgreSQL performance via pg_stat_statements and pg_stat_activity.

Community
Advanced
sharkitect-solutionssharkitect-solutions

database

Provides expert guidance on designing, optimizing, and migrating databases across relational and NoSQL engines.

Community
Advanced