4 ms·

The other founder of Splitgraph here! I've been experimenting recently with this, since we want users to be able to write to Splitgraph images efficiently witho

The other founder of Splitgraph here! I've been experimenting recently with this, since we want users to be able to write to Splitgraph images efficiently without having to turn them into PG tables and then re-snapshot them. My setup is similar to your cold-hot idea (diagram [1]), where the cold layer uses our "layered querying" FDW. A scan through it is basically a scan through a UNION of cstore_fdw files (columnar format) and we can use the object metadata to determine which objects to download/scan through. If the constraints don't match the object boundaries at all, the scan returns empty. This emulates PG partitions but more dynamically (you can change them without a full table lock). This means that we can have a view on top of the "hot" and the "cold" blocks that will query both of them, but exclude the "cold" OLAP layer most of the time. We can also redirect writes to the view to hit the "hot" OLTP layer instead using `INSTEAD OF` triggers. In my PoC, I record inserts/updates/deletes in that table and then collapse them into one action per PK using a window function, but if the table is append-only, this should be much easier. I'm not completely sure re: the locks at changeover/flush time. The idea is that we can create a new object (up to some bookmark) in one transaction, attach it to the Splitgraph table in the second transaction, then truncate the "hot" table up to the same bookmark in the final transaction. Duplicate rows can be eliminated by the view, so the final table will look consistent between transactions. Re: "using a foreign table as a partition of a local parent table", I think it's actually possible, even though we don't use it here: there's a cool setup in [3] where they use it to shard a table onto multiple worker nodes (the coordinator node has the partitioned table, each partition points to a postgres_fdw shim on the same machine that then points to the actual physical partition on the worker). We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data. Interesting to think about, though at some point it feels like reinventing Presto/Snowflake/Spark. Hope this helps! [1] https://imgur.com/a/1p394PI https://imgur.com/a/1p394PI [2] https://www.splitgraph.com/docs/large-datasets/layered-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [3] https://swarm64.com/post/scaling-elastic-postgres-cluster/ https://swarm64.com/post/scaling-elastic-postgres-cluster/

derefr 5y ago
> We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data. IMHO, if you're going to do this, I'd recommend not doing this in Postgres itself, but rather doing it at the filesystem level. It's effectively just a tiered-storage read-through cache, and filesystems have those all figured out already. You know how pgBackRest does "partial restore" (https://pgbackrest.org/user-guide.html#restore/option-db-include https://pgbackrest.org/user-guide.html#restore/option-db-inc...), by making all the heap files seem to be there, but actually the ones you don't need are sparse files ftruncate(1)'d to the right length to make PG happy? And that this works because PG only cares about DB objects it's not actively querying insofar as making sure they're there under readdir(2) with the expected metadata? Well, an object-storage FUSE filesystems, e.g. https://github.com/kahing/goofys https://github.com/kahing/goofys, would make PG just as happy, because PG could see all the right files as "being there" under readdir(2), even though the files aren't really "there", and PG would block on first fopen(2) of each file while goofys fetched the actual object to back the file. (IIRC PG might fopen(2) all its files once on startup, just to ensure it can; you can hack around this by modding the origin-object-storage filesystem library to not eagerly "push down" its fopen(2)s into object fetches — instead just returning a file-descriptor connected to a lazy promise for the object — and then have read(2) and write(2) thunk that lazy promise, such that the first real IO done against the virtual file be what ends up blocking to fetch the object.) So you could just make your pg_base dir into an overlayfs mountpoint for: • top layer: tmpfs (only necessary if you don't give temp tables their own tablespace) • middle layer: https://github.com/kahing/catfs https://github.com/kahing/catfs • bottom layer: goofys mount of the shared heap-file origin-storage bucket Note that catfs here does better than just "fetching objects and holding onto them" — it does LRU cache eviction of origin objects when your disk gets full! (Of course, this setup doesn't allow writes to the tables held under it. So maybe don't make this your default tablespace, but instead a secondary tablespace that "closed" partitions live in, while "open" partitions live in a node-local tablespace, with something like pg_partman creating new hourly tables, and then pg_cron running a session to note down the old ones and do a VACUUM FREEZE ?; ALTER TABLE ? SET TABLESPACE ?; on them to shove them into the secondary tablespace — which will write-through the catfs cache, pushing them down into object storage.)

derefr 5y ago

> We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data. IMHO, if you're going to do this, I'd recommend not doing this in Postgres itself, but rather doing it at the filesystem level. It's effectively just a tiered-storage read-through cache, and filesystems have those all figured out already. You know how pgBackRest does "partial restore" (https://pgbackrest.org/user-guide.html#restore/option-db-include https://pgbackrest.org/user-guide.html#restore/option-db-inc...), by making all the heap files seem to be there, but actually the ones you don't need are sparse files ftruncate(1)'d to the right length to make PG happy? And that this works because PG only cares about DB objects it's not actively querying insofar as making sure they're there under readdir(2) with the expected metadata? Well, an object-storage FUSE filesystems, e.g. https://github.com/kahing/goofys https://github.com/kahing/goofys, would make PG just as happy, because PG could see all the right files as "being there" under readdir(2), even though the files aren't really "there", and PG would block on first fopen(2) of each file while goofys fetched the actual object to back the file. (IIRC PG might fopen(2) all its files once on startup, just to ensure it can; you can hack around this by modding the origin-object-storage filesystem library to not eagerly "push down" its fopen(2)s into object fetches — instead just returning a file-descriptor connected to a lazy promise for the object — and then have read(2) and write(2) thunk that lazy promise, such that the first real IO done against the virtual file be what ends up blocking to fetch the object.) So you could just make your pg_base dir into an overlayfs mountpoint for: • top layer: tmpfs (only necessary if you don't give temp tables their own tablespace) • middle layer: https://github.com/kahing/catfs https://github.com/kahing/catfs • bottom layer: goofys mount of the shared heap-file origin-storage bucket Note that catfs here does better than just "fetching objects and holding onto them" — it does LRU cache eviction of origin objects when your disk gets full! (Of course, this setup doesn't allow writes to the tables held under it. So maybe don't make this your default tablespace, but instead a secondary tablespace that "closed" partitions live in, while "open" partitions live in a node-local tablespace, with something like pg_partman creating new hourly tables, and then pg_cron running a session to note down the old ones and do a VACUUM FREEZE ?; ALTER TABLE ? SET TABLESPACE ?; on them to shove them into the secondary tablespace — which will write-through the catfs cache, pushing them down into object storage.)