OpenResty + PostgreSQL 实现分布式自增 ID API

当你的业务做到需要分布式部署，第一个卡住你的往往不是数据库性能，而是 ID 怎么保证全局唯一。

UUID 是个方案，但 UUID v4 无序，对 B+ 树索引不友好——插入性能随数据量增长急剧下降。UUID v7 有时序性，但 128 位太长，做 URL 路径参数不忍直视。雪花算法（Snowflake）依赖时钟，一旦时钟回拨就是灾难。

我最常用的方案：PostgreSQL 序列 + OpenResty 缓存。架构简单、代码可读、运维成本低，单机能扛住大部分中小业务的 QPS。这篇文章就是它的完整实现。

整体方案

客户端 → Nginx/OpenResty (:8080) → pgmoon (Lua) → PostgreSQL (序列)

数据流只有三步：

1. Nginx 接收 GET /next-id 请求
2. OpenResty 用 Lua 调用 pgmoon 库连接 PostgreSQL
3. PostgreSQL 执行 nextval('global_id_seq') 返回下一个值

全局唯一由数据库的序列原子操作保证，你不需要在应用层加锁、不用管分布式协调、不用处理时钟同步。

一、环境准备

1. PostgreSQL 创建序列

连接你的 PostgreSQL 执行：

CREATE SEQUENCE global_id_seq
    START WITH 1
    INCREMENT BY 1
    NO CYCLE;

验证：

SELECT nextval('global_id_seq') AS id;

每次执行都会返回递增的整数值，不会重复。

2. OpenResty 安装 pgmoon

# 用 OPM 包管理器安装 pgmoon
opm install leafo/pgmoon

如果你的 OpenResty 没有安装 OPM，也可以直接从 GitHub 拉 pgmoon 源码放到 lua_package_path 目录：

git clone https://github.com/leafo/pgmoon.git /usr/local/openresty/site/lualib/pgmoon

二、Nginx 完整配置

worker_processes  1;
error_log stderr info;

events {
    worker_connections 1024;
}

http {
    lua_package_path "/usr/local/openresty/site/lualib/?.lua;;";
    lua_package_cpath "/usr/local/openresty/site/lualib/?.so;;";

    # 缓存 Lua shared dict——用于连接池/可选的本地 ID 缓存
    lua_shared_dict pg_conn_cache 10m;

    server {
        listen 8080;
        server_name localhost;

        location /next-id {
            content_by_lua_block {
                local pgmoon = require "pgmoon"
                local cjson = require "cjson"

                -- 从环境变量读取数据库配置，绝不硬编码密码
                local pg_config = {
                    host = os.getenv("PG_HOST") or "127.0.0.1",
                    port = os.getenv("PG_PORT") or "5432",
                    database = os.getenv("PG_DATABASE") or "test_db",
                    user = os.getenv("PG_USER") or "postgres",
                    password = os.getenv("PG_PASSWORD")  -- 必须设置
                }

                if not pg_config.password then
                    ngx.status = 500
                    ngx.say(cjson.encode({
                        code = 500,
                        msg = "PG_PASSWORD not set"
                    }))
                    return
                end

                local pg, err = pgmoon.new(pg_config)
                if not pg then
                    ngx.status = 500
                    ngx.say(cjson.encode({
                        code = 500,
                        msg = "create pg client failed",
                        error = err
                    }))
                    return
                end

                local ok, conn_err = pg:connect()
                if not ok then
                    ngx.status = 500
                    ngx.say(cjson.encode({
                        code = 500,
                        msg = "connect postgresql failed",
                        error = conn_err
                    }))
                    return
                end

                local res, q_err = pg:query("SELECT nextval('global_id_seq') AS id;")
                if not res then
                    ngx.status = 500
                    ngx.say(cjson.encode({
                        code = 500,
                        msg = "query seq failed",
                        error = q_err
                    }))
                    pg:close()
                    return
                end

                local id = res[1].id

                -- 归还连接到连接池，60 秒超时
                pg:keepalive(60000)

                ngx.header["Content-Type"] = "application/json; charset=utf-8"
                ngx.say(cjson.encode({
                    code = 0,
                    msg = "success",
                    data = {
                        id = id
                    }
                }))
            }
        }
    }
}

启动：

openresty -c /path/to/nginx.conf

三、测试

curl http://127.0.0.1:8080/next-id

返回：

{"code":0,"msg":"success","data":{"id":1}}

每次请求返回的 id 递增 1。

四、核心设计点

1. 连接池

pg:keepalive(60000) 不会关闭连接，而是归还到 OpenResty 的连接池复用。避免每次请求新建 TCP 连接到 PostgreSQL，性能差距在一个数量级以上。

2. 序列的原子性

PostgreSQL 的 nextval() 是数据库内置的原子操作，多个并发请求同时调用不会出现重复值。事务隔离、锁竞争由 PostgreSQL 内部处理，应用层不需要关注。

3. 密码安全

配置里用了 os.getenv("PG_PASSWORD")——不要在 nginx.conf 里硬编码数据库密码。通过 systemd 或启动脚本注入环境变量：

PG_PASSWORD="your-pass" openresty -c /path/nginx.conf

或者写进 OpenResty 的 env 指令：

env PG_PASSWORD;

五、高级优化：本地 ID 缓存

上面的方案每次都查数据库。如果 QPS 上千，这个模式的瓶颈在 PostgreSQL 的 IO 上。

优化思路：批量预取 ID 缓存在 OpenResty 的 shared dict 中。

实现逻辑：

1. Lua 启动时从 shared dict 取一段连续 ID（比如 1-1000）
2. 每次请求从中取一个，移除该条
3. 缓存耗尽时，用 setval 批量取下一段（比如 setval 跳到 2001，拿到 1001-2000）
4. 数据库访问次数从 QPS 降到 N QPS / batch_size

这样 1000 QPS 的场景，数据库只需要每秒 1 次查询。代价是重启后会丢失未用完的 ID 段——但对于自增 ID 来说这不是问题，空段不影响业务。

简化版预取代码

local dict = ngx.shared.pg_conn_cache

-- atomic incr，不存在则初始化为 1
local id = dict:incr("id_counter", 1, 1)
local max_id = dict:get("id_max")

if not max_id or id > max_id then
    -- 缓存耗尽，从数据库取新一批
    local res = pg:query("SELECT nextval('global_id_seq') AS id")
    local batch_start = res[1].id
    max_id = batch_start + 999  -- 一批取 1000 个
    pg:query("SELECT setval('global_id_seq', " .. max_id .. ")")

    dict:set("id_max", max_id)
    dict:set("id_counter", batch_start + 1)
    id = batch_start
end

-- id 就是可用的自增值

用 incr 而不是 rpop/lpush——ngx.shared.DICT 是 key-value 存储，没有列表操作。incr 是原子方法，并发安全。

注意：这个优化不是必须的。对于日均请求百万以内的场景，不优化直接查序列也不会有问题。别过度工程。

六、安全补充

几个生产环境必须做的事：

• 速率限制：用 limit_req_zone 限制单 IP 请求频率
• 鉴权：加上简单的 Header Token 校验，防止被乱刷
• 密码不要明文：前面已经说了，环境变量走起
• TLS：如果 API 对外暴露，加 HTTPS

七、局限性与选型边界

坦白说这个方案的局限性：

如果你要的是跨多机房的全局唯一 ID、或者 ID 不能暴露业务量——这个方案不适合你。去看雪花算法（Snowflake）或者美团的 Leaf 方案。

但如果你只是一个 PostgreSQL 实例、多个应用实例，需要一个简单可靠的全局自增 ID——这就是最轻的解法。

Distributed Auto-Increment ID API with OpenResty + PostgreSQL

When your business scales to distributed deployment, the first bottleneck is often not database performance — it's how to guarantee globally unique IDs across instances.

UUID v4 is an option, but it's unordered and unfriendly to B+ tree indexes. UUID v7 has temporal ordering but wastes 128 bits. Snowflake depends on clock synchronization — clock drift or rollback is a nightmare to handle.

My go-to solution: PostgreSQL sequence + OpenResty caching. Simple architecture, readable code, low operational overhead. It handles the QPS of most small-to-medium businesses on a single machine. Here's the complete implementation.

Architecture

Client → Nginx/OpenResty (:8080) → pgmoon (Lua) → PostgreSQL (sequence)

Three steps:

1. Nginx receives GET /next-id
2. OpenResty connects to PostgreSQL via pgmoon (Lua library)
3. PostgreSQL executes nextval('global_id_seq') and returns the next value

Global uniqueness is guaranteed by the database's atomic sequence operations. No application-level locking, no distributed coordination, no clock sync handling.

1. Environment Setup

PostgreSQL Sequence

CREATE SEQUENCE global_id_seq
    START WITH 1
    INCREMENT BY 1
    NO CYCLE;

-- Verify
SELECT nextval('global_id_seq') AS id;

Install pgmoon for OpenResty

opm install leafo/pgmoon

Or clone the source:

git clone https://github.com/leafo/pgmoon.git /usr/local/openresty/site/lualib/pgmoon

2. Nginx Configuration

worker_processes  1;
error_log stderr info;

events {
    worker_connections 1024;
}

http {
    lua_package_path "/usr/local/openresty/site/lualib/?.lua;;";
    lua_package_cpath "/usr/local/openresty/site/lualib/?.so;;";

    lua_shared_dict pg_conn_cache 10m;

    server {
        listen 8080;
        server_name localhost;

        location /next-id {
            content_by_lua_block {
                local pgmoon = require "pgmoon"
                local cjson = require "cjson"

                -- Read DB config from environment — never hardcode passwords
                local pg_config = {
                    host = os.getenv("PG_HOST") or "127.0.0.1",
                    port = os.getenv("PG_PORT") or "5432",
                    database = os.getenv("PG_DATABASE") or "test_db",
                    user = os.getenv("PG_USER") or "postgres",
                    password = os.getenv("PG_PASSWORD")  -- required
                }

                if not pg_config.password then
                    ngx.status = 500
                    ngx.say(cjson.encode({code = 500, msg = "PG_PASSWORD not set"}))
                    return
                end

                local pg, err = pgmoon.new(pg_config)
                if not pg then
                    ngx.status = 500
                    ngx.say(cjson.encode({code = 500, msg = "create pg client failed", error = err}))
                    return
                end

                local ok, conn_err = pg:connect()
                if not ok then
                    ngx.status = 500
                    ngx.say(cjson.encode({code = 500, msg = "connect postgresql failed", error = conn_err}))
                    return
                end

                local res, q_err = pg:query("SELECT nextval('global_id_seq') AS id;")
                if not res then
                    ngx.status = 500
                    ngx.say(cjson.encode({code = 500, msg = "query seq failed", error = q_err}))
                    pg:close()
                    return
                end

                local id = res[1].id
                pg:keepalive(60000)

                ngx.header["Content-Type"] = "application/json; charset=utf-8"
                ngx.say(cjson.encode({code = 0, msg = "success", data = {id = id}}))
            }
        }
    }
}

Start:

openresty -c /path/to/nginx.conf

3. Test

curl http://127.0.0.1:8080/next-id

Response:

{"code":0,"msg":"success","data":{"id":1}}

4. Key Design Decisions

Connection pooling: pg:keepalive(60000) returns the connection to OpenResty's connection pool instead of closing it. This avoids creating a new TCP connection to PostgreSQL on every request — orders of magnitude faster.

Atomic sequences: PostgreSQL's nextval() is an atomic operation. Concurrent calls never produce duplicate IDs. The database handles all locking internally. Your application doesn't need to worry about race conditions.

Password security: Use os.getenv("PG_PASSWORD") — never hardcode database credentials in nginx.conf. Inject them via systemd unit files or startup scripts.

5. Performance Optimization: Local ID Cache

The baseline implementation hits the database on every request. At thousands of QPS, this becomes a bottleneck.

The optimization: Batch-prefetch a range of IDs into OpenResty's shared dict.

Logic:

1. Lua pops one ID from a cached pool in shared dict
2. When the pool is empty, fetch the next batch from PostgreSQL (e.g., 1000 IDs)
3. Database queries drop from QPS to QPS / batch_size

For 1000 QPS with batch size 1000, that's 1 DB query per second. The cost: unused IDs are lost on restart, which is harmless for auto-increment use cases.

Simplified Prefetch Code

local dict = ngx.shared.pg_conn_cache

-- atomic incr, init to 1 if missing
local id = dict:incr("id_counter", 1, 1)
local max_id = dict:get("id_max")

if not max_id or id > max_id then
    -- Pool exhausted, fetch from DB
    local res = pg:query("SELECT nextval('global_id_seq') AS id")
    local batch_start = res[1].id
    max_id = batch_start + 999
    pg:query("SELECT setval('global_id_seq', " .. max_id .. ")")

    dict:set("id_max", max_id)
    dict:set("id_counter", batch_start + 1)
    id = batch_start
end

-- id is the usable auto-increment value

Use incr instead of rpop/lpush — ngx.shared.DICT is a key-value store, not a Redis list. incr is atomic and concurrency-safe.

Don't over-engineer: For under a million daily requests, skip the cache and query the sequence directly. It works fine.

6. Production Security Checklist

• Rate limiting: Use limit_req_zone to throttle per-IP requests
• Authentication: Add a simple header token check
• Credentials: Environment variables for everything
• TLS: HTTPS if the API is publicly exposed

7. Limitations & When Not to Use This

If you need globally unique IDs across multiple data centers or IDs that conceal business scale — this isn't the right solution. Look into Snowflake or Meituan's Leaf.

But if you have one PostgreSQL instance and multiple application instances that need a simple, reliable global auto-increment ID — this is the lightest solution you'll find.

本文由 Grout 撰写，采用 CC BY-NC 4.0 许可。