postgresql

Provide PostgreSQL optimization rules covering data types, indexing, JSONB, and partitioning.

1|Updated Mar 16, 2026

Search Tags:#indexing #database #postgresql #sql #partitioning #performance tuning #jsonb

Ready to run

npx skills add https://github.com/iceflower/opencode-agents-and-skills --skill postgresql-iceflower

💬 Prompt for ChatGPT / Claude Chat▼

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

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill provides comprehensive guidance on leveraging PostgreSQL's advanced features to optimize database performance, ensure data integrity, and write efficient SQL queries.

Core Features & Use Cases

Advanced Data Types: Utilize specialized types like JSONB, UUID, and arrays for efficient data modeling.
Indexing Strategies: Implement B-tree, GIN, GiST, and BRIN indexes for faster data retrieval.
Partitioning: Design and manage partitioned tables for improved query performance on large datasets.
Performance Tuning: Apply best practices for VACUUM, connection pooling, and query analysis.
Use Case: Optimize a high-traffic e-commerce database by implementing JSONB for product metadata, GIN indexes for searching, and range partitioning for order history.

Quick Start

Use the postgresql skill to create a partial index on the 'email' column for active users in the 'users' table.

Frequently Asked Questions about postgresql

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

FAQPage Schema

How do I optimize PostgreSQL query performance for large datasets?▼

Optimize PostgreSQL queries by applying proper indexing strategies, partitioning large tables, and utilizing performance tuning practices like VACUUM and query analysis for efficient data retrieval.

What is the best way to use JSONB for storing and searching product metadata?▼

JSONB allows efficient storage and querying of product metadata in PostgreSQL. Combine it with GIN indexes to significantly speed up complex search operations across the JSON document structure.

How do I create a partial index on active users in a PostgreSQL table?▼

Create a partial index by applying a WHERE clause to the CREATE INDEX statement, targeting only active users. This reduces index size and improves query performance for that specific subset.

When should I use table partitioning in PostgreSQL?▼

Use PostgreSQL table partitioning, such as range partitioning for order history, to improve query performance and manage large datasets by dividing them into smaller, more manageable physical blocks.

Does this PostgreSQL tuning guide cover connection pooling and extensions?▼

Yes, it covers connection pooling for managing high traffic and explores PostgreSQL extensions, alongside CTEs, advanced data types like UUID and arrays, and comprehensive performance tuning.

Which index type should I use for full-text search in PostgreSQL?▼

For full-text search or JSONB querying in PostgreSQL, GIN indexes are highly recommended. They efficiently handle complex queries on composite data types compared to standard B-tree indexes.