postgres-pro

To optimize slow PostgreSQL queries, you must analyze the execution plan using EXPLAIN ANALYZE to identify bottlenecks like sequential scans. This skill examines query plans and recommends appropriate indexing strategies, such as B-tree, GIN, or BRIN indexes, to improve data retrieval latency.

Managing JSONB operations and configuring PostgreSQL extensions involves using specialized indexing like GIN for document data. This skill provides guidance on handling JSONB operations efficiently and configuring advanced extensions such as PostGIS or pgvector for geospatial or vector similarity searches.

Setting up streaming or logical replication for PostgreSQL databases requires configuring replication slots and managing WAL files. This skill implements replication strategies to satisfy high-availability requirements and ensure low-latency data access across database instances.

PostgreSQL table bloat happens due to dead tuples left by updates and deletes, which you resolve by automating the vacuuming process. This skill monitors database health via pg_stat views and automates maintenance to resolve table bloat and manage storage efficiently.

You need GIN or BRIN indexes instead of B-tree indexes when handling JSONB operations or large sorted physical datasets. This skill analyzes query patterns to design appropriate indexing strategies, applying GIN for composite values and BRIN for block-level range scans.