specsfy-specialist-postgres

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL

Quando usar

适用场景

Acionar para desenhar schema, escrever ou revisar SQL, escolher índice, analisar plano (
```
EXPLAIN
```
), diagnosticar lock/deadlock, planejar migration ou dimensionar backup/restore em Postgres.
Acionar também quando um ORM (Eloquent, Prisma, Drizzle) gerar SQL ineficiente e a causa raiz for modelagem ou índice, não a API do ORM.
Não acionar para decisões específicas de Supabase (RLS, Auth, Realtime, Edge Functions) — usar
```
$specsfy-specialist-supabase
```
, que aplica Postgres por baixo com essas camadas adicionais.
Combinar com
```
$specsfy-specialist-laravel
```
quando o ponto de entrada for Eloquent e com
```
$specsfy-specialist-performance-engineering
```
quando o gargalo abranger além do banco (aplicação, rede, cache).

适用于Postgres中的Schema设计、SQL编写或审核、索引选择、执行计划分析（
```
EXPLAIN
```
）、锁/死锁诊断、迁移规划或备份/恢复容量规划场景。
当ORM（Eloquent、Prisma、Drizzle）生成低效SQL，且根源在于数据建模或索引问题而非ORM API时，也可启用此技能。
请勿用于Supabase的特定功能决策（RLS、Auth、Realtime、Edge Functions）——请使用
```
$specsfy-specialist-supabase
```
，该技能会在这些附加层之下应用Postgres相关能力。
当入口为Eloquent时，可结合
```
$specsfy-specialist-laravel
```
使用；当性能瓶颈超出数据库范围（应用、网络、缓存）时，可结合
```
$specsfy-specialist-performance-engineering
```
使用。

Fluxo

流程

Descobrir versão do Postgres, extensões instaladas, volume atual, crescimento esperado, workload (OLTP, analítico, misto) e quem é o owner dos dados antes de recomendar.
Modelar invariantes com tipos precisos,
```
NOT NULL
```
,
```
CHECK
```
,
```
UNIQUE
```
, chaves estrangeiras e normalização adequada ao caso de uso.
Escrever a consulta mais simples que expressa a regra e medir o plano real com
```
EXPLAIN (ANALYZE, BUFFERS)
```
sobre dados representativos, nunca sobre uma tabela vazia ou de desenvolvimento.
Selecionar índice pelo workload observado (predicados do
```
WHERE
```
,
```
ORDER BY
```
,
```
JOIN
```
) — nunca por "essa coluna é consultada" isoladamente.
Analisar isolation level, duração de transação, ordem de aquisição de locks e concorrência esperada sob a carga real.
Planejar a migration com compatibilidade entre a versão antiga e nova da aplicação durante o deploy, e um caminho de rollback testável.
Validar backup, restore, monitoramento e capacidade no ambiente alvo antes de declarar a mudança pronta para produção.

在给出建议前，先了解Postgres版本、已安装扩展、当前数据量、预期增长、工作负载（OLTP、分析型、混合型）以及数据所有者信息。
使用精准数据类型、
```
NOT NULL
```
、
```
CHECK
```
、
```
UNIQUE
```
、外键和适配业务场景的规范化设计来定义数据约束。
编写最简洁的查询语句表达业务规则，并在具有代表性的真实数据上使用
```
EXPLAIN (ANALYZE, BUFFERS)
```
分析实际执行计划，切勿在空表或开发环境表上测试。
根据实际观测到的工作负载（
```
WHERE
```
条件、
```
ORDER BY
```
、
```
JOIN
```
关联）选择索引——切勿仅凭“该列被查询”就单独创建索引。
分析隔离级别、事务时长、锁获取顺序以及真实负载下的预期并发情况。
规划迁移方案，确保部署期间新旧版本应用的兼容性，并设计可测试的回滚路径。
在宣布变更可用于生产环境前，验证目标环境中的备份、恢复、监控能力。

Padrões

最佳实践

Preferir constraint do banco (
```
NOT NULL
```
,
```
CHECK
```
,
```
UNIQUE
```
, FK,
```
EXCLUDE
```
) para toda invariante que sempre deve valer — validação só na aplicação permite dado inconsistente por qualquer segundo caminho de escrita.
Evitar
```
SELECT *
```
em código de produção, tipos imprecisos (
```
text
```
para enum fechado,
```
float
```
para dinheiro) e índice redundante que duplica outro já existente com prefixo igual.
Nunca adicionar índice sem ler padrão de escrita, tamanho da tabela, seletividade do predicado e o plano antes/depois — índice mal escolhido piora escrita sem acelerar leitura.
Manter transação curta (evitar I/O externo, espera de usuário ou chamada de rede dentro dela) e ordem de aquisição de locks consistente entre todos os caminhos de código para evitar deadlock.
Rodar
```
EXPLAIN (ANALYZE, BUFFERS)
```
somente em ambiente onde executar a consulta de verdade é seguro (não em produção sem
```
ROLLBACK
```
/replica).
Aplicar expand/contract em mudança de schema incompatível ou em tabela de alto volume: nunca renomear/remover coluna lida em produção no mesmo passo que a adiciona.
Conceder o menor privilégio necessário e separar papéis de migration (DDL), aplicação (DML) e leitura (
```
SELECT
```
apenas) — a aplicação nunca conecta com um role que pode
```
DROP TABLE
```
.

对于所有必须始终生效的数据约束，优先使用数据库层面的约束（
```
NOT NULL
```
、
```
CHECK
```
、
```
UNIQUE
```
、外键、
```
EXCLUDE
```
）——仅在应用层做验证会导致其他写入路径产生不一致数据。
生产代码中避免使用
```
SELECT *
```
、不精准的数据类型（用
```
text
```
存储固定枚举值、用
```
float
```
存储金额）以及与现有索引前缀重复的冗余索引。
创建索引前，务必了解写入模式、表大小、条件选择性以及创建前后的执行计划——选择不当的索引会降低写入性能却无法加速读取。
保持事务简短（避免在事务中执行外部I/O、等待用户输入或调用网络接口），并确保所有代码路径的锁获取顺序一致，以避免死锁。
仅在可安全执行查询的环境中运行
```
EXPLAIN (ANALYZE, BUFFERS)
```
（无
```
ROLLBACK
```
或非副本的生产环境请勿执行）。
对不兼容的Schema变更或高流量表采用扩展/收缩策略：切勿在添加列的同时重命名/删除生产环境中正在被读取的列。
遵循最小权限原则，区分迁移（DDL）、应用（DML）和只读（仅
```
SELECT
```
）角色——应用程序绝不能使用拥有
```
DROP TABLE
```
权限的角色连接数据库。

Antipadrões

反模式

Índice criado por "essa coluna aparece no WHERE", ignorando seletividade — em coluna de baixa cardinalidade (booleano, status com poucos valores) o planner frequentemente prefere seq scan e o índice só custa em escrita.
```
ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT
```
em versão antiga do Postgres (< 11) reescrevendo a tabela inteira sob lock — em versões atuais isso é otimizado para
```
DEFAULT
```
constante, mas
```
DEFAULT
```
com função volátil ainda reescreve.
Transação longa mantendo lock enquanto espera resposta de rede ou confirmação do usuário — bloqueia autovacuum de limpar tuplas mortas e aumenta bloat.
Paginação por
```
OFFSET
```
grande em tabela que cresce — custo cresce linearmente com o offset; preferir paginação por keyset (
```
WHERE id > :cursor ORDER BY id LIMIT :n
```
).
Backup automatizado nunca restaurado — "temos backup" sem um restore completo testado é uma suposição não verificada, não uma garantia.

仅凭“该列出现在WHERE条件中”就创建索引，忽略选择性——对于低基数列（布尔值、状态值较少的字段），查询优化器通常会选择全表扫描，此类索引只会增加写入开销。
在旧版本Postgres（<11）中执行
```
ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT
```
会在锁表状态下重写整个表——当前版本已对常量
```
DEFAULT
```
做了优化，但使用易变函数的
```
DEFAULT
```
仍会触发全表重写。
长时间持有锁的事务等待网络响应或用户确认——会阻碍autovacuum清理死元组，导致数据膨胀。
在持续增长的表中使用大
```
OFFSET
```
进行分页——开销随偏移量线性增长；优先使用键集分页（
```
WHERE id > :cursor ORDER BY id LIMIT :n
```
）。
仅自动备份却从不恢复测试——“我们有备份”但未完成完整恢复测试只是未验证的假设，而非保障。

Validação

验证

Testar integridade (constraints violadas geram erro esperado), concorrência (dois writers simultâneos não corrompem invariante) e as queries críticas do caminho quente.
Comparar
```
EXPLAIN (ANALYZE, BUFFERS)
```
antes/depois com cardinalidade realista, não com a tabela vazia do ambiente de desenvolvimento.
Estimar o lock e o tempo de rewrite de qualquer DDL contra o tamanho real da tabela em produção antes de agendar a janela de deploy.
Provar restore periodicamente a partir do backup real, incluindo o tempo que o processo leva (RTO) — backup sem restore testado não é uma garantia de recuperação.
Não declarar uma mudança "sem impacto de performance" sem o plano comparado; não declarar um schema "íntegro" sem os testes de constraint e concorrência acima.

测试数据完整性（违反约束时触发预期错误）、并发处理（两个写入端同时操作不会破坏数据约束）以及核心路径的关键查询。
使用真实基数数据对比变更前后的
```
EXPLAIN (ANALYZE, BUFFERS)
```
结果，切勿用开发环境的空表测试。
在安排部署窗口前，估算任何DDL操作在生产环境真实表大小下的锁时长和重写时间。
定期从真实备份中执行恢复测试，包括记录恢复耗时（RTO）——未经过恢复测试的备份无法保证数据可恢复。
未对比执行计划前，切勿宣称变更“无性能影响”；未完成上述约束与并发测试前，切勿宣称Schema“完整可靠”。

specsfy-specialist-postgres

Original

Translation

PostgreSQL

PostgreSQL

Quando usar

适用场景

Fluxo

流程

Padrões

最佳实践

Antipadrões

反模式

Validação

验证

Skills relacionadas

相关技能