Making Postgres Distributed with FoundationDB

Making Postgres Distributed with FoundationDB(fabianlindfors.se)

45 points by emptysea 360 days ago | 17 comments

This is my project, thanks a lot for posting! If anybody happens to have questions, I'm happy to answer them

dev-ns8 359 days ago | |

Super interesting project! I have zero experience writing Postgres extensions, but I was browsing a blog post about an extension the other day. I was curious what ones workflow looked like while developing a PG extension. From the little I read, it looked like you would have to recompile postgres, including your extension, then enable it just to run it. Seems like a very long feedback loop. Was I missing something?

fabianlindfors 359 days ago | | |

Glad you like it!

You don't need to recompile it actually! It's enough to compile your extension and Postgres can then dynamically link to it, which makes for a perfectly good feedback loop. pgfdb is built with pgrx: https://github.com/pgcentralfoundation/pgrx, which is a framework for building Postgres extensions in Rust that handles all the compiling and linking for you. Highly recommend that if you want to try writing extensions and are also a fan of Rust!

amw-zero 359 days ago | |

How do table access methods work with standard PG concepts such as the shared_buffers cache? Is that irrelevant since data is stored in FDB? Also, how do the deployment semantics of FDB affect this. If I remember correctly, you typically run one FDB process per CPU on a machine. How do PG processes map to those?

fabianlindfors 358 days ago | | |

A table access method can decide itself if it wants to use the PG buffer cache, pgfdb does not so it's irrelevant in that case.

> Also, how do the deployment semantics of FDB affect this. If I remember correctly, you typically run one FDB process per CPU on a machine. How do PG processes map to those

You would run the two separately, so PG processes would run and scale separately from FDB processes. Postgres spawns a new process for each connection so you would not want to map Postgres itself 1:1 with CPUs!

atombender 359 days ago |

This is a fun project.

From what I can tell, this doesn't replace the lower level page heap storage, but instead actually provides new implementation of table and indexes through a plugin mechanism called table access methods and index access methods, respectively. This looks very similar to the virtual table mechanism in SQLite, for example, but the C API looks much nicer.

I've not paid much attention to the Postgres extension API, but I'm pleasantly surprised it's that flexible. I've been hearing for years about how Postgres pluggable engine interface isn't flexible enough to implement certain features, but it actually looks really rich. Maybe some of those improvements come from recent work by the OrioleDB people, and others like Citus who develop various alternative table engines?

fabianlindfors 358 days ago | |

> I've not paid much attention to the Postgres extension API, but I'm pleasantly surprised it's that flexible. I've been hearing for years about how Postgres pluggable engine interface isn't flexible enough to implement certain features, but it actually looks really rich. Maybe some of those improvements come from recent work by the OrioleDB people, and others like Citus who develop various alternative table engines?

I was also pleasantly surprised by this! I've actually been working on this project for more than a year now with a few false starts trying to find the "right" way to integrate with Postgres that fits well enough with FoundationDB.

Yes! There is a lot of ongoing work on Postgres extensibility and it keeps getting better. The ecosystem is really amazing. I'm for example excited about work being done by Enterprise DB to make custom index access method more generically pluggable, which would allow them to be used for primary keys amongst other things: https://www.postgresql.org/message-id/flat/E72EAA49-354D-4C2...

atombender 356 days ago | | |

I suppose that means you can't implement primary keys until the EnterpriseDB work gets merged?

amw-zero 359 days ago | |

> From what I can tell, this doesn't replace the lower level page heap storage, but instead actually provides new implementation of table and indexes

These seem contradictory. If the data is stored in FoundationDB, then it won't be stored in the filesystem as blocks, right?

atombender 359 days ago | | |

Correct, my point is that it replaces storage at a higher level.

For example, mvsqlite implements a SQLite VFS that maps page to FoundationDB keys. This means that once the VFS is in place, everything is in FDB. But this means that you have to deal with page conflicts if you want multiple writers to mount the same database, so it's a somewhat coarse-grained system.

What this project is doing allows one to represent each row (tuple) as a FDB key/value, giving you finer-grained control. But it comes at a cost of having to implement all the access methods, including index scans, in terms of FDB.

This is presumably why database metadata (DDL) isn't currently persisted, because those structures aren't normal tables.

bithavoc 359 days ago |

This is interesting, it would be like YugabyteDB but on top of FoundationDB.