← Back to context

Comment by tathougies

7 years ago

> I use Postgres in the first place because there are so many improvements available on top of SQL.

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

36 comments

tathougies

Reply
brightball  7 years ago

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

It's got a lot of stuff going on in there.

  • cagmz  7 years ago

    > 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?

ken  7 years ago

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".

  • paulddraper  7 years ago

    > 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.

  • ErwinSmout  7 years ago

    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.

  • mbreese  7 years ago

    Why should it be? Indexing is an implementation detail for each RDBMS, so it doesn’t make sense to add it to the language spec.

    • garmaine  7 years ago

      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.

      1 reply →

    • ken  7 years ago

      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.

inferiorhuman  7 years ago

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.

jtms  7 years ago

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 :-)

  • CuriouslyC  7 years ago

    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.

    • menssen  7 years ago

      > you just need to parse incoming sql to sanitize it and make sure there is no role escalation

      "Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql".

      Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.

      1 reply →

    • jtms  7 years ago

      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?

      1 reply →