Hacker News Reader

Hacker News

Comment by sandGorgon | Hacker News Reader

Parent Full thread

sandGorgon

postgres has a jsonb datatype that works pretty much like mongodb.

https://blog.codeship.com/unleash-the-power-of-storing-json-...

View on HN

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

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

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.

Didn't PostgreSQL get hstore before MariaDB got dynamic columns?

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.

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

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

(Disclaimer: I work for Oracle's MySQL Engineering team)

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?

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.

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.

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.

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

And in PG 12 we've removed the magic oid columns (as in WITH OIDS), although you can still create one manually (but there'll not be any values assigned automatically).

Can I make queries into nested keys of those objects?

yes

> There usually is a tradeoff when something is forced into something alien.

Exactly! My thoughts whenever I see people using nosql databases.

Reply on news.ycombinator.com