The world of PostgreSQL wire compatibility

The world of PostgreSQL wire compatibility
datastation.multiprocess.io

- YugabyteDB is currently on Postgres 11.2 with plans to keep upgrading.

- Cockroach is not pinned to any Postgres version so its compatibility is reliant on their implementation. Not a bad thing, just different than YB.

- Like you said Timescale is in lockstep with latest Postgres (14 at the time of this comment) https://blog.timescale.com/blog/massive-scale-for-time-serie...

- Materialize is not really focused on scaling because that is not really what it is for, https://materialize.com/blog-roadmap/

- I might consider moving Spanner to the Somewhat compatible category. Lack of stored procedures and triggers makes it not a drop in replacement for those moving from Oracle/MS SQL https://cloud.google.com/spanner/docs/reference/postgresql/o...

- For native Postgres scaling strategies, we could call attention to Crunchy Data or Percona

Very nice to lay this out and I pretty much agree with the labels and categories on here. I would be you will get some people reaching out since Postgres compatibility is something these vendors compete on.

Just to note, MS have long published the details of their wire protocol, TDS - https://docs.microsoft.com/en-us/openspecs/windows_protocols...

This project is an interesting one: https://github.com/dolthub/go-mysql-server that provides a MySQL interface (wire and SQL) to arbitrary "backends" implemented in go.

It's really interesting how compatibility with existing protocols has become an important feature of new databases - there's so much existing tooling that already speaks postgres (or mysql), being able to leverage that is a huge advantage IMO

We’ve been based on Postgres from the beginning, and although the backend is a bit more complex at this point, we’ve kept the wire protocol intact. We’re also heavily invested in FDWs, not only for federated queries (e.g. querying data at Snowflake – btw, you might enjoy our blog post on achieving a 100x speedup with aggregation pushdown [1]), but also for queries on warehoused data stored as Splitgraph images.

By keeping Postgres compatibility as our guiding constraint, we’ve been able to build a lot of functionality on top of just a few simple abstractions. The result is something akin to a magic Postgres database – you can connect dozens of live sources to it using FDW plugins, or you can ingest from hundreds of data sources using Airbyte connectors, ultimately storing the data as immutable Splitgraph images in object storage. But as far as your standard Postgres client is concerned, you’re just connected to a normal database with 40k tables on it.

As for the wire protocol, our implementation is heavily reliant on (a forked version of) PgBouncer. Basically, a query arrives, we parse it for references to tables (which look like Docker image tags), and the proxy layer performs whatever orchestration is necessary to satisfy the query. That could mean instantiating a foreign server to a saved connection, loading some data from object storage, or even lazily loading only the requisite data (we call this “layered querying” since it’s implemented similarly to AUFS). In the future, it could also mean delegating the query to a more specialized engine like Presto.

Point is, by keeping the frontend intact, we’re able to retain compatibility with all Postgres clients, but we’re free to implement the backend in more scalable or domain-specific ways. For example, we’re able to horizontally scale our query capacity by simply adding more “cache nodes” that perform the layered querying.

We are definitely all-in on the Postgres wire protocol, and all the ecosystem compatibility that comes along with it. You can read our blog for more in-depth discussions of this, but I don’t want to spam too many links here. :)

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

[1] https://www.splitgraph.com/blog/postgresql-fdw-aggregation-p...