Subscribe to receive notifications of new posts:

Integrating Kyoto Tycoon With PostgreSQL


3 min read

Postgres meets Kyoto Tycoon

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,, where website owners interact with the system. This creates a giant configuration propagation problem.

Any time you log into 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.

Replicating Rules

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 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.

Getting Rules Out of Postgres

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.

Try It Yourself

We think this integration is so useful that we have decided to open source the FDW code and make it available at

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

(host '', port '1978', timeout '-1');
(the above options are the defaults)


CREATE FOREIGN TABLE <table name> (key TEXT, value TEXT) SERVER <server name>;

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.

We protect entire corporate networks, help customers build Internet-scale applications efficiently, accelerate any website or Internet application, ward off DDoS attacks, keep hackers at bay, and can help you on your journey to Zero Trust.

Visit from any device to get started with our free app that makes your Internet faster and safer.

To learn more about our mission to help build a better Internet, start here. If you're looking for a new career direction, check out our open positions.
SQLSpeed & ReliabilityCloudflare Network

Follow on X


Related posts

September 29, 2023 1:00 PM

Cloudflare is free of CAPTCHAs; Turnstile is free for everyone

Now that we’ve eliminated CAPTCHAs at Cloudflare, we want to hasten the demise of CAPTCHAs across the internet. We’re thrilled to announce that Turnstile is generally available, and Turnstile’s ‘Managed’ mode is now completely free to everyone for unlimited use. ...