postgres-performance
PostgreSQL 쿼리 성능 최적화
데이터베이스 성능 문제는 애플리케이션 속도를 저하시키고 사용자를 불편하게 합니다. 이 스킬은 인덱싱 전략, 쿼리 튜닝, 확장 가능한 아키텍처 패턴을 포함한 PostgreSQL 최적화를 위한 검증된 패턴을 제공합니다.
下载技能 ZIP
在 Claude 中上传
前往 设置 → 功能 → 技能 → 上传技能
开启并开始使用
测试它
正在使用“postgres-performance”。 My query SELECT * FROM events WHERE user_id = 'abc-123' ORDER BY created_at DESC LIMIT 10 is slow on a table with 5M rows.
预期结果:
The query likely performs a sequential scan on the large events table. Here is the optimization approach:
**Problem:** Missing index for the filter and ORDER BY columns.
**Solution:** Create a covering index:
```sql
CREATE INDEX CONCURRENTLY ix_events_user_created
ON events (user_id, created_at DESC);
```
This index allows:
1. Filtering by user_id directly from the index
2. Sorting by created_at DESC using the index order
3. Index-only scan if you select only indexed columns
**Verification:**
```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE user_id = 'abc-123'
ORDER BY created_at DESC
LIMIT 10;
```
Look for "Index Scan" instead of "Seq Scan" in the plan.
正在使用“postgres-performance”。 How should I implement pagination for a high-traffic API endpoint?
预期结果:
Avoid OFFSET-based pagination for large datasets. Use cursor-based (keyset) pagination instead.
**Why OFFSET is slow:**
```sql
SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Must scan and discard 10,000 rows before returning results
```
**Cursor-based approach:**
```sql
SELECT * FROM events
WHERE created_at < '2024-01-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;
-- Jumps directly to the right place via index
```
**Python implementation:**
```python
async def get_events_page(session, cursor_time=None, limit=20):
query = select(Event).order_by(Event.created_at.desc())
if cursor_time:
query = query.where(Event.created_at < cursor_time)
return await session.execute(query.limit(limit))
```
**Trade-offs:**
- Cannot jump to arbitrary page numbers
- Requires passing cursor to client
- Much faster for deep pagination
安全审计
低风险Static analysis flagged 78 potential issues but all are false positives. The scanner misidentified SQL keywords as cryptographic code and markdown code fences as shell execution. This is a legitimate PostgreSQL performance optimization skill containing documentation and example queries. Risk factors are standard for documentation skills that include code examples.
质量评分
你能构建什么
느린 애플리케이션 쿼리 디버깅
PostgreSQL 진단 도구와 EXPLAIN 분석을 사용하여 애플리케이션 쿼리의 성능 병목 현상을 식별하고 수정합니다.
확장 가능한 데이터베이스 스키마 설계
높은 처리량의 데이터베이스 워크로드를 위한 인덱싱 전략, 파티셔닝 및 비정규화 패턴을 적용합니다.
배치 작업 최적화
SKIP LOCKED를 사용한 배치 패턴으로 잠금이나 타임아웃 없이 대용량 데이터셋을 효율적으로 처리합니다.
试试这些提示
My PostgreSQL query is slow. Analyze and optimize it: ```sql SELECT * FROM orders WHERE user_id = ? AND status = 'pending' ORDER BY created_at DESC LIMIT 20; ``` How can I improve this query?
I need to optimize these frequently-run queries on a table with 10M+ rows: 1. SELECT * FROM products WHERE category_id = ? AND in_stock = true 2. SELECT id, name, price FROM products WHERE category_id = ? ORDER BY price ASC 3. SELECT COUNT(*) FROM products WHERE category_id = ? Design an optimal index strategy.
Our API uses OFFSET pagination and it is slow for deep pages:
```python
# Current implementation
def get_events(page: int, limit: int = 20):
offset = (page - 1) * limit
return db.execute("SELECT * FROM events ORDER BY created_at DESC LIMIT ? OFFSET ?", limit, offset)
```
Convert this to cursor-based pagination with example code.Configure PostgreSQL connection pooling for our use case: - Serverless Python API on Lambda (spiky traffic, ~1000 req/min) - We see connection errors during traffic spikes - Database has 16GB RAM, shared_buffers = 4GB What pool settings do you recommend?
最佳实践
- 변경 전후로 항상 EXPLAIN (ANALYZE, BUFFERS)를 사용하여 쿼리 개선 사항을 검증하세요
- 자주 사용하는 쿼리의 힙 페치를 피하기 위해 커버링 인덱스(INCLUDE 절)를 사용하세요
- 10만 행 이상의 테이블에서는 OFFSET 대신 커서 기반 페이지네이션을 구현하세요
避免
- 성능이 중요한 쿼리에서 SELECT * 사용 - 필요한 컬럼만 지정하세요
- 배치 처리 없이 대규모 UPDATE 또는 DELETE 작업 실행 - 잠금 및 타임아웃을 유발합니다
- 동시 배치 처리에서 FOR UPDATE SKIP LOCKED 패턴 생략