5 ms·

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 a

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
    - anarazel 8y ago
      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).
nailer 8y ago
Can I make queries into nested keys of those objects?
- detaro 8y ago
  yes

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
  - anarazel 8y ago
    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).

nailer 8y ago

Can I make queries into nested keys of those objects?

detaro 8y ago
yes