Agent Skills
› kinncj/Heimdall
› postgresql-patterns
postgresql-patterns
GitHub提供PostgreSQL模式、迁移和查询的最佳实践，包括RLS配置、索引优化、PgBouncer连接池及幂等SQL规范，适用于数据库架构设计与迁移开发。
Trigger Scenarios
编写或修改PostgreSQL数据库表结构
生成或执行数据库迁移脚本
优化慢查询或设计数据库索引
配置PostgreSQL连接池
Install
npx skills add kinncj/Heimdall --skill postgresql-patterns -g -y
SKILL.md
Frontmatter
{
"name": "postgresql-patterns",
"description": "Apply PostgreSQL schema, migration, and query patterns with idempotent SQL. Use when writing database schemas or migrations."
}
SKILL: PostgreSQL Patterns
Migration Naming
migrations/
V001__create_users.sql # Flyway
0001_create_users.sql # Generic
20240115_create_users.sql # Timestamp-based
Table Pattern with RLS
-- Create table
CREATE TABLE IF NOT EXISTS users (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
email text UNIQUE NOT NULL,
name text NOT NULL,
role text NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
created_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL
);
-- Indexes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_role ON users(role) WHERE role != 'user';
-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
-- RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_select_own ON users
FOR SELECT USING (id = current_user_id());
CREATE POLICY users_admin_all ON users
FOR ALL USING (current_user_role() = 'admin');
Query Optimization
-- Always EXPLAIN ANALYZE in development
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.id;
-- Partial index for common filter
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- GIN index for full-text search
CREATE INDEX idx_products_search
ON products USING gin(to_tsvector('english', name || ' ' || description));
Connection Pooling (PgBouncer)
# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction # transaction pooling for most apps
max_client_conn = 100
default_pool_size = 20
Rules
- NEVER modify existing migration files.
- Always create new migration for schema changes.
- Index all foreign keys.
- Use CONCURRENTLY for index creation on large tables.
- EXPLAIN ANALYZE all queries > 100ms.
- Enable RLS on tables with user data.
Version History
- f4ea31f Current 2026-07-05 10:42
