skills.rest
LeoTechPro
LeoTechProCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

postgres

Guide PostgreSQL schema design, query optimization, and operational best practices.

1|Updated Nov 13, 2023
Search Tags:#schema design#database#postgresql#optimization#sql#replication#performance tuning
One-click install
npx skills add https://github.com/LeoTechPro/intTools --skill postgres-leotechpro
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres
Source: https://github.com/LeoTechPro/intTools/tree/main/codex/assets/codex-home/skills/postgres
Command: npx skills add https://github.com/LeoTechPro/intTools --skill postgres-leotechpro

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) components.

What problem does it solve?

This Skill provides comprehensive guidance and tools for designing, optimizing, and operating PostgreSQL databases, ensuring high performance, reliability, and scalability.

Core Features & Use Cases

  • Schema Design & Migrations: Best practices for creating normalized schemas and managing database changes with Alembic.
  • Query Optimization: Techniques for analyzing and improving slow queries using EXPLAIN ANALYZE and indexing strategies.
  • Database Engineering: Guidance on replication, high availability, backup/recovery, and performance tuning.
  • Use Case: A developer needs to optimize a slow-running report query. They can use this Skill to analyze the query plan, identify missing indexes, and implement the necessary changes for significant performance gains.

Quick Start

Use the postgres skill to analyze the performance of the query 'SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day''.

Frequently Asked Questions about postgres

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

FAQPage Schema
How do I optimize a slow PostgreSQL query?▼

To optimize a slow PostgreSQL query, use `EXPLAIN ANALYZE` to analyze the query plan and identify missing indexes. This Skill guides you through indexing strategies and performance tuning techniques to achieve significant query execution gains.

What is the best way to design a PostgreSQL schema and manage migrations?▼

Designing a PostgreSQL schema involves applying normalization best practices, while migrations can be managed using tools like Alembic. This Skill provides expert guidance on creating reliable schemas and handling database changes efficiently.

How does PostgreSQL replication work for high availability?▼

PostgreSQL replication ensures high availability by copying data across multiple database instances. This Skill covers replication configurations, backup, and recovery operations to maintain database reliability and scalability during failures.

Can I use JSONB and full-text search in PostgreSQL database engineering?▼

Yes, PostgreSQL supports advanced features like JSONB for unstructured data and full-text search for text querying. This Skill provides expert-level guidance on implementing and optimizing these features within your database engineering workflow.

When do I need table partitioning in PostgreSQL?▼

Table partitioning in PostgreSQL is needed when managing large tables to improve query performance and data maintenance. This Skill explains partitioning strategies alongside other operational best practices for scalable database design.

Why does my PostgreSQL database need performance tuning?▼

PostgreSQL performance tuning is required to resolve bottlenecks, optimize indexing strategies, and improve query execution times. This Skill helps analyze query plans and implement operational best practices for high-performance database systems.

Related Skills

Explore More Software Engineering Skills→
kubiyabotkubiyabot
16

postgres

Run PostgreSQL queries and admin tasks via the psql CLI.

Official
Advanced
Ikram-AlamIkram-Alam

operating-k8s-local

Provision and operate local Kubernetes clusters with Minikube.

Community
Advanced
NelkitNelkit
1

alembic-migration

Automate PostgreSQL schema changes with SQLAlchemy ORM and Alembic migrations.

Community
Intermediate
TNHoang2708TNHoang2708
1

ck:databases

Manage MongoDB and PostgreSQL databases with schema design, querying, and performance tuning.

Community
Advanced