Horizontally Scaling PostgreSQL with YugabyteDB: The Problem, the Fix, and a Three-Node Demo

本文为原文前 6,000 字符的节选翻译，完整内容请查看原文。

The problem: PostgreSQL scales up, not out

PostgreSQL is a remarkable database. It is correct, extensible, and has a huge ecosystem. For most applications, a single well-tuned instance is the right answer for years. But a single Postgres server has one writer, and that sets a ceiling. When you hit it, the usual escape routes each have a cost:

问题：PostgreSQL 只能纵向扩展，无法横向扩展

PostgreSQL 是一款出色的数据库。它正确、可扩展，并拥有庞大的生态系统。对于大多数应用程序而言，单个经过良好调优的实例在多年内都是正确的选择。但单个 Postgres 服务器只有一个写入者，这设定了性能上限。当你达到这个上限时，通常的解决路径各有代价：

Bigger hardware (vertical scaling). It works until the largest instance you can buy is still too small, and costs grow faster than capacity. Read replicas. They offload reads, but every write still goes through one primary. Replicas lag, so reads can be stale, and a write-heavy workload doesn’t improve at all.

更大的硬件（纵向扩展）。这种方法有效，直到你买到的最大实例仍然不够用，且成本增长速度超过了容量增长。只读副本。它们可以分担读取压力，但每次写入仍然必须通过主节点。副本存在延迟，因此读取的数据可能是过时的，且重写入的工作负载不会有任何改善。

Citus + Patroni. Patroni on its own only protects availability: a standby sits idle waiting for the primary to die, so it adds no write capacity. Citus is the piece that scales writes. It turns Postgres into a sharded cluster, with a coordinator node routing queries to worker nodes that each hold a slice of your distributed tables. Pairing the two gets you scale and availability, but you assemble and operate the result yourself: Every node group needs its own HA setup, so you end up running a Patroni-managed primary and standby for the coordinator and for each worker. That is a lot of clusters to monitor, upgrade, and back up.

Citus + Patroni。Patroni 本身仅保护可用性：备用节点处于空闲状态等待主节点故障，因此它不会增加写入容量。Citus 是实现写入扩展的部分。它将 Postgres 转变为分片集群，通过协调节点将查询路由到各自持有分布式表切片的工作节点。将两者结合可以获得扩展性和可用性，但你需要自行组装和运维：每个节点组都需要自己的高可用设置，因此你最终需要为协调器和每个工作节点运行由 Patroni 管理的主节点和备用节点。这需要监控、升级和备份大量的集群。

The coordinator is a funnel that all queries pass through, so it has to be kept highly available too. Promotion inside each group is still a window of downtime, with a risk of losing recent transactions if replication is asynchronous. Your data model changes. You choose a distribution column, and joins and foreign keys across shards only work when tables are co-located on that key or are small reference tables replicated everywhere. Adding workers means triggering a shard rebalance.

协调器是所有查询通过的漏斗，因此它也必须保持高可用。每个组内的提升过程仍然存在停机窗口，如果复制是异步的，则存在丢失近期事务的风险。你的数据模型会发生变化。你需要选择一个分布列，且跨分片的连接和外键仅在表位于该键上或作为复制到各处的参考小表时才有效。增加工作节点意味着触发分片重平衡。

Manual sharding. Splitting data across several Postgres instances by tenant or key range does scale writes, but you inherit the hard parts yourself. Cross-shard queries and transactions, foreign keys across shards, schema migrations on every shard, rebalancing when one grows faster than the others, and application code that now has to know where data lives.

手动分片。通过租户或键范围将数据拆分到多个 Postgres 实例中确实可以扩展写入，但你需要自行承担其中的困难部分。包括跨分片查询和事务、跨分片外键、每个分片上的模式迁移、当某个分片增长过快时的重平衡，以及现在必须知道数据存储位置的应用程序代码。

What teams actually want is Postgres semantics (SQL, transactions, foreign keys, the drivers and ORMs they already use) on a system that adds capacity by adding machines and survives node failures without a human in the loop.

团队真正想要的是在这样一个系统上使用 Postgres 语义（SQL、事务、外键、他们已经使用的驱动程序和 ORM）：通过增加机器来增加容量，并在无需人工干预的情况下从节点故障中恢复。

How YugabyteDB approaches it

YugabyteDB is a distributed SQL database. Its core is open source under the Apache 2.0 license, so you can self-host it, inspect it, and run it anywhere without a license fee. The key design choice is to reuse PostgreSQL’s query layer on top of a distributed storage engine. From the application’s side it speaks the Postgres wire protocol, so standard drivers and tools connect to it.

YugabyteDB 的实现方式

YugabyteDB 是一个分布式 SQL 数据库。其核心在 Apache 2.0 许可下开源，因此你可以自行托管、检查并在任何地方运行，无需支付许可费用。其关键设计选择是在分布式存储引擎之上重用 PostgreSQL 的查询层。从应用程序端来看，它使用 Postgres 通信协议，因此标准的驱动程序和工具都可以连接到它。

The architecture has a few moving parts worth understanding: Tablets (shards). Each table is automatically split into tablets, which are chunks of rows assigned by hashing the primary key (or by key range, if you choose). Tablets are the unit of distribution. You never write shard-routing logic.

该架构有几个值得理解的组件：Tablets（分片）。每个表都会自动拆分为 Tablets，这是通过对主键进行哈希（或根据你选择的键范围）分配的行块。Tablets 是分布的单位。你无需编写分片路由逻辑。

Replication with Raft. Every tablet is replicated across nodes. The replication factor (RF) says how many copies exist; with RF=3, each tablet has three. For each tablet, the replicas run a Raft consensus group, with one leader serving reads and writes and followers staying in sync. If a leader’s node dies, the followers elect a new leader in seconds, with no committed data lost.

基于 Raft 的复制。每个 Tablet 都会在节点间进行复制。复制因子（RF）决定了副本的数量；当 RF=3 时，每个 Tablet 有三个副本。对于每个 Tablet，副本运行一个 Raft 共识组，其中一个领导者负责处理读写，跟随者保持同步。如果领导者节点宕机，跟随者会在几秒钟内选出新的领导者，且不会丢失已提交的数据。

Two server processes. yb-master stores cluster metadata (which tables exist, which tablets live where) and runs the background balancer. yb-tserver stores and serves the data. In a small cluster, each node runs both. Automatic rebalancing. Add a node and the cluster moves tablets onto it in the background. Remove one and its tablets are re-replicated elsewhere.

两个服务器进程。yb-master 存储集群元数据（存在哪些表、Tablet 位于何处）并运行后台平衡器。yb-tserver 存储并提供数据。在小型集群中，每个节点都运行这两个进程。自动重平衡。增加一个节点，集群会在后台将 Tablet 迁移到该节点上。移除一个节点，其 Tablet 会被重新复制到其他地方。

Distributed transactions. ACID transactions work across tablets and nodes, which is what makes foreign keys, joins, and multi-row updates behave the way Postgres developers expect. Put together, that is horizontal scaling for writes (more nodes means more tablet leaders to spread load), high availability (Raft failover per tablet), and a familiar interface.

分布式事务。ACID 事务跨 Tablet 和节点工作，这使得外键、连接和多行更新的行为符合 Postgres 开发者的预期。综上所述，这实现了写入的横向扩展（更多节点意味着更多 Tablet 领导者来分担负载）、高可用性（每个 Tablet 的 Raft 故障转移）以及熟悉的接口。

The honest trade-offs

A distributed database isn’t free. Every write has to reach a majority of replicas, so write latency is higher than a single-node Postgres on the same hardware, especially across zones or regions. Postgres feature parity is very good but not total, so check the extensions and features you depend on. Resource needs are heavier, since each node runs a full storage engine and a Postgres layer. And the benefit only shows up when you need it. If one Postgres box comfortably handles your load, keep it.

诚实的权衡

分布式数据库并非没有代价。每次写入都必须到达大多数副本，因此写入延迟比在相同硬件上的单节点 Postgres 要高，尤其是在跨可用区或跨区域时。Postgres 的功能对等性非常好，但并非完全一致，因此请检查你所依赖的扩展和功能。资源需求更高，因为每个节点都运行完整的存储引擎和 Postgres 层。而且，只有当你需要时，这种优势才会显现。如果一台 Postgres 服务器能轻松处理你的负载，那就继续使用它。

Demo: a three-node cluster on DigitalOcean

We’ll build a three-node YugabyteDB cluster on DigitalOcean droplets, load some data, look at how it’s distributed, and then kill a node to watch the cluster keep serving.

演示：DigitalOcean 上的三节点集群

我们将会在 DigitalOcean droplets 上构建一个三节点的 YugabyteDB 集群，加载一些数据，观察其分布方式，然后关闭一个节点，观察集群如何继续提供服务。

1. Provision the droplets. Create three droplets in the same region and the same VPC, so the nodes can reach each other on private IPs. OS: Ubuntu LTS. Binaries are built against a specific glibc baseline, so check the supported operating systems list before choosing. Newer or unusual distros can crash during initialization. Size: 2 vCPU / 4 GB RAM each is comfortable. Smaller boxes can work for a throwaway demo, but memory pressure causes confusing slowness rather than clean errors. Note each droplet’s private IP (for cluster traffic) and public IP (for your SSH access).

2. 配置 droplets。在同一区域和同一 VPC 中创建三个 droplet，以便节点可以通过私有 IP 相互访问。操作系统：Ubuntu LTS。二进制文件是针对特定的 glibc 基准构建的，因此在选择前请检查支持的操作系统列表。较新或不常见的发行版可能会在初始化期间崩溃。规格：每个 2 vCPU / 4 GB RAM 是比较舒适的配置。更小的机器可以用于一次性演示，但内存压力会导致令人困惑的缓慢，而不是清晰的错误。记录每个 droplet 的私有 IP（用于集群流量）和公共 IP（用于 SSH 访问）。

3. Firewall. The database ports shouldn’t be reachable from the open internet. Apply a DigitalOcean Cloud Firewall (or ufw) that allows SSH from your IP and the following ports only within the VPC: Port Purpose 7100 yb-master RPC 9100 yb-tserver RPC (replication traffic) 7000 / 9000 Master / TServer web UIs 15433 yugabyted UI 5433 YSQL (the Postgres-compatible endpoint). If you run ufw, allow SSH before enabling it so you don’t lock yourself out.

4. 防火墙。数据库端口不应能从公共互联网访问。应用 DigitalOcean 云防火墙（或 ufw），仅允许来自你 IP 的 SSH 连接，并仅在 VPC 内允许以下端口：端口 用途 7100 yb-master RPC 9100 yb-tserver RPC（复制流量） 7000 / 9000 Master / TServer Web UI 15433 yugabyted UI 5433 YSQL（Postgres 兼容端点）。如果你运行 ufw，请在启用前允许 SSH，以免将自己锁定在系统之外。

5. Prepare each node. Run on every node. Raise fil…

6. 准备每个节点。在每个节点上运行。提高文件…