5 ms·

postgres has a jsonb datatype that works pretty much like mongodb. https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ https://blog.codesh

postgres has a jsonb datatype that works pretty much like mongodb. https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ https://blog.codeship.com/unleash-the-power-of-storing-json-...

willvarfar 8y ago
And other regular RDBMS like MariaDB and MySQL have much the same functionality. I store JSON in RDBMS all the time.
- sandGorgon 8y ago
  interesting - i never knew that. I use postgresql, so dont have first hand knowledge, but i saw this https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-quick-comparison.html https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-qu... > JSON support: PostgreSQL supports JSON and JSONB while MariaDB doesn’t. It supports an alias for JSON instead, which is a LONGTEXT column. Even the proposed json column, is not indexed - postgresql jsonb is indexed and queries fast.
  - willvarfar 8y ago
    Yeah MariaDB got 'dynamic columns' first, which iirc supported key values but not arrays, and then MySQL added a more complete 'json' data type, so the exact functionality differs by flavour and version. The postgres support is simply nicer to use and more powerful, e.g. the indexing. But if your RDBMS is any fairly-recent version of MariaDB or MySQL, you can use JSON too. Of the top of my head, the thing I find most griping about the MySQL syntax is you have to do thing->>"$.key" instead of postgres-style thing->>'key' Whereas what I really want to be able to do is thing.key But most of the consumers of these modern 'dynamic columns' are apps and then it all really matters less if you don't spend all your days at the sql prompt writing stuff by hand.
    - philliphaydon 8y ago
      Didn't PostgreSQL get hstore before MariaDB got dynamic columns?
      - willvarfar 8y ago
        Yeah I seem what I wrote could be ambiguous, but in that first para I was meaning to compare MariaDB and MySQL, not claim that MariaDB beat PostgreSQL.
  - johannes1234321 8y ago
    Ntoe that MySQL's `JSON` type is using an efficient binary storage scheme which allows fast lookups etc. https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json.html My favorite MySQL JSON feature probably is JSON_TABLE which converts a JSON document on the fly into a table which can hen be processed using SQL features. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-table-functions... (Disclaimer: I work for Oracle's MySQL Engineering team)
PurpleRamen 8y ago
But will it perform as good as a dedicated nosql-db? Will the abilitys match? The querys? There usually is a tradeoff when something is forced into something alien. Is this the case here too?
- amarraja 8y ago
  > There usually is a tradeoff when something is forced into something alien. Exactly! My thoughts whenever I see people using nosql databases.
- raphaelj 8y ago
  IIRC, Postgres use a key-value store internally, so it does not really matters if you store records (i.e. tables) or objects (i.e. JSON documents). There was an article on HN a couple of months ago that was comparing Mongo and PgSQL performance, and PgSQL ended up being as fast if not faster for most use cases.
  - anarazel 8y ago
    I don't see how you'd describe postgres validly as a key-value store at the storage level / internally. It's a pretty traditional row-store (without support for index oriented tables), i.e. all columns of a table are stored together. At the storage level there's no designation of what the key of a row is. Of course you can have primary keys, but that's a higher layer concern.
    - twic 8y ago
      Aren't tables a bit like key-value stores where the key is an object ID and the value is the column values? My understanding is very vague, but i think that came out of PostgreSQL's heritage as an "object-relational" database, even if it's vestigial today.
      - taffer 8y ago
        Since 8.1 PostgreSQL does not work this way anymore. The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.[1] Besides, "object-relational" only means that it supports custom data types and inheritance, and at least custom data types are still a core feature of PostgreSQL. [1] https://www.postgresql.org/docs/8.1/datatype-oid.html https://www.postgresql.org/docs/8.1/datatype-oid.html

willvarfar 8y ago

And other regular RDBMS like MariaDB and MySQL have much the same functionality. I store JSON in RDBMS all the time.

sandGorgon 8y ago
interesting - i never knew that. I use postgresql, so dont have first hand knowledge, but i saw this https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-quick-comparison.html https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-qu... > JSON support: PostgreSQL supports JSON and JSONB while MariaDB doesn’t. It supports an alias for JSON instead, which is a LONGTEXT column. Even the proposed json column, is not indexed - postgresql jsonb is indexed and queries fast.
- willvarfar 8y ago
  Yeah MariaDB got 'dynamic columns' first, which iirc supported key values but not arrays, and then MySQL added a more complete 'json' data type, so the exact functionality differs by flavour and version. The postgres support is simply nicer to use and more powerful, e.g. the indexing. But if your RDBMS is any fairly-recent version of MariaDB or MySQL, you can use JSON too. Of the top of my head, the thing I find most griping about the MySQL syntax is you have to do thing->>"$.key" instead of postgres-style thing->>'key' Whereas what I really want to be able to do is thing.key But most of the consumers of these modern 'dynamic columns' are apps and then it all really matters less if you don't spend all your days at the sql prompt writing stuff by hand.
  - philliphaydon 8y ago
    Didn't PostgreSQL get hstore before MariaDB got dynamic columns?
    - willvarfar 8y ago
      Yeah I seem what I wrote could be ambiguous, but in that first para I was meaning to compare MariaDB and MySQL, not claim that MariaDB beat PostgreSQL.
- johannes1234321 8y ago
  Ntoe that MySQL's `JSON` type is using an efficient binary storage scheme which allows fast lookups etc. https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json.html My favorite MySQL JSON feature probably is JSON_TABLE which converts a JSON document on the fly into a table which can hen be processed using SQL features. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-table-functions... (Disclaimer: I work for Oracle's MySQL Engineering team)

PurpleRamen 8y ago

But will it perform as good as a dedicated nosql-db? Will the abilitys match? The querys? There usually is a tradeoff when something is forced into something alien. Is this the case here too?

amarraja 8y ago
> There usually is a tradeoff when something is forced into something alien. Exactly! My thoughts whenever I see people using nosql databases.
raphaelj 8y ago
IIRC, Postgres use a key-value store internally, so it does not really matters if you store records (i.e. tables) or objects (i.e. JSON documents). There was an article on HN a couple of months ago that was comparing Mongo and PgSQL performance, and PgSQL ended up being as fast if not faster for most use cases.
- anarazel 8y ago
  I don't see how you'd describe postgres validly as a key-value store at the storage level / internally. It's a pretty traditional row-store (without support for index oriented tables), i.e. all columns of a table are stored together. At the storage level there's no designation of what the key of a row is. Of course you can have primary keys, but that's a higher layer concern.
  - twic 8y ago
    Aren't tables a bit like key-value stores where the key is an object ID and the value is the column values? My understanding is very vague, but i think that came out of PostgreSQL's heritage as an "object-relational" database, even if it's vestigial today.
    - taffer 8y ago
      Since 8.1 PostgreSQL does not work this way anymore. The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.[1] Besides, "object-relational" only means that it supports custom data types and inheritance, and at least custom data types are still a core feature of PostgreSQL. [1] https://www.postgresql.org/docs/8.1/datatype-oid.html https://www.postgresql.org/docs/8.1/datatype-oid.html