6 ms·

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

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

inferiorhuman 7y 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.
- jade12 7y ago
  Doesn't every major RDBMS support JSON out of the box these days?
  - zaarn 7y ago
    (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.
    - atwebb 7y ago
      MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.
- wtetzner 7y ago
  SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016
  - inferiorhuman 7y ago
    Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
    - crazysmoove 7y ago
      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]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
  - ioltas 7y ago
    Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.
- irrational 7y ago
  Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?
  - ken 7y ago
    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.
    - matharmin 7y ago
      While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
    - nbevans 7y ago
      And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
  - marcolussetti 7y ago
    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.
    - GordonS 7y ago
      Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
  - lefred 7y ago
    Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...
brightball 7y 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 7y 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 7y 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".
- mbreese 7y 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 7y 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.
    - ErwinSmout 7y ago
      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 ?
  - ken 7y 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.
- paulddraper 7y 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.
jtms 7y 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 :-)
- paulddraper 7y ago
  You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/
  - jtms 7y ago
    Graphile looks quite intriguing! any experience with it? Recommended?
    - Munksgaard 7y ago
      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.
    - dafrie 7y ago
      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
  - ar-jan 7y ago
    I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"
- CuriouslyC 7y 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.

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

jade12 7y ago
Doesn't every major RDBMS support JSON out of the box these days?
- zaarn 7y ago
  (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.
  - atwebb 7y ago
    MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.
wtetzner 7y ago
SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016 https://en.wikipedia.org/wiki/SQL:2016
- inferiorhuman 7y ago
  Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.
  - crazysmoove 7y ago
    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]. [0] https://modern-sql.com/ https://modern-sql.com/ [1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example (Edited for formatting.)
- ioltas 7y ago
  Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72b6460336e86ad5cafd3426af6013c7d8457367 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... The release is planned for roughly next September/October.
irrational 7y ago
Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?
- ken 7y ago
  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.
  - matharmin 7y ago
    While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.
  - nbevans 7y ago
    And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.
- marcolussetti 7y ago
  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.
  - GordonS 7y ago
    Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.
- lefred 7y ago
  Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mysql-80-document-store https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-nosql-sql-mysql https://www.slideshare.net/lefred.descamps/oracle-code-roma-...

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

mbreese 7y 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 7y 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.
  - ErwinSmout 7y ago
    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 ?
- ken 7y 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.
paulddraper 7y 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.

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

paulddraper 7y ago
You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/
- jtms 7y ago
  Graphile looks quite intriguing! any experience with it? Recommended?
  - Munksgaard 7y ago
    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.
  - dafrie 7y ago
    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
- ar-jan 7y ago
  I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"
CuriouslyC 7y 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.