前言
VACUUM 是 PostgreSQL MVCC (Multiversion concurrency control) 实现的核心机制之一,是 PostgreSQL 正常运行的重要保证。本文将通过实例演示 PostgreSQL 为什么需要做 VACUUM,以及一步一步精准触发 AUTOVACUUM, 到 VACUUM 优化实战,深入浅出,一看就懂。
测试环境准备
以下测试是在 PostgreSQL 11 中进行。
通过以下 SQL 创建:
测试用户: alvin,普通用户,非 superuser
测试数据库: alvindb,owner 是 alvin
测试 schema: alvin,owner 也是 alvin
这里采用的是 user 与 schema 同名,结合默认的 search_path("$user", public),这样操作对象(table, sequence, etc.)时就不需要加 schema 前缀了。
postgres=# CREATE USER alvin WITH PASSWORD 'alvin';
CREATE ROLE
postgres=# CREATE DATABASE alvindb OWNER alvin;
CREATE DATABASE
postgres=# \c alvindb
You are now connected to database "alvindb" as user "postgres".
alvindb=# CREATE SCHEMA alvin AUTHORIZATION alvin;
CREATE SCHEMA
alvindb=# \c alvindb alvin
You are now connected to database "alvindb" as user "alvin".
alvindb=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
PostgreSQL 为什么需要做 VACUUM
这要从 PostgreSQL MVCC UPDATE/DELETE 实现讲起。
下面通过简单演示 PostgreSQL 中 UPDATE/DELETE 时底层数据变化,揭秘其 MVCC 设计的艺术。
为了方便看其底层数据,通过 superuser postgres 创建 extension pageinspect:
$ psql -d alvindb -U postgres
alvindb=# CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION
alvindb=# \dx pageinspect
List of installed extensions
Name | Version | Schema | Description
-------------+---------+--------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
(1 row)
首先,创建测试表
$ psql -d alvindb -U alvin
alvindb=>
CREATE TABLE tb_test_vacuum (
test_id BIGSERIAL PRIMARY KEY,
test_num BIGINT
);
CREATE TABLE
插入 3 条测试数据
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT 0 3
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
test_id | test_num
---------+----------
3 | 3
2 | 2
1 | 1
(3 rows)
查看其底层数据。
alvindb=> SELECT