数据库冻结补偿落地实践

最后更新于：2025-12-12 14:57:47

Immutable Ledger Architecture: Implementation Strategies for "Freeze and Compensate" Patterns in PostgreSQL and MongoDB

1. Project Objective: The Principle of Historical Immutability

1.1. The "Freeze and Compensate" Paradigm

The fundamental objective of this architectural framework is to establish a database system where historical states, once subjected to a definitive "freeze" operation, become absolutely immutable. This paradigm is rooted in the concept of a strict append-only ledger, where the past is treated as a crystallized fact rather than a mutable variable. In this model, the integrity of the historical record is paramount; therefore, any requirement to correct, adjust, or rectify data from a closed period must never be executed by modifying the original records. Instead, all corrections are strictly managed through the introduction of new "compensation events" (often referred to as deltas). This approach ensures that the database serves not merely as a store of current state, but as a faithful, auditable log of all business activities, where the current truth is always a derivative function of the sum of all preceding events. By prohibiting retroactive history rewriting, the system eliminates the risks associated with silent data corruption and ensures compliance with rigorous financial and regulatory audit standards.

该架构框架的基本目标是建立一个数据库系统，其中历史状态一旦经过明确的“冻结”操作，即变得绝对不可变。这一范式植根于严格的追加型（append-only）账本概念，将过去视为结晶的事实而非可变的变量。在这个模型中，历史记录的完整性至关重要；因此，任何纠正、调整或修正已关账期间数据的需求，都绝不能通过修改原始记录来执行。相反，所有的纠正措施都必须严格通过引入新的“补偿事件”（通常称为增量/delta）来进行管理。这种方法确保数据库不仅作为当前状态的存储，而且作为所有业务活动的忠实、可审计日志，其中当前的真相始终是所有先前事件总和的衍生函数。通过禁止回溯性地重写历史，该系统消除了与静默数据损坏相关的风险，并确保符合严格的财务和监管审计标准。

2. Core Abstractions and Invariants

2.1. Three Categories of Objects

To implement this immutable ledger effectively, the system creates a conceptual model based on three distinct categories of objects, each playing a specific role in the data lifecycle. The first category is Fact Events. These are the raw, atomic units of business reality as they occurred in real-time. A Fact Event represents an initial truth—a transaction, a log entry, or a sensor reading—that is recorded immediately upon occurrence. Once persisted, a Fact Event is technically treated as immutable from the moment of insertion. The second category is the Freeze Point. This is a logical or temporal marker that delineates the boundary between the "open" (active) state and the "closed" (archived) state. It is not merely a timestamp but a systemic state transition that alters the rules governing the data that falls behind it. The third category is Compensation Events. These are distinct entities generated specifically to address discrepancies or errors discovered in the frozen history. Unlike a standard update which overwrites data, a Compensation Event acts as an additive adjustment—a counter-weight applied to the ledger to rectify the balance without touching the original error.

为了有效地实现这一不可变账本，系统建立了一个基于三类不同对象的概念模型，每一类对象在数据生命周期中都扮演着特定的角色。第一类是事实事件（Fact Events）。这是业务现实在实时发生时的原始、原子单元。事实事件代表了初始的真相——一笔交易、一条日志条目或一个传感器读数——它在发生时立即被记录。一旦被持久化，事实事件在技术上从插入的那一刻起就被视为不可变的。第二类是冻结点（Freeze Point）。这是一个逻辑或时间标记，划定了“开放”（活跃）状态与“关闭”（归档）状态之间的界限。它不仅仅是一个时间戳，而是一种系统性的状态转换，改变了对其后数据的管理规则。第三类是补偿事件（Compensation Events）。这是专门为解决在冻结历史中发现的差异或错误而生成的独立实体。与覆盖数据的标准更新不同，补偿事件作为一种叠加调整起作用——一种施加在账本上的平衡砝码，用于在不触及原始错误的情况下纠正余额。

2.2. Two Iron Rules of Integrity

The stability of this architecture is guaranteed by the enforcement of two inviolable "Iron Rules" that govern all interactions with the data.

Prohibition of Modification: The first rule dictates that once a dataset or time window passes the designated Freeze Point, strictly no UPDATE or DELETE operations are permitted on that data. The frozen data must essentially become "Write Once, Read Many" (WORM) storage. This restriction prevents the most common source of ledger corruption: the retroactive alteration of history that invalidates prior reports and audits.

Correction via Addition: The second rule mandates that every single correction, regardless of its nature or magnitude, must be implemented as a new Compensation Event. Whether fixing a typo or reversing a billion-dollar transaction, the mechanism is identical: append a new record that describes the change. This ensures that the audit trail remains unbroken and that the system can always reconstruct how the current state was reached, preserving the causal chain of events.

该架构的稳定性通过强制执行两条不可违背的“铁律”来保证，这两条规则管理着与数据的所有交互。

禁止修改：第一条规则规定，一旦数据集或时间窗口通过了指定的冻结点，严禁对该数据执行任何 UPDATE（更新）或 DELETE（删除）操作。冻结的数据必须本质上成为“一次写入，多次读取”（WORM）存储。这一限制防止了账本损坏的最常见来源：回溯性地更改历史，从而导致之前的报告和审计失效。

新增即纠错：第二条规则要求，每一个纠正措施，无论其性质或规模如何，都必须作为新的补偿事件来实现。无论是修正拼写错误还是逆转一笔十亿美元的交易，其机制都是相同的：追加一条描述变更的新记录。这确保了审计线索保持完整，并且系统始终可以重构当前状态是如何达成的，从而保留了事件的因果链。

3. Implementation Strategy: PostgreSQL

3.1. Partitioning as the Freezing Mechanism

In the PostgreSQL ecosystem, the most robust architectural pattern for implementing the freeze mechanism is to equate a "Partition" strictly with a "Billing Window" (or Accounting Period). PostgreSQL's declarative partitioning allows for the segmentation of large tables into smaller, physically separated pieces based on specific criteria, such as time ranges.1 By using Range Partitioning, the system can automatically route data to the correct partition based on its timestamp. This alignment implies that a "Month" in the business sense maps one-to-one with a "Table Partition" in the database schema.

The definition of these ranges requires precise handling of boundary conditions to ensure data continuity without overlap. Standard PostgreSQL convention for range partitions dictates that the start boundary is inclusive and the end boundary is exclusive.1 For example, a partition for January might be defined as FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'). This structure ensures that a timestamp exactly at midnight on February 1st falls into the February partition, not January, maintaining a clean temporal separation. In Greenplum and newer PostgreSQL versions, explicit syntax like START INCLUSIVE and END EXCLUSIVE can further clarify these definitions, preventing "off-by-one" errors that could strand data in the wrong accounting period.3 This physical segmentation is critical because it allows administrative operations—such as freezing—to be applied to a specific slice of time as a whole unit, rather than relying on row-level filtering which is computationally expensive and error-prone.

在 PostgreSQL 生态系统中，实现冻结机制最稳健的架构模式是将“分区”严格等同于“账期窗口”（或会计期间）。PostgreSQL 的声明式分区允许根据特定标准（如时间范围）将大表分割成更小的、物理分离的部分 1。通过使用范围分区（Range Partitioning），系统可以根据时间戳自动将数据路由到正确的分区。这种对齐意味着业务意义上的“月”与数据库模式中的“表分区”是一一对应的。

这些范围的定义需要精确处理边界条件，以确保数据的连续性且无重叠。PostgreSQL 范围分区的标准惯例规定，起始边界是包含的（inclusive），而结束边界是不包含的（exclusive）1。例如，一月份的分区可能被定义为 FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')。这种结构确保了 2 月 1 日午夜的时间戳准确落入二月分区，而不是一月，从而保持清晰的时间分隔。在 Greenplum 和较新版本的 PostgreSQL 中，像 START INCLUSIVE 和 END EXCLUSIVE 这样的显式语法可以进一步阐明这些定义，防止可能导致数据滞留在错误会计期间的“差一错误” 3。这种物理分割至关重要，因为它允许管理操作（如冻结）作为一个整体单元应用于特定的时间片段，而不是依赖计算昂贵且容易出错的行级过滤。

3.2. Operationalizing the Freeze via Permissions

The act of "Freezing" a billing window is technically operationalized in PostgreSQL by manipulating the Write Permissions on the specific partition corresponding to that window. Rather than setting a logical flag like is_frozen=true in a control table, the system utilizes PostgreSQL's Role-Based Access Control (RBAC) to enforce immutability at the database engine level. When a period closes, the system issues a REVOKE command to strip INSERT, UPDATE, and DELETE privileges from the application roles for that specific partition.5

It is crucial to understand that partitions in PostgreSQL are ostensibly separate tables; however, permission inheritance can be complex. While granting permissions on a parent table typically propagates to children, revoking them can be targeted. By explicitly revoking the write privileges on the child partition (e.g., sales_2023_01), the database kernel itself rejects any subsequent attempt to modify that data, returning a "permission denied" error.6 This provides a much stronger guarantee than application-level logic. For further security, the ownership of the frozen partition can be transferred to a special superuser or "archivist" role, ensuring that even standard database administrators cannot accidentally mutate the historical record without deliberate escalation steps. This leverages the "Defense in Depth" principle, ensuring that the immutability is enforced by the lowest possible layer of the stack.

“冻结”账期窗口的操作在 PostgreSQL 中通过在对应特定分区的写权限上进行操作来实现技术落地。系统不是在控制表中设置逻辑标志（如 is_frozen=true），而是利用 PostgreSQL 的基于角色的访问控制（RBAC）在数据库引擎层面强制执行不可变性。当一个周期结束时，系统会发布 REVOKE 命令，从该特定分区的应用角色中剥夺 INSERT、UPDATE 和 DELETE 权限 5。

至关重要的是要理解，PostgreSQL 中的分区表面上是独立的表，但权限继承可能很复杂。虽然在父表上授予权限通常会传播给子表，但撤销权限可以是针对性的。通过显式撤销子分区（例如 sales_2023_01）上的写权限，数据库内核本身会拒绝任何后续修改该数据的尝试，并返回“权限被拒绝”的错误 6。这提供了比应用层逻辑强得多的保证。为了进一步提高安全性，冻结分区的所有权可以转移给特殊的超级用户或“档案管理员”角色，确保即使是标准的数据库管理员，在没有经过刻意的升级步骤下，也无法意外突变历史记录。这利用了“纵深防御”原则，确保不可变性由堆栈的最低可能层来强制执行。

3.3. Compensation Architecture: The Adjustments Table

Since history cannot be changed, the system handles error correction through a dedicated architecture utilizing an adjustments table. This table serves as the sole repository for Compensation Events. When a discrepancy is identified in a frozen partition—for instance, a transaction recorded in January was incorrect—the system does not unlock January. Instead, it inserts a corrective entry into the adjustments table.

This approach necessitates a modification in how data is queried. The "Current Truth" is no longer a simple SELECT from the facts table; it becomes a union or aggregation of the facts (from the frozen partitions) and the adjustments. Ideally, the adjustments table is also partitioned, potentially mirroring the structure of the main facts table to optimize join performance. The application logic must act as a projection layer, summing the immutable past with the corrective present. For example, if a revenue event of $100 in January needs to be corrected to $80, a compensation event of -$20 is inserted. The query SUM(amount) over the combined dataset yields the correct $80 without ever touching the original $100 record. This model preserves the evidentiary value of the original error while ensuring the reporting layer reflects the corrected reality.

由于历史不能被更改，系统通过利用 adjustments（调整）表的专用架构来处理错误纠正。该表作为补偿事件的唯一存储库。当在冻结分区中发现差异时——例如，一月份记录的一笔交易是错误的——系统不会解锁一月份。相反，它会在 adjustments 表中插入一条纠正条目。

这种方法需要修改数据的查询方式。“当前真相”不再是对事实表的简单 SELECT；它变成了 facts（来自冻结分区）与 adjustments 的联合或聚合。理想情况下，adjustments 表也应进行分区，可能镜像主事实表的结构以优化连接性能。应用逻辑必须充当投影层，将不可变的过去与纠正性的现在相加。例如，如果一月份的一笔 100 美元的收入事件需要修正为 80 美元，则插入一个 -20 美元的补偿事件。对组合数据集执行 SUM(amount) 查询将得出正确的 80 美元，而无需触及原始的 100 美元记录。该模型在保留原始错误证据价值的同时，确保报告层反映修正后的现实。

3.4. Defense in Depth: Permission Isolation and Trigger Fallbacks

To fortify the system against accidental or malicious tampering, it is recommended to layer permission isolation with trigger-based fallbacks.

Permission Isolation & Tablespaces: Beyond simple row permissions, "Structure Isolation" involves moving frozen partitions to separate, read-only Tablespaces. PostgreSQL allows administrators to define tablespaces on different physical filesystems.7 By moving a frozen partition to a tablespace residing on a read-only mount or a cheaper, slower storage tier (e.g., HDD vs. NVMe), the system gains a physical barrier against modification.7 Additionally, utilizing foreign data wrappers (like file_fdw or parquet_fdw) allows archived partitions to be exported to external immutable formats (like Parquet) and re-attached as external tables, making modification technically impossible within the database engine.9

Trigger Fallback: As a final safety net, BEFORE UPDATE and BEFORE DELETE triggers should be attached to the frozen partitions. These triggers should contain logic that unconditionally raises an exception (e.g., RAISE EXCEPTION 'Partition is frozen').10 While permissions block standard users, superusers might bypass RBAC checks. A trigger, however, executes regardless of the user's privilege level (unless explicitly disabled), providing a fail-safe mechanism that prevents administrative scripts or maintenance jobs from inadvertently altering historical data. This combination of logical permissions, physical storage isolation, and active trigger defense creates a robust "Defense in Depth" strategy.

为了加固系统以防止意外或恶意的篡改，建议将权限隔离与基于触发器的兜底机制相结合。

权限隔离与表空间：除了简单的行权限外，“结构隔离”涉及将冻结分区移动到独立的只读表空间（Tablespaces）。PostgreSQL 允许管理员在不同的物理文件系统上定义表空间 7。通过将冻结分区移动到驻留在只读挂载点或更便宜、更慢存储层（例如 HDD 对比 NVMe）的表空间，系统获得了一个防止修改的物理屏障 7。此外，利用外部数据包装器（如 file_fdw 或 parquet_fdw）允许将归档分区导出为外部不可变格式（如 Parquet）并作为外部表重新挂载，从而在数据库引擎内部使得修改在技术上成为不可能 9。

触发器兜底：作为最后的安全网，应将 BEFORE UPDATE 和 BEFORE DELETE 触发器附加到冻结分区。这些触发器应包含无条件抛出异常的逻辑（例如 RAISE EXCEPTION 'Partition is frozen'）10。虽然权限可以阻止标准用户，但超级用户可能会绕过 RBAC 检查。然而，触发器无论用户的特权级别如何都会执行（除非被显式禁用），这提供了一个故障安全机制，防止管理脚本或维护作业无意中更改历史数据。这种逻辑权限、物理存储隔离和主动触发器防御的结合，构建了一个稳健的“纵深防御”策略。

4. Implementation Strategy: MongoDB

4.1. Atomicity, Write Concern, and Journaling

In the MongoDB implementation, achieving the reliability of a frozen ledger requires leveraging the database's specific consistency mechanisms: single-document atomicity and Write Concern. Unlike relational databases that rely on multi-row ACID transactions for every operation, MongoDB's foundational unit of atomicity is the single document.11 This means that any update to a single document (even one with complex nested arrays) is guaranteed to be atomic.

However, to approximate the durability guarantees required for a financial ledger, the default configuration is insufficient. The system must enforce a Write Concern of { w: "majority", j: true } for all critical write operations.

w: "majority": This setting ensures that a write is acknowledged only after it has propagated to a majority of the voting members in the replica set. This protects against data loss in the event of a primary node failover.13

j: true: This is critical for immutability and durability. It mandates that the write operation is not acknowledged until it has been physically written to the on-disk journal.13 Without j: true, there exists a window (typically 100ms) where a write resides only in memory; if the server crashes during this window, the "confirmed" write could vanish.16 For a system premised on an immutable history, losing a "committed" fact is unacceptable. Therefore, forcing journal synchronization is non-negotiable to ensure that once an event is recorded, it survives system resets.

在 MongoDB 实现中，要达到冻结账本的可靠性，需要利用数据库特有的一致性机制：单文档原子性和写关注（Write Concern）。与依赖多行 ACID 事务进行每次操作的关系型数据库不同，MongoDB 的原子性基础单元是单个文档 11。这意味着对单个文档的任何更新（即使是包含复杂嵌套数组的文档）都保证是原子的。

然而，为了近似实现财务账本所需的持久性保证，默认配置是不足的。系统必须对所有关键写入操作强制执行 { w: "majority", j: true } 的写关注。

w: "majority"：该设置确保写入只有在传播到副本集的大多数投票成员后才会被确认。这可以防止在主节点故障转移时发生数据丢失 13。

j: true：这对不可变性和持久性至关重要。它强制要求写入操作在物理写入磁盘日志（Journal）之前不予确认 13。如果没有 j: true，会存在一个窗口期（通常为 100ms），此时写入仅驻留在内存中；如果服务器在此窗口期内崩溃，“已确认”的写入可能会消失 16。对于一个基于不可变历史的系统来说，丢失“已提交”的事实是不可接受的。因此，强制日志同步是不可协商的，以确保事件一旦被记录，就能在系统重置后幸存。

4.2. Immutable Document Design

The conceptual "Event" object in the MongoDB schema should be modeled explicitly as an immutable document. Once a fact event is persisted to the collection, the application logic must treat it as read-only. The schema design should support this by including all necessary context within the document at the time of insertion, precluding the need for future enrichment or status updates. For instance, rather than updating a status field from "PENDING" to "COMPLETED" within the same document (which mutates the record), the system should insert a second document referencing the first, representing the "COMPLETION" event. This "Log-Structured" or "Event Sourcing" approach aligns perfectly with MongoDB's document model, where documents are self-contained. The _id field serves as the permanent, immutable handle for that specific point-in-time fact, and the absence of updates avoids the performance penalties associated with document growth and fragmentation.

MongoDB 模式中的概念性“事件”对象应被显式建模为不可变文档。一旦事实事件被持久化到集合中，应用逻辑必须将其视为只读。模式设计应通过在插入时包含所有必要的上下文来支持这一点，从而排除未来进行丰富或状态更新的需求。例如，系统不应在同一文档中将 status 字段从“PENDING”更新为“COMPLETED”（这会突变记录），而应插入引用第一个文档的第二个文档，代表“COMPLETION”（完成）事件。这种“日志结构”或“事件溯源”方法与 MongoDB 的文档模型完美契合，其中文档是自包含的。_id 字段充当该特定时间点事实的永久、不可变句柄，而且避免更新操作也规避了与文档增长和碎片化相关的性能惩罚。

4.3. Collection-Based Freezing Strategy

To implement the freezing mechanism in MongoDB, it is recommended to adopt a strategy where one Collection corresponds to one Billing Window (e.g., events_2023_01, events_2023_02). This mimics the partitioning strategy of relational databases but leverages MongoDB's efficient handling of collections.

In this model, the "Freeze" action involves a coordinate switch:

Switching: The application router or writing service is updated to point to the new collection for the incoming period (e.g., from ..._01 to ..._02).

Locking: Simultaneously, the old collection is marked as Read-Only. This is achieved by modifying the privileges of the database users. MongoDB's role management allows administrators to define roles that have read privileges on specific namespaces (collections) but strictly no insert or update privileges.17 By revoking the write capability on the specific collection events_2023_01 for the service user, the system enforces the freeze at the database access level.

This segregation creates a hard physical boundary. Unlike filtering a massive single collection by a date field, dropping or archiving an entire collection is an instantaneous metadata operation, significantly simplifying data lifecycle management.

为了在 MongoDB 中实现冻结机制，建议采用一个集合（Collection）对应一个账期窗口（例如 events_2023_01, events_2023_02）的策略。这模仿了关系型数据库的分区策略，但利用了 MongoDB 对集合的高效处理能力。

在该模型中，“冻结”动作涉及协调切换：

切换：更新应用路由器或写入服务，使其指向新周期的集合（例如从 ..._01 指向 ..._02）。

锁定：同时，将旧集合标记为只读。这是通过修改数据库用户的权限来实现的。MongoDB 的角色管理允许管理员定义对特定命名空间（集合）具有 read 权限但严格没有 insert 或 update 权限的角色 17。通过撤销服务用户在特定集合 events_2023_01 上的写入能力，系统在数据库访问层面强制执行冻结。

这种隔离创造了一个硬物理边界。与通过日期字段过滤庞大的单个集合不同，删除或归档整个集合是一个瞬时的元数据操作，这显著简化了数据生命周期管理。

4.4. Compensation and Consistency

Compensation in MongoDB follows the logic established for PostgreSQL: utilizing a dedicated collection for adjustments (e.g., adjustments_2023_01) or appending compensation documents to a unified adjustments stream. The challenge lies in ensuring consistency when applying these compensations or when switching the active collection.

There are two primary approaches to ensure consistency:

Multi-Document Transactions (ACID): Since MongoDB 4.0 (and refined in 4.2 for sharded clusters), the database supports multi-document ACID transactions.11 This allows the "Freeze" operation—which might involve inserting a "period closed" marker and updating metadata configuration—to be executed atomically. If any part of the freeze process fails, the entire transaction rolls back, preventing the system from entering an inconsistent "limbo" state.18

Idempotent State Switching: Alternatively, if the overhead of transactions is undesirable or if running on older versions, the system can rely on idempotent state transitions. The "Freeze" command should be designed such that it can be retried safely. For example, a "Freeze" command checks if the collection is already read-only; if so, it returns success immediately. This allows the use of robust retry logic to handle network glitches without risking partial freezes or data corruption.

MongoDB 中的补偿遵循为 PostgreSQL 建立的逻辑：利用专用集合进行调整（例如 adjustments_2023_01）或将补偿文档追加到统一的调整流中。挑战在于应用这些补偿或切换活跃集合时如何确保一致性。

确保一致性主要有两种方法：

多文档事务（ACID）：自 MongoDB 4.0（并在 4.2 中针对分片集群进行了改进）起，数据库支持多文档 ACID 事务 11。这允许“冻结”操作——可能涉及插入“周期结束”标记和更新元数据配置——被原子地执行。如果冻结过程的任何部分失败，整个事务将回滚，防止系统进入不一致的“中间”状态 18。

幂等状态切换：另外，如果由于开销原因不希望使用事务，或者在旧版本上运行，系统可以依赖幂等的状态转换。“冻结”命令的设计应确保其可以安全重试。例如，“冻结”命令检查集合是否已经是只读的；如果是，则立即返回成功。这允许使用稳健的重试逻辑来处理网络故障，而不会有部分冻结或数据损坏的风险。

5. Critical Design Choices

5.1. Selecting Structural Isolation over Logical Branching

A pivotal design choice in this architecture is the selection of "Structural Isolation" over Logical Branching. Logical Branching relies on fields within the data, such as a status column (e.g., WHERE status = 'ACTIVE' vs WHERE status = 'FROZEN'), to differentiate between mutable and immutable records. This approach is fragile; a single developer error in a WHERE clause can inadvertently expose frozen data to modification.

In contrast, Structural Isolation enforces separation at the schema or physical level. By placing frozen data in separate partitions (PostgreSQL) or collections (MongoDB), the system erects a hard barrier. The database engine itself enforces the boundary. Furthermore, this facilitates advanced lifecycle management: frozen partitions/collections can be moved to cheaper storage (Tiering), compressed heavily, or detached entirely for archiving without impacting the performance of the "Hot" active data.9 This isolation reduces the "blast radius" of any accidental write operation—you cannot accidentally UPDATE a table that your user role effectively cannot see or write to.

该架构中的一个关键设计选择是选用“结构隔离”而非逻辑分支。逻辑分支依赖于数据中的字段，例如 status 列（例如 WHERE status = 'ACTIVE' 对比 WHERE status = 'FROZEN'），来区分可变和不可变记录。这种方法是脆弱的；开发人员在 WHERE 子句中的一个错误就可能无意中将冻结数据暴露给修改。

相比之下，结构隔离在模式或物理层面强制执行分离。通过将冻结数据放置在独立的分区（PostgreSQL）或集合（MongoDB）中，系统建立了一个硬屏障。数据库引擎本身强制执行该边界。此外，这促进了高级生命周期管理：冻结的分区/集合可以被移动到更便宜的存储（分层），进行深度压缩，或完全分离以进行归档，而不会影响“热”活跃数据的性能 9。这种隔离减少了任何意外写入操作的“波及范围”——你无法意外 UPDATE 一个你的用户角色实际上无法查看或写入的表。

5.2. The Append-Only Methodology

Post-freeze, the system strictly adheres to an "Append-Only" modification strategy. The fundamental tenet is that the frozen state is sacrosanct. This methodology simplifies the complexity of concurrency control and versioning. Instead of managing locks on historical rows to prevent race conditions during updates, the system only allows inserts into the adjustments container.

This means that the logic for retrieving the "current state" shifts from a simple read to a computational aggregation: $State_{current} = \sum(Facts_{frozen}) + \sum(Adjustments)$. This eliminates the need for complex temporal tables or "Slowly Changing Dimensions" (SCD) logic inside the core facts table. The past remains static; the present is constructed by layering new context (compensations) upon it. This aligns with modern "Event Sourcing" architectures, where the log of events is the source of truth, not the transient state of a row.

冻结后，系统严格遵循“仅追加”（Append-Only）的修改策略。其基本宗旨是冻结状态神圣不可侵犯。这种方法简化了并发控制和版本管理的复杂性。系统不再管理历史行上的锁以防止更新期间的竞争条件，而是仅允许向 adjustments 容器中进行插入。

这意味着检索“当前状态”的逻辑从简单的读取转变为计算聚合：$State_{current} = \sum(Facts_{frozen}) + \sum(Adjustments)$。这消除了在核心事实表内部使用复杂时态表或“缓慢变化维度”（SCD）逻辑的需要。过去保持静态；现在是通过在其上叠加新的上下文（补偿）来构建的。这与现代“事件溯源”架构一致，其中事件日志是真相的来源，而不是行的瞬时状态。

5.3. Idempotency of the Freeze Action

The "Freeze Action" itself must be engineered as an idempotent operation. The freeze involves multiple state transitions: closing the current window, opening the new one, revoking permissions, and potentially aggregating data for reporting. In a distributed environment, network failures or process crashes can cause this sequence to fail halfway.

If the operation is not idempotent, a retry could lead to errors (e.g., trying to create a partition that already exists) or inconsistent states (e.g., the new window opens, but the old one remains writable). Idempotency ensures that the freeze command freeze_window('2023-01') can be executed ten times in a row, and the result will always be the same: the system converges to the correct state where '2023-01' is read-only and '2023-02' is open, without duplicating side effects. This is typically implemented by checking the target state before executing actions (e.g., "IF NOT EXISTS CREATE PARTITION", "IF HAS_PERMISSIONS REVOKE").

“冻结动作”本身必须被设计为幂等操作。冻结涉及多个状态转换：关闭当前窗口、打开新窗口、撤销权限，以及可能的报表数据聚合。在分布式环境中，网络故障或进程崩溃可能导致此序列中途失败。

如果该操作不是幂等的，重试可能会导致错误（例如，尝试创建已存在的分区）或不一致的状态（例如，新窗口打开，但旧窗口仍可写入）。幂等性确保冻结命令 freeze_window('2023-01') 可以连续执行十次，结果始终相同：系统收敛到“2023-01”只读且“2023-02”开放的正确状态，而不会产生重复的副作用。这通常通过在执行动作前检查目标状态来实现（例如，“IF NOT EXISTS CREATE PARTITION”，“IF HAS_PERMISSIONS REVOKE”）。

6. Implementation Specifics and Code Recommendations

6.1. PostgreSQL Partition Granularity and Lifecycle

For PostgreSQL, selecting the correct partition granularity is a critical configuration decision. It is highly recommended to align partition boundaries strictly with the business billing cycle (e.g., Monthly or Daily).

Alignment: If the business closes books monthly, use PARTITION BY RANGE (created_at) with monthly intervals. Mismatched granularity (e.g., weekly partitions for a monthly close) complicates the freeze logic, requiring the locking of multiple partitions to close one logical period.

Boundaries: Use explicit FROM ('2023-01-01') TO ('2023-02-01') syntax. Avoid relying on default partition logic which might be ambiguous. Ensure the application time zone logic matches the database partition definitions to prevent "off-by-one" hour errors at the month's edge.2

Archiving Code: Administrators should script the lifecycle using ALTER TABLE... DETACH PARTITION. In PostgreSQL 14+, DETACH PARTITION CONCURRENTLY allows removing a partition from the parent table without locking the entire table, preventing service interruptions.9 Once detached, the table can be moved to an "Archive" schema or a different tablespace using ALTER TABLE... SET TABLESPACE archive_hdd to save costs.7

对于 PostgreSQL，选择正确的分区粒度是一个关键的配置决策。强烈建议将分区边界与业务计费周期（例如按月或按日）严格对齐。

对齐：如果业务按月关账，请使用按月间隔的 PARTITION BY RANGE (created_at)。不匹配的粒度（例如按月关账却使用按周分区）会使冻结逻辑复杂化，需要锁定多个分区才能关闭一个逻辑周期。

边界：使用显式的 FROM ('2023-01-01') TO ('2023-02-01') 语法。避免依赖可能模棱两可的默认分区逻辑。确保应用时区逻辑与数据库分区定义匹配，以防止在月末边缘出现“差一小时”的错误 2。

归档代码：管理员应使用 ALTER TABLE... DETACH PARTITION 编写生命周期脚本。在 PostgreSQL 14+ 中，DETACH PARTITION CONCURRENTLY 允许从父表中移除分区而无需锁定整个表，从而防止服务中断 9。一旦分离，可以使用 ALTER TABLE... SET TABLESPACE archive_hdd 将表移动到“Archive”（归档）模式或不同的表空间以节省成本 7。

6.2. MongoDB Collection Frequency and Router Logic

In the MongoDB context, the frequency of creating new collections should mirror the reporting requirements, typically "One Collection per Account Period".

Frequency: Monthly collections are standard. While MongoDB can handle a large number of collections, creating a new collection every hour is discouraged due to namespace file overhead. Conversely, a single yearly collection might be too large to manage efficiently as a "frozen" unit.

Routing Logic: The "Freeze" implies a hard switch. The application code (or middleware) must possess robust logic to handle this transition.

Write Path: The router must explicitly stop writing to collection_N and start writing to collection_N+1 at the exact cut-over moment.

Read Path: Queries spanning multiple periods must be essentially "scatter-gather" operations. However, for specific billing inquiries, the query can be targeted to a single, frozen collection, which is highly efficient.

Safety: Ensure that the user role used by the active writer service loses permissions to the old collection immediately after the switch. This prevents "zombie" processes (e.g., a lagged worker queue) from accidentally inserting late-arriving events into the now-closed history.17

在 MongoDB 环境下，创建新集合的频率应反映报告需求，通常为“每个账期一个集合”。

频率：按月集合是标准的。虽然 MongoDB 可以处理大量集合，但不建议每小时创建一个新集合，因为这会带来命名空间文件的开销。相反，单个年度集合可能太大，难以作为一个“冻结”单元进行高效管理。

路由逻辑： “冻结”意味着硬切换。应用代码（或中间件）必须具备稳健的逻辑来处理这种转换。

写入路径：路由器必须在确切的切换时刻显式停止向 collection_N 写入，并开始向 collection_N+1 写入。

读取路径：跨越多个周期的查询本质上必须是“分散-聚集”（scatter-gather）操作。然而，对于特定的账单查询，查询可以针对单个冻结集合，这非常高效。

安全性：确保活跃写入服务使用的用户角色在切换后立即失去对旧集合的权限。这防止了“僵尸”进程（例如滞后的工作队列）意外地将迟到的事件插入到现已关闭的历史记录中 17。

Works cited

Documentation: 18: 5.12. Table Partitioning - PostgreSQL, accessed December 12, 2025,

Partitioning in Postgres — Part 2 | by Krish Na - Medium, accessed December 12, 2025,

About Changes to Table Partitioning in Greenplum 7 - TechDocs - Broadcom Inc., accessed December 12, 2025,

Declarative partitioning improvements - PostgreSQL wiki, accessed December 12, 2025,

Documentation: 18: REVOKE - PostgreSQL, accessed December 12, 2025,

Is giving permission "A" to a partitioned table the same as giving permission "A" to each sub-table? : r/PostgreSQL - Reddit, accessed December 12, 2025,

Documentation: 18: 22.6. Tablespaces - PostgreSQL, accessed December 12, 2025,

Managing PostgreSQL partitions with the pg_partman extension - Amazon Relational Database Service, accessed December 12, 2025,

Data archiving and retention in PostgreSQL. Best practices for large datasets, accessed December 12, 2025,

Permissions inheritance on create table partition in postgresql - Stack Overflow, accessed December 12, 2025,

Atomicity and Transactions - Database Manual - MongoDB Docs, accessed December 12, 2025,

MongoDB 4 Update: Multi-Document ACID Transactions, accessed December 12, 2025,

Write Concern - Database Manual - MongoDB Docs, accessed December 12, 2025,

MongoDB Write Concern: A Simple Guide | by Ashwini Singh - Medium, accessed December 12, 2025,

Journaling - Database Manual - MongoDB Docs, accessed December 12, 2025,

Does MongoDB journaling guarantee durability? - Stack Overflow, accessed December 12, 2025,

Transactions - Database Manual - MongoDB Docs, accessed December 12, 2025,

Performance Best Practices: Transactions And Read / Write Concerns - MongoDB, accessed December 12, 2025,

Postgres move partition to archive table - Stack Overflow, accessed December 12, 2025,