Making Postgres distributed with FoundationDB

Yes, the transaction timeout and size limit might be the most obvious limits of FoundationDB. It would technically be possible to work around them by basically using Postgres’ transaction processing on top of FoundationDB, but you would then likely lose out on a ton of performance and wouldn’t really be using FoundationDB the way it was meant to be used.

But what application types would pgfdb not be for

It would definitely not be for anything that is not squarely OLTP, like a hybrid workload that mixes long running analytical queries with short transactional ones. Postgres works perfectly fine for hybrid and OLAP workloads to some degree, but scaling out that kind of workload can bottleneck the database and impact transaction processing. I recently read this nice article on how OpenAI has been battling exactly that (they seem to be pushing plain Postgres to its limits, really cool to see!): https://www.pixelstech.net/article/1747708863-openai%3A-scaling-postgresql-to-the-next-level

Sure thing! Schemas I haven’t mapped out yet, only tables and indices where the actual data is stored and queried.

The mapping is actually rather simple at the moment. It looks something like this currently (in an abstract form):

• /tables/{table_id}/{row_id} -> encoded row data
• /indices/{index_id}/{encoded_column_data}/{row_id} -> empty

Where row ID is an internal randomly generated ID for each row. Could potentially make that into the primary key in the future for even better performance. Will probably also nest indices under their respective tables by prefix for better data locality.

It uses FoundationDB’s tuple encoding for the mapping so that index reads can be done efficiently. If you have an index over an integer column column for example and do an index search for > 5, pgfdb will perform an index read for any key “larger” than /indices/{index_id}/5.

Surprisingly maybe the mapping from PG transactions to FoundationDB transaction is the easy part! The harder part is just making the querying patterns fit into the Postgres model and making the best use out of FoundationDB at the same time. Postgres architecture makes some assumptions that makes sense from a “reading pages from disk” model that don’t fit as well when the data is distributed, which takes some working around.

Noted! Would love to write a more extensive deep-dive kind of post on this eventually. I do feel I need to make a bit more progress on the project to get an even better sense for the architectural challenges, to be able to write a post detailed enough to be worth reading!

Not sure if I can offer a valuable yet concise explanation here but on a high-level, pgfdb relies on three extension points in Postgres:

• Custom table access method. pgfdb implements its own that does away with all writing to disk of table data and also WAL handling. Instead it encodes and writes new data to FDB in the format explained in my other comment. pgfdb identifies each table row with a randomly generated ID, this takes the place internally of what Postgres calls a TID that usually identifies the page and offset into that page where a row is located. Table scans are implemented simply by doing a range scan over the table’s FDB subspace and decoding each value.
• Custom index access method. pgfdb implements its own here as well and uses the FDB key format I also described in another comment. When doing an index scan, the index access method translates the query into an FDB range scan that only scans the parts of the index that fit the query filters. The index scan results in row IDs that are returned to Postgres as TIDs, which Postgres the calls the table access method to get the actual row data for. There is also an optimization here that issues the table lookups for an index scan ahead of time, to avoid sequentially waiting for individual reads to FDB when doing index scans.
• Transaction hooks. pgfdb hooks into when a Postgres transaction is committed or aborted and performs the equivalent operations on the FDB transaction. Each Postgres transaction maps 1:1 with a FDB transaction.

The key thing remaining to making Postgres truly stateless here is syncing DDL to FDB as well, which would mean you could simply spin up an all new Postgres server, point it to your FDB database, and it would be able to serve queries. You can scale out compute by adding new Postgres servers, and scale down to zero as well if you share a single FoundationDB cluster between multiple databases (what some might call a serverless model).

It depends a bit! If the extension in any way relies on adding custom index storage or similar, then it naturally wouldn’t work. Take for example pgvector that implements its own index types for approximate nearest neighbor search on vectors.

What would work though is the non-index parts of pgvector, like the custom data types and operators. One thing I’m pretty excited for is taking extensions like pgvector and keeping them as is, but implementing a FDB-backed alternative to their indexing. Postgres actually enables this because it disaggregates data types, operators and index types. That way one could get all the same syntax and ergonomics as a user of the extension, but behind the scenes the implementation would be distributed and FDB-optimized.