software

为大规模数据应用优化 PostgreSQL 查询

发布: (2025年12月27日 GMT+8 12:31)
8 min read
原文: Dev.to

Source: Dev.to

介绍

在过去的两年里，我们已将查询时间从 > 15 秒降低到更低的数值。

❌ 查询时间：18 秒 – 破坏了响应性。

2. 索引策略

2.1 B‑tree 索引（等值与范围）

-- 单列索引
CREATE INDEX idx_orders_created_at   ON orders(created_at);
CREATE INDEX idx_orders_customer_id  ON orders(customer_id);

-- 复合索引用于多列过滤
CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, created_at);

影响： 查询时间 ↓ 至 8 秒。

2.2 部分索引（仅索引活跃行）

CREATE INDEX idx_orders_active
    ON orders(created_at)
    WHERE status = 'active';

将索引大小减少约 60%。

影响： 查询时间 ↓ 至 4.5 秒。

2.3 用于 JSONB 列的 GIN 索引

CREATE INDEX idx_orders_metadata
    ON orders USING GIN (metadata);
-- 快速 JSONB 查询
SELECT *
FROM orders
WHERE metadata @> '{"priority": "high"}';

2.4 监控与维护

-- 索引使用统计
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- 删除未使用的索引
DROP INDEX IF EXISTS unused_index_name;

-- 并发重建膨胀的索引
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

3. 查询层面的优化

3.1 只选择需要的列

-- ❌ Bad
SELECT * FROM orders WHERE status = 'active';

-- ✅ Good
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'active';

影响： 大约 40 % 的数据传输时间减少。

3.2 Explain & Analyze

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

需要关注的关键指标

计划节点解释说明
Seq Scan坏 – 需要索引
Index Scan好
Bitmap Heap Scan好 – 适用于大结果集
Nested Loop坏 – 对大表不适用
Hash Join好 – 适用于大表

3.3 连接类型选择

-- ❌ Bad: many LEFT JOINs returning NULLs
SELECT o.*, c.*, p.*, s.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN products  p ON o.product_id = p.id
LEFT JOIN shipments s ON o.id = s.order_id;

-- ✅ Good: use INNER JOIN when rows must exist
SELECT o.id, o.total_amount, c.name, p.title
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products  p ON o.product_id = p.id
WHERE o.status = 'completed';

4. 分区

4.1 按 created_at 的范围分区

-- Parent table
CREATE TABLE orders (
    id           SERIAL,
    customer_id  INTEGER,
    total_amount DECIMAL(10,2),
    created_at   TIMESTAMP NOT NULL,
    status       VARCHAR(50)
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Index on each partition
CREATE INDEX idx_orders_2024_01_customer
    ON orders_2024_01(customer_id);

影响： 日期范围扫描提升了 10 倍。

4.2 按 status 的列表分区

CREATE TABLE orders (
    id           SERIAL,
    customer_id  INTEGER,
    status       VARCHAR(50),
    created_at   TIMESTAMP
) PARTITION BY LIST (status);

CREATE TABLE orders_active PARTITION OF orders
FOR VALUES IN ('pending', 'processing', 'shipped');

CREATE TABLE orders_completed PARTITION OF orders
FOR VALUES IN ('delivered', 'completed');

5. 连接池 (PgBouncer)

pgbouncer.ini

[databases]
myapp_db = host=localhost port=5432 dbname=production

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode   = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

6. Node.js 实现

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 6432,               // PgBouncer port
  database: 'myapp_db',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  max: 20,                  // max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Efficient query execution
async function getActiveOrders() {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT id, total_amount FROM orders WHERE status = $1',
      ['active']
    );
    return result.rows;
  } finally {
    client.release(); // always release!
  }
}

影响: 连接‑开销 ↓ 70 %.

7. Redis 缓存层

// cache.js
const redis = require('redis');
const client = redis.createClient();

async function getCustomerOrders(customerId) {
  const cacheKey = `customer:${customerId}:orders`;

  // 1️⃣ Check cache first
  const cached = await client.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2️⃣ Fallback to DB
  const result = await pool.query(
    'SELECT * FROM orders WHERE customer_id = $1',
    [customerId]
  );

  // 3️⃣ Cache for 5 minutes
  await client.setEx(cacheKey, 300, JSON.stringify(result.rows));

  return result.rows;
}

8. 用于大量聚合的物化视图

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(created_at)          AS sale_date,
    COUNT(*)                  AS total_orders,
    SUM(total_amount)        AS total_revenue,
    AVG(total_amount)         AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

根据需要刷新（例如，每晚一次），以保持报告快速。

TL;DR

  1. 添加适当的 B‑tree、partial 和 GIN 索引。
  2. 仅选择所需列，并在可能的情况下使用 INNER JOIN。
  3. 对大表按日期或状态进行分区。
  4. 监控 索引使用情况并 reindex 膨胀的索引。
  5. 使用 PgBouncer Pool connections（连接池）并在 Redis 中 cache（缓存）频繁查询。
  6. 对于昂贵的聚合，使用 materialized views（物化视图）。

这些步骤共同将原本需要 15 秒的查询压缩至不足 200 毫秒的响应时间，使我们的应用能够优雅地扩展到每日数百万笔交易。

物化视图索引

CREATE INDEX idx_daily_sales_date 
ON daily_sales_summary(sale_date);

刷新视图（可安排）

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

性能影响： 仪表板查询从 12 秒 降至 100 毫秒。

批量插入

❌ 不好 – 多个单独的插入

// 10 000 separate INSERTs
for (let i = 0; i  1000
    `),
    connections: await pool.query(`
      SELECT COUNT(*) FROM pg_stat_activity 
      WHERE state = 'active'
    `),
    tableSize: await pool.query(`
      SELECT pg_size_pretty(pg_database_size(current_database()))
    `)
  };

  console.log('Database Health:', checks);

  // Alert if thresholds exceeded
  if (parseInt(checks.slowQueries.rows[0].count, 10) > 10) {
    error('⚠️ Too many slow queries detected!');
  }
}

// Run every 5 minutes
setInterval(healthCheck, 5 * 60 * 1000);

查询模式

❌ 错误 – N+1 查询

const orders = await pool.query('SELECT * FROM orders LIMIT 100');
for (let order of orders.rows) {
  const customer = await pool.query(
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer.rows[0];
}

✅ 正确 – 单一 JOIN

const result = await pool.query(`
  SELECT o.*, c.name, c.email
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  LIMIT 100
`);

❌ 错误 – SQL 注入风险且没有计划缓存

const query = `SELECT * FROM orders WHERE id = ${userId}`;

✅ 正确 – 预处理语句

const query = 'SELECT * FROM orders WHERE id = $1';
await pool.query(query, [userId]);

全文搜索设置

-- Add tsvector column
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Create GIN index
CREATE INDEX idx_products_search 
ON products USING GIN(search_vector);
-- Update trigger to maintain search_vector
CREATE TRIGGER tsvector_update 
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, description);
-- Efficient full‑text search
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'laptop & gaming');

高写入工作负载调优

wal_compression = on
wal_writer_delay = 200ms
commit_delay = 10
commit_siblings = 5

Best‑Practice Checklist

  • 先测量： 在优化之前使用 EXPLAIN ANALYZE。
  • 明智建索引： 索引越多 ≠ 性能越好。
  • 持续监控： pg_stat_statements 是你的最佳伙伴。
  • 在近似生产环境中测试： 本地数据库无法暴露扩展性问题。
  • 逐步应用更改： 一次只优化一件事。
  • 记录一切： 未来的你会感谢现在的你。

对大型应用进行 PostgreSQL 优化是一个迭代过程。这里分享的技术将查询时间降低了 100×，并将基础设施成本削减了 60 %。

关键要点

  • ✅ 战略性建索引（避免过度索引）
  • ✅ 查询重构与正确的 JOIN 使用
  • ✅ 针对时间序列数据的分区
  • ✅ 使用 PgBouncer 进行连接池管理
  • ✅ 多层级缓存
  • ✅ 定期监控与维护

工具： pgAdmin（GUI 管理），pg_stat_statements

  • 查询统计
  • PgBouncer – 连接池
  • pgBadger – 日志分析器
  • explain.depesz.com – 可视化 EXPLAIN 分析器

你是否也遇到过类似的扩展性挑战？哪些优化技术对你的场景有效？欢迎在评论区分享！

标签： performance #optimization #sql #backend #devops #scalability

Back to Blog

相关文章

阅读更多 »

索引与DBMS的崛起

你好，我是Maneshwar。我正在开发 FreeDevTools online https://hexmos.com/freedevtools，当前正打造一个汇集所有 dev tools、cheat codes 和 TLDRs 的统一平台。