AGENT SKILL

postgres-patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

License 许可
MIT
最近更新
2026-11-10
Stars
★ 1
Forks
0

01 前言 Front Matter

name
postgres-patterns
description
PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

03 SKILL.md 正文

PostgreSQL 模式

PostgreSQL 最佳實務快速參考。詳細指南請使用 database-reviewer agent。

何時啟用

  • 撰寫 SQL 查詢或 migrations
  • 設計資料庫 schema
  • 疑難排解慢查詢
  • 實作 Row Level Security
  • 設定連線池

快速參考

索引速查表

查詢模式 索引類型 範例
WHERE col = value B-tree(預設) CREATE INDEX idx ON t (col)
WHERE col > value B-tree CREATE INDEX idx ON t (col)
WHERE a = x AND b > y 複合 CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}' GIN CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ query GIN CREATE INDEX idx ON t USING gin (col)
時間序列範圍 BRIN CREATE INDEX idx ON t USING brin (col)

資料類型快速參考

使用情況 正確類型 避免
IDs bigint int、隨機 UUID
字串 text varchar(255)
時間戳 timestamptz timestamp
金額 numeric(10,2) float
旗標 boolean varchar、int

常見模式

複合索引順序:

-- 等值欄位優先,然後是範圍欄位
CREATE INDEX idx ON orders (status, created_at);
-- 適用於:WHERE status = 'pending' AND created_at > '2024-01-01'

覆蓋索引:

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- 避免 SELECT email, name, created_at 時的表格查詢

部分索引:

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- 更小的索引,只包含活躍使用者

RLS 政策(優化):

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- 用 SELECT 包裝!

UPSERT:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

游標分頁:

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET 是 O(n)

佇列處理:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

反模式偵測

-- 找出未建索引的外鍵
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- 找出慢查詢
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- 檢查表格膨脹
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

設定範本

-- 連線限制(依 RAM 調整)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- 逾時
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- 監控
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 安全預設值
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

相關

  • Agent:database-reviewer - 完整資料庫審查工作流程
  • Skill:clickhouse-io - ClickHouse 分析模式
  • Skill:backend-patterns - API 和後端模式

基於 [Supabase Agent Skills](Supabase Agent Skills (credit: Supabase team))(MIT 授權)

04 同仓库更多 Skills

YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

vercel-sandbox

Run agent-browser + Chrome inside Vercel Sandbox microVMs for browser automation from any Vercel-deployed app. Use when the user needs browser automation in a Vercel app (Next.js, SvelteKit, Nuxt, Remix, Astro, etc.), wants to run headless Chrome without binary size limits, needs persistent browser sessions across commands, or wants ephemeral isolated browser environments. Triggers include "Vercel Sandbox browser", "microVM Chrome", "agent-browser in sandbox", "browser automation on Vercel", or any task requiring Chrome in a Vercel Sandbox.

★ 1
YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

slack

Interact with Slack workspaces using browser automation. Use when the user needs to check unread channels, navigate Slack, send messages, extract data, find information, search conversations, or automate any Slack task. Triggers include "check my Slack", "what channels have unreads", "send a message to", "search Slack for", "extract from Slack", "find who said", or any task requiring programmatic Slack interaction.

★ 1
YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

electron

Automate Electron desktop apps (VS Code, Slack, Discord, Figma, Notion, Spotify, etc.) using agent-browser via Chrome DevTools Protocol. Use when the user needs to interact with an Electron app, automate a desktop app, connect to a running app, control a native app, or test an Electron application. Triggers include "automate Slack app", "control VS Code", "interact with Discord app", "test this Electron app", "connect to desktop app", or any task requiring automation of a native Electron application.

★ 1
YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

dogfood

Systematically explore and test a web application to find bugs, UX issues, and other problems. Use when asked to "dogfood", "QA", "exploratory test", "find issues", "bug hunt", "test this app/site/platform", or review the quality of a web application. Produces a structured report with full reproduction evidence -- step-by-step screenshots, repro videos, and detailed repro steps for every issue -- so findings can be handed directly to the responsible teams.

★ 1
YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

agent-browser

Browser automation CLI for AI agents. Use when the user needs to interact with websites, including navigating pages, filling forms, clicking buttons, taking screenshots, extracting data, testing web apps, or automating any browser task. Triggers include requests to "open a website", "fill out a form", "click a button", "take a screenshot", "scrape data from a page", "test this web app", "login to a site", "automate browser actions", or any task requiring programmatic web interaction.

★ 1
YYC-Cube
YYC-Cube/YYC3-Knowledge-Base

shadcn

Manages shadcn components and projects — adding, searching, fixing, debugging, styling, and composing UI, including chat interfaces. Provides project context, component docs, and usage examples. Applies when working with shadcn/ui, component registries, presets, --preset codes, or any project with a components.json file. Also triggers for "shadcn init", "create an app with --preset", "switch to --preset", and for building a design system or applying a DESIGN.md or brand spec to shadcn/ui.

★ 1