skills.rest
aaaa47080
aaaa47080COMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

Apply PostgreSQL data type, indexing, and query patterns to optimize OLTP performance.

4|Updated Dec 5, 2025
Search Tags:#anti-patterns#indexing#postgresql#query-performance#data-types#sql-patterns#database-optimization
One-click install
npx skills add https://github.com/aaaa47080/stock_agent --skill postgres-patterns-aaaa47080
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/aaaa47080/stock_agent/tree/main/.opencode/skills/postgres-patterns
Command: npx skills add https://github.com/aaaa47080/stock_agent --skill postgres-patterns-aaaa47080

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

PostgreSQL patterns address common performance pitfalls in database design and query execution, such as inappropriate data types, missing or poorly used indexes, and risky anti-patterns, offering practical guidance to improve reliability and speed.

Core Features & Use Cases

  • Data Types: use appropriate types (NUMERIC vs REAL for money, TIMESTAMPTZ vs TIMESTAMP, TEXT for IDs, VARCHAR(255) for emails, BOOLEAN defaults, JSONB for metadata)
  • Index Strategy: primary keys and foreign keys should be indexed; use partial unique indexes; index timestamps for time-series workloads
  • Query Patterns: parameterized queries, explain analyze for slow queries, limit on list endpoints, and queue processing with FOR UPDATE SKIP LOCKED
  • Connection Pooling: pool size and timeouts configurable via environment variables; Neon pooler support
  • Anti-Patterns to Avoid: avoid N+1 queries, avoid SELECT *; ensure indexes exist; avoid storing secrets in DB; avoid using real numbers for money

Quick Start

Apply these patterns to your PostgreSQL schema and queries to improve performance and reliability.

Frequently Asked Questions about postgres-patterns

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

FAQPage Schema
What are common PostgreSQL anti-patterns that slow down query performance?▼

PostgreSQL query performance anti-patterns include N+1 queries, using SELECT *, missing indexes on foreign keys, and using real numbers for money. Avoiding these patterns ensures faster query execution and safer database operations.

How do I choose the right PostgreSQL data types for schema optimization?▼

To optimize PostgreSQL schema design, use NUMERIC instead of REAL for money, TIMESTAMPTZ for timestamps, JSONB for metadata, and appropriate VARCHAR lengths for emails. Correct data types reduce storage overhead and improve query speed.

What is the best indexing strategy for PostgreSQL time-series workloads?▼

For PostgreSQL time-series workloads, index timestamp columns and ensure primary keys and foreign keys are indexed. Using partial unique indexes further optimizes data retrieval for time-specific queries.

How do I use FOR UPDATE SKIP LOCKED for queue processing in PostgreSQL?▼

Use the FOR UPDATE SKIP LOCKED query pattern in PostgreSQL to process queues concurrently. This mechanism locks available rows for update while skipping locked rows, ensuring safe parallel queue processing.

Does this approach support connection pooling for OLTP workloads?▼

Yes, these PostgreSQL patterns support OLTP workloads by configuring connection pool sizes and timeouts via environment variables. This includes specific support for Neon pooler to manage concurrent database connections efficiently.

Why should I avoid SELECT * in PostgreSQL queries?▼

You should avoid SELECT * in PostgreSQL because it retrieves unnecessary columns, increasing memory usage and network latency. Specifying exact columns improves query performance and ensures safer schema evolution.

Related Skills

Explore More Software Engineering Skills→
gazollagazolla

supabase-jdbc

Connect Java applications to Supabase PostgreSQL via JDBC with HikariCP pooling.

Community
Advanced
DVNghiemDVNghiem
20

postgres-patterns

Optimize PostgreSQL schema, indexing and query performance with proven patterns.

Community
Intermediate
HafizFasihHafizFasih

neon-db-expert

Enforce Neon pooler usage and verify schemas via MCP before migrations.

Community
Advanced
Likas07Likas07

database-optimizer

Analyze SQL queries and schema design for PostgreSQL, MySQL, Supabase, and PlanetScale.

Community
Advanced