mildbyte

PostgREST is great and really reduces the boilerplate around building REST APIs. They recommend implementing access rules and extra business logic using PostgreSQL-native features (row level security, functions etc) but once you get your head around that, it really speeds things up!

If you're interested in playing around with a PostgREST-backed API, we run a fork of PostgREST internally at Splitgraph to generate read-only APIs for every dataset on the platform. It's OpenAPI-compatible too, so you get code and UI generators out of the box (example [0]):

    $ curl -s "https://data.splitgraph.com/splitgraph/oxcovid19/latest/-/rest/epidemiology?and=countrycode.eq.GBR,adm_area_3.eq.Oxford)&limit=1&order=date.desc"
    [{"source":"GBR_PHE","date":"2020-11-20", "country":"United Kingdom", "countrycode":"GBR", "adm_area_1":"England", "adm_area_2":"Oxfordshire", "adm_area_3":"Oxford", "tested":null, "confirmed":3079, "recovered":null, "dead":41, "hospitalised":null, "hospitalised_icu":null, "quarantined":null, "gid":["GBR.1.69.2_1"]}]

[0] https://www.splitgraph.com/splitgraph/oxcovid19/latest/-/api...

We use them in production at Splitgraph [0] to power our DDN (like a CDN, but for data). We make a PostgreSQL-compatible endpoint available to the public to query any of the tens of thousands of open datasets by referencing them as virtual tables: they're not hosted by us but we proxy to them using Postgres FDWs. When a query comes in, we intercept it and redirect it to a FDW instance that handles query translation and planning from the PG dialect to that of the backend data source.

We wrote an FDW for Socrata-powered [1] government open data portals to query the public datasets that we index in the Splitgraph catalog as a proof-of-concept. However, there are plenty of other FDWs that we're working on integrating to let people add their own backend data sources (RDS, Snowflake etc).

FDW plugin quality varies (some of them can't push down all predicates or JOINs) but it's definitely an interesting way to think about accessing data. We also added a lot of scaffolding around foreign data wrappers in our open-source tool [2] that makes it easy to add a FDW-managed data source to a PostgreSQL instance.

[0] https://www.splitgraph.com/blog/data-delivery-network-launch

[1] https://www.tylertech.com/products/socrata

[2] https://www.splitgraph.com/blog/foreign-data-wrappers

Hey George, thanks for the comment and for the good points!

We want to initially focus on the use case with lots of diverse backend datasets and ad-hoc APIs (maybe with a no-code like solution on top of a generic FDW) where performance won't be the bottleneck. If necessary, the backend data sources can perform aggregation and fast query execution. For example, you can also put Splitgraph in front of Presto (through JDBC). The value we want to provide in these cases is:

* granular access control (e.g. masking for PII columns, auditing etc)

* firewalling/query rewriting/rate limiting (for publicly accessible endpoints that proxy to internal databases that vendors want to publish more easily than through cronjobs with data dumps)

* cataloguing (so you get to discover datasets/data silos, get their metadata and query it over multiple interfaces in the same product)

We also like keeping the PG wire format in any case, as there are so many BI tools and clients that use it that it makes sense to not break that abstraction. We started with PG FDWs just because of the simplicity and the availability of FDWs, but we might swap the actual Postgres FDW layer for some faster execution in the future, if it's needed.

Behind the scenes, for Splitgraph images, we use cstore_fdw as an intermediate storage format (it's a columnar store similar to ORC with support for all PG types like PostGIS geodata). There's a potential in using this as a format for a local query/table cache on Splitgraph nodes that we intend to deploy around the world for low-latency read-only query execution.

I think we fixed this now, was a bug in how we did our client introspection query rewrites (wasn't actually DBeaver-specific).

Could you try again when you have the chance?

Yeah, sorry, got too excited! It will return JSON by default and CSV with Accept: text/csv:

    $ curl -sH "Accept: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations?select=lab_report_date,cases_total,deaths_total | head -n5
    lab_report_date,cases_total,deaths_total
    2020-03-01,0,0
    2020-03-02,0,0
    2020-03-03,0,0
    2020-03-04,0,0

Currently we control and set up all the FDWs (well, an orchestration layer does it on the fly as the query comes in and routes the query to the correct schema with foreign tables).

You can also run a Splitgraph engine locally and add your own FDWs to it. We have a lot of scaffolding around FDWs to make their instantiation much more simple and even wrote a blog post [0] about adding a custom FDW to Splitgraph.

However, in the future we'll be adding the ability to add your own backend data sources to Splitgraph that it can proxy to (whether as a private dataset on the public Splitgraph instance or as a "data virtualization" layer when you have an in-house Splitgraph deployment).

The cool thing about this is that this can be a single gateway to all your data silos (Snowflake, third-party SaaS, public datasets) that can handle federated query execution, data discovery and access control (e.g. firewalling queries to sensitive columns even if the backend data source doesn't support this level of granularity).

[0] https://www.splitgraph.com/blog/foreign-data-wrappers

We use pglast [0]: it's basically a Python wrapper around Postgres's query parsing code.

[0] https://github.com/lelit/pglast

The Splitgraph core code on GitHub [0], around which we've built the DDN, is all about managing "data images" which are basically snapshots of PostgreSQL schemata. You can build them with a format similar to Dockerfiles as well as do a "checkout" into a local instance of Splitgraph (which you can connect to with any PG client) -- this enables change tracking and delta compression too.

Behind the scenes, we store them as cstore_fdw [2] files which is a columnar storage format that helps with analytical queries.

[0] https://github.com/splitgraph/splitgraph/

[1] https://splitgraph.com/docs/concepts/images

[2] https://github.com/citusdata/cstore_fdw

Absolutely, having ability to download the actual data and keep it is always going to be important. We want to facilitate access to data and think it should be available from the source. But, there will inevitably be fragmentation, so it's valuable to have a service available to catalog and aggregate it and make it available over a single protocol.

For what it's worth, we run PostgREST [0] on top of the DDN, so you can get your query results in JSON and CSV files. For example:

    $ curl -sSH "Content-Type: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations | wc -l

    172

It's limited to 10k rows but we might have an ability in the future to "order" a CSV dump asynchronously and place in a destination (like an S3 bucket) of choice.

[0] https://postgrest.org/en/latest/

We currently limit all queries to 30s of execution and 10000 rows returned (by adding a `LIMIT` clause to queries that don't have it). We also have some mechanisms like query result caching and rate limiting for better QoS. One of our directions is building a basically CDN for databases, so it's good to figure these things out as early as possible.

Co-founder here. The 63-char limit still applies (we didn't recompile Postgres!) but we have some code in front, embedded in a layer of PgBouncers, that intercepts the query, parses it and rewrites it into a shorter dataset ID hash that we then "mount" on the database on the fly using Postgres FDWs before forwarding it.

We also use this to drop unwanted queries and rewrite clients' introspection queries (e.g. information_schema.tables) to give them a list of featured datasets instead of normal Postgres schema names.

We hit it in real time for most datasets. A lot of government open data portals are powered by Socrata [0] and we wrote a foreign data wrapper that translates the query into their proprietary query language. If it's a dataset that we host ourselves, there's no problem with the upstream being unavailable.

We have a cache too, but it currently just hashes the query's AST. In the future, we'll be looking at caching actual tables as well -- basically, we'd store some regions of the remote table and selectively pull data from upstream when a query comes in to fill out our view of what the remote table looks like.

[0] https://www.splitgraph.com/docs/ingesting-data/socrata

Do you mean to run or to buy?

To run: this is very lean (the whole stack, including our public website, our REST API etc is currently running on a ~60EUR/pcm Scaleway instance). This is because for most datasets we proxy queries to upstream government data portals (there's a few datasets that we host ourselves). So the only cost is compute and storage costs for a cache of frequent queries.

To buy: we're currently developing a self-hostable deployable version of this, except it will be an internal proxy that forwards queries to your data warehouse, third-party SaaS etc, with extra services on top like access control/caching/scheduled queries. We aren't selling it yet, but we do want to work closely with a few potential clients to prioritize feature development. You can read more about our plans at [0] if you're interested!

[0] https://www.splitgraph.com/about/company/private-cloud-beta

Thanks for the report!

Our error reporting could definitely be more informative here. I've just looked at the logs and in this case the problem is that the upstream government data portal (https://data.brla.gov/) is temporarily unavailable.

It varies depending on how the user chooses to structure storage (we're flexible with that) and what mode of querying they use. We have a more in-depth explanation and some benchmarks in an IPython notebook at [1].

We store Splitgraph "image" (schema snapshot) metadata in PostgreSQL itself and each image has a timestamp, so you could create a PG index on that to quickly get to an image valid at a certain time.

Each image consists of tables and each table is a set of possibly overlapping objects, or "chunks". If two chunks have a row with the same PK, the row from the latter will take precedence. Within these constraints, you can store table versions however you want -- e.g. as a big "base" chunk and multiple deltas (least storage, slowest querying) or as a multiple big chunks (faster querying, more storage).

You can query tables in two ways. Firstly, you can perform a "checkout". Like Git, this replays changes to a table in the staging area and turns it into a normal PostgreSQL table with audit triggers. You get same read performance (and can create whatever PG indexes you want to speed it up). Write performance is 2x slower than normal PostgreSQL since every change has to be mirrored by the audit trigger. When you "commit" the table (a Splitgraph commit, not the Postgres commit), we grab those changes and package them into a new chunk. In this case, you have to pay the initial checkout cost.

You can also query tables without checking them out (we call this "layered querying" [2]). We implemented this through a read-only foreign data wrapper, so all PG clients still support it. In layered querying, we find the chunks that the query requires (using bloom filters and other metadata), direct the query to those and assemble the result. The cool thing about this is you don't have to have the whole table history local to your machine: you can store some chunks on S3 and Splitgraph will download them behind the scenes as required, without interrupting the client. Especially for large tables, this can be faster than PostgreSQL itself, since we are backed by a columnar store [3].

[1] https://www.splitgraph.com/docs/getting-started/frequently-a...

[2] https://www.splitgraph.com/docs/large-datasets/layered-query...

[3] https://www.splitgraph.com/docs/concepts/objects

Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions.

[0] https://www.splitgraph.com/product/data-lifecycle/research

Splitgraph co-founder (and post author) here. Most PostgreSQL clients don't treat foreign tables any differently than real ones, but note that things like FK constraints or triggers will have to be resolved on the remote server (your app essentially talks to an adapter that rewrites queries and forwards them to the remote database). This might not work that well as a scaling/reliability strategy (since you're still sending queries to one central database) though.

But this is actually one of the cool use cases we built Splitgraph for: you can mount a bunch of remote databases (doesn't have to be PG, can be Mongo/MySQL etc), Splitgraph images and even remote datasets like Socrata[1] into a single workspace and run e.g. JOINs between them. We optimize for the OLAP (read-only) use case and have a special FDW for querying remote Splitgraph images (we call this layered querying [0]). It downloads required regions of the table in the background, completely seamlessly to the client application. So you can spin up a lightweight Splitgraph engine at the edge and point a PostgreSQL client to it. This will let you satisfy read-only queries to huge remote datasets with a small local cache.

Re: compatibility, we have tested this setup with various analytics software and PostgreSQL clients like DBeaver/Metabase/dbt[2] and it works pretty well.

[0] https://www.splitgraph.com/docs/large-datasets/layered-query...

[1] https://www.splitgraph.com/blog/40k-sql-datasets

[2] https://www.splitgraph.com/product/splitgraph/integrations

There's some query planner tweaks you can use to speed up JOINs with FDWs [0]. In layered querying [1], we had an issue with the planner choosing nested loop joins (which essentially run as multiple small single-row fetches) which tank performance if starting a scan has a large latency overhead. This can happen if the FDW underreports its startup cost.

If you use `SET enable_nestloop=off`, this will disable them for that session and use alternative strategies (like hash or merge join) which might be faster.

[0] https://www.postgresql.org/docs/current/runtime-config-query...

[1] https://www.splitgraph.com/docs/large-datasets/layered-query...

Splitgraph co-founder here. cstore_fdw is great (we even use it in Splitgraph to store data [0])! It's not as fast as purpose-built columnar stores like MonetDB. However, it plugs seamlessly into PostgreSQL and supports all types, even those added via extensions. Read performance for OLAP workloads like aggregations is better than PostgreSQL [1] and it has a much smaller IO load and disk footprint (you can get long runs of similar values in column-oriented storage, which compress better). As a nice bonus, you can simply swap cstore_fdw files in and out of the database without having to "load" them into PostgreSQL. We use this idea too to enable data sharing.

[0] https://www.splitgraph.com/docs/concepts/objects

[1] https://tech.marksblogg.com/billion-nyc-taxi-rides-postgresq...

You always have the latency/bandwidth overhead from moving queries/data between instances, but FDW performance can be surprisingly fast. There's a performance-FDW complexity spectrum and you can choose a point on it that's applicable to your use case.

At its simplest, the FDW can just return all tuples from the remote database without filtering them (letting the local DB run filtering). But more advanced FDWs like postgres_fdw[0] can push down qualifiers and joins to the remote database. postgres_fdw even runs EXPLAIN on the remote instance and parses its output -- essentially letting the local and the foreign query planners collaborate on execution.

[0] https://www.postgresql.org/docs/current/postgres-fdw.html#id...

Splitgraph co-founder (and post author!) here. By far the biggest problem with foreign data wrappers is that you're still forced into PostgreSQL's format of treating and returning each tuple separately. There's some research being done in PostgreSQL [0] with pluggable storage formats as an alternative to using FDWs for querying. Also, Citus had a prototype that would hook into the query planner to vectorize cstore_fdw aggregations [1]: this is also promising for getting around some FDW restrictions.

FDWs aren't really that layperson-friendly: to set one up, you need to run a lot of SQL boilerplate like CREATE FOREIGN SERVER, CREATE USER MAPPING and CREATE FOREIGN TABLE. We wanted to make them more accessible through our sgr mount [2] command and snapshottable via Splitfiles (e.g. [3]).

The point about data types changing is also a good one. Where the foreign data wrapper doesn't implement IMPORT FOREIGN SCHEMA [4] through introspection on the foreign database side, you have to enumerate all columns and types in CREATE FOREIGN TABLE. If a column on the remote table goes away, the FDW might still query it and cause runtime errors.

[0] https://wiki.postgresql.org/wiki/Future_of_storage

[1] https://github.com/citusdata/postgres_vectorization_test

[2] https://www.splitgraph.com/docs/sgr/data-import-export/mount

[3] https://www.splitgraph.com/docs/ingesting-data/socrata#split...

[4] https://www.postgresql.org/docs/current/sql-importforeignsch...

That does sound very ambitious for now! I discussed below that we're focused on the OLAP use case (manage actual data, not DDL around it), so triggers, indexes and functions that you create won't be stored in the Splitgraph image you'll make (a Splitgraph image is not a full database dump).

For things like schema migrations, PostgreSQL itself has transactional DDL: column deletions/additions can be wrapped in a transaction, so you can ROLLBACK if your migration fails. This might be more appropriate for your use case?

(Note that you can still add DDL commands to a Splitgraph table after you check it out, since at that point it's just a Postgres table. In theory it would be possible to track DDL changes with some other mechanism, and apply them after loading a version of data)

There aren't many places where Splitgraph intersects with Dolt. Dolt aims to build a database from the ground up to have Git semantics and a real commit graph, whereas Splitgraph works on top of an existing RDBMS (PostgreSQL) and performs its operations by manipulating database tables. Here's a quick overview of differences where we do intersect.

With data versioning, we cherry-picked (no pun intended!) only a few concepts/commands from Git that we think are applicable. In particular, like Tim mentioned, we don't support cell-level merges. However, we offer a higher level DSL (Splitfiles[-1]) to collaborate on data.

After a Splitgraph image is checked-out, it's just a set of ordinary tables, so you get PostgreSQL feature parity and read-write performance out of the box. That means we're compatible with any existing PostgreSQL clients (DataGrip, pgcli, pgAdmin, DBeaver...), tools (Metabase, dbt...) and extensions, including those that define custom types (e.g. PostGIS for geospatial data)[0].

We also offer a way of querying Splitgraph images without checking them out[1]. This lets it download required table regions on the fly (IIRC with Dolt, you have to fully clone the whole dataset and all of its history before querying it) and in a lot of cases can be faster than PostgreSQL itself[2]. This is also completely transparent to and compatible with existing clients.

Splitgraph is decentralized and can treat any instance as a remote and push data there, with authorization provided by PostgreSQL, so you can use methods like LDAP/RADIUS/Kerberos to control access. It looks like you can spin up a Dolt remote with [3] but it's not very well documented so I can't comment on it.

Finally, we offer a lot of features beyond data versioning (reproducible dataset builds with provenance tracking, mounting other databases, automatically generated REST API for all datasets on Splitgraph Cloud etc).

When I tried it out a couple of months ago, Dolt's MySQL server didn't work with mysql_fdw. But their MySQL compatibility is progressing at an impressive pace and if it works now, you'll be able to query Dolt datasets directly from Splitgraph (by running sgr mount mysql[4]) and use them in Splitfiles. In the meantime, you can import data from Dolt into Splitgraph by using their dolt-to-pg adapter.

[-1] https://www.splitgraph.com/docs/concepts/splitfiles

[0] https://www.splitgraph.com/product/splitgraph/integrations

[1] https://www.splitgraph.com/docs/large-datasets/layered-query...

[2] https://github.com/splitgraph/splitgraph/blob/master/example...

[3] https://github.com/liquidata-inc/dolt/tree/master/go/utils/r...

[4] https://www.splitgraph.com/docs/ingesting-data/foreign-data-...

Thanks, glad you like it!

Theoretically, yes, you can use Splitgraph as a PostgreSQL replication client and then occasionally run a commit to produce a new delta. But we're currently focused on the OLAP use case and so Splitgraph images don't yet support storing things like indexes, views, triggers, stored procedures etc.

There indeed was a 4-hour shift that I somehow completely ignored when labelling my axis. I've fixed it now, thanks!

Possibly: it doesn't currently support anonymous public notes, but that would be easy to add.

Thanks! That was actually me tinkering around with the post timestamp (it was about 6:30pm UTC when I had posted it privately to preview and I bumped it a bit when I published it). All times should be in UTC: I realised there's no good way to find the user agent's timezone, so mostly am using relative times throughout.

Thanks! My background is in slower trading, and on the tech side, so this was quite different for me.

No really good reason to use Scala, I thought the speed would help with market making + having static typing did catch some bugs before I ran it. OTOH I can see the benefit of running the same codebase for backtest or live trading.