PostgreSQL可按需/每日重置的invoice_id实现方案优化咨询

这个方案比你原有的单记录表逻辑更清晰，天然支持按周期（日/自定义周期）重置，查询和更新效率拉满，完全规避百万级数据下的性能问题。

1. 创建计数器表

CREATE TABLE invoice_counters (
    reset_period DATE PRIMARY KEY, -- 用日期标记重置周期，按需可改为自定义周期标识
    current_value INT NOT NULL DEFAULT 0
);

2. 新增发票的原子逻辑（事务+行级锁避免竞态）

BEGIN;

-- 锁定当前周期的计数器记录，不存在则自动插入初始值0
INSERT INTO invoice_counters (reset_period, current_value)
VALUES (CURRENT_DATE, 0)
ON CONFLICT (reset_period) DO NOTHING;

-- 获取并递增计数器值
SELECT current_value + 1 INTO :new_invoice_id
FROM invoice_counters
WHERE reset_period = CURRENT_DATE
FOR UPDATE;

-- 更新计数器
UPDATE invoice_counters
SET current_value = current_value + 1
WHERE reset_period = CURRENT_DATE;

-- 插入发票记录
INSERT INTO invoices (created_at, invoice_id)
VALUES (CURRENT_TIMESTAMP, :new_invoice_id);

COMMIT;

3. 重置操作

• 每日自动重置：无需手动干预，CURRENT_DATE会自动切换到新日期，新日期的计数器不存在时会自动初始化，下次新增发票时自然从1开始计数。
• 按需重置：直接更新对应周期的计数器为0，或者删除记录让下次新增时重新初始化：

-- 重置当前日期的计数器为0
UPDATE invoice_counters SET current_value = 0 WHERE reset_period = CURRENT_DATE;

-- 或者删除记录，下次新增时自动初始化
DELETE FROM invoice_counters WHERE reset_period = CURRENT_DATE;

优势：

• 不需要处理invoice_id的位数截取，展示时直接用invoice_id即可，逻辑极简。
• 计数器按周期存储，所有操作都是基于主键的行级操作，性能不受数据量影响。
• 支持任意重置周期，比如按周/月重置，只需修改reset_period的取值逻辑即可。

如果你的需求以每日自动重置为主，用PostgreSQL原生序列+定时任务会是最简单的选择：

1. 创建序列

CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1;

2. 新增发票时直接调用序列值

INSERT INTO invoices (created_at, invoice_id)
VALUES (CURRENT_TIMESTAMP, nextval('invoice_seq'));

3. 每日自动重置序列
   先安装pg_cron扩展（PostgreSQL官方定时任务工具），然后添加凌晨的定时任务：

-- 每天凌晨0点重启序列，回到初始值1
SELECT cron.schedule('daily-invoice-reset', '0 0 * * *', 'ALTER SEQUENCE invoice_seq RESTART WITH 1;');

4. 按需重置时手动执行：

ALTER SEQUENCE invoice_seq RESTART WITH 1;

优势：

• 完全依赖PostgreSQL原生功能，几乎不需要额外代码，逻辑极简。
• 序列的nextval是原子操作，天然避免竞态问题，性能最优。

注意：

• 若需要保证全表invoice_id唯一，需给invoices表加复合唯一约束：UNIQUE(reset_period, invoice_id)（可以新增reset_period字段存储周期标识）；如果业务允许不同周期的invoice_id重复，只需保证单周期内唯一即可，序列重启后不会在同周期内重复生成值。
• 确保执行ALTER SEQUENCE的用户有足够权限，且pg_cron扩展已正确安装配置。

内容的提问来源于stack exchange，提问作者Mohammad Zohar