PostgreSQL中的自增列：SERIAL 与 IDENTITY 类型的实现原理与分布式方案

一、故事开篇：数据库里的身份证号

当我们设计数据库表结构时，总需要为每行数据准备一个独特的身份证号——主键。在PostgreSQL数据库里，最经典的两种自增字段解决方案莫过于SERIAL和IDENTITY类型。但你知道吗？这两种看似相似的方案，内部竟藏着截然不同的实现逻辑。当我们将视角延伸到分布式架构时，它们的表现差异更让人瞠目结舌。

二、探秘SERIAL类型

2.1 SERIAL的实现本质

SERIAL本质上是个语法糖，它的真实身份由三个部件构成：

-- 创建SERIAL字段时，系统自动生成序列和依赖关系
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 神秘的SERIAL背后
    username VARCHAR(50)
);

-- 等效的手动操作
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq'),
    username VARCHAR(50)
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

2.2 重要特性展示

通过系统目录表可以窥探到它们的依存关系：

-- 查看序列的从属关系（技术栈：PostgreSQL 14）
SELECT sequencename, sequenceowner, obj_description(oid) 
FROM pg_sequences 
WHERE sequencename LIKE 'users_id_seq';

-- 查询列默认值表达式（示例输出）
\d+ users

-- 输出结果节选：
-- Column |  Type   | Collation | Nullable | Default              
-- -------+---------+-----------+----------+----------------------
-- id     | integer |           | not null | nextval('users_id_seq'::regclass)

三、IDENTITY类型解剖

3.1 SQL标准实现

IDENTITY类型作为PostgreSQL 10引入的新特性，体现了更优雅的标准实现：

CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY 
        (START WITH 100 INCREMENT BY 2),
    product_code VARCHAR(20)
);

-- 插入测试
INSERT INTO orders (product_code) VALUES ('P1001');
INSERT INTO orders (product_code) VALUES ('P1002');
SELECT * FROM orders;

-- 输出结果：
-- order_id | product_code
-- ---------+-------------
--      100 | P1001
--      102 | P1002

3.2 系统级管控差异

查看系统元数据会发现截然不同的信息结构：

-- 查看列属性（技术栈：PostgreSQL 14）
SELECT a.attname, 
       pg_get_expr(d.adbin, d.adrelid) as default_expr
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)
WHERE a.attrelid = 'orders'::regclass;

-- 查询结果示例：
-- attname  |              default_expr              
-- ---------+-----------------------------------------
-- order_id | GENERATED BY DEFAULT AS IDENTITY (...)

四、分布式环境的噩梦

4.1 单机世界的优雅陷阱

假设我们有一个简单的分片设计：

-- 分片1配置
CREATE TABLE shard_1.products (
    product_id INT GENERATED ALWAYS AS IDENTITY,
    shard_key INT CHECK (shard_key = 1)
);

-- 分片2配置
CREATE TABLE shard_2.products (
    product_id INT GENERATED ALWAYS AS IDENTITY,
    shard_key INT CHECK (shard_key = 2)
);

当同时向两个分片插入数据时，主键就会发生冲突：

分片1数据：1, 2, 3...
分片2数据：1, 2, 3...

4.2 经典解决方案

使用步长调整策略：

-- 分片1初始序列
ALTER SEQUENCE shard_1_products_product_id_seq 
    START WITH 1 INCREMENT BY 5;

-- 分片2初始序列
ALTER SEQUENCE shard_2_products_product_id_seq 
    START WITH 2 INCREMENT BY 5;

但这种方案面临三个致命问题：

1. 需要精确的全局协调
2. 扩容时面临步长重新计算
3. 人为修改序列值的风险

五、深度技术对比

5.1 底层实现比较

通过EXPLAIN命令观察查询计划：

EXPLAIN ANALYZE INSERT INTO serial_table DEFAULT VALUES;
EXPLAIN ANALYZE INSERT INTO identity_table DEFAULT VALUES;

-- 典型计划对比：
-- SERIAL类型查询计划：会显示sequence值获取步骤
-- IDENTITY类型查询计划：采用更优化的执行路径

5.2 事务测试实验

开启两个会话进行并发插入测试：

-- 会话1
BEGIN;
SELECT nextval('users_id_seq');  -- 返回100

-- 会话2
BEGIN;
SELECT nextval('users_id_seq');  -- 返回101

-- 序列不等待事务提交的特性
-- 可能导致回滚后的序列值空洞

六、新型分布式解决方案

6.1 外部协调器方案

使用Redis实现的分布式ID生成服务：

# 分布式ID生成服务（技术栈：Python + Redis）
import redis

class DistributedIDGenerator:
    def __init__(self):
        self.redis = redis.Redis(host='redis-cluster')
        
    def get_next_id(self, business_type):
        key = f"dist_id_{business_type}"
        return self.redis.incr(key)

6.2 改进版雪花算法

适用于PostgreSQL的封装版本：

CREATE OR REPLACE FUNCTION snowflake_id() 
RETURNS BIGINT AS $$
DECLARE
    epoch BIGINT = 1609459200000; -- 2021-01-01
    seq_id BIGINT;
BEGIN
    seq_id = nextval('snowflake_seq') % 4096;
    RETURN (
        (EXTRACT(EPOCH FROM clock_timestamp()) * 1000 - epoch) << 22) 
        | (1 << 17)  -- 假设机器ID
        | seq_id);
END;
$$ LANGUAGE plpgsql;

七、核心结论指引

7.1 功能选型矩阵

7.2 黄金操作准则

1. 数据迁移场景：从SERIAL迁移到IDENTITY时，需注意序列所有权变更问题
2. 主从复制环境：使用逻辑复制时要特别注意序列复制配置
3. 分布式事务：当使用XA事务时需要显式控制ID生成