PostgreSQL “改个字段就锁表”黑历史--DDL操作中的问题解决了吗？

AustinDatabases

发布于 2026-05-29 10:43:10

480

最近有人在问一个问题，PostgreSQL这么好，那么好，就是上手不友好。这话从哪里来的，其实就是从DDL来的，修改字段类型 int -- bigint 重建表。不过随着数据库的版本的更新一些老的PostgreSQL的问题其实已经解决了很多。

今天我就拿PG18来说说PG在这方面的进步和已经解决的问题。那些原来的问题已经解决，在操作中可以不进行rewrite。

我们这样通过 SELECT pg_relation_size('orders'); 来查看做DDL之前和之后的表的大小，来分辨出来到底有没有重建表。

问题1 添加自动带有默认值

这个问题在早期的PG11之前的版本存在，后面从PG11后就解决了这个问题，

我们在PG18上对这个语句进行验证，在验证前先进行建表和冲入数据的工作

postgres=# create database test;
CREATE DATABASE
postgres=# CREATE TABLE orders
postgres-# (
postgres(#     id bigint PRIMARY KEY,
postgres(#     user_id bigint,
postgres(#     amount numeric(12,2),
postgres(#     buyer_name varchar(100),
postgres(#     memo text,
postgres(#     created_at timestamp
postgres(# );
CREATE TABLE
postgres=# INSERT INTO orders
postgres-# SELECT
postgres-#     gs,
postgres-#     (random()*100000)::bigint,
postgres-#     round((random()*10000)::numeric,2),
postgres-#     md5(gs::text),
postgres-#     repeat(md5(gs::text),2),
postgres-#     now()-(random()*interval '365 days')
postgres-# FROM generate_series(1,1000000) gs;
INSERT 0 1000000
postgres=# SELECT pg_relation_size('orders');
 pg_relation_size 
------------------
        167182336
(1 row)

postgres=# UPDATE orders
postgres-# SET
postgres-#     amount = round((random() * 10000)::numeric, 2),
postgres-#     memo = md5(random()::text)
postgres-# WHERE id IN (
postgres(#     SELECT id
postgres(#     FROM orders
postgres(#     ORDER BY random()
postgres(#     LIMIT 100000
postgres(# );
UPDATE 100000
postgres=# SELECT pg_relation_size('orders');
 pg_relation_size 
------------------
        177848320
(1 row)

postgres=# ALTER TABLE orders
postgres-# ADD COLUMN status varchar(20) DEFAULT 'CREATED';
ALTER TABLE
postgres=# SELECT pg_relation_size('orders');
 pg_relation_size 
------------------
        177848320
(1 row)

postgres=# select * from orders limit 1;
 id | user_id | amount  |            buyer_name            |                               memo                      
         |         created_at         | status  
----+---------+---------+----------------------------------+---------------------------------------------------------
---------+----------------------------+---------
  1 |   13920 | 5760.62 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a
6f75849b | 2026-03-05 08:41:30.811358 | CREATED
(1 row)

postgres=#

上面100万的数据，更新了10万行，然后对表添加字段，并给出默认值，这是可以的。那么在PG18上，如果是给出默认值，添加字段还给出NOT NULL呢？是否可以

我们看看,结果是一样的，同样是OK的。

postgres=# SELECT pg_relation_size('orders');
 pg_relation_size 
------------------
        177848320
(1 row)

postgres=# ALTER TABLE orders ADD COLUMN statuss varchar(20) NOT NULL DEFAULT 'CREATED';
ALTER TABLE
postgres=# SELECT pg_relation_size('orders');
 pg_relation_size 
------------------
        177848320
(1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from orders limit 1;
-[ RECORD 1 ]----------------------------------------------------------------
id         | 1
user_id    | 13920
amount     | 5760.62
buyer_name | c4ca4238a0b923820dcc509a6f75849b
memo       | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
created_at | 2026-03-05 08:41:30.811358
status     | CREATED
statuss    | CREATED

postgres=#

为什么给出NOT NULL数据库也不重写表了，这是PostgreSQL 11 引入的 Fast Default核心思想默认值不再强制写入每一行只在系统目录（pg_attribute）中记录，新列存在默认值是什么（如 'CREATED'）旧行磁盘上不存储该列查询时由执行器动态补齐默认值。

在修改后，SELECT * FROM orders LIMIT 1; 执行器在读取旧 tuple 时发现该 tuple 缺少 status列从 pg_attribute读取默认值动态补上status = 'CREATED'。

NOT NULL也没有触发表重写ALTER TABLE orders ADD COLUMN status varchar(20) NOT NULL DEFAULT 'CREATED';是系统推出数据库的推理逻辑PostgreSQL 能推导出所有旧行在读取时都会自动补上默认值 'CREATED''CREATED'≠ NULL因此NOT NULL 约束天然成立不需要扫描全表更新每一行重写堆表（heap rewrite）。

那么剩下的哪些在PostgreSQL中操作的还是会产生重建表到了PG18中的，或者我们认为已经不是了的，我们来确认一下。

1 之前 bigint -- int int --- bigint 会重建表我们看下面的实验过程，到PG18 INT -- BIGINT BIGINT --INT 都是会触发重建表的，

在每次操作修改字段类型，文件的filenode都会改变，大小也会改变。

postgres=# DROP TABLE IF EXISTS orders;
DROP TABLE
postgres=# 
postgres=# CREATE TABLE orders(
postgres(#     id int,
postgres(#     user_id int,
postgres(#     amount numeric(12,2),
postgres(#     memo text,
postgres(#     created_at timestamp default now()
postgres(# );
CREATE TABLE
postgres=# INSERT INTO orders
postgres-# SELECT
postgres-#     gs,
postgres-#     (random()*100000)::int,
postgres-#     round((random()*10000)::numeric,2),
postgres-#     repeat(md5(gs::text),2),
postgres-#     clock_timestamp()
postgres-# FROM generate_series(1,1000000) gs;
INSERT 0 1000000
postgres=# 
postgres=# 
postgres=# 
postgres=# SELECT
postgres-#     oid::regclass,
postgres-#     relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]-------
oid         | orders
relfilenode | 16402

postgres=# SELECT
postgres-#     pg_size_pretty(pg_relation_size('orders'));
-[ RECORD 1 ]--+-------
pg_size_pretty | 112 MB

postgres=# SELECT relpages,reltuples
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]----
relpages  | 14286
reltuples | 1e+06

postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN id TYPE bigint;
ALTER TABLE
postgres=# SELECT relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16409

postgres=# SELECT
postgres-# pg_size_pretty(pg_relation_size('orders'));
-[ RECORD 1 ]--+-------
pg_size_pretty | 121 MB

postgres=# SELECT
postgres-#     pg_column_size(id)
postgres-# FROM orders
postgres-# LIMIT 5;
-[ RECORD 1 ]--+--
pg_column_size | 8
-[ RECORD 2 ]--+--
pg_column_size | 8
-[ RECORD 3 ]--+--
pg_column_size | 8
-[ RECORD 4 ]--+--
pg_column_size | 8
-[ RECORD 5 ]--+--
pg_column_size | 8

postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN id TYPE int;
ALTER TABLE
postgres=# SELECT relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16414

最后的实验是varchar的部分，小改大，大改小，varchar 改成text等等的实验，看看是否在那一块存在重建表的情况。

postgres=# DROP TABLE IF EXISTS orders;
DROP TABLE
postgres=# 
postgres=# CREATE TABLE orders(
postgres(#     id bigint,
postgres(#     buyer_name varchar(100),
postgres(#     memo text,
postgres(#     email varchar(100),
postgres(#     created_at timestamp,
postgres(#     created_date date
postgres(# );
CREATE TABLE
postgres=# INSERT INTO orders
postgres-# SELECT
postgres-#     gs,
postgres-#     substr(md5(gs::text),1,20),
postgres-#     repeat(md5(gs::text),2),
postgres-#     md5(gs::text)||'@mail.com',
postgres-#     clock_timestamp(),
postgres-#     current_date
postgres-# FROM generate_series(1,1000000) gs;
INSERT 0 1000000
postgres=# 
postgres=# 
postgres=# 
postgres=# 
postgres=# SELECT relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16419

postgres=# 
postgres=# 
postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN buyer_name
postgres-# TYPE varchar(500);
ALTER TABLE
postgres=# 
postgres=# SELECT relfilenode
FROM pg_class
WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16419

postgres=# 
postgres=# 
postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN buyer_name
postgres-# TYPE varchar(20);
ALTER TABLE
postgres=# SELECT relfilenode
FROM pg_class
WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16424

postgres=# 
postgres=# 
postgres=# SELECT relfilenode
FROM pg_class
WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16424

postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN buyer_name
postgres-# TYPE text;
ALTER TABLE
postgres=# SELECT relfilenode
FROM pg_class
WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16424

postgres=# ALTER TABLE orders
postgres-# ALTER COLUMN memo
postgres-# TYPE varchar(500);
ALTER TABLE
postgres=# SELECT relfilenode
FROM pg_class
WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16432

以上的测试中，普通的varchar小改大是不会触发重建表的，但其他的方式大改小都是会触发重建表的,varchar修改成text也是不会触发重建表的。

下面还有一个部分也是我们需要讨论的，drop column

postgres=# 
postgres=# DROP TABLE IF EXISTS orders;
DROP TABLE
postgres=# 
postgres=# CREATE TABLE orders(
postgres(#     id bigint,
postgres(#     buyer_name varchar(100),
postgres(#     memo text,
postgres(#     amount numeric(12,2),
postgres(#     created_at timestamp
postgres(# );
CREATE TABLE
postgres=# INSERT INTO orders
postgres-# SELECT
postgres-#     gs,
postgres-#     md5(gs::text),
postgres-#     repeat(md5(gs::text),2),
postgres-#     round((random()*10000)::numeric,2),
postgres-#     clock_timestamp()
postgres-# FROM generate_series(1,1000000) gs;
INSERT 0 1000000
postgres=# 
postgres=# 
postgres=# 
postgres=# SELECT
postgres-#     relname,
postgres-#     relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]-------
relname     | orders
relfilenode | 16437

postgres=# SELECT
postgres-#     pg_size_pretty(pg_relation_size('orders'));
-[ RECORD 1 ]--+-------
pg_size_pretty | 150 MB

postgres=# ALTER TABLE orders
postgres-# DROP COLUMN memo;
ALTER TABLE
postgres=# SELECT relfilenode
postgres-# FROM pg_class
postgres-# WHERE oid='orders'::regclass;
-[ RECORD 1 ]------
relfilenode | 16437

从上面的信息中，我们可以看到删除字段并没有引发重建表，这也就意味着删除字段不能回收空间。在进行了PostgreSQL 表删除字段后，并不会立即把历史 tuple 中该字段的数据从磁盘页中擦除。实际上发生的是pg_attribute 中将该列标记为 droppedSQL 层不再可见新查询忽略该列旧数据页暂时保留。真正想回收这个数据库空间，必须重建表或者通过vacuumF Full 的方式来进行。