3 ms·

We are building a solution for this problem at Splitgraph [0] – it sounds like we could probably help with your use case, assuming this is for analytical (OLAP

• derefr 5y ago
  We're already sharding our datasets onto separate PG nodes using FDWs (mostly due to us running PG on GCP, and GCE VMs having inherent vertical scaling limitations on node-local storage, which we rely 100% on for our workloads.) Also, our "datasets" are all live data. They aren't modified by the users querying our API, but they are constantly appended to (and I mean constantly, i.e. every few milliseconds.) For us, PG is in our stack at this point because of its hybrid-OLAP nature: it can do realtime, row-at-a-time ingestion of data without degradation, like an OLTP store / time-series DB; but it can then perform intensive OLAP workloads against that up-to-the-moment data, involving joins, CTEs, partial computed-expression indices, etc. (The use-case for this sort of mixed workload? Think "realtime monitoring/alerting on custom-per-user financial Business-Intelligence queries from a common financial transaction stream." We can't pre-denormalize the data, because each client wants something different. Instead, we need a normalized representation with tons of indices that serves all potential queries equally well, in roughly real time.) For non-realtime analysis of "data at rest" (i.e. data that can be ingested in at-most-hourly batches), we can just use Snowflake. We already do, for logs and other things. To be honest, our fondest dream would be to have a two-tiered DB setup: 1. a cold layer, where we get the SQL semantics of Postgres's querying engine, but where the storage engine is similar to those of scale-out DW services like Snowflake, with elastic per-workload compute warehouse nodes fetching compressed-columnar data from object storage into local per-worker caches (it sounds like this is similar to what you're building?) 2. a hot layer, using traditional Postgres storage, which serves as a sort of writeback cache for the cold layer: • where all INSERTs hit the hot layer and then get async-batched to the cold layer; • where the hot layer keeps its own indices to enable fancy OLAP querying of the hot data; • where those queries see the cold data as "part of" the hot data, in a UNION ALL sense (probably through a FDW); • where those queries will constraint-exclude the cold data (and thus trigger no workload on the cold layer) if the cold data isn't relevant to resolving the query — like PG11 partition constraint-exclusion. AFAIK, you can't currently use a foreign table as a partition of a local parent table, so that'd be the first thing needing to be solved there. The next problem after that would be resolving the need for an AccessExclusiveLock to modify the range constraint on each of the parititons, since 1. the split-point between "historical" and "timely" data would be sliding forward every hour-or-so, but 2. the table would be just saturated with long-running SELECTs, so much so that it would never get a moment to lock itself to make a modification like that. (Really, the magic wand there would be to allow partitions to have multiple parents and for "a partition" — the metadata that contains the list/range/hash constraint — to be a separate DB object from the DB table it refers to, where multiple "partitions" can reference one table-with-the-partition's-data-in-it, as long as the data meets the union of all their constraints. With those abstractions, you could build a new parent table that also references the old child tables through new partition metadata, and then just do an atomic swap of the parent tables' names when you're ready, where old queries would go on using the tables they had already dereferenced to their OIDs, and new queries would start using the new tables. Then queue up an effectively-async DROP TABLE on the old parent table, that would resolve when the DB runs out of old queries locking it open.)
  • mildbyte 5y ago
    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/
    • 5y ago