skills.rest
coagus
coagusCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

postgres-expert

Optimize and debug PostgreSQL 14+ code for PHP database interactions.

2|Updated Feb 28, 2024
Search Tags:#postgresql#indexes#query-tuning#uuid#jsonb#timestamps-tz#pdo-pgsql
One-click install
npx skills add https://github.com/coagus/php-api-builder --skill postgres-expert-coagus
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-expert
Source: https://github.com/coagus/php-api-builder/tree/main/.claude/skills/postgres-expert
Command: npx skills add https://github.com/coagus/php-api-builder --skill postgres-expert-coagus

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Provide expert guidance to write, optimize, and debug PostgreSQL 14+ code for PHP-based APIs, leveraging advanced features to improve performance, reliability, and correctness.

Core Features & Use Cases

  • JSONB, TIMESTAMPTZ, UUID, arrays, and ENUM types: design schemas and queries that maximize storage efficiency and query performance.
  • Partial/functional/GIN indexes and EXPLAIN ANALYZE: craft index strategies and analyze plans to minimize slow scans.
  • CTEs and window functions: build readable, efficient queries for complex aggregations and analytics.
  • RETURNING, upserts, and robust data modification patterns: implement safe write-heavy workflows with minimal round-trips.
  • Full-text search (tsvector) and row-level security: enable advanced search capabilities and fine-grained access control.
  • Partitioning and multi-database PDO pgsql driver specifics: scale data architectures and ensure portable, driver-aware code.
  • Practical best-practices for PostgreSQL-driven PHP apps: guidance that translates into production-ready implementations.

Quick Start

Provide a PostgreSQL optimization and debugging plan using 14+ features for PHP-based API projects.

Frequently Asked Questions about postgres-expert

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

FAQPage Schema
How do I optimize slow PostgreSQL queries using EXPLAIN ANALYZE and advanced indexes?▼

Optimize PostgreSQL queries by analyzing execution plans with EXPLAIN ANALYZE and applying partial, functional, or GIN indexes to minimize slow sequential scans. This Skill provides expert guidance on crafting index strategies tailored for PostgreSQL 14+.

What is the best way to store and query JSONB data in PostgreSQL for PHP applications?▼

Store and query JSONB data in PostgreSQL by designing schemas that maximize storage efficiency and leveraging GIN indexes for fast retrieval. This Skill offers PostgreSQL-specific practices for robust JSONB interactions within PHP-based APIs.

How do I perform upserts and use RETURNING in PostgreSQL to minimize database round-trips?▼

Perform upserts and use the RETURNING clause in PostgreSQL to implement safe write-heavy workflows with minimal round-trips. This approach ensures robust data modification patterns for PHP-based database interactions.

Does the PDO pgsql driver support PostgreSQL 14+ features like arrays and TIMESTAMPTZ?▼

The PDO pgsql driver supports PostgreSQL 14+ features including arrays, TIMESTAMPTZ, and UUIDs. This Skill provides driver-aware code specifics to ensure portable and robust database interactions across multi-database architectures.

When should I use table partitioning and row-level security in PostgreSQL?▼

Use table partitioning to scale large data architectures and row-level security to enable fine-grained access control. This Skill guides you on applying these advanced PostgreSQL features for production-ready implementations.

How do I implement full-text search with tsvector in PostgreSQL?▼

Implement full-text search in PostgreSQL by utilizing tsvector capabilities to enable advanced search functionalities. This Skill provides expert guidance on integrating these features effectively into your database design.

Related Skills

Explore More Software Engineering Skills→
ortus-boxlangortus-boxlang

bx-jdbc

Select and install the correct BoxLang JDBC driver module for a database.

Official
Intermediate
francanetefrancanete
1

postgres-query-optimization

Analyzes slow PostgreSQL queries using EXPLAIN ANALYZE to recommend index and query optimizations.

Community
Advanced
latchagentlatchagent
2

adding-sqlite-stores

Add SQLite data stores with idempotent migrations and JSON serialization.

Official
Advanced
rnavarychrnavarych
14

role-database:newsql-distributed

Compare 12 NewSQL and distributed SQL databases for consistency and sharding.

Community
Advanced