PostgreSQL笔记8: 基于二八法则的基础性能调优

构建高效知识库:从结构化存储到智能检索 知识管理系统是现代企业应对信息过载的核心基础设施,其技术实现涉及结构化存储、信息检索和知识图谱等多个领域。从技术原理看,通过Elasticsearch等搜索引擎实现全文检索,结合NLP技术提升语义理解能力,是构建高效知识库的关键。在工程实践中,采用Markdown+Front Matter标准化内容格式,配合PostgreSQL+Elasticsearch的混合存储方案,能有效平衡灵活性与查询性能。典型应用场景包括技术文档管理、企业内部Wiki和客户支持知识库等场景,其中知识图谱技术可显著提升专业领域知识的 阅读详情

纲要

  • PostgreSQL 基础性能调优的核心原则:帕累托法则(二八法则)
  • 调优辅助工具:pgtune 与 pg_configurator
  • 内存相关参数调优
    • shared_buffers —— 共享内存缓冲区
    • work_mem —— 每个后端进程的工作内存
    • maintenance_work_mem —— 维护操作专用内存
    • effective_cache_size —— 优化器缓存估算(非实际分配)
    • temp_buffers —— 临时表缓冲区
  • 检查点(Checkpoint)相关参数调优
    • checkpoint_timeout
    • checkpoint_completion_target
    • max_wal_size / min_wal_size
  • 自动清理(Autovacuum)参数调优
    • autovacuum_max_workers
    • autovacuum_naptime
    • autovacuum_vacuum_cost_limit / autovacuum_vacuum_cost_delay
  • 查询规划器成本常量调优
    • random_page_cost
    • seq_page_cost
  • 配置文件生效规则:以最后一次出现的参数值为准

二八法则:性能调优的核心指导思想

PostgreSQL 的默认配置面向兼容性和通用场景设计,并未针对服务器的具体硬件资源(CPU 核心数、内存大小、磁盘类型)做针对性优化。因此,数据库安装完成后,首要任务便是根据实际硬件配置与业务负载特征,对关键参数进行基础调整。

在调优实践中,帕累托法则(Pareto Principle,即二八法则) 是一个极为实用的指导原则:80% 的性能收益往往来自于对 20% 的关键参数进行调整。PostgreSQL 拥有数百个配置参数,但在绝大多数业务系统的初期阶段,只需将内存、检查点、自动清理和 I/O 成本这几类核心参数调整到位,即可获得显著的性能提升。无需一开始便深入研究全部参数,也无需盲目照搬他人的配置文件。

调优辅助工具:pgtune 与 pg_configurator

在手动调整参数之前,可借助自动化工具生成一份基于硬件规格的基础配置,再根据实际业务场景进行微调。

pgtune

pgtune 是一款经典的 PostgreSQL 配置向导工具。它根据用户输入的硬件信息(CPU 数量、内存大小、磁盘类型等)和预期工作负载类型(OLTP、数据仓库、混合负载等),自动计算并生成推荐的 postgresql.conf 配置参数。

# pgtune 命令行示例(以 pgtune 的 Python 实现为例)
pgtune -i postgresql.conf -o postgresql-new.conf

pgtune 也提供在线 Web 版本,用户可通过表单填写服务器规格后一键生成配置。生成后的参数可直接追加到 postgresql.conf 文件末尾,或通过 ALTER SYSTEM 命令设置。

pg_configurator

pg_configurator 是一个更为现代化的图形化配置工具。与 pgtune 相比,它的可配置项更加丰富,支持设置日志回收策略、数据可靠性级别、备库数量等高级选项。其核心原理与 pgtune 一致:根据输入的硬件与业务参数生成优化后的配置参数。

建议:先使用上述工具生成一份基础配置,再结合实际监控数据与业务特点进行针对性微调。

内存相关参数调优

PostgreSQL 采用多进程架构,进程间通信依赖共享内存。以下是几个最核心的内存参数。

shared_buffers

shared_buffers 是 PostgreSQL 的共享内存缓冲区,用于缓存数据页(表数据和索引数据)。所有增删改查操作均在 shared_buffers 池中进行。

  • 默认值:128MB(通常过小)
  • 推荐设置:服务器物理内存的 25%
    • 内存 < 2GB:设为总内存的 20%
    • 内存 < 32GB:设为总内存的 25%
    • 内存 > 32GB:可设为 8GB 左右

配置

# postgresql.conf
shared_buffers = 4GB      # 假设服务器内存为 16GB

为何不建议设为 80%~90%?PostgreSQL 采用独特的双缓存架构:数据库自身管理 shared_buffers,操作系统同时也有自己的页面缓存(Page Cache)。两者共同作用,需为操作系统缓存预留足够内存。

work_mem

work_mem 是每个后端进程在执行排序(ORDER BY、DISTINCT)、哈希连接(Hash Join)等操作时可用的内存量。此为私有内存,每个会话的每个执行节点都可能消耗此内存。

  • 默认值:4MB
  • 风险:设置过大且并发连接数较高时,可能导致内存溢出(OOM)
  • 建议:在会话级别按需设置,而非全局设置过大
-- 会话级别设置 work_mem(仅对当前会话生效)
SET work_mem = '64MB';

-- 执行大型排序或哈希操作
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table ORDER BY large_column;
# 全局默认值(谨慎设置)
work_mem = '32MB'   # 保守值,建议从 32-64MB 起步

maintenance_work_mem

maintenance_work_mem 专用于维护性操作的内存,包括 VACUUM、CREATE INDEX、REINDEX、ALTER TABLE ADD FOREIGN KEY 等。

  • 默认值:64MB
  • 推荐设置:每 1GB 内存配置 50MB,即 16GB 内存可设为 800MB
  • 上限:一般不超过 1GB~2GB

配置

# postgresql.conf
maintenance_work_mem = '1GB'

effective_cache_size

effective_cache_size 用于告知查询优化器操作系统和数据库可用的磁盘缓存总量。此参数并非实际分配内存,仅影响优化器的执行计划选择。

  • 推荐设置:服务器总内存的 50%~75%

    # postgresql.conf
    effective_cache_size = '12GB'   # 假设服务器内存为 16GB
    

temp_buffers

temp_buffers 用于临时表操作的内存缓冲。每个会话可独立设置。

  • 默认值:8MB

    -- 会话级别设置
    SET temp_buffers = '64MB';
    CREATE TEMP TABLE temp_data AS SELECT * FROM large_table;
    

检查点(Checkpoint)参数调优

检查点(Checkpoint)是将内存中的脏页(Dirty Pages)刷写到磁盘的操作,用于保证数据一致性。检查点过于频繁会增加 I/O 压力;间隔过长则会延长崩溃恢复时间。

checkpoint_timeout

两次自动检查点之间的最大时间间隔。

  • 默认值:5 分钟(300 秒)

  • 取值范围:30 秒 ~ 1 小时

  • 推荐设置:15~30 分钟

    # postgresql.conf
    checkpoint_timeout = '15min'
    

checkpoint_completion_target

检查点完成的目标比例,即在下一次检查点开始前,本次检查点应完成的比例。

  • 默认值:0.5(50%)

  • 取值范围:0.0 ~ 1.0

  • 推荐设置:0.9(让写入更平滑,避免 I/O 尖峰)

    # postgresql.conf
    checkpoint_completion_target = 0.9
    

max_wal_size / min_wal_size

控制 WAL(Write-Ahead Log)日志总量的上下限。当 WAL 总量达到 max_wal_size 时会触发检查点。

  • 默认值:max_wal_size = 1GB,min_wal_size = 80MB

  • 推荐设置:根据实际业务写入量调整,通常可适当增大

    # postgresql.conf
    max_wal_size = '4GB'
    min_wal_size = '1GB'
    

自动清理(Autovacuum)参数调优

PostgreSQL 采用 MVCC(多版本并发控制)机制,更新和删除操作会产生"过期"元组(Dead Tuples),需通过 VACUUM 进行清理回收。autovacuum 是 PostgreSQL 的自动清理守护进程。

autovacuum_max_workers

同时运行的 autovacuum 工作进程的最大数量。

  • 默认值:3

  • 推荐设置:根据 CPU 核心数调整,32 核可设为 5~10

    # postgresql.conf
    autovacuum_max_workers = 5
    

autovacuum_naptime

autovacuum 启动器检查各数据库并触发清理任务的时间间隔。

  • 默认值:1 分钟(1min)

  • 推荐设置:通常保持默认即可

    # postgresql.conf
    autovacuum_naptime = '1min'
    

autovacuum_vacuum_cost_limit / autovacuum_vacuum_cost_delay

控制 autovacuum 的 I/O 成本上限与延迟,用于限制清理操作对正常业务的影响。

  • 默认值:autovacuum_vacuum_cost_limit = -1(使用 vacuum_cost_limit 的值,默认为 200),autovacuum_vacuum_cost_delay = 2ms
  • 推荐设置:
    • SSD 存储:可将 autovacuum_vacuum_cost_limit 设为 1000~2000
    • NVMe SSD:可设为 2000~10000

配置

# postgresql.conf
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = '2ms'

查询规划器成本常量调优

PostgreSQL 的查询优化器基于成本模型选择执行计划。seq_page_cost 与 random_page_cost 是其中两个最重要的 I/O 成本常量。

seq_page_cost 与 random_page_cost

  • seq_page_cost:顺序读取一页的成本,默认 1.0
  • random_page_cost:随机读取一页的成本,默认 4.0

两者的比值决定了优化器在顺序扫描(Sequential Scan)与索引扫描(Index Scan)之间的偏好。

# postgresql.conf(适用于 NVMe SSD 或高性能存储)
random_page_cost = 1.5
seq_page_cost = 1.0

注意:虽然系统允许将 random_page_cost 设为小于 seq_page_cost,但这在物理上是不合理的。对于全内存缓存的数据库,可考虑将两者设为相等。

调优原理

可将数据库选择执行计划的过程类比为从成都到北京的出行方式选择:

出行方式时间成本金钱成本对应数据库操作
飞机低高索引扫描(随机 I/O)
高铁中中位图扫描
自驾高低顺序扫描(顺序 I/O)

random_page_cost 与 seq_page_cost 的比值就是优化器判断"走索引"还是"走全表扫描"的决策依据。对于现代 SSD 或 NVMe 存储,随机 I/O 与顺序 I/O 的性能差距已大幅缩小,因此应适当降低 random_page_cost。

配置文件生效规则

PostgreSQL 的 postgresql.conf 配置文件采用以最后一次出现的参数值为准的生效规则。若同一参数在文件中出现多次,仅最后一次生效。

因此,建议将调优后的参数统一追加至文件末尾,并添加注释说明:

# ============================================
# 以下为 pgtune 生成的调优参数(2026-08-13)
# ============================================
shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9
max_wal_size = '4GB'
random_page_cost = 1.5
autovacuum_max_workers = 5
autovacuum_vacuum_cost_limit = 2000

修改配置文件后,需重启 PostgreSQL 或执行 pg_ctl reload 使参数生效(部分参数需重启)。

API 速览

本博客涉及的核心 PostgreSQL 配置参数 API 汇总如下:

参数名所属类别类型默认值推荐范围生效方式
shared_buffers内存integer128MB总内存的 20%~25%重启
work_mem内存integer4MB32~64MB(起步)会话/全局
maintenance_work_mem内存integer64MB每 1GB 内存 50MB会话/全局
effective_cache_size内存integer4GB总内存的 50%~75%重载
temp_buffers内存integer8MB视临时表大小而定会话/全局
checkpoint_timeout检查点integer300s15~30min重载
checkpoint_completion_target检查点floating0.50.8~0.9重载
max_wal_size检查点integer1GB视业务写入量而定重载
autovacuum_max_workers自动清理integer3CPU 核数的 1/4~1/3重启
autovacuum_naptime自动清理integer1min保持默认重载
autovacuum_vacuum_cost_limit自动清理integer-1 (200)1000~10000(SSD)重载
random_page_cost查询规划floating4.01.0~2.0(SSD)重载
seq_page_cost查询规划floating1.01.0重载

参数生效方式说明:

  • 重启:需重启 PostgreSQL 服务方可生效
  • 重载:执行 SELECT pg_reload_conf() 或 pg_ctl reload 即可生效,无需重启

Demo 简单示例

以下 Demo 演示了如何使用 Node.js 和 pg 驱动连接 PostgreSQL,并通过 SHOW 命令查看当前配置参数,以及通过 SET 命令在会话级别调整 work_mem。

运行说明

# 1. 初始化项目
mkdir pg-tuning-demo && cd pg-tuning-demo
npm init -y

# 2. 安装依赖
npm install pg

# 3. 创建 index.js 文件(内容见下方)

# 4. 运行
node index.js

代码说明

const { Client } = require('pg');

// PostgreSQL 连接配置
const config = {
  host: 'localhost',
  port: 5432,
  database: 'postgres',
  user: 'postgres',
  password: 'your_password',
};

async function main() {
  const client = new Client(config);
  
  try {
    await client.connect();
    console.log('✅ 已连接到 PostgreSQL');

    // 1. 查看当前关键参数
    const params = [
      'shared_buffers',
      'work_mem',
      'maintenance_work_mem',
      'effective_cache_size',
      'checkpoint_timeout',
      'max_wal_size',
      'random_page_cost',
      'autovacuum_max_workers'
    ];

    console.log('\n📊 当前配置参数:');
    for (const param of params) {
      const res = await client.query(`SHOW ${param};`);
      console.log(`  ${param} = ${res.rows[0][param]}`);
    }

    // 2. 会话级别调整 work_mem
    console.log('\n⚙️  会话级别调整 work_mem...');
    await client.query(`SET work_mem = '64MB';`);
    const res = await client.query(`SHOW work_mem;`);
    console.log(`  work_mem (当前会话) = ${res.rows[0].work_mem}`);

    // 3. 创建测试表并插入数据
    await client.query(`
      DROP TABLE IF EXISTS tuning_demo;
      CREATE TABLE tuning_demo (
        id SERIAL PRIMARY KEY,
        value INTEGER,
        created_at TIMESTAMP DEFAULT NOW()
      );
    `);
    console.log('\n📋 已创建测试表 tuning_demo');

    // 4. 插入 10 万条测试数据
    console.log('📝 插入 100,000 条测试数据...');
    await client.query(`
      INSERT INTO tuning_demo (value)
      SELECT generate_series(1, 100000);
    `);
    console.log('✅ 数据插入完成');

    // 5. 执行排序查询,验证 work_mem 效果
    console.log('\n🔍 执行排序查询 (ORDER BY value DESC)...');
    const explainRes = await client.query(`
      EXPLAIN (ANALYZE, BUFFERS) 
      SELECT * FROM tuning_demo ORDER BY value DESC LIMIT 100;
    `);
    console.log('\n📈 执行计划:');
    explainRes.rows.forEach(row => {
      console.log(`  ${row['QUERY PLAN']}`);
    });

  } catch (err) {
    console.error('❌ 错误:', err.message);
  } finally {
    await client.end();
    console.log('\n🔌 连接已关闭');
  }
}

main();

对应的 PostgreSQL 原生指令

-- 查看当前配置参数
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;
SHOW checkpoint_timeout;
SHOW max_wal_size;
SHOW random_page_cost;
SHOW autovacuum_max_workers;

-- 会话级别设置 work_mem
SET work_mem = '64MB';

-- 创建测试表
DROP TABLE IF EXISTS tuning_demo;
CREATE TABLE tuning_demo (
    id SERIAL PRIMARY KEY,
    value INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入测试数据
INSERT INTO tuning_demo (value)
SELECT generate_series(1, 100000);

-- 查看执行计划
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM tuning_demo ORDER BY value DESC LIMIT 100;

技术点总结

  1. pg 驱动连接:使用 Node.js 的 pg 库连接 PostgreSQL 数据库
  2. SHOW 命令:查看当前生效的配置参数值
  3. SET 命令:在会话级别动态调整 work_mem,无需重启数据库
  4. EXPLAIN (ANALYZE, BUFFERS):分析查询执行计划,观察内存与 I/O 使用情况
  5. 参数分层管理:全局参数(postgresql.conf)与会话级参数(SET)的优先级关系

多语言 Demo 示例

以下分别提供 Go、Python、Java 三种语言的完整可运行示例,功能与前述 Node.js 版本一致:连接 PostgreSQL,查看关键配置参数,会话级调整 work_mem,创建测试表并插入 10 万条数据,最后执行带 EXPLAIN (ANALYZE, BUFFERS) 的查询并输出执行计划。

Go 语言示例

使用 lib/pq 或 pgx 驱动,此处采用 pgx/v5(推荐)。

运行说明
# 初始化模块
go mod init pg-tuning-demo
go get github.com/jackc/pgx/v5

# 运行
go run main.go
代码 (main.go)
package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

	// 连接配置
	connStr := "postgres://postgres:your_password@localhost:5432/postgres"
	conn, err := pgx.Connect(ctx, connStr)
	if err != nil {
		log.Fatalf("无法连接数据库: %v", err)
	}
	defer conn.Close(ctx)

	fmt.Println("✅ 已连接到 PostgreSQL")

	// 1. 查看当前关键参数
	params := []string{
		"shared_buffers",
		"work_mem",
		"maintenance_work_mem",
		"effective_cache_size",
		"checkpoint_timeout",
		"max_wal_size",
		"random_page_cost",
		"autovacuum_max_workers",
	}

	fmt.Println("\n📊 当前配置参数:")
	for _, p := range params {
		var val string
		err = conn.QueryRow(ctx, fmt.Sprintf("SHOW %s;", p)).Scan(&val)
		if err != nil {
			log.Printf("获取参数 %s 失败: %v", p, err)
			continue
		}
		fmt.Printf("  %s = %s\n", p, val)
	}

	// 2. 会话级别调整 work_mem
	fmt.Println("\n⚙️  会话级别调整 work_mem...")
	_, err = conn.Exec(ctx, "SET work_mem = '64MB';")
	if err != nil {
		log.Fatalf("设置 work_mem 失败: %v", err)
	}
	var workMem string
	err = conn.QueryRow(ctx, "SHOW work_mem;").Scan(&workMem)
	if err == nil {
		fmt.Printf("  work_mem (当前会话) = %s\n", workMem)
	}

	// 3. 创建测试表
	fmt.Println("\n📋 创建测试表 tuning_demo...")
	_, err = conn.Exec(ctx, `
		DROP TABLE IF EXISTS tuning_demo;
		CREATE TABLE tuning_demo (
			id SERIAL PRIMARY KEY,
			value INTEGER,
			created_at TIMESTAMP DEFAULT NOW()
		);
	`)
	if err != nil {
		log.Fatalf("创建表失败: %v", err)
	}

	// 4. 插入 10 万条数据
	fmt.Println("📝 插入 100,000 条测试数据...")
	_, err = conn.Exec(ctx, `
		INSERT INTO tuning_demo (value)
		SELECT generate_series(1, 100000);
	`)
	if err != nil {
		log.Fatalf("插入数据失败: %v", err)
	}
	fmt.Println("✅ 数据插入完成")

	// 5. 执行排序查询并查看执行计划
	fmt.Println("\n🔍 执行排序查询 (ORDER BY value DESC)...")
	rows, err := conn.Query(ctx, `
		EXPLAIN (ANALYZE, BUFFERS) 
		SELECT * FROM tuning_demo ORDER BY value DESC LIMIT 100;
	`)
	if err != nil {
		log.Fatalf("查询执行计划失败: %v", err)
	}
	defer rows.Close()

	fmt.Println("\n📈 执行计划:")
	for rows.Next() {
		var planLine string
		if err := rows.Scan(&planLine); err != nil {
			log.Printf("扫描结果失败: %v", err)
			continue
		}
		fmt.Printf("  %s\n", planLine)
	}

	fmt.Println("\n🔌 连接已关闭")
}
对应的 PostgreSQL 原生指令(与 Node 版相同,此处省略重复,请参照前文)

Python 语言示例

使用 psycopg2(或 asyncpg,此处选用同步的 psycopg2)。

运行说明
# 创建虚拟环境(可选)
python -m venv venv
source venv/bin/activate  # Windows: venv\Scripts\activate

# 安装依赖
pip install psycopg2-binary

# 运行
python main.py
代码 (main.py)
import psycopg2
from psycopg2 import sql

def main():
    # 连接配置
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="postgres",
        user="postgres",
        password="your_password"
    )
    conn.autocommit = True
    cur = conn.cursor()

    print("✅ 已连接到 PostgreSQL")

    # 1. 查看当前关键参数
    params = [
        "shared_buffers",
        "work_mem",
        "maintenance_work_mem",
        "effective_cache_size",
        "checkpoint_timeout",
        "max_wal_size",
        "random_page_cost",
        "autovacuum_max_workers",
    ]

    print("\n📊 当前配置参数:")
    for p in params:
        cur.execute(sql.SQL("SHOW {};").format(sql.Identifier(p)))
        val = cur.fetchone()[0]
        print(f"  {p} = {val}")

    # 2. 会话级别调整 work_mem
    print("\n⚙️  会话级别调整 work_mem...")
    cur.execute("SET work_mem = '64MB';")
    cur.execute("SHOW work_mem;")
    work_mem = cur.fetchone()[0]
    print(f"  work_mem (当前会话) = {work_mem}")

    # 3. 创建测试表
    print("\n📋 创建测试表 tuning_demo...")
    cur.execute("""
        DROP TABLE IF EXISTS tuning_demo;
        CREATE TABLE tuning_demo (
            id SERIAL PRIMARY KEY,
            value INTEGER,
            created_at TIMESTAMP DEFAULT NOW()
        );
    """)

    # 4. 插入 10 万条数据
    print("📝 插入 100,000 条测试数据...")
    cur.execute("""
        INSERT INTO tuning_demo (value)
        SELECT generate_series(1, 100000);
    """)
    print("✅ 数据插入完成")

    # 5. 执行排序查询并查看执行计划
    print("\n🔍 执行排序查询 (ORDER BY value DESC)...")
    cur.execute("""
        EXPLAIN (ANALYZE, BUFFERS) 
        SELECT * FROM tuning_demo ORDER BY value DESC LIMIT 100;
    """)
    rows = cur.fetchall()

    print("\n📈 执行计划:")
    for row in rows:
        print(f"  {row[0]}")

    cur.close()
    conn.close()
    print("\n🔌 连接已关闭")

if __name__ == "__main__":
    main()
对应的 PostgreSQL 原生指令(同前,略)

Java 语言示例

使用 JDBC 驱动(org.postgresql:postgresql)和 Maven 构建。

运行说明
# 创建 Maven 项目结构
mkdir -p pg-tuning-demo/src/main/java
cd pg-tuning-demo

# 创建 pom.xml(内容见下方)
# 将 Java 源码放入 src/main/java/PgTuningDemo.java

# 编译并运行
mvn compile exec:java -Dexec.mainClass="PgTuningDemo"
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>pg-tuning-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.6.0</version>
        </dependency>
    </dependencies>
</project>
代码 (PgTuningDemo.java)
import java.sql.*;
import java.util.Arrays;
import java.util.List;

public class PgTuningDemo {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        String user = "postgres";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("✅ 已连接到 PostgreSQL");

            // 1. 查看当前关键参数
            List<String> params = Arrays.asList(
                "shared_buffers",
                "work_mem",
                "maintenance_work_mem",
                "effective_cache_size",
                "checkpoint_timeout",
                "max_wal_size",
                "random_page_cost",
                "autovacuum_max_workers"
            );

            System.out.println("\n📊 当前配置参数:");
            for (String p : params) {
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SHOW " + p + ";")) {
                    if (rs.next()) {
                        System.out.println("  " + p + " = " + rs.getString(1));
                    }
                }
            }

            // 2. 会话级别调整 work_mem
            System.out.println("\n⚙️  会话级别调整 work_mem...");
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("SET work_mem = '64MB';");
            }
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SHOW work_mem;")) {
                if (rs.next()) {
                    System.out.println("  work_mem (当前会话) = " + rs.getString(1));
                }
            }

            // 3. 创建测试表
            System.out.println("\n📋 创建测试表 tuning_demo...");
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("DROP TABLE IF EXISTS tuning_demo;");
                stmt.execute("""
                    CREATE TABLE tuning_demo (
                        id SERIAL PRIMARY KEY,
                        value INTEGER,
                        created_at TIMESTAMP DEFAULT NOW()
                    );
                """);
            }

            // 4. 插入 10 万条数据
            System.out.println("📝 插入 100,000 条测试数据...");
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("""
                    INSERT INTO tuning_demo (value)
                    SELECT generate_series(1, 100000);
                """);
            }
            System.out.println("✅ 数据插入完成");

            // 5. 执行排序查询并查看执行计划
            System.out.println("\n🔍 执行排序查询 (ORDER BY value DESC)...");
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("""
                     EXPLAIN (ANALYZE, BUFFERS) 
                     SELECT * FROM tuning_demo ORDER BY value DESC LIMIT 100;
                 """)) {
                System.out.println("\n📈 执行计划:");
                while (rs.next()) {
                    System.out.println("  " + rs.getString(1));
                }
            }

            System.out.println("\n🔌 连接已关闭");

        } catch (SQLException e) {
            System.err.println("❌ 错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
对应的 PostgreSQL 原生指令(同前,略)

各语言技术点对比

语言驱动/库连接方式参数查询会话级 SET执行计划
Node.jspgClient 连接client.query('SHOW ...')client.query('SET ...')EXPLAIN (ANALYZE, BUFFERS)
Gopgx/v5pgx.Connectconn.QueryRowconn.Execconn.Query
Pythonpsycopg2connect() + cursorcur.execute(sql.SQL(...))cur.execute(...)cur.execute(...) + fetchall
JavaJDBC (PostgreSQL)DriverManager.getConnectionStatement.executeQueryStatement.executeStatement.executeQuery

以上四种语言示例均实现了相同逻辑,可根据项目技术栈选择参考。

官方文档

参考链接

总结

本文围绕 PostgreSQL 基础性能调优展开,核心要点如下:

  1. 二八法则:聚焦 20% 的关键参数(内存、检查点、自动清理、I/O 成本),即可获得 80% 的性能收益。
  2. 内存参数:shared_buffers 设为总内存 25%;effective_cache_size 设为 50%~75%(非实际分配);work_mem 建议在会话级别按需设置,避免全局过大导致 OOM;maintenance_work_mem 按每 1GB 内存 50MB 配置。
  3. 检查点参数:checkpoint_timeout 设为 15~30 分钟,checkpoint_completion_target 设为 0.9 以实现平滑写入。
  4. 自动清理:根据 CPU 核心数调整 autovacuum_max_workers,SSD 存储可提高 autovacuum_vacuum_cost_limit。
  5. I/O 成本常量:现代 SSD 环境下,将 random_page_cost 从默认 4.0 降至 1.5~2.0,让优化器更准确地选择索引扫描。
  6. 辅助工具:使用 pgtune 或 pg_configurator 生成初始配置,再结合实际监控数据进行微调。
  7. 配置生效:PostgreSQL 以最后一次出现的参数值为准,建议将调优参数统一追加至配置文件末尾。
LLM语义缓存实战:从原理到架构,破解AI应用成本难题 缓存是计算机系统中提升性能、降低负载的核心技术,其原理是将高频访问的数据暂存于快速存储介质,避免重复计算或远程获取。在AI工程领域,随着大语言模型应用规模扩大,调用成本成为关键瓶颈。语义缓存技术通过向量化与近似匹配,智能识别用户意图相似的查询,直接从缓存返回结果,能显著减少对昂贵LLM API的调用,实现降本增效。本文聚焦于LLM缓存系统的工程实践,深入探讨如何设计高命中率的语义缓存策略,并合理选型向量数据库与嵌入模型,以应对高并发、多租户场景下的成本与性能挑战,为构建可持续的AI应用提供核心优化思路。 阅读详情

相关推荐

告别黑窗口!用VSCode+Native Debug调试C/C++程序,比GDB命令行舒服多了

本文介绍了如何使用VSCode配合Native Debug扩展来调试C/C++程序,替代传统的GDB命令行方式。通过可视化调试环境,开发者可以更高效地管理断点、查看变量和调用栈,大幅提升调试效率。文章详细讲解了环境配置、核心调试功能及高级技巧,帮助开发者快速上手这一现代调试工具。

weixin_26833139的博客 251

Java Web性能分析与优化实战:从慢SQL到JVM调优全链路排查指南

在分布式系统与高并发场景下,服务端性能优化是保障应用稳定性和用户体验的核心工程实践。其本质在于通过系统性的监控、分析与调优,解决资源瓶颈与低效代码,从而提升吞吐量并降低延迟。从技术原理层面看,性能问题通常根植于计算资源(如CPU)、内存管理、I/O操作(网络与磁盘)及数据访问等基础环节。例如,数据库慢查询与不合理的索引设计会直接导致响应时间飙升,而JVM垃圾回收机制配置不当则可能引发频繁的停顿,影响服务可用性。在Java Web开发领域,结合性能剖析工具(如Arthas、Async-Profiler)与AP

weixin_33985679的博客 309

抖音小程序开发 唤起收银台支付(可以选择支付宝APP支付或微信H5支付)

什么都别说,先看效果: 字节跳动也开放了小程序给商家接入,可以在旗下APP如抖音、今日头条、今日头条极速版等应用中即点即用,基于庞大的数亿用户量为其引流,帮助商家获取用户流量,销售商品,其模式和微信小程序差不多。 1、后台管理地址: https://developer.toutiao.com/ 2、开发文档地址: https://developer.toutiao.com/dev/cn/min...

悟空码字的博客 1万+

postgresql 性能调优

postgresql 性能调优;元祖清理

weixin_44313745的博客 2839

【Vite+Vue3+TS】基于Vite+Vue3+TypeScript+ESLint+Prettier+Stylelint搭建项目(亲测超详细)

结合了很多项目和文章,总结一篇项目构建的笔记。 温馨提示:文章末尾记录了我在搭建项目过程中,遇到的一些问题和解决办法 ~

aDiaoYa_的博客 4234

低功耗模式下的看门狗使用

最近用国产芯片AT32在做一个低功耗的项目,为了实现低功耗模式下的定时唤醒,我选择的是使用相对比较简单的看门狗来做,下面就分享一下使用过程~ 为了最大限度的降低功耗,我用的是待机模式,先看看几种低功耗模式以及唤醒的区别: 而看门狗AT32提供了两种,分别是独立看门狗(IWDG)和窗口看门狗(WWDG),其区别大概有以下几点: 1、独立看门狗(IWDG)由专用的低速时钟(LSI)驱动,即使主时钟发生故障它也仍然有效;窗口看门狗由从 APB1 时钟分频后得到的时钟驱动,通过可配置的时间窗口来检测应用程序非正常

dc18883988396的博客 3029

postgres相关参数学习1(vacuum和autovacuum)

1 基于开销的清理延迟 在 VACUUM 和 ANALYZE 命令执行过程中,系统维护一个内部的记数器,跟踪所执行的各种 I/O 操作的近似开销。如果积累的开销达到了 vacuum_cost_limit 声明的限制,那么执行这个操作的进程将睡眠 vacuum_cost_delay 指定的时间。然后它会重置记数器然后继续执行。 这个特性的目的是允许管理员减少这些命令在并发活动的数据库上的 I/O 影响。比如,像VACUUM和ANALYZE这样的维护命令并不需要迅速完成,并且不...

乡下人 1713

Metal 一、初识 Metal 及其语言规范

游戏开发者的新的技术平台,该技术能够为3D图像提高 10 倍的渲染性能。苹果2018年推出 Metal,在此之前一直使用OpenGL ES。1图形渲染;2高并发计算。苹果文档给出的 Metal 优化:CPU 低消耗、GPU 高利用、连续处理器并行、有效的资源利用Metal 官方文档。

irainsa的博客 1225

PostgreSQL技术大讲堂 - 第18讲:Tuning Autovacuum

Autovacuum是启动PostgreSQL时自动启动的后台实用程序进程之一在生产系统中不应该将其设置为关闭。

m0_65303136的博客 294

GPU 运维实战:H100/H800/B300 换卡与压测

(烧多久、看什么、什么算合格)。文末还给了一个能直接跑的验收判定脚本,三个真实场景的裁决输出都在。

老韩的Linux云计算架构师进阶之路 281

Unreal使用nDisplay制作裸眼3D(9宫格)

摘要: nDisplay是虚幻引擎的分布式实时渲染技术,可将内容同步输出到多个显示设备(如LED墙、投影仪等),构建沉浸式可视化系统。其核心功能包括:分布式渲染(支持多台设备协同处理高分辨率内容)、空间透视校正(通过追踪观众动态调整画面几何关系)以及虚拟拍摄应用。配置时需设置集群组件(Cluster、Host、Node、Viewport)定义渲染逻辑,空间组件(nDisplayScreen、ViewPoint)确定投影关系。文中以9宫格配置为例,详细演示了从工程创建到影片渲染合成的全流程,最终通过多视口合成

TxNet 611

PostgreSQL优化技巧:加速大数据处理

为了解决这个问题,我们可以使用一些技术来优化它。本文介绍了优化PostgreSQL数据库的一些技巧,例如使用索引、连接池、分区表、复制、预处理语句和分析工具。在优化 Postgre SQL 时,您还应该考虑其他因素,例如缓存大小、数据库架构和网络带宽等。通过使用这些技术,您可以显著提高数据库的性能并更快地处理大量数据。例如,您可以使用 pg_stat_statements 模块来跟踪查询的执行时间。连接池避免了每个新连接都需要建立到数据库的新连接的开销,因此使用连接池可以更快地管理连接。

qq_28245087的博客 2242

华为OD机试 - 机器人活动区域(Python/JS/C/C++ 双机位C卷 200分)

本专栏收录于《华为OD机试真题(Python/JS/C/C++)》。刷的越多,抽中的概率越大,私信哪吒,备注华为OD,加入华为OD刷题交流群,每一题都有详细的答题思路、详细的代码注释、3个测试用例、为什么这道题采用XX算法、XX算法的适用场景,发现新题目,随时更新。现有一个机器人,可放置于 M × N的网格中任意位置,每个网格包含一个非负整数编号。当相邻网格的数字编号差值的绝对值小于等于 1 时,机器人可在网格间移动问题:求机器人可活动的最大范围对应的网格点数目。说明:1)网格左上角坐标为 (0, 0),右

学Java,找哪吒 1436

PostgreSQL (七) 性能优化

SQL优化, 数据库结构优化, 数据库参数优化

fracong的博客 4844

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

迈瑞BS系列全自动生化分析仪LIS协议接口手册(内含HL7和ASTM)

迈瑞BS系列全自动生化分析仪LIS协议接口手册,内含HL7和ASTM)

HAProxy读写分离优化:5个提升PostgreSQL集群性能的配置技巧

本文详细介绍了通过HAProxy实现PostgreSQL集群读写分离的5个关键配置技巧,包括TCP连接复用、智能健康检查、精细化路由、动态负载均衡算法和生产环境监控。这些优化策略在高并发场景下可显著提升数据库性能,特别适用于电商大促等流量高峰场景。

q6r7s8t9的博客 514

IDEA的使用指南

IDEA基于java的使用手册,可以最快速的上手,在使用中不断的丰富自己的使用技巧.

MySQL 性能调优实战指南:基于 claude-skills 的 database-optimizer 实践手册

本指南以 claude-skills 仓库中 [database-optimizer](https://link.gitcode.com/i/f098e9f33fd8f79c888037fa6040861a) 技能的 [MySQL 调优参考文档](https://link.gitcode.com/i/793c61c92704e1e3e0663faf8433aa0c) 为核心骨架,系统讲解 Inno

gitblog_00513的博客 875

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

PostgreSQL表空间实战:从SSD到HDD的智能数据分层策略

本文深入探讨PostgreSQL表空间的实战应用,通过智能数据分层策略优化存储资源配置。针对电商场景中的性能瓶颈,详细介绍了从SSD到HDD的三层存储架构设计、自动化数据迁移方案及性能监控体系,显著提升查询性能并降低存储成本。

fern8的博客 798

高级 SQL 实战教程(华为云 DWS / PostgreSQL 版)

📌 本教程不会只扔代码。每一个知识点都会从真实的“业务问题”出发,先弄清楚,再讲明白,最后给出,并指出。

全网同名,欢迎关注。 497

SSM框架整合遇到的问题汇总(Spring+SpringMVC+Mybatis+PostgreSQL+Tomcat7+Maven)v.18

一 问题一:mybatis生成的映射文件1 (mapper.xml)找不到: 解决办法: 二 问题二:mappingJacksonHttpMessageConverter类找不到 解决办法: 三: 问题三:jackson.core找不到 解决办法: 四 问题四:web容器找不到数据库驱动org.postgresql.Driver 解决办法:-将驱动jar包 手动放入we...

白衣 4984

SQL原生批量生成百万级业务测试数据实战

SQL批量插入是数据库性能优化与测试验证的基础能力,其核心在于理解数据生成的执行范式:从客户端串行提交转向服务端内存计算。递归CTE、generate_series、INSERT SELECT等原生机制,通过消除网络往返和WAL高频刷盘,实现百倍级性能跃升;结合RANDOM()向量化计算、CASE业务规则嵌入与时间/金额分布模拟,可生成具备真实语义的活数据。该技术广泛应用于SQL练手、索引调优、执行计划分析及CI/CD自动化测试等工程场景,是DBA、数据工程师与SQL开发者必须掌握的‘数据工厂’底层能力。

weixin_34056162的博客 383

pgrust 监控指南:pg_stat_statements 与性能观测实战

数据库跑得再快,如果不知道瓶颈在哪里,性能优化就无从下手。pgrust 作为一款用 Rust 重写 PostgreSQL 的开源数据库,在基准测试中展现出超越原生 Postgres 甚至 ClickHouse 的惊人性能,而要让这份性能红利真正落地,一套可靠的可观测手段必不可少。这篇 pgrust 监控指南,将带你从零上手 pg_stat_statements,掌握 SQL 级性能观测的核心方法,

gitblog_00789的博客 1079

云数据库性能测评实战:从业务场景设计到核心指标解读

数据库性能优化是保障应用稳定性和用户体验的关键技术领域,其核心在于理解系统在负载下的真实表现。从原理上看,性能测评通过模拟真实业务压力,揭示数据库的吞吐量、延迟、资源利用率和可扩展性等核心指标的内在关联。这项技术的价值在于,它能帮助工程师在技术选型与架构设计阶段做出数据驱动的决策,避免因性能瓶颈导致成本激增或用户体验下降。在实际应用场景中,无论是高并发的OLTP事务系统、高性能缓存,还是复杂的OLAP分析场景,一套科学的测评方法都是不可或缺的。本文将聚焦于云数据库,深入探讨如何设计贴近业务的测试场景,并解读

weixin_33812433的博客 367

PowerJob 适配达梦 DM8 数据库:兼容模式与数据源配置实践指南

在信创和国产化替代的浪潮下,分布式任务调度平台如何平滑迁移到达梦数据库,是很多后端团队需要直面的工程课题。分布式调度中间件通常依赖底层数据库存储任务元数据与运行状态,而达梦 DM8 作为国产数据库的代表,其兼容模式决定了上层 SQL 的适配成本。理解达梦的 COMPATIBLE_MODE 参数、JDBC 驱动选择以及数据源连接串中的 schema 语义,是打通 MySQL 生态应用与国产数据库的关键。通过合理配置 HikariCP 连接池、正确替换驱动依赖并手工执行初始化脚本,可以最大限度保留原有 SQL

weixin_34216107的博客 345

SQL窗口函数实战:从订单表到ABC库存分类的完整实现

在数据分析与工程实践中,基于有序集合的累计计算是高频需求,窗口函数正是解决这类问题的核心SQL工具。通过排序与逐行累加,可以轻松实现帕累托分析(二八法则)在业务场景中的落地。库存管理中的ABC分类便是典型应用:它按销售额贡献度将SKU划分为A、B、C类,而累计占比的计算直接决定了分类结果的准确性。本文从订单明细表出发,逐步拆解销售汇总、占比计算、窗口函数累计以及分类打标签的完整SQL思路,并给出可直接运行的代码示例。同时涵盖MySQL、PostgreSQL等数据库的写法差异、性能优化技巧及常见数据坑,帮助读

weixin_34098209的博客 326

大模型调用量:AI落地的硬核指标与工程实践指南

大模型调用量是衡量AI技术真实落地效果的核心指标,它反映的是有效推理请求次数,而非参数规模或榜单排名。其底层原理基于HTTP API标准协议、Token计费逻辑与服务端响应验证机制,具备强业务驱动性与可审计性。相比传统模型评估方式,调用量更直接体现成本效率、延迟敏感度与中文场景适配能力,已成为企业AI选型、开发者资源调度和投融资尽调的关键依据。在电商客服、政务问答、教育内容生成等高频中文应用中,Qwen3.6 Plus(free)与GLM-5 Turbo等模型正通过低门槛接入、流式响应优化和Prompt架构

cuanku6549的博客 555

从一台华硕A555L的‘解剖’,聊聊我们该如何理性看待‘电子垃圾’与‘升级价值’

本文通过解剖华硕A555L笔记本电脑,探讨了电子产品的生命周期价值与升级潜力。通过性能测试、成本效益分析和接口实用性评估,揭示了老旧设备在办公、游戏等场景下的实际表现,并提供了升级决策的实用建议,帮助用户理性看待‘电子垃圾’与‘升级价值’。

weixin_30455023的博客 347
上一篇: PostgreSQL笔记7: 源码编译选项深度解析
下一篇: PostgreSQL笔记9: psql 客户端——从入门到精通
Wang's Blog
博客等级 码龄12年 7440粉丝 2427原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值