Back to Details

postgresql-skill

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

PostgreSQL Development

PostgreSQL开发

Purpose

用途

PostgreSQL is a powerful relational database. This skill documents patterns for safe transactions, optimization, and concurrent access.
PostgreSQL是一款功能强大的关系型数据库。本技能记录了安全事务、性能优化及并发访问相关的实现模式。

When to Use

适用场景

Use this skill when:
  • Designing database schema
  • Creating migrations
  • Implementing transactions
  • Optimizing queries
  • Managing connections
在以下场景中可使用本技能：
  • 设计数据库架构
  • 创建迁移脚本
  • 实现事务逻辑
  • 优化查询语句
  • 管理数据库连接

Key Patterns

核心模式

1. Transaction Management

1. 事务管理

Wrap operations in transactions:
python
async def transfer_funds(self, from_account, to_account, amount):
    async with self.db.transaction():
        await self.db.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (amount, from_account)
        )
        await self.db.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_account)
        )
将操作包裹在事务中执行：
python
async def transfer_funds(self, from_account, to_account, amount):
    async with self.db.transaction():
        await self.db.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (amount, from_account)
        )
        await self.db.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_account)
        )

2. Connection Pooling

2. 连接池

Use connection pools for efficiency:
python
pool = await asyncpg.create_pool(
    'postgresql://user:password@localhost/db',
    min_size=10,
    max_size=20
)
使用连接池提升效率：
python
pool = await asyncpg.create_pool(
    'postgresql://user:password@localhost/db',
    min_size=10,
    max_size=20
)

3. Query Optimization

3. 查询优化

Use proper indexing:
sql
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_transaction_date ON transactions(created_at);
合理使用索引：
sql
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_transaction_date ON transactions(created_at);

See Also

相关链接