Hacker News Reader

Comment by PurpleRamen | Hacker News Reader

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?

View on HN

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