Subscribe to receive notifications of new posts:

Integrating Kyoto Tycoon With PostgreSQL

2013-07-08

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

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

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.

FDW FTW

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

Cloudflare's connectivity cloud protects entire corporate networks, helps customers build Internet-scale applications efficiently, accelerates any website or Internet application, wards off DDoS attacks, keeps hackers at bay, and can help you on your journey to Zero Trust.

Visit 1.1.1.1 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

Cloudflare|@cloudflare

Related posts

October 09, 2024 1:00 PM

Improving platform resilience at Cloudflare through automation

We realized that we need a way to automatically heal our platform from an operations perspective, and designed and built a workflow orchestration platform to provide these self-healing capabilities across our global network. We explore how this has helped us to reduce the impact on our customers due to operational issues, and the rich variety of similar problems it has empowered us to solve....

October 07, 2024 1:00 PM

Thermal design supporting Gen 12 hardware: cool, efficient and reliable

Great thermal solutions play a crucial role in hardware reliability and performance. Gen 12 servers have implemented an exhaustive thermal analysis to ensure optimal operations within a wide variety of temperature conditions and use cases. By implementing new design and control features for improved power efficiency on the compute nodes we also enabled the support of powerful accelerators to serve our customers....