Integrating Kyoto Tycoon With PostgreSQL

CloudFlare has Points of Presence (PoPs) in 23 datacenters around the world and plans to expand to many more soon. It also has a single portal, CloudFlare.com, where website owners interact with the system. This creates a giant configuration propagation problem.

Any time you log into CloudFlare.com and turn on a feature, app, or update a DNS record, you create what we refer to as a new rule. Whenever a request arrives at a PoP our DNS and web servers use these rules to determine how to respond.

When you make a new rule, you expect this rule to be in effect everywhere, right away. Furthermore, you want CloudFlare to respect creation time ordering. For example, if you turn an app on and then off, you need a guarantee that the final state is off rather than on. And just to make things more fun, at peak times CloudFlare sees thousands of these rules created per second.

To solve this problem, CloudFlare uses two technologies. The first, PostgreSQL, is a classic SQL database engine. It powers CloudFlare.com. Any time you create a rule, on the backend this logic is inserted into a row oriented table.

The second, Kyoto Tycoon or KT, is a distributed key-value store. A primary instance here in our main datacenter is replicated out to all of our PoPs. This means that any key/value pair inserted in the primary location will be readable at any place in the world. Timestamp-based replication ensures eventual consistency and guarantees ordering. In practice, we see at most around 3 seconds of delay for full propagation in normal conditions. The trick though is in moving data from PostgreSQL to Kyoto Tycoon.

This process of reconciling the PostgreSQL with KT has always been a somewhat painful process. Currently, it is accomplished with a homebrewed system of scripts that periodically read the database and perform the operations necessary to reconcile the two systems.

This method is far from optimal because it introduces artificial lag to the system as well as the additional complexity and logic necessary for reconciliation.

Thanks to PostgreSQL 9.3's new writable foreign data wrappers (FDW) though, that's all about to change.

PostgreSQL's FDW are a method of integrating external sources of data within databases.

To a PostgreSQL user, FDW data sources appear as any other database table while the logic to communicate with the external data sources and convert data into tables is handled by the FDW. Prior to version 9.3, FDW were read-only with no capacity to export data. But now, PostgreSQL can write to foreign data sources.

By making a FDW for Kyoto Tycoon, we allow data in KT to be represented as a database table. This then allows us to use database triggers to perform data synchronization with KT, both lowering latency and simplifying the logic of data synchronization.

As an added bonus, we translate PostgreSQL transactions into KT transactions.

This means that all the ACID guarantees of PostgreSQL are exported to KT, allowing you to perform transactional operations on KT using the familiar SQL commands of BEGIN, COMMIT and ROLLBACK.

We think this integration is so useful that we have decided to open source the FDW code and make it available at https://github.com/cloudflare/kt_fdw.

Usage is very simple. After installing the KT FDW simply run:

CREATE SERVER FOREIGN DATA WRAPPER kt_fdw OPTIONS (host '127.0.0.1', port '1978', timeout '-1'); (the above options are the defaults)

CREATE USER MAPPING FOR PUBLIC SERVER kt_server;

CREATE FOREIGN TABLE (key TEXT, value TEXT) SERVER ;

And now you can SELECT, INSERT, UPDATE, and DELETE from this table. Those commands will perform the corresponding operation on the KT server.

As one of the first FDWs to make use of the new writable API for PostgreSQL, we hope this code will be used by other people who wish to plug in any alternative storage engine.