# 内存参数
shared_buffers = 4GB # 物理内存的25%
work_mem = 32MB # 每个工作进程的内存
maintenance_work_mem = 1GB # 维护操作的内存

# I/O参数
random_page_cost = 1.1 # SSD存储
seq_page_cost = 1.0 # 顺序读取成本
effective_cache_size = 12GB # 操作系统缓存

# WAL参数
wal_buffers = 16MB # WAL缓冲区大小
checkpoint_timeout = 15min # 检查点超时
max_wal_size = 4GB # 最大WAL大小
checkpoint_completion_target = 0.9 # 检查点完成目标

# 示例：优化存储参数
ALTER SYSTEM SET shared_buffers = ‘4GB’;
ALTER SYSTEM SET work_mem = ’32MB’;
ALTER SYSTEM SET maintenance_work_mem = ‘1GB’;
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = ’12GB’;
SELECT pg_reload_conf();

# 文件系统选择
– ext4：稳定可靠
– xfs：适合大文件
– zfs：高级功能

# 文件系统参数
– noatime：禁用访问时间记录
– nodiratime：禁用目录访问时间记录
– discard：启用TRIM

# 磁盘调度
– deadline：适合数据库
– cfq：默认，通用
– noop：适合SSD

# 示例：配置文件系统
# /etc/fstab
/dev/sdb1 /postgresql/data ext4 noatime,nodiratime,discard 0 2

# 示例：设置磁盘调度
echo deadline > /sys/block/sdb/queue/scheduler

# 创建表空间目录
mkdir -p /ssd1/fgedutbss/{data,index}
mkdir -p /hdd1/fgedutbss/{archive,backup}

# 设置权限
chown -R pgsql: pgsql /ssd1/fgedutbss/
chown -R pgsql: pgsql /hdd1/fgedutbss/
chmod -R 700 /ssd1/fgedutbss/
chmod -R 700 /hdd1/fgedutbss/

# 创建表空间
CREATE TABLESPACE fgedu_data LOCATION ‘/ssd1/fgedutbss/data’;
CREATE TABLESPACE fgedu_index LOCATION ‘/ssd1/fgedutbss/index’;
CREATE TABLESPACE fgedu_archive LOCATION ‘/hdd1/fgedutbss/archive’;
CREATE TABLESPACE fgedu_backup LOCATION ‘/hdd1/fgedutbss/backup’;

# 移动对象到表空间
ALTER TABLE fgedu_fgedus SET TABLESPACE fgedu_data;
ALTER INDEX idx_fgedu_fgedus_email SET TABLESPACE fgedu_index;

# 创建分区表
CREATE TABLE fgedu_fgfgfgsales (
id SERIAL,
sale_date DATE NOT NULL,
amount NUMERIC(10,2) NOT NULL,
customer_id INTEGER NOT NULL
)
PARTITION BY RANGE (sale_date);

# 创建分区
CREATE TABLE fgedu_fgfgfgsales_2024_q1
PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2024-01-01’) TO (‘2024-04-01’);

CREATE TABLE fgedu_fgfgfgsales_2024_q2
PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2024-04-01’) TO (‘2024-07-01’);

CREATE TABLE fgedu_fgfgfgsales_2024_q3
PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2024-07-01’) TO (‘2024-10-01’);

CREATE TABLE fgedu_fgfgfgsales_2024_q4
PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2024-10-01’) TO (‘2025-01-01’);

# 创建索引
CREATE INDEX idx_fgedu_fgfgfgsales_customer_id ON fgedu_fgfgfgsales(customer_id);
CREATE INDEX idx_fgedu_fgfgfgsales_sale_date ON fgedu_fgfgfgsales(sale_date);

# 插入数据
INSERT INTO fgedu_fgfgfgsales (sale_date, amount, customer_id)
VALUES (‘2024-01-15’, 100.00, 1),
(‘2024-04-15’, 200.00, 2),
(‘2024-07-15’, 300.00, 3),
(‘2024-10-15’, 400.00, 4);

# 分析表
ANALYZE fgedu_fgedus;

# 查看执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 创建索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# 再次查看执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 优化查询语句
— 避免SELECT *
SELECT id, fgeduname FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

— 使用LIMIT
SELECT * FROM fgedu_fgedus ORDER BY id LIMIT 10;

— 使用JOIN优化
SELECT u.id, u.fgeduname, o.amount
FROM fgedu_fgedus u
JOIN fgedu_orders o ON u.id = o.customer_id
WHERE u.email = ‘fgedu@fgedu.net.cn’;

# 批量操作
BEGIN;
INSERT INTO fgedu_fgedus (fgeduname, email) VALUES
(‘fgedu1’, ‘fgedu1@fgedu.net.cn’),
(‘fgedu2’, ‘fgedu2@fgedu.net.cn’),
(‘fgedu3’, ‘fgedu3@fgedu.net.cn’);
COMMIT;

# 使用保存点
BEGIN;
INSERT INTO fgedu_orders (customer_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO fgedu_order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
ROLLBACK TO sp1;
INSERT INTO fgedu_order_items (order_id, product_id, quantity) VALUES (1, 2, 1);
COMMIT;

# 优化事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM fgedu_fgedus WHERE id = 1;
UPDATE fgedu_fgedus SET fgeduname = ‘newfgedu’ WHERE id = 1;
COMMIT;