skills.rest
afonsoft
afonsoftCOMMUNITY
Explore all skills by this author →
Website𝕏@afonsoftGitHub

postgresql-optimization

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

Updated Jul 21, 2026
Search Tags:#query optimization#indexing#postgresql#sql#full-text search#database performance#jsonb
One-click install
npx skills add https://github.com/afonsoft/gamehub --skill postgresql-optimization-afonsoft
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-optimization
Source: https://github.com/afonsoft/gamehub/tree/main/.claude/skills/postgresql-optimization
Command: npx skills add https://github.com/afonsoft/gamehub --skill postgresql-optimization-afonsoft

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing and tuning PostgreSQL queries that leverage the database's unique features is difficult, and generic SQL advice often misses PostgreSQL-specific capabilities like JSONB operators, GIN indexes, and range types, leading to slow queries and underused features. ## Core Features & Use Cases - PostgreSQL-Specific Query Patterns: Provides idiomatic SQL for JSONB operations, array types, window functions, full-text search, custom types, and range/geometric types. - Performance Tuning Guidance: Covers EXPLAIN ANALYZE interpretation, index strategies (composite, partial, covering, GIN/GiST), pg_stat_statements analysis, and connection/memory configuration. - Optimization Checklists: Supplies structured review lists for query analysis, index strategy, security, and monitoring, plus good-vs-bad pattern comparisons for pagination, aggregation, and JSON queries. - Use Case: A developer notices a slow endpoint backed by a JSONB query. Use this Skill to rewrite the query with proper JSONB operators, add a GIN index, and verify the improvement with EXPLAIN ANALYZE. ## Quick Start Ask the assistant to review and optimize your PostgreSQL query or schema, for example by requesting an index strategy and rewritten SQL for a slow JSONB lookup.

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, poor join orders, and missing indexes. Then add appropriate indexes such as composite, partial, or covering indexes, and verify the improvement by re-running the analysis.

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 so containment queries use the index rather than scanning the table.

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 vectors, and GiST for geometric and range exclusion constraints. Partial and covering indexes further reduce cost for filtered or read-heavy queries.

Does PostgreSQL support full-text search natively?▼

Yes, PostgreSQL includes built-in full-text search using tsvector and tsquery. Store a generated tsvector column, index it with GIN, and query with plainto_tsquery while ranking results with ts_rank.

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 a WHERE clause on the last seen key with ORDER BY and LIMIT keeps performance constant.

When should I use table partitioning in PostgreSQL?▼

Use declarative partitioning when tables grow very large and queries consistently filter on a partition key such as date. It improves query pruning and maintenance operations, but adds complexity that is unnecessary for smaller tables.

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
vix-4800vix-4800
1

database-patterns

Review database schemas, migrations, and ORM query strategies for safe data evolution.

Community
Intermediate
InteligentsensingsolutionsInteligentsensingsolutions
1

postgres-best-practices

Optimize PostgreSQL performance with EXPLAIN ANALYZE, indexing, and connection pooling guidance.

Official
Advanced
seb1nseb1n
147

query-optimization

Analyze EXPLAIN plans and rewrite slow SQL queries for better performance.

Community
Advanced