## All you need is PostgreSQL ## 你需要的只是 PostgreSQL June 25, 2026 by Eduardo Bellani ### Introduction ### 简介 There is a deep cultural reflex in modern engineering: whenever a problem appears, reach for a packaged solution instead of thinking from first principles. The result is architectural cargo culting and lots of missed opportunities. Some intentionally absurd-but-familiar examples: 在现代软件工程领域，存在一种根深蒂固的文化本能：每当遇到问题时，工程师们往往习惯直接寻找现成的打包解决方案，而不是从第一性原理出发去思考。这种习惯导致了架构上的“货物崇拜”（即不求甚解地盲目跟风采用某些流行架构），并错失了大量优化系统的机会。以下是一些有意夸张但大家却耳熟能详的例子： > We need an audit trail, let’s use {temporal/event sourcing DBMS} > > “我们需要审计追踪功能，引入【时态/事件溯源数据库，如依托 Kafka 的事件流架构】吧。” > Our application is slow, let’s cache that using {in-memory key-value database} > > “我们的应用太慢了，引入【内存键值对数据库，如 Redis】做一层缓存吧。” And since a relational database like PostgreSQL is still considered mandatory,thanks mostly to its unmatched reputation, companies end up stacking product on top of product on top of PostgreSQL. They inflate the number of moving parts, operational risk, headcount demand, and overall system entropy. Complexity grows, not because the problems demand it, but because someone reached for a tool they saw in a conference talk. 由于像 PostgreSQL 这样的关系型数据库凭借其无与伦比的声誉，依然被认为是系统必不可少的基础设施，公司最终往往会在 PostgreSQL 之上堆叠一层又一层的外部产品。他们不断增加系统中运转组件的数量，随之而来的是运维风险的飙升、对人力需求的膨胀，以及整体系统熵（混乱度）的增加。系统复杂度的增长并不是因为业务问题真的需要这么复杂，而是因为某人在技术大会上看到了某个新工具，便迫不及待地把它搬了进来。 In this post, I’ll walk through a set of common misconceptions that drive teams to introduce new infrastructure when they don’t need to. All of these can be solved with vanilla PostgreSQL 18 using standard extensions available on RDS, with no special infrastructure and no distributed-systems cosplay. 在本文中，我将梳理一系列常见的误区，正是这些误区驱使团队在毫无必要的情况下引入新的基础设施。事实上，所有这些问题都可以通过原生的 PostgreSQL 18（注：设定于2026年的版本，该版本正式完善了原生应用时间段的支持）以及云数据库（如 AWS RDS）上标配的标准扩展来解决，完全不需要额外部署特殊的基础设施，也不需要搞复杂的分布式系统“角色扮演”。 The goal in this article is not to argue that specialized systems are never appropriate, but to show that the default assumption for your data problems should be that **my company can do fine with just PostgreSQL**. 本文的目的并非主张专用系统毫无用处，而是为了证明：面对数据问题时，你的默认假设应该是“**仅靠 PostgreSQL，我们公司就能应对得很好**”。 ### The setup ### 场景设定 Here is a list of arguments that people put forth to reach for other tools besides PostgreSQL, based on my experience: 根据我的经验，当人们想要在 PostgreSQL 之外引入其他工具时，通常会提出以下理由： * I’ll need auditing and reconstructing state * Write throughput is too low * The transactional queries are too slow * The analytical queries are too slow * My app will be coupled to the Database * “我需要审计功能和重构历史状态的能力。” * “数据库的写入吞吐量太低了。” * “事务性（OLTP）查询太慢了。” * “分析型（OLAP）查询太慢了。” * “我的应用程序会与数据库产生严重耦合。” To address these, I’m going to use a variation of the *Drosophila melanogaster* of the database field: the classic Supplier and Parts database. I’ll update it to be more in line with the usual problematic tables: Financial Transaction and their originating transfers. 为了回应这些质疑，我将使用数据库领域的“黑腹果蝇”（即标准的基础研究模型）：经典的“供应商与零件”数据库设计的变体。我将其更新为现代应用中更常见的、也是最容易出问题的表结构：金融交易流水（Transaction）及其发源的转账记录（Transfer）。 For the rest of this article we will be constructing a database design based on modern PostgreSQL that will achieve the general goals listed above and specific business requirements. Here is a requirements snippet from a very popular banking API company: 在接下来的文章中，我们将基于现代 PostgreSQL 构建一个数据库设计，该设计将实现上述总体目标以及特定的业务需求。以下需求片段摘自一家非常受欢迎的提供 API 优先银行业务的真实金融科技公司（Increase, Inc.）： > Transactions: are immutable records of financial interactions with Increase. You can think of them as the line items on your bank statement. A Transaction with a positive amount means there’s more money in your account. A Transaction with a negative amount means there’s less money in your account. You can’t directly create a Transaction, and they never change after they are made. Anything that causes money to move around your Increase account results in a Transaction - initiated or received transfers, card payments, earned interest, and more. > > 交易流水（Transactions）：是与 Increase 平台发生金融交互的不可变记录。你可以把它们看作银行对账单上的明细行。金额为正的流水意味着你的账户里钱变多了；为负则意味着钱变少了。你无法直接创建一条交易流水，且它们一旦生成就绝对不可更改。任何导致你的账户资金发生变动的事情——发起或接收转账、刷卡支付、赚取利息等——都会产生一条交易流水。 > Transfers: which includes ACH Transfers, Wire Transfers, etc - are the most common way to initiate money movement over external networks with Increase. Transfers are one-to-many with Transactions, which they create as side-effects. Unlike Transactions, Transfers are stateful and transition through a lifecycle of different statuses as they move across the network. > > 转账记录（Transfers）：包括 ACH 转账、电汇等，这是在外部网络中发起资金移动最常见的方式。一次转账会对应多条交易流水（一对多关系），这些流水是转账产生的副作用。与不可变的交易流水不同，转账是有状态的，在网络中流转时，它们会经历不同状态的生命周期。 > Pending Transactions: represent potential future credits or debits of money into your account and are a separate resource from Transactions (despite their similar name). Notably, while Transactions are immutable, Pending Transactions are not, as they don’t guarantee the movement of money. For example, Pending Transactions are created for card authorizations (which can mutate or timeout) and also when placing a hold on an account (which can be removed). Pending Transactions do not affect your current balance (which is the balance you earn interest on), but do affect your available balance (which is the amount you’re able to move out of Increase). > > 待处理交易（Pending Transactions）：代表未来可能存入或扣除的潜在资金，它们是与已结算交易（Transactions）独立的资源。值得注意的是，已结算交易是不可变的，但待处理交易不是，因为它们不保证资金最终一定会移动。例如，当信用卡进行预授权或对账户进行资金冻结时，就会创建待处理交易。待处理交易不影响你的“当前余额”（你用来计算利息的余额），但会影响你的“可用余额”（你实际能转出的资金量）。 Below are 2 screenshots from increase’s sandbox dashboard that showcase the requirements: 以下是 Increase 沙盒控制台的两张截图，展示了上述需求：

From these 2 images, here is a list of requirements (functional and not) that I have extracted, which I consider to be common in financial systems like increase: 从这两张图中，我提取了以下功能性和非功能性需求列表，我认为这些在类似 Increase 的金融系统中是非常典型的： 1. Accounts are defined by immutable routing numbers and account numbers and have a status that can vary. 1. 账户（Accounts）由不可变的路由号和账号定义，并具有可变的状态。 2. Accounts are discriminated between external and managed, and one account must be one or the other exclusively. Transfers are made only between external and managed accounts. 2. 账户被严格区分为“外部账户”和“托管账户”，一个账户只能属于其中之一。转账只能在外部账户和托管账户之间进行。 3. Transactions and transfers are listed, paginated by their respective creation times. 3. 交易流水和转账记录需要展示为列表，并按各自的创建时间进行分页。 4. Current and available balance are shown, both their present and historical daily values 4. 能够显示“当前余额”和“可用余额”，同时支持查询现在的余额及每天的历史余额。 5. Transfers behave like a state machine where the progression between states are exposed to the user. The user can see the full state history of a transfer and some of these states are linked to pending/settled transactions. 5. 转账类似于一个状态机，状态的推进对用户可见。用户能够看到转账的完整历史状态，其中某些状态与待处理/已结算的交易流水相关联。 6. The user can also see the details of a transaction, and see the transfer that generated it. 6. 用户还可以查看交易流水的详情，并追溯到生成该流水的源头转账记录。 7. We should maximize write throughput of transactions and transfers. Transfers are editable, and so we should be able to update them fast too. 7. 我们需要最大化交易流水和转账记录的写入吞吐量。转账状态是可编辑的，因此必须能够极快地更新它们。 ### Laying the foundation ### 奠定基础 In this section we build the core tables and the role necessary to restraint updates and achieve the immutability mentioned on the requirements (requirement 1 for example). 在这一节中，我们将构建核心数据表，并配置必要的数据库角色（Role）来限制无意的更新操作，从而实现需求中提到的数据不可变性（例如需求1）。 #### The foundation: schemas and user roles for modularity #### 基础：利用 Schema 和用户角色实现模块化 Modularity, defined by the capacity to have a many-to-many relation between implementations and interfaces, is crucial for software development. Part of the base tools we have for that on SQL are schemas and roles. In particular, a proper role can be used for defining very precise interfaces on top of database objects. 模块化（定义为实现与接口之间具有多对多关系的能力）对软件开发至关重要。在 SQL 中，我们用来实现模块化的基础工具部分就是 Schema（模式）和 Role（角色）。特别是，通过合理的权限角色分配，可以在数据库对象之上定义极其精确的访问接口，防止应用程序越权访问或破坏数据完整性。 ```sql create schema finance; create role finance; grant usage on schema finance to finance; alter default privileges in schema finance grant select, insert, update, delete on tables to finance; alter default privileges in schema finance grant usage, select on sequences to finance; ``` #### Domains #### 域（Domains） Database domains are usually scoffed at by practitioners, but that is a big mistake. Properly seen, they are: > an application of the abstract data type to database management. 数据库的域（Domains）通常被现代开发者嗤之以鼻，但这其实是个大错特错的观念。正确地看待域，它们其实是： > 抽象数据类型（Abstract Data Type）在数据库管理中的应用。 As such, domains are the core building blocks for logical design. 通过使用域，我们可以在数据库层面将通用的数据规则封装起来（比如校验手机号、银行账号格式），因此，域是逻辑数据库设计的核心构件。 ```sql create domain finance.routing_number as text check (value ~ '^[0-9]{9}$'); create domain finance.account_number as text check (value ~ '^[0-9]{12}$'); create domain finance.transfer_status as text check(value in ('pending', 'returned', 'completed')); ``` The `transfer_status` in particular is crucial, since it represents the valid states that a state machine can have. 特别是 `transfer_status`（转账状态）这个域至关重要，因为它从底层限制了状态机能拥有的有效状态集。 #### Accounts, managed and external #### 托管账户与外部账户 Managed Accounts are the accounts that are owned by the our system. When receiving a transfer, we control only one side of the transfer, and that is the managed account side. 托管账户（Managed Accounts）是由我们系统自身拥有的账户。当接收转账时，我们只能控制转账的一方，那就是托管账户这一侧。 Managed accounts can be deactivated and reactivated. This falls neatly within the set of temporal features introduced in SQL 2011, in particular application time, recently introduced in PostgreSQL 18. This feature allows us to represent accounts going in and out of activity without overlapping. 托管账户可以被停用和重新激活。这一需求完美契合了 SQL:2011 标准中引入的时态特性（Temporal Features），特别是最近在 PostgreSQL 18 中正式引入的原生“应用时间（Application Time）”功能。利用该功能，我们可以精准地表示账户在有效与无效状态之间的切换，且能在数据库引擎层面自动保证时间段不发生重叠。 ```sql -- To use temporal constraints, you need to install the btree_gist extension, which provides the necessary operator classes for creating GiST indexes on scalar data types: -- 要使用时态约束，你需要安装 btree_gist 扩展，该扩展提供了在标量数据类型上创建 GiST（通用搜索树）索引所必需的运算符类： create extension if not exists btree_gist; create table finance.managed_active_account( routing_number finance.routing_number not null, account_number finance.account_number not null, account_name text not null, account_active_period tstzrange not null default tstzrange(now(), 'infinity', '[)'), primary key (routing_number, account_number, account_active_period without overlaps) ); comment on table finance.managed_active_account is 'Managed Accounts are what transactions are performed against. Think of your bank account. They store money, receive transfers, and send payments. They earn interest and have depository insurance. This relation holds the accounts that are active. No transfer may be created for accounts in the period that they were inactive.'; create table finance.external_account( routing_number finance.routing_number not null, account_number finance.account_number not null, account_name text not null, primary key (routing_number, account_number) ); comment on table finance.external_account is 'External accounts represent counterparty accounts at other institutions. They are the other side of a transfer. Unlike managed accounts, they have no temporal active period since we do not control their lifecycle.'; ``` And below we finish the accounts by making sure a managed account and an external account can’t be the same. We need to use `alter table` instead of adding these `check` constraints on the table definitions because of the circular dependency (one table depends on the other, and vice versa). 下面我们要完善账户体系，确保托管账户和外部账户绝不能是同一个。由于两张表存在循环依赖（互相依赖对方的存在），我们需要使用 `alter table` 来添加 `check` 约束，而不是直接写在表定义中。 ```sql -- Ensure managed and external accounts never share the same identity -- 确保托管账户和外部账户永远不会共享同一个身份标识 create or replace function finance.not_external_account( p_routing_number finance.routing_number, p_account_number finance.account_number ) returns boolean language sql stable as $$ select not exists ( select 1 from finance.external_account where routing_number = p_routing_number and account_number = p_account_number ); $$; create or replace function finance.not_managed_account( p_routing_number finance.routing_number, p_account_number finance.account_number ) returns boolean language sql stable as $$ select not exists ( select 1 from finance.managed_active_account where routing_number = p_routing_number and account_number = p_account_number ); $$; alter table finance.managed_active_account add constraint managed_not_external check (finance.not_external_account(routing_number, account_number)); alter table finance.external_account add constraint external_not_managed check (finance.not_managed_account(routing_number, account_number)); ``` #### Transfers, constrained by a state machine and temporal periods #### 转账：由状态机和时间周期约束 Below are the transfers, which represents the movement of money between managed accounts and external accounts. They can be seen as a state machine progressing over the transfer_status domain `pending -> (completed | returned)`. 以下是转账表，代表托管账户和外部账户之间的资金移动。它们可以被视为在转账状态域上推进的状态机：`待处理(pending) -> (已完成(completed) | 已退回(returned))`。 Another crucial point here is the `period` keyword in the references section. This makes a transfer period be consistent with active managed accounts, implementing a core financial safety requirement declaratively in the most deepest level one can. 这里另一个关键点是外键引用部分的 `period` 关键字。这使得转账的发生周期必须与托管账户的活跃周期保持一致，从而在数据库的最底层，以声明式的方式实现了核心的金融安全要求（即：账户冻结或注销期间，绝对无法发生转账）。 ```sql create table finance.transfer ( transfer_period tstzrange not null default tstzrange(now(), 'infinity', '[)'), transfer_created_at timestamptz generated always as (lower(transfer_period)) stored, account_number finance.account_number not null, routing_number finance.routing_number not null, counterparty_account_number finance.account_number not null, counterparty_routing_number finance.routing_number not null, amount bigint not null, status finance.transfer_status not null default 'pending', -- Natural order: account identity, then time, then counterparty -- This enables efficient time-range queries on account transfers primary key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ), foreign key ( counterparty_routing_number, counterparty_account_number ) references finance.external_account ( routing_number, account_number ), -- temporal foreign key: ensure managed account exists during transfer period -- 时态外键：确保在转账期间托管账户是有效活跃的 foreign key ( routing_number, account_number, period transfer_period ) references finance.managed_active_account ( routing_number, account_number, period account_active_period ) ); comment on table finance.transfer is 'Transfers represent money movement between an external account and a managed account. Status follows state machine: pending -> (completed | returned). Period closes on terminal state. transfer_created_at is a stored generated column derived from lower(transfer_period), eliminating redundancy while remaining usable in primary keys and foreign key references.'; revoke insert on finance.transfer from finance; revoke update on finance.transfer from finance; -- transfer_period will be managed based on the status -- transfer_period 将基于状态机自动管理，因此不对外部暴露插入和修改权限 grant insert (routing_number, account_number, counterparty_routing_number, counterparty_account_number, amount) on finance.transfer to finance; grant update (status) on finance.transfer to finance; ``` Note that `transfer_created_at` is a stored generated column: `lower(transfer_period)` is an immutable function, and the lower bound of `transfer_period` never changes (the state machine trigger only closes the upper bound). This eliminates the redundancy between `transfer_created_at` and `lower(transfer_period)` while keeping the column usable in primary keys and as a foreign key target. 值得注意的是，`transfer_created_at` 是一个存储的生成列（Stored Generated Column）：`lower(transfer_period)` 获取时间段的下界，这是一个不可变操作，且 `transfer_period` 的下界永远不会改变（我们的状态机触发器只会关闭上界）。这种设计消除了冗余，同时保留了该列作为主键和外键目标的作用。 #### Transfer state history #### 转账历史状态追踪 In order to implement feature 5, we need system-time temporal tables. PostgreSQL supports several approaches for temporal tables. For simplicity and portability (including RDS), we use the temporal_tables extension. 为了实现需求 5（展示历史状态），我们需要系统时间段时态表（System-Time Temporal Tables，通过记录数据实际存在的时间实现版本控制）。PostgreSQL 支持多种实现时态表的方法。为了简单和可移植性（包括支持 RDS），我们使用了 `temporal_tables` 扩展。 The extension automatically writes old versions of each row to a history table on every `UPDATE` or `DELETE`. Together with a tstzrange period, this gives you a full history suitable for `AS OF` queries and state reconstruction. We create a focused history table that only tracks what we need: the transfer identity, status, and system-time period. 每当发生 `UPDATE` 或 `DELETE` 时，该扩展会自动将每一行的旧版本写入历史表。结合 tstzrange 时间段字段，这为你提供了一个适用于 `AS OF`（时间点快照查询）和状态重构的完整历史记录，无需像微服务架构那样引入繁重的事件溯源系统。我们创建了一个精简的历史表，只追踪我们需要的内容：转账标识、状态和系统时间周期。 ```sql -- Add system-time period column to transfer table -- 为转账表添加系统时间段列 alter table finance.transfer add column if not exists sys_period tstzrange default tstzrange(current_timestamp, null); alter table finance.transfer alter column sys_period set not null; -- Focused history table - only what we need for status transitions -- 精简的历史表——只记录状态转换所需的信息 create table if not exists finance.transfer_status_log ( like finance.transfer ); comment on table finance.transfer_status_log is 'Automatic log of all transfer status transitions via temporal_tables extension. Shows complete state machine history.'; alter table finance.transfer_status_log add primary key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number, sys_period ); -- Use temporal_tables versioning procedure -- 绑定 temporal_tables 扩展的自动版本控制触发器 create trigger transfer_save_status_history before insert or update or delete on finance.transfer for each row execute procedure versioning('sys_period', 'finance.transfer_status_log', true); ``` Once applied, `finance.transfer_status_log` will contain every past version of every transfer’s status, from which you can reconstruct the state machine history over time. 一旦应用，`finance.transfer_status_log` 就会自动包含所有转账状态的每一个过去版本，你可以从中随时随地重构出随时间变化的状态机历史。 #### Account auditing #### 账户审计 Since we are on the subject of temporal tables, we might as well add support for auditing accounts. It lies outside our list of features, but I think account disabling is a major event that should have strong auditing behind it, so we might as well add it. 既然聊到了时态表，我们不妨顺手加上对账户的审计支持。虽然它不在我们最初的功能列表中，但我认为账户的注销/停用是一个重大事件，背后应该有强大的审计支撑，所以在底层加上它非常值得。 ```sql -- Add system-time period column to the account table alter table finance.managed_active_account add column if not exists sys_period tstzrange default tstzrange(current_timestamp, null); alter table finance.managed_active_account alter column sys_period set not null; create table if not exists finance.managed_active_account_log ( like finance.managed_active_account ); comment on table finance.managed_active_account_log is 'Automatic log of all account activity via temporal_tables extension.'; create index on finance.managed_active_account_log (sys_period); create index on finance.managed_active_account_log (routing_number, account_number); -- Use temporal_tables versioning procedure create trigger account_save_history before insert or update or delete on finance.managed_active_account for each row execute procedure versioning('sys_period', 'finance.managed_active_account_log', true); ``` #### Transactions, the immutable events #### 交易流水：不可变的事件 Contrasted with the above, below we have transactions, which represent changes in the balances (current and available) of an account and are therefore immutable. 与上述可变的转账记录相对，以下是交易流水（Transactions）。它们代表账户余额（当前余额和可用余额）的具体变动，因此在定义上就是绝对不可变的。 Transactions reference their originating transfer by identity (the transfer’s natural key including `transfer_created_at`), not by period. There is no `transfer_period` stored on transactions — the transfer’s temporal state is queried from the transfer table when needed. 交易流水通过转账记录的身份标识（包括 `transfer_created_at` 在内的自然主键）来引用其源头转账，而不是通过时间段。流水表中不存储 `transfer_period` —— 只有在需要时，才会通过连表查询动态获取关联转账的时态状态。 ```sql create table finance.settled_transaction ( transaction_created_at timestamptz not null default now(), account_number finance.account_number not null, routing_number finance.routing_number not null, counterparty_account_number finance.account_number not null, counterparty_routing_number finance.routing_number not null, transfer_created_at timestamptz not null, amount bigint not null, primary key ( account_number, routing_number, transaction_created_at ), foreign key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) references finance.transfer ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) ); comment on table finance.settled_transaction is 'Settled transactions affect both your available balance and your current balance. They are immutable events --- no updates or deletes permitted.'; -- 强制不可变：直接回收更新和删除权限 revoke update, delete on finance.settled_transaction from finance; create table finance.pending_transaction ( like finance.settled_transaction including all, foreign key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) references finance.transfer ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) ); comment on table finance.pending_transaction is 'Pending transactions represent potential future credits or debits. They affect available balance but not current balance. Immutable once created.'; revoke update, delete on finance.pending_transaction from finance; ``` ### On maintaining business rules via meaningful constraints ### 论通过有意义的约束维护业务规则 Constraints should really be part of the foundation, but I have made them a separate section because they are usually seen as something apart from defining tables. In reality, a proper mathematical relation should embrace both intentionality (constraints) and extensionality (rows). 约束其实本应是基础设计的一部分，但我将其单独列为一节，因为很多开发者通常将约束视为游离于表定义之外的附属品。实际上，一个严谨的关系模型应当同时包含内涵（约束规则）和外延（数据行）。将业务规则推到数据库底层可以避免应用层遗漏导致的脏数据。 > It is impossible to design and interrogate a database sensibly, and ensure semantic consistency of results … it is intended to represent without … DBMS knowledge of the meaning assigned to the database… > > 如果不让数据库管理系统（DBMS）理解赋予数据的业务语义，就不可能合理地设计和查询数据库，也无法保证结果的语义一致性…… In our case, several business rules must hold across tables. In the absence of SQL’s `assert` we rely on constraint triggers keeping them as declarative as possible. 在我们的案例中，必须保证多个跨表的业务规则成立。由于 PostgreSQL 目前尚未支持 SQL 标准的全局 `ASSERT` 语法，我们依靠约束触发器（Constraint Triggers）来尽量以声明式的方法保持这些规则。 #### The transfer state machine #### 转账状态机 ```sql create or replace function finance.enforce_transfer_state_machine() returns trigger language plpgsql as $$ begin if OLD.status = NEW.status then return NEW; end if; if OLD.status = 'pending' then if NEW.status not in ('completed', 'returned') then raise exception 'Invalid state transition: pending can only transition to completed or returned, not %', NEW.status; end if; NEW.transfer_period := tstzrange(lower(OLD.transfer_period), now(), '[]'); elsif OLD.status in ('completed', 'returned') then raise exception 'Invalid state transition: % is a terminal state and cannot transition to %', OLD.status, NEW.status; end if; return NEW; end; $$; create trigger transfer_z_enforce_state_machine before update of status on finance.transfer for each row when (OLD.status <> NEW.status) execute function finance.enforce_transfer_state_machine(); ``` #### Transactions must fall within the transfer period #### 交易流水必须落在转账时间周期内 A transaction’s `transaction_created_at` must fall within the originating transfer’s `transfer_period`. This prevents creating transactions against transfers that haven’t started yet or have already closed: 交易流水的创建时间（`transaction_created_at`）必须处于其源头转账记录的 `transfer_period`（转账周期）内。这从物理层面杜绝了“为尚未开始或已经关闭的转账创建流水”的幽灵 bug： ```sql create or replace function finance.transaction_within_transfer_period() returns trigger language plpgsql as $$ declare v_transfer_period tstzrange; begin select transfer_period into v_transfer_period from finance.transfer where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number; if not found then raise exception 'Transfer not found for transaction'; end if; if not (v_transfer_period @> NEW.transaction_created_at) then raise exception 'Transaction created_at % is outside transfer period %', NEW.transaction_created_at, v_transfer_period; end if; return NEW; end; $$; create constraint trigger settled_transaction_within_transfer_period after insert on finance.settled_transaction deferrable initially deferred for each row execute function finance.transaction_within_transfer_period(); create constraint trigger pending_transaction_within_transfer_period after insert on finance.pending_transaction deferrable initially deferred for each row execute function finance.transaction_within_transfer_period(); ``` #### Pending transactions require a pending transfer #### 待处理流水要求对应转账必须是待处理状态 A pending transaction can only be created against a transfer that is still in the `pending` state. Settled transactions, conversely, can only be created against transfers that have left the `pending` state (i.e. `completed` or `returned`): 待处理的流水只能基于仍处于 `pending`（待处理）状态的转账创建。反之，已结算的流水只能基于已经脱离 `pending` 状态（即处于 `completed` 或 `returned` 状态）的转账创建： ```sql create or replace function finance.is_pending_transfer( p_routing_number finance.routing_number, p_account_number finance.account_number, p_transfer_created_at timestamptz, p_counterparty_routing_number finance.routing_number, p_counterparty_account_number finance.account_number ) returns boolean language sql stable as $$ select exists ( select 1 from finance.transfer t where t.routing_number = p_routing_number and t.account_number = p_account_number and t.transfer_created_at = p_transfer_created_at and t.counterparty_routing_number = p_counterparty_routing_number and t.counterparty_account_number = p_counterparty_account_number and t.status = 'pending' ); $$; create or replace function finance.ensure_pending_transfer() returns trigger language plpgsql as $$ begin if not finance.is_pending_transfer( NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number ) then raise exception 'Must reference a pending transfer (%, %, %, %, %)', NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number; end if; return NEW; end; $$; create or replace function finance.ensure_non_pending_transfer() returns trigger language plpgsql as $$ begin if finance.is_pending_transfer( NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number ) then raise exception 'Cannot reference a pending transfer (%, %, %, %, %)', NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number; end if; return NEW; end; $$; create constraint trigger pending_transaction_requires_pending_transfer after insert on finance.pending_transaction deferrable initially deferred for each row execute function finance.ensure_pending_transfer(); create constraint trigger settled_transaction_requires_non_pending_transfer after insert on finance.settled_transaction deferrable initially deferred for each row execute function finance.ensure_non_pending_transfer(); ``` #### No future transactions when closing a transfer #### 关闭转账时不得存在“未来”的流水 A transfer cannot transition to `completed` or `returned` if any of its transactions have a `transaction_created_at` that falls after the moment of closure. This prevents the state machine from closing a transfer’s period and stranding transactions in the future: 如果某个转账的任何下属流水存在发生在关闭时刻之后的 `transaction_created_at`，那么该转账不允许过渡到 `completed` 或 `returned` 状态。这就防止了状态机提前关闭转账周期，却把流水“遗留”在未来的情况发生： ```sql create or replace function finance.no_future_transactions_on_close() returns trigger language plpgsql as $$ begin if exists ( select 1 from finance.settled_transaction where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number and transaction_created_at > now() union all select 1 from finance.pending_transaction where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number and transaction_created_at > now() ) then raise exception 'Cannot close transfer (%, %, %): transaction(s) exist after current time', NEW.routing_number, NEW.account_number, NEW.transfer_created_at; end if; return NEW; end; $$; create constraint trigger transfer_no_future_transactions_on_close after update of status on finance.transfer deferrable initially deferred for each row when (NEW.status in ('completed', 'returned')) execute function finance.no_future_transactions_on_close(); ``` These three constraint groups — temporal containment, status matching, and future-transaction prevention — together enforce the full lifecycle invariant: transactions can only exist within the temporal and logical boundaries of their originating transfer. In application code, this would typically require a coordination framework spanning multiple services. Here it is enforced declaratively at the deepest possible level. 这三组约束——时态包含、状态匹配和防范未来流水——共同强制执行了整个生命周期的不变量：流水只能存在于其源头转账的时间和逻辑边界内。如果在应用层代码中实现这一点，通常需要一个跨越多微服务的分布式协调框架，且极易出错。而在我们的设计中，它是通过数据库最底层以声明式强制保障的。 ### On capacity planning ### 论容量规划 In order to be able to efficiently modify transfers and run the constraints needed to keep transactions consistent with the business rules we should keep in the working set 2 days of transfers/transactions. The number 2 is chosen because wire transfers typically settle within one business day and while international transfers take one to five days, with most completing within two we. 为了能够高效地修改转账数据并运行触发器约束，我们应该在 PostgreSQL 的“工作集”（Working Set，即常驻内存以支撑高性能读写的热点数据，通常存放在 `shared_buffers` 中）里保留 2 天的转账及流水数据。选择 2 天是因为电汇通常在一个工作日内结算，而国际转账虽然需要 1 到 5 天，但大多数也会在 2 天内完成。 Taking `finance.transfer` as a reference (the widest row), we estimate the per-row size (ignoring alignment): 以列数最多的 `finance.transfer` 表为例，我们粗略估算单行数据的大小（忽略字节对齐等微观细节）： | Name | Type | Size (bytes) | |---|---|---| | transfer_period | tstzrange | 32 | | transfer_created_at | timestamptz | 8 | | account_number | text(12) | 13 | | routing_number | text(9) | 10 | | counterparty_account_number | text(12) | 13 | | counterparty_routing_number | text(9) | 10 | | amount | bigint | 8 | | status | text(10) | 11 | | sys_period | tstzrange | 32 | | Total (plus 24 row header) | | 161 | So roughly 160 bytes per row. The log tables have the same width. The transaction tables are slightly narrower (no `transfer_period`, no `status`), around 120 bytes per row. 也就是说每行大约 160 字节。日志表与此相同。流水表略窄一点（没有 `transfer_period` 和 `status`），每行约 120 字节。 #### Working set estimation #### 工作集（内存）容量估算 The working set is the data that must be in `shared_buffers` for the system to perform well. As stated above, the working set will consist of 2 days of transfers transactions. 工作集是系统维持高性能所必须缓存在 `shared_buffers` 中的数据。如前所述，工作集将由最近 2 天的转账和流水数据组成。 Let’s assume that a transfer row corresponds to 2 rows in `transfer_status_log` (the initial insert plus a state change) and 3 transactions (2 pending transactions and a settled transaction). That gives us 3 transfer like rows + 3 transaction rows. 假设一条转账对应 `transfer_status_log` 里的 2 行（初始插入和一次状态变更）以及 3 条流水（2条待处理，1条已结算）。这意味着单次完整业务会产生 3 条宽行（160字节）+ 3 条窄行（120字节）。 Each index entry carries 8 bytes of overhead plus the indexed data. In our design, most tables have one index: the B-tree backing the primary key (the temporal table has 2, but lets ignore that for the sake of simplicity). The primary key for `finance.transfer` indexes 5 columns totalling ~54 bytes, so each index entry is ~62 bytes. This amounts to roughly 40% of the row size. So, a safe rule of thumb: add a 1.4 multiplier to the table sizes to account for for index overhead. 另外，除了数据本身，每个索引条目带有 8 字节的开销加上被索引的数据。在我们的设计中，表通常有主键 B树索引，索引条目大约 62 字节，相当于单行数据的 40% 左右。所以一个安全的经验法则是：将表大小乘以 1.4，以覆盖索引带来的额外内存开销。 This whole argument boils down to the byte sum of: 整体算下来，单个业务单元占用字节数为： ```calc ((3 * 160) + (3 * 120)) * 1.4 ``` ```text 1176. ``` | Scale | Transfers/day | Working set size for 2 days | |---|---|---| | Startup (初创公司) | 10,000 | 22M | | Mid/large bank (中大型银行, 比如Nubank) | 50,000,000 | 110G | | Global processor (全球处理器, 比如Visa) | 900,000,000 | 1.9T | Up to a mid level bank one can fit the working set comfortably in modern cloud database servers. AWS RDS, for instance, supports up to 4TiB of memory per instance for PostgreSQL-compatible instances. 我们可以看到，即便是一家中大型银行，其 2 天的核心交易工作集也能非常轻松地完全装入现代云数据库服务器的内存中。例如，AWS RDS 上兼容 PostgreSQL 的实例最高支持单机 4TiB 内存，装下千兆级乃至 TB 级的热点数据完全不在话下。 ### On write throughput ### 论写入吞吐量 If you want to maintain semantic enforcement of your data (which in our model is done via fkeys and constraint triggers) about the best thing you can do to optimize write throughput is to lower the write amplification, specifically to use indexes in a smart way. After all, `indexes, if not carefully chosen, can kill performance in a write-heavy application`. 如果你想在数据库层面强行维护数据的业务语义（在我们的模型中通过外键和约束触发器实现），那么优化写入吞吐量最好的方法就是**降低写放大（Write Amplification）**，特别是要聪明地使用索引。毕竟，“在重写入的应用中，如果索引选择不当，足以扼杀所有性能”。 #### Enabling HOT Updates for Transfers #### 为转账表开启 HOT（仅堆元组）更新 By keeping the primary key of the `finance.transfer` table aligned with the immutable columns, we enable Heap-Only Tuple (HOT) updates for that table. Such HOT updates are important because status transitions (pending to completed/returned) are common operations and we want to minimize write amplification. 通过让 `finance.transfer` 表的主键全部由**不可变列**组成，我们在底层为该表启用了 HOT（Heap-Only Tuples，仅堆元组）更新机制。在 PostgreSQL 的多版本并发控制（MVCC）中，普通更新会导致索引也需要被重写一次（即写放大）。但转账状态转换（从待处理到已完成/已退回）是非常频繁的操作，启用 HOT 机制允许数据库跳过更新索引，将写放大降到最低。 What happens is that, because neither the transfer’s `status` nor its `transfer_period` participate in any indexes, PostgreSQL can write the new tuple version to the same page (if space permits) and also skip the index update entirely. 具体原理是：因为转账的 `status` 和 `transfer_period` 都没有参与任何索引的构建，PostgreSQL 可以将新版本的元组写入同一个数据页（如果页面内空间充足的话），并且**完全跳过索引更新过程**。 We therefore need to make sure there is enough page space in the table: 因此，我们需要微调表的配置，确保数据页内有足够的预留空间： ```sql alter table finance.transfer set (fillfactor = 70); ``` This reserves 30% free space per page, allowing updated tuples to fit on the same page. Combined with no indexes on `status` or `transfer_period`, status updates become HOT-eligible, providing: 这会将每页（默认8KB）保留出 30% 的空闲空间，使更新后的元组能够直接塞进同一页中。结合未在这几个常更字段上建索引的优势，状态更新就完全符合 HOT 条件了，这能带来： * 2-3x faster updates compared to non-HOT updates * No table and index bloat from status changes * Simpler vacuum maintenance on the table * Smaller WAL, since there is less write activity overall * 比非 HOT 更新快 2-3 倍的写入速度。 * 消除状态变更引发的表膨胀和索引膨胀。 * 数据库底层的垃圾回收（VACUUM 维护）变得极简。 * 更小的 WAL（预写式日志）体积，因为整体的磁盘写入活动大大减少了。 #### Making sure there are no Unused indexes #### 确保没有闲置的未使用索引 A common problem is to have a bunch of unused indexes for critical tables, since they are not visible directly and experiments can be forgotten. So, make sure all indexes are being used: 核心表常犯的另一个大忌是堆积了一堆从未使用的废弃索引。因为它们在代码里不可见，以前尝试性能优化时随手建的索引往往会被遗忘。因此，一定要确保所有建了的索引都在实际发挥作用（我们可以通过查看 `pg_stat_user_indexes` 这个系统统计视图来排查）： ```sql SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes where schemaname='finance' ORDER BY idx_scan; schemaname │ relname │ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch ════════════╪══════════════════════════╪═════════════════════════════════════════╪══════════╪══════════════╪═══════════════ finance │ settled_transaction │ settled_transaction_pkey │ 0 │ 0 │ 0 finance │ transfer_status_log │ transfer_status_log_sys_period_idx │ 0 │ 0 │ 0 finance │ account │ account_pkey │ 100 │ 80 │ 60 finance │ pending_transaction │ pending_transaction_pkey │ 1220000 │ 120000 │ 120000 finance │ transfer │ transfer_pkey │ 2210002 │ 1110002 │ 1110000 ``` idx_scan : How many times the index has been scanned (used). This can be either directly by a application query... or indirectly due to a JOIN. 索引被扫描（使用）了多少次。它可以是应用程序的直接查询，也可以是 JOIN 导致的间接调用。如果这个值长期为 0，说明它是写放大的元凶，应当果断删掉。 ### OLTP ### 在线事务处理（OLTP） In OLTP workloads, the DBMS needs to quickly read and write individual rows of data while ensuring data integrity. 在 OLTP 负载中，数据库需要在保证数据完整性的同时，飞快地读取和写入单行数据。 Below we showcase some interesting OLTP workflows on the feature list: 1. Listing with pagination 2. Display details of a transfer, including the transactions that were generated 下面展示了之前需求列表中的一些有趣场景： 1. 列表展示与分页 2. 展示转账详情，包含其产生的关联流水 #### Proper pagination #### 正确的分页方式 Listing should be simple enough, but it contains traps for if one uses the naive `OFFSET` approach: > 1. the pages drift when inserting new sales because the numbering is always done from scratch; > 2. the response time increases when browsing further back. 列表分页看似简单，但如果使用幼稚的 `OFFSET` 语法（比如 `LIMIT 10 OFFSET 1000`），则暗藏深坑： > 1. 当有新数据频繁插入时，翻页数据会发生“漂移”（重叠或遗漏），因为 OFFSET 每次都是从头重新数； > 2. 用户翻页越深，数据库响应时间就越长（越往后的页性能断崖式下跌）。 In order to avoid the problems above, we need to leverage the primary key index directly by using values instead of offsets. Given that we are listing all transfers/transactions for a given account sorted by time, this maps directly to the composite primary key index, allowing PostgreSQL to do an equality match on the first two columns and then a backward index scan from the cursor position on `transfer_created_at`: 为了解决这些问题，我们需要采用“键集分页”（Keyset Pagination，也叫基于游标的分页）。它利用索引中的具体值直接定位，而不是傻傻地用 offset 去数数。鉴于我们要按时间倒序列出某账户的所有记录，这直接完美匹配了我们的复合主键索引。PostgreSQL 会在前两列进行极速等值匹配，然后根据游标时间在 `transfer_created_at` 上向后扫描索引： ```sql select * from finance.transfer where routing_number = ? and account_number = ? and transfer_created_at < ? order by transfer_created_at desc fetch first 10 rows only; ``` #### Primary Key Column Order #### 主键列的顺序奥秘 In order to enable the listing above, the primary key definition needs to reflect the access pattern: 1. `routing_number, account_number` - identifies the account (most selective) 2. `transfer_created_at` - enables efficient time-range queries on account transfers, which enables the pagination 3. `counterparty_routing_number, counterparty_account_number` - completes the transfer identity 为了使上述分页飞速运行，主键索引的顺序必须紧密贴合查询访问模式： 1. `routing_number, account_number`：识别指定账户（过滤性最强，前缀精确匹配）。 2. `transfer_created_at`：支持对账户转账进行高效的时间范围查询，从而实现高性能分页。 3. `counterparty_routing_number, counterparty_account_number`：补全信息，确保主键的唯一性。 #### The history of a transfer #### 转账的完整历史 In order to implement feature 5, we need to query the full state history of transfers intermingling it with the proper transactions. We define this as a view, since it represents a derived relation that will be reused throughout the system: 为了实现需求5（让用户看到转账的流转历史及其引发的流水），我们需要将转账的历史状态和关联的流水合并查询。我们将其定义为一个视图（View），因为这是一个会在系统各处被复用的派生关系： ```sql create or replace view finance.transfer_activity as select 'transfer' as kind, t.routing_number, t.account_number, t.counterparty_routing_number, t.counterparty_account_number, t.amount, t.status, t.transfer_created_at as created_at from finance.transfer t union all select 'transfer_history' as kind, h.routing_number, h.account_number, h.counterparty_routing_number, h.counterparty_account_number, h.amount, h.status, h.transfer_created_at as created_at from finance.transfer_status_log h union all select 'pending_transaction' as kind, pt.routing_number, pt.account_number, pt.counterparty_routing_number, pt.counterparty_account_number, pt.amount, 'pending' as status, pt.transaction_created_at as created_at from finance.pending_transaction pt union all select 'settled_transaction' as kind, st.routing_number, st.account_number, st.counterparty_routing_number, st.counterparty_account_number, st.amount, 'settled' as status, st.transaction_created_at as created_at from finance.settled_transaction st; comment on view finance.transfer_activity is 'Unified view of the full history of transfers and their associated transactions. Each row is tagged with a kind discriminator. Used for transfer history display and as the foundation for the updatable interface defined in the On decoupling section.'; ``` Querying the full history of a specific transfer is now a simple filter on the view: 如今，要查询某次转账的完整历史轨迹，对应用程序端来说只需针对这个视图加个简单的条件过滤： ```sql select * from finance.transfer_activity where routing_number = ? and account_number = ? and counterparty_routing_number = ? and counterparty_account_number = ? order by created_at; ``` ### OLAP ### 在线分析处理（OLAP） In OLAP workloads, the DBMS needs manage large volumes of data while allowing for quick query response times. Calculating account balances, which is what we need to do in order to implement requirement 4, fit squarely into that category. 在 OLAP 负载中，数据库需要管理海量数据，同时还必须保证快速的查询响应。计算账户余额（我们实现需求4的核心）刚好就属于这一类。 As a reminder, we need to compute two balances per account, queryable at any point in time: * **Current balance:** the balance you earn interest on, derived from settled transactions only. * **Available balance:** the amount you’re able to move out, derived from both settled and pending transactions. 重温一下，我们需要为每个账户随时随地计算两种余额： * **当前余额：**用来算利息的实际余额，只能从“已结算流水”中推导出来。 * **可用余额：**你可以实际转出的金额，需从“已结算流水”+“待处理流水”合并推导。 Obviously scanning the full transaction history and computing the sum using a row oriented DBMS like PostgreSQL would yield correct results, but it would certainly not be `quick`. Worse, as the transaction table grows, every balance query becomes a range scan over an ever-larger set of rows. 显然，每次查询都去扫描海量的流水表并动态 `SUM()` 加和肯定能得到正确结果，但这绝对不“快”。更糟的是，随着流水表越来越大，每一次查余额都会变成对无底洞般数据范围的全盘扫描。 One solution is to maintain a **balance ledger** incrementally via triggers. The principle is the same one behind incremental view maintenance: the cost of keeping derived data up to date is borne by the process changing the base data, with the extra operations added to the execution plan of the original insert. We shift work from read time to write time. 解决方案之一是通过触发器增量维护一个**余额账本（Balance Ledger）**。它的底层逻辑与增量视图维护（Incremental View Maintenance）是一致的：即让插入数据的那个动作来顺手承担更新派生数据的开销。我们将巨大的计算成本从“读阶段”转移到了“写阶段”。 In our case, this trade-off is acceptable: transactions are append-only (no updates or deletes), and per account, we don’t expect a swarm of concurrent transactions. The write overhead of maintaining the balance ledger is small compared to the read savings of never having to aggregate the full transaction history. 在我们的业务里，这个权衡是极其划算的：因为流水数据是“仅追加”的（绝对没有更新或删除），而且针对单一账户，并不会出现像秒杀系统那样每秒几万次的密集并发流水。所以维护余额账本的微小写入开销，能换来“永远不需要全表聚合查余额”的巨大读取收益。 #### Balance ledger #### 余额账本 The `finance.balance_ledger` table stores a snapshot of both balances after every transaction. Each row records the cumulative totals as of that transaction’s timestamp, enabling queries like “what was the balance at close of business yesterday?” via a single index-backed lookup. `finance.balance_ledger` 记录了每一笔流水发生后的余额快照。每一行记录了截至该流水发生时的累计总额。这使得像“昨天关账时的余额是多少？”这样的分析查询，只需一次毫秒级的索引查找就能搞定。 ```sql create table finance.balance_ledger ( routing_number finance.routing_number not null, account_number finance.account_number not null, as_of timestamptz not null, current_total bigint not null, available_total bigint not null, primary key (routing_number, account_number, as_of) ); comment on table finance.balance_ledger is 'Running balance snapshots per account. Each row records the cumulative current and available totals as of a given transaction timestamp. Maintained incrementally by triggers on settled and pending transaction inserts. Current total reflects settled transactions only. Available total reflects both settled and pending transactions.'; revoke update, delete on finance.balance_ledger from finance; ``` #### Incremental maintenance via triggers #### 通过触发器进行增量维护 When a settled transaction is inserted, both the current and available totals change. When a pending transaction is inserted, only the available total changes. Each trigger fetches the most recent ledger row for the account and appends a new snapshot with the updated running totals. 当插入一条已结算的流水时，当前余额和可用余额都要同时变动。而当插入一条待处理流水时，只有可用余额变动。触发器会迅速调取该账户最新的一条账本记录，加上变动额，然后追加生成一个新的快照。 ```sql create or replace function finance.update_balance_on_settled() returns trigger language plpgsql as $$ declare v_current bigint; v_available bigint; begin select current_total, available_total into v_current, v_available from finance.balance_ledger where routing_number = NEW.routing_number and account_number = NEW.account_number order by as_of desc limit 1; if not found then v_current := 0; v_available := 0; end if; insert into finance.balance_ledger (routing_number, account_number, as_of, current_total, available_total) values (NEW.routing_number, NEW.account_number, NEW.transaction_created_at, v_current + NEW.amount, v_available + NEW.amount); return NEW; end; $$; create trigger settled_update_balance after insert on finance.settled_transaction for each row execute function finance.update_balance_on_settled(); create or replace function finance.update_balance_on_pending() returns trigger language plpgsql as $$ declare v_current bigint; v_available bigint; begin select current_total, available_total into v_current, v_available from finance.balance_ledger where routing_number = NEW.routing_number and account_number = NEW.account_number order by as_of desc limit 1; if not found then v_current := 0; v_available := 0; end if; insert into finance.balance_ledger (routing_number, account_number, as_of, current_total, available_total) values (NEW.routing_number, NEW.account_number, NEW.transaction_created_at, v_current, v_available + NEW.amount); return NEW; end; $$; create trigger pending_update_balance after insert on finance.pending_transaction for each row execute function finance.update_balance_on_pending(); ``` With this design, querying the balance at any point in time is a simple index-backed lookup: 有了这样的设计，查询任何时间点的余额都简化成了极致的索引单行查找： ```sql -- Balance as of a specific timestamp -- 某特定时间点的历史余额 select current_total, available_total from finance.balance_ledger where routing_number = ? and account_number = ? and as_of <= ? order by as_of desc fetch first 1 row only; -- Latest balance -- 当前最新余额 select current_total, available_total from finance.balance_ledger where routing_number = ? and account_number = ? order by as_of desc fetch first 1 row only; ``` ### On serializable isolation ### 论可序列化隔离级别 The constraint triggers in the constraints section and the balance ledger triggers in the OLAP section share a common vulnerability under PostgreSQL’s default `READ COMMITTED` isolation level: they all follow a read-then-write pattern. A transaction reads some state (a transfer’s status, a transfer’s period bounds, the latest balance row), makes a decision based on that state, and then writes. Under `READ COMMITTED`, concurrent transactions can each read a snapshot that does not reflect the other’s uncommitted changes, and both proceed to write, producing an inconsistent result. 在之前章节中我们写到的“约束触发器”和“余额账本触发器”，如果运行在 PostgreSQL 默认的 `READ COMMITTED`（读已提交）隔离级别下，会存在一个致命的并发漏洞：它们都是典型的“先读后写（Read-Then-Write）”模式。一个事务读取了某条状态，基于此做出判断，然后执行写入。而在默认隔离级别下，两个高并发事务可能读到同样的历史快照，并在互相不知道对方未提交写入的情况下双双执行，从而产生可怕的数据不一致。 Concrete examples: 具体例子包括： * **Lost balance updates**: Two concurrent inserts into `settled_transaction` for the same account both read the latest `balance_ledger` row as `current_total = 1000`. Both insert a new ledger row with `current_total = 1000 + amount`, when the second should have been `1000 + amount_A + amount_B`. The running total is silently corrupted. * **余额更新丢失：**两笔并发汇给同一账户的钱，触发器同时读取到当前余额为 1000。两者都插入了 1000 + 自己的金额，导致流水进了两笔，但余额却只加了一笔。余额被悄无声息地破坏了。 * **Pending transaction against a closing transfer**: `ensure_pending_transfer` reads the transfer as `status = 'pending'` and allows the insert. Concurrently, another transaction updates the transfer to `completed`. Both commit, and a pending transaction now references a completed transfer. * **在转账关闭时塞入待处理流水：**触发器A检查发现转账仍是 `pending` 状态，允许流水插入。与此同时并发事务B把转账更新为了 `completed`。双双提交后，你会神奇地发现一笔已完成转账下面挂了个“待处理”流水，破坏了业务逻辑。 All of these are instances of the same fundamental problem: write skew under snapshot isolation. The solution is `SERIALIZABLE` isolation: 所有这些问题归根结底都是同一个底层原理作祟：快照隔离下的**写倾斜（Write Skew）**（一种并发异常，两个事务读取重叠的数据并进行不相交的更新，导致逻辑状态不一致）。针对金融级系统，解决方案必须一步到位：使用 PostgreSQL 的最高隔离级别 `SERIALIZABLE`（可序列化隔离）： ```sql alter role finance set default_transaction_isolation = 'serializable'; ``` Under PostgreSQL’s Serializable Snapshot Isolation (SSI), the engine tracks read-write dependencies between concurrent transactions. When it detects a dependency cycle that could produce a result impossible under any serial execution, it aborts one of the transactions with a serialization failure. The application must be prepared to retry aborted transactions, but the data invariants are never violated. 在 PostgreSQL 的“可序列化快照隔离（SSI）”架构下，引擎会在底层自动跟踪并发事务间的读写依赖图。当检测到可能引发数据不一致的环形依赖时，它会当机立断直接抛弃其中一个事务，并抛出序列化失败错误。这就要求应用程序端做好重试机制的准备，但你的核心数据绝对不会出现任何错乱。 This is a global setting because the problem is global: every read-then-write constraint trigger and every incremental balance update is vulnerable. Setting isolation per-transaction would require the application to know which transactions touch which tables, and a single missed annotation would silently open a consistency hole. The database default eliminates that risk. 这必须是一个全局设定，因为写倾斜的问题是全局的。如果交由应用层的代码来对每个事务指定隔离级别，但凡某个程序员漏写了一处注解，系统就会悄然开出一个无法察觉的金额丢失漏洞。直接将数据库角色的默认隔离级别锁死，能从根源消除这一风险。 The cost is that some transactions will be aborted and must be retried. In our case, this cost is low: the serialization conflicts occur only between concurrent writes to the *same account*, and per account, we don’t expect a swarm of concurrent transactions. The application can use straightforward retry logic such as exponential backoff. The alternative of risking silent data corruption is certainly not acceptable in a financial system. 代价确实存在：一小部分事务会因为并发冲突被打回重试。但在我们的场景里，这个代价极低。因为序列化冲突仅仅发生在同时写入**同一个账户**的极少数并发场景下，对于单个账户，并不会出现秒杀级高并发。应用程序只需写一个简单的退避重试（Exponential Backoff）代码即可应对。相比之下，“容忍隐蔽的资金余额毁损”在金融系统中是绝对无法容忍的。 ### On decoupling ### 论解耦 Views are the canonical way to implement modularity in SQL DBMSes like PostgreSQL. As Codd originally envisioned, views provide logical data independence: application programs and terminal activities remain unaffected when the internal representation of data changes. In non-alien language: the relation between interfaces (views) and implementations (base tables) is many-to-many, which is precisely the definition of modularity. 在像 PostgreSQL 这样的关系数据库中，视图（Views）是实现系统模块化的正统途径。正如关系型数据库之父 Codd 最初设想的那样，视图提供了“逻辑数据独立性（Logical Data Independence）”：当底层数据的物理结构发生巨变时，应用程序和终端操作完全不受影响。用人话来说：接口（视图）和实现（底层物理表）之间的关系解绑成了多对多关系，而这，恰恰就是软件工程里梦寐以求的“模块化解耦”的定义。 This means that instead of reaching for microservices to achieve decoupling — with all their attendant costs in serialization overhead, distributed consistency problems, and operational complexity — we can achieve the same logical property using views over a single PostgreSQL instance. 这意味着，我们完全不需要盲目迷信通过拆分微服务来实现系统解耦——那样会带来序列化开销、臭名昭著的分布式一致性问题以及地狱级的运维复杂性——我们只需在一个单一的 PostgreSQL 实例中，巧妙地铺设视图层，就能获得同等甚至更好的逻辑解耦属性。 The `finance.transfer_activity` view defined in the history of a transfer section already provides a stable read interface. Now we make it updatable, turning it into the single interface through which applications insert transfers and transactions, and update transfer status: 在之前“转账的完整历史”小节中定义的 `finance.transfer_activity` 视图已经充当了坚如磐石的“读接口”。现在，我们将利用触发器赋予它“可更新（Updatable）”的能力，使它成为应用程序发起转账、插入流水、更新状态的唯一**超级 API 入口**： ```sql create or replace function finance.transfer_activity_insert() returns trigger language plpgsql as $$ begin case NEW.kind when 'transfer' then insert into finance.transfer (routing_number, account_number, counterparty_routing_number, counterparty_account_number, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.amount); when 'pending_transaction' then insert into finance.pending_transaction (routing_number, account_number, counterparty_routing_number, counterparty_account_number, transfer_created_at, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.created_at, NEW.amount); when 'settled_transaction' then insert into finance.settled_transaction (routing_number, account_number, counterparty_routing_number, counterparty_account_number, transfer_created_at, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.created_at, NEW.amount); else raise exception 'Unknown kind: %. Must be transfer, pending_transaction, or settled_transaction', NEW.kind; end case; return NEW; end; $$; create trigger transfer_activity_insert_trigger instead of insert on finance.transfer_activity for each row execute function finance.transfer_activity_insert(); create or replace function finance.transfer_activity_update() returns trigger language plpgsql as $$ begin if OLD.kind <> 'transfer' then raise exception 'Only current transfers can be updated, not %', OLD.kind; end if; update finance.transfer set status = NEW.status where routing_number = OLD.routing_number and account_number = OLD.account_number and transfer_created_at = OLD.created_at and counterparty_routing_number = OLD.counterparty_routing_number and counterparty_account_number = OLD.counterparty_account_number; return NEW; end; $$; create trigger transfer_activity_update_trigger instead of update on finance.transfer_activity for each row execute function finance.transfer_activity_update(); ``` Applications interact with `finance.transfer_activity` as a single unified stream. They can: 从此，所有的业务微服务或应用层代码，只需像操作单一的数据流一样与 `finance.transfer_activity` 这个视图打交道。它们可以： * **Read** the full history of a transfer and its transactions, filtered by account and transfer identity, ordered by time. * **读取**一次转账及其流水的全生命周期轨迹（甚至支持强大的过滤、排序）。 * **Insert** new transfers (`kind = 'transfer'`), pending transactions (`kind = 'pending_transaction'`), or settled transactions (`kind = 'settled_transaction'`). The `INSTEAD OF` trigger routes each insert to the correct underlying table, where all constraint triggers, temporal foreign keys, and balance ledger maintenance fire as usual. For transactions, `created_at` identifies the originating transfer. * **插入**一笔新转账（标记 `kind` 字段），或插入流水。视图上的 `INSTEAD OF` 拦截触发器会在底层悄悄将其路由至对应的物理表，所有前述的严苛约束、余额账本更新都会在黑盒内完美执行。 * **Update** a transfer’s status by updating a row where `kind = 'transfer'`. The `INSTEAD OF` trigger routes the status change to `finance.transfer`, where the state machine trigger and all constraint triggers fire as usual. * **更新**状态机。应用层只需对查出来的记录发出 `UPDATE` 指令，底层引擎同样会将其路由给底层的转账物理表，并无缝触发严密的防腐拒止验证机制。 If the underlying table structure changes — columns are renamed, new columns are added, tables are split or merged — the view definition is updated once, and every application continues to work unchanged. 未来，即使底层物理结构发生了天翻地覆的变化——列改名了，表为了扩展被切分（Sharding）了，或者是多表合并了——只要视图接口的定义映射保持兼容，你的数十个应用程序代码都不需要修改任何一行代码就能继续完美运行！ This is decoupling achieved at the data level, with no network hops, no serialization overhead, and no distributed consistency problems. The view *is* the interface, and the base tables are the implementation. 这就是彻头彻尾在数据层面实现的“降维打击式解耦”。这里没有跨服务器的网络跳转，没有 JSON/gRPC 序列化开销，没有分布式事务扯皮带来的脱发。视图**就是**提供给后端的契约接口，物理表**就是**底层黑盒实现。 ### Benchmarking the startup scenario ### 初创公司规模下的基准测试验证 To validate the design under realistic conditions, we use `pgbench` with custom scripts that exercise the full write and read paths through the `finance.transfer_activity` view. The scenario models the startup tier from the capacity planning section: 10,000 transfers per day with an 80/20 read/write split. 为了在真实场景下验证该设计的强悍实力，我们使用了 PostgreSQL 官方自带的轻量级并发测试工具 `pgbench`，并编写了直接轰炸 `finance.transfer_activity` 视图读写路径的脚本。测试场景正是我们在“容量规划”一节中定义的初创公司规模：按每天 1 万笔转账设计，模拟读写比 80/20。 #### Seed data #### 铺设种子数据 First, we seed 100 managed and 100 external accounts: 首先初始化 100 个托管账户和 100 个外部账户： ```sql -- Seed external accounts insert into finance.external_account (routing_number, account_number, account_name) select lpad(i::text, 9, '0'), lpad(i::text, 12, '0'), 'External Account ' || i from generate_series(1, 100) as i on conflict do nothing; -- Seed managed accounts insert into finance.managed_active_account (routing_number, account_number, account_name) select lpad((i + 100)::text, 9, '0'), lpad((i + 100)::text, 12, '0'), 'Managed Account ' || i from generate_series(1, 100) as i on conflict do nothing; ``` #### Write script: full transfer lifecycle #### 写脚本：完整的转账生命周期模拟 Each invocation of this script exercises the complete lifecycle through the updatable view: create a transfer, insert a pending transaction, then complete the transfer and insert a settled transaction. Transfer creation and the pending transaction each run in their own transaction, mirroring how a real application would process these at different points in time. The completion and settled transaction are grouped in a single transaction because the state machine closes the transfer period with `now()`, and the settled transaction’s `transaction_created_at` (which also defaults to `now()`) must fall within that period. Every constraint trigger, the state machine, the temporal foreign key, and the balance ledger triggers all fire on each run. 该压测脚本每次运行都会完整打通上述可更新视图的全生命周期：创建转账 -> 插入待处理流水 -> 完成转账 -> 插入已结算流水。前两步被分离开独立运行，模拟真实世界中这两步发生的时间差。后两步被捆绑进一个原生事务中，因为转账关闭时刻与流水结账时刻需要一致以通过边界严苛的触发器检验。你可以想象，在这一套组合拳中，上文所有的状态机、时态验证、余额触发器、全表系统存档等“重型机制”都会在一瞬间全面爆发。 ```sql \set managed_id random(1, 100) \set external_id random(1, 100) \set amount random(100, 100000) -- 1. Create transfer via the view -- 1. 通过视图创建转账 insert into finance.transfer_activity (kind, routing_number, account_number, counterparty_routing_number, counterparty_account_number, amount) values ('transfer', lpad((:managed_id + 100)::text, 9, '0'), lpad((:managed_id + 100)::text, 12, '0'), lpad(:external_id::text, 9, '0'), lpad(:external_id::text, 12, '0'), :amount); -- 2. Retrieve the transfer we just created -- 2. 查回刚刚创建的那笔转账（获取时间戳主键） select created_at as xfer_ts from finance.transfer_activity where kind = 'transfer' and routing_number = lpad((:managed_id + 100)::text, 9, '0') and account_number = lpad((:managed_id + 100)::text, 12, '0') and counterparty_routing_number = lpad(:external_id::text, 9, '0') and counterparty_account_number = lpad(:external_id::text, 12, '0') and status = 'pending' order by created_at desc fetch first 1 row only \gset -- 3. Insert pending transaction via the view (transfer is still pending) -- 3. 通过视图插入待处理流水（转账依旧保持待处理） insert into finance.transfer_activity (kind, routing_number, account_number, counterparty_routing_number, counterparty_account_number, created_at, amount) values ('pending_transaction', lpad((:managed_id + 100)::text, 9, '0'), lpad((:managed_id + 100)::text, 12, '0'), lpad(:external_id::text, 9, '0'), lpad(:external_id::text, 12, '0'), ':xfer_ts', :amount); -- 4. Complete the transfer and insert settled transaction atomically. -- This ensures now() is the same for the period close and the -- settled transaction's transaction_created_at. -- 4. 原子地完成转账关闭并插入最终流水。 begin; update finance.transfer_activity set status = 'completed' where kind = 'transfer' and routing_number = lpad((:managed_id + 100)::text, 9, '0') and account_number = lpad((:managed_id + 100)::text, 12, '0') and counterparty_routing_number = lpad(:external_id::text, 9, '0') and counterparty_account_number = lpad(:external_id::text, 12, '0') and created_at = ':xfer_ts'; insert into finance.transfer_activity (kind, routing_number, account_number, counterparty_routing_number, counterparty_account_number, created_at, amount) values ('settled_transaction', lpad((:managed_id + 100)::text, 9, '0'), lpad((:managed_id + 100)::text, 12, '0'), lpad(:external_id::text, 9, '0'), lpad(:external_id::text, 12, '0'), ':xfer_ts', :amount); commit; ``` #### Read script: activity stream and balance #### 读脚本：活动信息流与余额查询 Each invocation queries the transfer activity stream for a random account (paginated) and its latest balance: 每次调用都随机获取一个账户的转账历史流（利用键集分页拉取前 20 条）和它经过实时运算后的秒级余额： ```sql \set managed_id random(1, 100) -- Read transfer activity stream (latest 20 entries) select * from finance.transfer_activity where routing_number = lpad((:managed_id + 100)::text, 9, '0') and account_number = lpad((:managed_id + 100)::text, 12, '0') order by created_at desc fetch first 20 rows only; -- Read latest balance select current_total, available_total from finance.balance_ledger where routing_number = lpad((:managed_id + 100)::text, 9, '0') and account_number = lpad((:managed_id + 100)::text, 12, '0') order by as_of desc fetch first 1 row only; ``` #### Running the benchmark #### 运行压测 ```bash # 1. Apply the full schema (Appendix A) # 1. 应用完整的底层Schema定义 psql -f only_postgres_code.sql # 2. Seed accounts # 2. 播撒初始的 100 账户种子 psql -f pgbench_setup.sql # 3. Run 80/20 read/write mix for 60 seconds # 3. 运行 80%读 / 20%写的高压并发，持续 60 秒 # @4 = weight 4 (80%), @1 = weight 1 (20%) # -c 10: 10 concurrent clients (10个并发客户端轰炸) # -j 4: 4 worker threads (使用4核工作线程) # -P 5: progress every 5 seconds # -T 60: run for 60 seconds pgbench -h localhost -p 54321 -U admin -d blog \ --no-vacuum \ -f pgbench_read.sql@4 \ -f pgbench_write.sql@1 \ -c 10 -j 4 -T 60 -P 5 ``` #### Results #### 惊人的结果 ```nil pgbench (18.4) transaction type: multiple scripts scaling factor: 1 query mode: simple number of clients: 10 number of threads: 4 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 45985 number of failed transactions: 0 (0.000%) latency average = 13.044 ms latency stddev = 12.449 ms initial connection time = 17.840 ms tps = 766.151693 (without initial connection time) SQL script 1: pgbench_read.sql - weight: 4 (targets 80.0% of total) - 36793 transactions (80.0% of total) - latency average = 8.080 ms - latency stddev = 6.135 ms SQL script 2: pgbench_write.sql - weight: 1 (targets 20.0% of total) - 9191 transactions (20.0% of total) - latency average = 32.915 ms - latency stddev = 11.455 ms ``` Key takeaways: 压测给出的核心结论： * **766 TPS overall** with 0 failed transactions. The startup target of 10,000 transfers/day is ~0.12 TPS. We exceed that by over 6,000x, confirming massive headroom on modest hardware. * **总吞吐量高达 766 TPS**（每秒处理完整的读/写链条766次），且 **0 失败率**。对于初创公司“每天 10,000 笔”的目标，实际上只要求 0.12 TPS。我们在普通开发电脑上超标完成了近 6000 倍的性能盈余，证明了即便硬件一般，该架构也能有无底洞般的性能上升空间。 * **0 serialization failures**: Despite 10 concurrent clients writing to 100 accounts under `SERIALIZABLE` isolation, no transactions were aborted. The working set is distributed across enough accounts that write contention is negligible at this scale. * **0 序列化冲突错误**：即便我们在极高的隔离级别 `SERIALIZABLE` 下使用 10 个线程狂写 100 个账户，依然没有一个事务被打回重试。工作集分布得很散，所以在这种量级下写冲突几乎可以忽略。 * **8ms average read latency**: The `UNION ALL` view over 4 tables plus the balance ledger lookup completes well within interactive response time, even as the tables grow throughout the 60-second run. * **超快的 8 毫秒平均读延迟**：哪怕是包含 4 张表连表的巨大 `UNION ALL` 视图查询，外加一笔聚合账本查询，也能在眨眼之间（远远快于人的交互反应）瞬间完成。而且在整个压测期间表急剧膨胀的情况下，性能丝毫未受损。 * **33ms average write latency**: Each write transaction exercises the full constraint set that implements complex business logic (audit trails, time constraints, balances, etc) all within 33ms. This is the true cost of enforcing every business rule at the data level, and it is more than acceptable. * **33 毫秒的平均写延迟**：每一笔写操作实际上在底层背负了所有的“沉重锁链”——时空状态机防腐机制、余额核算系统、审计历史归档……全套打下来只要 33 毫秒。这证明了将一切业务规则压到最底层去校验的性能开销，是完全可以闭眼接受的。 These results were obtained on a development laptop, not production hardware. On an RDS instance sized for the working set (as described in the capacity planning section), performance would be significantly better. 所有上述恐怖的结果甚至只是一台开发用笔记本电脑上跑出来的。如果跑在正规调优过、拥有充沛内存容纳全量工作集的生产级 RDS 实例上，性能还会好上一个次元。 ### Conclusion ### 结论 We set out to show that the default assumption for your data problems should be that your company can do fine with just PostgreSQL. Let’s revisit each objection from the setup and see how it was addressed: 我们一开始的目的是证明：针对你的企业数据困境，“仅靠原生的 PostgreSQL 就足矣应对”应该作为所有架构决策的默认选项。让我们重温一下开头列出的那些质疑，看看我们是如何逐一反杀的： **I’ll need auditing and reconstructing state** System-time temporal tables give you a complete, automatic audit trail of every transfer status transition. No event store, no append-only log infrastructure — just a history table maintained by a trigger. **“我需要完善的审计与历史溯源能力”** 借用系统时间段时态表（时态机制），你自动获得了一份严密、全貌的状态变迁审计追踪。无需去搞什么高大上的事件溯源，不用弄专门的追加式日志基建服务器——仅仅通过一个底层触发器维护的历史表就达到了目的。 **Write throughput is too low** By keeping the working set (2 days of transfers and transactions) sized to fit in `shared_buffers`, and by aligning indexes with immutable columns to enable HOT updates that eliminate write amplification on transfer status transitions, we maximize write performance without any external caching layer. **“写入速度瓶颈太低了”** 通过良好的容量规划，把 2 天内的核心热点数据常驻于内存（`shared_buffers`）中，并通过“让常被更新的状态列规避索引”这一巧妙设计激发了 HOT 引擎底层的免索引更新极速通道，我们在不引入哪怕一层 Redis 缓存的前提下，榨干了单机极高的写入性能。 **The transactional queries are too slow** Keyset pagination over composite primary keys, as shown in the OLTP section, gives stable, index-backed listing performance that doesn’t degrade as you page deeper. Transfer history queries are a single `UNION ALL` over indexed tables. **“我的 OLTP 系统接口翻页太慢了”** 抛弃传统分页，改用严格顺着复合主键游走的键集游标分页（Keyset Pagination），不论用户往后翻多深，底层性能稳定得犹如磐石。查阅一个长条流水的历史记录，被优化成了走索引引擎下单一且极速的 `UNION ALL` 查询。 **The analytical queries are too slow** The balance ledger maintains running balance snapshots incrementally via triggers, turning what would be a full-table aggregation into a single index-backed lookup at any point in time. **“我的分析报表系统查余额太慢了”** “余额账本”架构依靠底层的微小写入前置处理，做到了“增量聚合”。无论何时你想查任意时间点的总账，都会从“全表耗时大扫描”降维打击成“一次瞬间的键值极速捞取”。 **My app will be coupled to the Database** The updatable view `finance.transfer_activity` serves as a stable interface for reading activity, inserting transfers and transactions, and updating transfer status through a single unified stream. If the underlying tables change, the view definition is updated once and every application continues to work unchanged. This is modularity achieved at the data level, with no network hops, no serialization overhead, and no distributed consistency problems. **“如果业务逻辑都放在数据库，应用程序会和数据库产生死亡耦合”** 我们通过定义 `finance.transfer_activity` 这样一个可自由进出的“可更新视图（Updatable View）”，为应用程序铺就了一条固若金汤的统一标准操作接口流。只要视图本身定义不破，底层无论怎么分表、重构，应用代码依然畅通无阻，半行无需修改。这种数据底层的“真・解耦”，免除了微服务架构里无数的网络跳跃开销、JSON/gRPC 的硬转码灾难以及微服务间互扯头发的一致性难题。 All of this runs on a single vanilla PostgreSQL 18 instance with standard extensions available on RDS. No distributed-systems cosplay, no infrastructure proliferation, no operational complexity tax. We have only scratched the surface of what is available both in PostgreSQL as a SQL engine and in relational theory properly understood. 上述如此庞大且坚固的架构，仅仅跑在**一台毫无魔改的原生 PostgreSQL 18** 节点上，外加常规 RDS 云主机自带的零星扩展插件而已。毫无所谓的分布式角色扮演，无休止的“堆机器”被终结，复杂的云原生运维税被省下。更震撼的是，我们今日所展示的魔法，仅仅是正统关系型理论与 PostgreSQL 这个数据库巨兽无限潜能的冰山一角。

Figure 3: 1936 fire in the Sagrada Familia, set by communist revolutionaries

### Appendix A: Full code suite. ### 附录 A：完整的代码套件 ```sql create schema finance; create role finance; grant usage on schema finance to finance; alter default privileges in schema finance grant select, insert, update, delete on tables to finance; alter default privileges in schema finance grant usage, select on sequences to finance; create domain finance.routing_number as text check (value ~ '^[0-9]{9}$'); create domain finance.account_number as text check (value ~ '^[0-9]{12}$'); create domain finance.transfer_status as text check(value in ('pending', 'returned', 'completed')); -- To use temporal constraints, you need to install the btree_gist extension, which provides the necessary operator classes for creating GiST indexes on scalar data types: create extension if not exists btree_gist; create table finance.managed_active_account( routing_number finance.routing_number not null, account_number finance.account_number not null, account_name text not null, account_active_period tstzrange not null default tstzrange(now(), 'infinity', '[)'), primary key (routing_number, account_number, account_active_period without overlaps) ); comment on table finance.managed_active_account is 'Managed Accounts are what transactions are performed against. Think of your bank account. They store money, receive transfers, and send payments. They earn interest and have depository insurance. This relation holds the accounts that are active. No transfer may be created for accounts in the period that they were inactive.'; create table finance.external_account( routing_number finance.routing_number not null, account_number finance.account_number not null, account_name text not null, primary key (routing_number, account_number) ); comment on table finance.external_account is 'External accounts represent counterparty accounts at other institutions. They are the other side of a transfer. Unlike managed accounts, they have no temporal active period since we do not control their lifecycle.'; -- Ensure managed and external accounts never share the same identity create or replace function finance.not_external_account( p_routing_number finance.routing_number, p_account_number finance.account_number ) returns boolean language sql stable as $$ select not exists ( select 1 from finance.external_account where routing_number = p_routing_number and account_number = p_account_number ); $$; create or replace function finance.not_managed_account( p_routing_number finance.routing_number, p_account_number finance.account_number ) returns boolean language sql stable as $$ select not exists ( select 1 from finance.managed_active_account where routing_number = p_routing_number and account_number = p_account_number ); $$; alter table finance.managed_active_account add constraint managed_not_external check (finance.not_external_account(routing_number, account_number)); alter table finance.external_account add constraint external_not_managed check (finance.not_managed_account(routing_number, account_number)); create table finance.transfer ( transfer_period tstzrange not null default tstzrange(now(), 'infinity', '[)'), transfer_created_at timestamptz generated always as (lower(transfer_period)) stored, account_number finance.account_number not null, routing_number finance.routing_number not null, counterparty_account_number finance.account_number not null, counterparty_routing_number finance.routing_number not null, amount bigint not null, status finance.transfer_status not null default 'pending', -- Natural order: account identity, then time, then counterparty -- This enables efficient time-range queries on account transfers primary key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ), foreign key ( counterparty_routing_number, counterparty_account_number ) references finance.external_account ( routing_number, account_number ), -- temporal foreign key: ensure managed account exists during transfer period foreign key ( routing_number, account_number, period transfer_period ) references finance.managed_active_account ( routing_number, account_number, period account_active_period ) ); comment on table finance.transfer is 'Transfers represent money movement between an external account and a managed account. Status follows state machine: pending -> (completed | returned). Period closes on terminal state. transfer_created_at is a stored generated column derived from lower(transfer_period), eliminating redundancy while remaining usable in primary keys and foreign key references.'; revoke insert on finance.transfer from finance; revoke update on finance.transfer from finance; -- transfer_period will be managed based on the status grant insert (routing_number, account_number, counterparty_routing_number, counterparty_account_number, amount) on finance.transfer to finance; grant update (status) on finance.transfer to finance; -- Add system-time period column to transfer table alter table finance.transfer add column if not exists sys_period tstzrange default tstzrange(current_timestamp, null); alter table finance.transfer alter column sys_period set not null; -- Focused history table - only what we need for status transitions create table if not exists finance.transfer_status_log ( like finance.transfer ); comment on table finance.transfer_status_log is 'Automatic log of all transfer status transitions via temporal_tables extension. Shows complete state machine history.'; alter table finance.transfer_status_log add primary key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number, sys_period ); -- Use temporal_tables versioning procedure create trigger transfer_save_status_history before insert or update or delete on finance.transfer for each row execute procedure versioning('sys_period', 'finance.transfer_status_log', true); -- Add system-time period column to the account table alter table finance.managed_active_account add column if not exists sys_period tstzrange default tstzrange(current_timestamp, null); alter table finance.managed_active_account alter column sys_period set not null; create table if not exists finance.managed_active_account_log ( like finance.managed_active_account ); comment on table finance.managed_active_account_log is 'Automatic log of all account activity via temporal_tables extension.'; create index on finance.managed_active_account_log (sys_period); create index on finance.managed_active_account_log (routing_number, account_number); -- Use temporal_tables versioning procedure create trigger account_save_history before insert or update or delete on finance.managed_active_account for each row execute procedure versioning('sys_period', 'finance.managed_active_account_log', true); create table finance.settled_transaction ( transaction_created_at timestamptz not null default now(), account_number finance.account_number not null, routing_number finance.routing_number not null, counterparty_account_number finance.account_number not null, counterparty_routing_number finance.routing_number not null, transfer_created_at timestamptz not null, amount bigint not null, primary key ( account_number, routing_number, transaction_created_at ), foreign key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) references finance.transfer ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) ); comment on table finance.settled_transaction is 'Settled transactions affect both your available balance and your current balance. They are immutable events --- no updates or deletes permitted.'; revoke update, delete on finance.settled_transaction from finance; create table finance.pending_transaction ( like finance.settled_transaction including all, foreign key ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) references finance.transfer ( routing_number, account_number, transfer_created_at, counterparty_routing_number, counterparty_account_number ) ); comment on table finance.pending_transaction is 'Pending transactions represent potential future credits or debits. They affect available balance but not current balance. Immutable once created.'; revoke update, delete on finance.pending_transaction from finance; create or replace function finance.enforce_transfer_state_machine() returns trigger language plpgsql as $$ begin if OLD.status = NEW.status then return NEW; end if; if OLD.status = 'pending' then if NEW.status not in ('completed', 'returned') then raise exception 'Invalid state transition: pending can only transition to completed or returned, not %', NEW.status; end if; NEW.transfer_period := tstzrange(lower(OLD.transfer_period), now(), '[]'); elsif OLD.status in ('completed', 'returned') then raise exception 'Invalid state transition: % is a terminal state and cannot transition to %', OLD.status, NEW.status; end if; return NEW; end; $$; create trigger transfer_z_enforce_state_machine before update of status on finance.transfer for each row when (OLD.status <> NEW.status) execute function finance.enforce_transfer_state_machine(); create or replace function finance.transaction_within_transfer_period() returns trigger language plpgsql as $$ declare v_transfer_period tstzrange; begin select transfer_period into v_transfer_period from finance.transfer where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number; if not found then raise exception 'Transfer not found for transaction'; end if; if not (v_transfer_period @> NEW.transaction_created_at) then raise exception 'Transaction created_at % is outside transfer period %', NEW.transaction_created_at, v_transfer_period; end if; return NEW; end; $$; create constraint trigger settled_transaction_within_transfer_period after insert on finance.settled_transaction deferrable initially deferred for each row execute function finance.transaction_within_transfer_period(); create constraint trigger pending_transaction_within_transfer_period after insert on finance.pending_transaction deferrable initially deferred for each row execute function finance.transaction_within_transfer_period(); create or replace function finance.is_pending_transfer( p_routing_number finance.routing_number, p_account_number finance.account_number, p_transfer_created_at timestamptz, p_counterparty_routing_number finance.routing_number, p_counterparty_account_number finance.account_number ) returns boolean language sql stable as $$ select exists ( select 1 from finance.transfer t where t.routing_number = p_routing_number and t.account_number = p_account_number and t.transfer_created_at = p_transfer_created_at and t.counterparty_routing_number = p_counterparty_routing_number and t.counterparty_account_number = p_counterparty_account_number and t.status = 'pending' ); $$; create or replace function finance.ensure_pending_transfer() returns trigger language plpgsql as $$ begin if not finance.is_pending_transfer( NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number ) then raise exception 'Must reference a pending transfer (%, %, %, %, %)', NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number; end if; return NEW; end; $$; create or replace function finance.ensure_non_pending_transfer() returns trigger language plpgsql as $$ begin if finance.is_pending_transfer( NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number ) then raise exception 'Cannot reference a pending transfer (%, %, %, %, %)', NEW.routing_number, NEW.account_number, NEW.transfer_created_at, NEW.counterparty_routing_number, NEW.counterparty_account_number; end if; return NEW; end; $$; create constraint trigger pending_transaction_requires_pending_transfer after insert on finance.pending_transaction deferrable initially deferred for each row execute function finance.ensure_pending_transfer(); create constraint trigger settled_transaction_requires_non_pending_transfer after insert on finance.settled_transaction deferrable initially deferred for each row execute function finance.ensure_non_pending_transfer(); create or replace function finance.no_future_transactions_on_close() returns trigger language plpgsql as $$ begin if exists ( select 1 from finance.settled_transaction where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number and transaction_created_at > now() union all select 1 from finance.pending_transaction where routing_number = NEW.routing_number and account_number = NEW.account_number and transfer_created_at = NEW.transfer_created_at and counterparty_routing_number = NEW.counterparty_routing_number and counterparty_account_number = NEW.counterparty_account_number and transaction_created_at > now() ) then raise exception 'Cannot close transfer (%, %, %): transaction(s) exist after current time', NEW.routing_number, NEW.account_number, NEW.transfer_created_at; end if; return NEW; end; $$; create constraint trigger transfer_no_future_transactions_on_close after update of status on finance.transfer deferrable initially deferred for each row when (NEW.status in ('completed', 'returned')) execute function finance.no_future_transactions_on_close(); create or replace view finance.transfer_activity as select 'transfer' as kind, t.routing_number, t.account_number, t.counterparty_routing_number, t.counterparty_account_number, t.amount, t.status, t.transfer_created_at as created_at from finance.transfer t union all select 'transfer_history' as kind, h.routing_number, h.account_number, h.counterparty_routing_number, h.counterparty_account_number, h.amount, h.status, h.transfer_created_at as created_at from finance.transfer_status_log h union all select 'pending_transaction' as kind, pt.routing_number, pt.account_number, pt.counterparty_routing_number, pt.counterparty_account_number, pt.amount, 'pending' as status, pt.transaction_created_at as created_at from finance.pending_transaction pt union all select 'settled_transaction' as kind, st.routing_number, st.account_number, st.counterparty_routing_number, st.counterparty_account_number, st.amount, 'settled' as status, st.transaction_created_at as created_at from finance.settled_transaction st; comment on view finance.transfer_activity is 'Unified view of the full history of transfers and their associated transactions. Each row is tagged with a kind discriminator. Used for transfer history display and as the foundation for the updatable interface defined in the On decoupling section.'; create table finance.balance_ledger ( routing_number finance.routing_number not null, account_number finance.account_number not null, as_of timestamptz not null, current_total bigint not null, available_total bigint not null, primary key (routing_number, account_number, as_of) ); comment on table finance.balance_ledger is 'Running balance snapshots per account. Each row records the cumulative current and available totals as of a given transaction timestamp. Maintained incrementally by triggers on settled and pending transaction inserts. Current total reflects settled transactions only. Available total reflects both settled and pending transactions.'; revoke update, delete on finance.balance_ledger from finance; create or replace function finance.update_balance_on_settled() returns trigger language plpgsql as $$ declare v_current bigint; v_available bigint; begin select current_total, available_total into v_current, v_available from finance.balance_ledger where routing_number = NEW.routing_number and account_number = NEW.account_number order by as_of desc limit 1; if not found then v_current := 0; v_available := 0; end if; insert into finance.balance_ledger (routing_number, account_number, as_of, current_total, available_total) values (NEW.routing_number, NEW.account_number, NEW.transaction_created_at, v_current + NEW.amount, v_available + NEW.amount); return NEW; end; $$; create trigger settled_update_balance after insert on finance.settled_transaction for each row execute function finance.update_balance_on_settled(); create or replace function finance.update_balance_on_pending() returns trigger language plpgsql as $$ declare v_current bigint; v_available bigint; begin select current_total, available_total into v_current, v_available from finance.balance_ledger where routing_number = NEW.routing_number and account_number = NEW.account_number order by as_of desc limit 1; if not found then v_current := 0; v_available := 0; end if; insert into finance.balance_ledger (routing_number, account_number, as_of, current_total, available_total) values (NEW.routing_number, NEW.account_number, NEW.transaction_created_at, v_current, v_available + NEW.amount); return NEW; end; $$; create trigger pending_update_balance after insert on finance.pending_transaction for each row execute function finance.update_balance_on_pending(); alter role finance set default_transaction_isolation = 'serializable'; create or replace function finance.transfer_activity_insert() returns trigger language plpgsql as $$ begin case NEW.kind when 'transfer' then insert into finance.transfer (routing_number, account_number, counterparty_routing_number, counterparty_account_number, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.amount); when 'pending_transaction' then insert into finance.pending_transaction (routing_number, account_number, counterparty_routing_number, counterparty_account_number, transfer_created_at, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.created_at, NEW.amount); when 'settled_transaction' then insert into finance.settled_transaction (routing_number, account_number, counterparty_routing_number, counterparty_account_number, transfer_created_at, amount) values (NEW.routing_number, NEW.account_number, NEW.counterparty_routing_number, NEW.counterparty_account_number, NEW.created_at, NEW.amount); else raise exception 'Unknown kind: %. Must be transfer, pending_transaction, or settled_transaction', NEW.kind; end case; return NEW; end; $$; create trigger transfer_activity_insert_trigger instead of insert on finance.transfer_activity for each row execute function finance.transfer_activity_insert(); create or replace function finance.transfer_activity_update() returns trigger language plpgsql as $$ begin if OLD.kind <> 'transfer' then raise exception 'Only current transfers can be updated, not %', OLD.kind; end if; update finance.transfer set status = NEW.status where routing_number = OLD.routing_number and account_number = OLD.account_number and transfer_created_at = OLD.created_at and counterparty_routing_number = OLD.counterparty_routing_number and counterparty_account_number = OLD.counterparty_account_number; return NEW; end; $$; create trigger transfer_activity_update_trigger instead of update on finance.transfer_activity for each row execute function finance.transfer_activity_update(); ```

Code Snippet 1 (代码片段 1): Full code listing. (完整的代码列表。)