The various extensions / plugins that allow for custom data types, indexes, use of multiple programming languages to write functions, ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, varieties of powerful search capabilities, etc
> ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function,
This sounds amazing. Does anyone have a link to some docs for this?
That "most" is a pretty loaded word! Last I checked, even CREATE INDEX is not part of any ANSI or ISO SQL specification. (That's why every RDBMS has such different features and syntax for this.) Good luck building a system with just "standard SQL".
> even CREATE INDEX is not part of any ANSI or ISO SQL specification
That's correct, and that's why PostgreSQL has ADD CONSTRAINT.
The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.).
* Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.
SQL has nothing to say about the layer of physical implementation, and that's where INDEX belongs (as does STOGROUP [DB2] and what have you).
That's not a bug, that's a feature. Deriving from the deliberate intent to make physical independence (which was simply nonexistent at the time the model was conceived) a reality.
It does make sense when the goal of the language spec is interoperability, and it's something that everyone has to do, in practice, no matter the platform.
Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.
Interoperability is achieved if the same query with the same inputs yields the same result. "In the same amount of time" (which is the part that indexes are aimed at) is manifestly not part of that picture, and that's by design.
And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?
That sounds backwards to me. It's only lack of standardization that forces it to be an "implementation detail". To any application that needs to be able to query data in reasonable time, it's fundamental and necessary functionality.
I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1].
SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.
If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.
(Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG.
In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.
I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL, well then I really like standard SQL :-)
I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity.
Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.
You don't have to do full statement parsing, you basically just have to do a limited parse that looks for the various ways that someone could execute a set role statement. As long as you don't let a user execute set role, you use db roles for user accounts, you have a reasonable statement timeout in place to prevent DOS, and your postgres security model is tight (the big gotcha is not to allow access to untrusted code), this approach works fine.
In terms of authorization, you can either create per user connection pools if using web sockets and log the user in directly that way (which makes things easy) or if you must use rest, use a single connection pool with a master user then use some form of token to tell the shim who to set role to before executing the query.
Thank you for posting this. I have been thinking about using something along these lines for awhile now. Next project that fits the bill I will see about building a proof of concept implementation and go from there. Have you had any exposure to any of the other projects similar to PostgREST that you have any thoughts about?
I've played with Graphile, it's not a bad choice if you are already invested in GraphQL. I'm not a GraphQL fan for a number of reasons, but it's a definitely a solid project.
I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres
I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.
Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.