PostgreSQL 数据碎片整理与表空间优化的全过程

vacuum table_name;

vacuum full table_name;

autovacuum_vacuum_threshold = 50          # 触发 vacuum 的最小死亡元组数
autovacuum_vacuum_scale_factor = 0.2      # 表大小的 20% + 50 行
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

alter table orders set (autovacuum_vacuum_scale_factor = 0.05);

select
    schemaname,
    tablename,
    pg_size_pretty(pg_table_size(schemaname || '.' || tablename)) as real_size,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    n_tup_ins - n_tup_del as net_rows,
    n_dead_tup,
    round(100.0 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1), 1) as dead_pct
from pg_stat_user_tables
where n_dead_tup > 1000
order by dead_pct desc;

select
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_tup_read,
    idx_tup_fetch,
    case
        when idx_tup_read = 0 then 'never used'
        when idx_tup_fetch = 0 then 'only for scans'
        else 'active'
    end as usage
from pg_stat_user_indexes
join pg_index on pg_stat_user_indexes.indexrelid = pg_index.indexrelid
where pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- >10mb
order by pg_relation_size(indexrelid) desc;

create extension if not exists pgstattuple;
select * from pgstattuple('orders');

sudo apt-get install postgresql-14-repack

pg_repack -d your_db -t orders

-- 假设 /ssd/data 是一个高速 ssd 挂载点
create tablespace fast_ssd location '/ssd/data';
-- 将表创建在指定表空间
create table hot_orders (
    id serial primary key,
    user_id int,
    amount numeric
) tablespace fast_ssd;
-- 将现有表移动到新表空间
alter table cold_data set tablespace slow_hdd;

select
    spcname as tablespace_name,
    pg_size_pretty(pg_tablespace_size(oid)) as size
from pg_tablespace;

@component
public class tablespacemigrator {
    @autowired
    private jdbctemplate jdbctemplate;
    // 每天凌晨 2 点执行
    @scheduled(cron = "0 0 2 * * ?")
    public void migratecoldorders() {
        string sql = """
            alter table orders 
            set tablespace slow_hdd 
            where created_at < now() - interval '90 days'
            """;
        // 注意：postgresql 不支持 where 子句的 alter table
        // 实际应通过分区表或物化视图实现
        // 正确做法：使用分区表（见下文）
    }
}

-- 主表（分区父表）
create table orders (
    id serial,
    order_date date not null,
    amount numeric
) partition by range (order_date);
-- 2023 年分区（放在 ssd）
create table orders_2023 partition of orders
for values from ('2023-01-01') to ('2024-01-01')
tablespace fast_ssd;
-- 2022 年分区（放在 hdd）
create table orders_2022 partition of orders
for values from ('2022-01-01') to ('2023-01-01')
tablespace slow_hdd;

@entity
@table(name = "orders")
public class order {
    @id
    private long id;
    private localdate orderdate;
    private bigdecimal amount;
}
// repository
public interface orderrepository extends jparepository<order, long> {
    // 自动路由到对应分区
    list<order> findbyorderdatebetween(localdate start, localdate end);
}

@scheduled(cron = "0 0 1 1 * ?") // 每月 1 日
public void createnextmonthpartition() {
    localdate nextmonth = localdate.now().plusmonths(1);
    string partitionname = "orders_" + nextmonth.getyear();
    string tablespace = nextmonth.isafter(localdate.now().plusmonths(6)) ? "slow_hdd" : "fast_ssd";
    string sql = string.format(
        "create table if not exists %s partition of orders " +
        "for values from ('%s-01-01') to ('%s-01-01') " +
        "tablespace %s",
        partitionname,
        nextmonth.getyear(), nextmonth.plusyears(1).getyear(),
        tablespace
    );
    jdbctemplate.execute(sql);
}

# prometheus.yml
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']

cluster orders using idx_orders_date;

explain analyze
select * from orders where order_date between '2023-06-01' and '2023-06-30';

-> seq scan on orders_2023 (...)

@transactional
public void updateorderstatusbatch(list<long> ids, string status) {
    string sql = "update orders set status = ? where id = any(?)";
    jdbctemplate.update(sql, status, ids.toarray());
}

string sql = """
    insert into user_stats (user_id, login_count, last_login)
    values (?, 1, now())
    on conflict (user_id)
    do update set 
        login_count = user_stats.login_count + 1,
        last_login = now()
    """;
jdbctemplate.update(sql, userid);

public map<string, object> getvacuumstats(string tablename) {
    string sql = """
        select n_dead_tup, n_live_tup, last_autovacuum
        from pg_stat_user_tables
        where relname = ?
        """;
    return jdbctemplate.queryformap(sql, tablename);
}

// 默认 read committed 即可
@transactional(isolation = isolation.read_committed)
public void processorder(long orderid) {
    // 业务逻辑
}