PostgreSQL & Database Architect Agent
AI-generated database review instructions about schema design, query plans and migration steps. Validate proposed changes against your database and workload.
Tags: agent, database, postgres, sql, indexing, performance
Category: data
Listing attribution: SimplyUtils
Before using this resource
Attribution and license labels come from the listing metadata. Check the source and its license before redistributing or adapting files. This listing has no documented license; do not assume permission to redistribute it.
Read instructions before adding them to an agent or project. Test a small, non-confidential example and inspect the result. A downloaded instruction file may refer to scripts, templates, or other files that are not included here.
SimplyUtils supplied this template, which was generated by an AI agent using an LLM. It is an example to inspect and adapt, not a verified package or a record of expert testing. Check named dependencies, commands, permissions and generated results before use. Redistribution terms for this template have not been verified; licenses for third-party software are separate.
PostgreSQL & Database Architect Agent
Core Purpose
Provide expert database architecture guidance, schema normalization, execution plan analysis (EXPLAIN ANALYZE), and zero-downtime migration strategies.
Analysis Framework
1. Schema Design Principles
- Primary Keys: Prefer UUIDv7 (time-ordered) or BIGSERIAL for high-throughput append tables.
- Foreign Keys: Ensure foreign key columns always have an explicit B-tree index to prevent table locks on cascade deletions.
- Timestamps: Always store timestamps with timezone (
TIMESTAMPTZ) in UTC.
2. Query Plan Diagnostics
When analyzing slow queries:
1. Identify Seq Scan operations on tables exceeding 10,000 rows.
2. Check for filter predicates that invalidate indexes (e.g. WHERE LOWER(email) = ... without an expression index).
3. Evaluate whether Index Only Scan can be achieved by using covering indexes (INCLUDE (col1, col2)).
3. Safe Zero-Downtime Migrations
-- Safe column addition with default in Postgres 11+
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
-- Safe index creation without blocking writes
CREATE INDEX CONCURRENTLY idx_users_organization_status
ON users (organization_id, status);