4 ms·

JSON in Postgres and Node

68 points by JohnDotAwesome 14y ago

semihandy 14y ago
Why would you want this over a NoSQL store? How does Postgres even index an hstore?
- deleted 14y ago
  [deleted]
- JohnDotAwesome 14y ago
  Depends on your needs really. The querying capability in Postgres is so much nicer than NoSql DB's and generally, or at least is the case with Mongo, you don't get transactions.
- joevandyk 14y ago
  You can use gist indexes to index hstore's.
- pygy_ 14y ago
  hstore supports indexing out of the box. http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html You can also create custom indexing functions using the PL/$language extensions. See here for some benchmarks comparing Mongo to several Postgresql options: https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fo... MongoDB is not that fast.
- jeffdavis 14y ago
  "Why would you want this over a NoSQL store?" If you can use a more general-purpose system like Postgres and get everything you need, then that's better than relying on special-purpose NoSQL systesm. The better question to ask is: why use a NoSQL system when Postgres works just fine and is useful in more situations? The history of databases is a history of absorbing special-purpose systems into general-purpose SQL systems. XML databases were once a major topic (albeit misguided); now it's just a feature. Same with Columnar storage, or OO databases, or geospatial (postgres is a leader in geospatial, as well). Because data integration is so incredibly valuable, it pushes strongly toward general-purpose systems and people dislike one-off special-purpose databases unless they deliver a huge amount of additional value.
- jeffdavis 14y ago
  "How does Postgres even index an hstore?" It allows normal btree indexing of, for example, the values for some given key by using functional and/or partial indexes. It also allows indexing of "contains", "contains key", "contains all of these keys" and "contains some of these keys" by using GiST and GIN indexes.
- scubaguy 14y ago
  Personally, I'd like the ability to make a transactional update to records in different tables.
  - JohnDotAwesome 14y ago
    THIS. Exactly why Mongo fails (though I love mongo). No freakin' transactions!
film42 14y ago
Wouldn't it be a lot nicer to just use a schema and serializer? I know that's not really the point of this article, but if you're trying to get a SQL db to operate like a NoSQl store, it feels like a lot of hand-waving.
- joevandyk 14y ago
  It's really handy to get a json graph out of the database, even if the data is stored in normalized tables. select order_details(53647); Can return an complex graph of line items, addresses, shipments, etc. No need for ORMs.
- JohnDotAwesome 14y ago
  It really depends on the data structure. If you've got something that can have an unknown amount of variables in it and you're already serving up JSON, then I think using JSON is the way to go. Another use-case: We've got hundreds of clients that send health statuses for a ton of different metrics every 10 minutes. Stuff like Wifi strength, exceptions caught/uncaught, various errors and crash reports, blah blah. Anyway, we need a flexible store for all of this stuff because we're always adding more metrics. Whatever the clients send as their request body gets added as a JSON object. We also want to dynamically display all of these metrics. We can literally grab the data as JSON and make the keys table column headers in an html view. Adding new metrics can automatically be reflected in both the database and in our html views. We can query against new fields without changing schemas or business logic.
  - film42 14y ago
    If it were up to me, I would create a schema for each metric and then if you need all on one page, create some nice /client/client_id/metrics/all route. Although if you're dealing with metrics, you probably want to have a good defined model of your data (schema), because statisticians want to know what they have to work with, and saying, "bunch 'o JSON" is not acceptable. Plus there's things like null values to deal with. Yuk. For my startup, we find the common denominator of social media data. For example, a tweet is very unique so it's itself. But then there's more generic types like photos. So then we look at photo services like flickr and instagram. We then evaluate the data available and look for common denominators again to hone in on as much similar data as possible. The result is a model of photo data from a wide range of sources, but are now unified. We could have just used instagram json and flickr json and tossed in the db, but because we defined a schema/ models to get data from, we can sleep soundly knowing the data we send to the frontend is fast and always valid.
hbbio 14y ago
In related news (here: https://news.ycombinator.com/item?id=5589593 https://news.ycombinator.com/item?id=5589593 ), the Opa framework which is based on Node.js just released support for both MongoDB and Postgres from the same application source.
andrewguenther 14y ago
Actually, Postgres does have a JSON datatype. The only extra thing it does is ensure the data is valid JSON, but it is still technically incorrect to say that there is no JSON type. http://www.postgresql.org/docs/devel/static/datatype-json.html http://www.postgresql.org/docs/devel/static/datatype-json.ht...
- JohnDotAwesome 14y ago
  Dang. Yeah - Definitely should have said there's not JSON type known to Node PG, which is actually incorrect as of this morning: https://github.com/brianc/node-postgres/pull/239 https://github.com/brianc/node-postgres/pull/239 Thanks for pointing out the error!
- jvm 14y ago
  > The only extra thing it does is ensure the data is valid JSON, Even that's actually not quite fair, it has special syntax for queries within the JSON (->> etc), and a bunch of functions: http://www.postgresql.org/docs/devel/static/functions-json.html http://www.postgresql.org/docs/devel/static/functions-json.h...
  - andrewguenther 14y ago
    These functions can be run on datatypes other than JSON, but yes, you are correct.
audreyt 13y ago
The plv8x project ( https://github.com/clkao/plv8x https://github.com/clkao/plv8x ) adds convenient JSON and JS/LiveScript selector operators: https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-database https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-da... covers the basic usage and the motivation, as well as the ongoing PgREST work ( https://github.com/clkao/pgrest https://github.com/clkao/pgrest ) that builds a MongoLab REST API server that resides within the database, so they can be queried over a pg connection as well as through HTTP.

Why would you want this over a NoSQL store? How does Postgres even index an hstore?

deleted 14y ago
[deleted]
JohnDotAwesome 14y ago
Depends on your needs really. The querying capability in Postgres is so much nicer than NoSql DB's and generally, or at least is the case with Mongo, you don't get transactions.
joevandyk 14y ago
You can use gist indexes to index hstore's.
pygy_ 14y ago
hstore supports indexing out of the box. http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html You can also create custom indexing functions using the PL/$language extensions. See here for some benchmarks comparing Mongo to several Postgresql options: https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fo... MongoDB is not that fast.
jeffdavis 14y ago
"Why would you want this over a NoSQL store?" If you can use a more general-purpose system like Postgres and get everything you need, then that's better than relying on special-purpose NoSQL systesm. The better question to ask is: why use a NoSQL system when Postgres works just fine and is useful in more situations? The history of databases is a history of absorbing special-purpose systems into general-purpose SQL systems. XML databases were once a major topic (albeit misguided); now it's just a feature. Same with Columnar storage, or OO databases, or geospatial (postgres is a leader in geospatial, as well). Because data integration is so incredibly valuable, it pushes strongly toward general-purpose systems and people dislike one-off special-purpose databases unless they deliver a huge amount of additional value.
jeffdavis 14y ago
"How does Postgres even index an hstore?" It allows normal btree indexing of, for example, the values for some given key by using functional and/or partial indexes. It also allows indexing of "contains", "contains key", "contains all of these keys" and "contains some of these keys" by using GiST and GIN indexes.
scubaguy 14y ago
Personally, I'd like the ability to make a transactional update to records in different tables.
- JohnDotAwesome 14y ago
  THIS. Exactly why Mongo fails (though I love mongo). No freakin' transactions!

Wouldn't it be a lot nicer to just use a schema and serializer? I know that's not really the point of this article, but if you're trying to get a SQL db to operate like a NoSQl store, it feels like a lot of hand-waving.

joevandyk 14y ago
It's really handy to get a json graph out of the database, even if the data is stored in normalized tables. select order_details(53647); Can return an complex graph of line items, addresses, shipments, etc. No need for ORMs.
JohnDotAwesome 14y ago
It really depends on the data structure. If you've got something that can have an unknown amount of variables in it and you're already serving up JSON, then I think using JSON is the way to go. Another use-case: We've got hundreds of clients that send health statuses for a ton of different metrics every 10 minutes. Stuff like Wifi strength, exceptions caught/uncaught, various errors and crash reports, blah blah. Anyway, we need a flexible store for all of this stuff because we're always adding more metrics. Whatever the clients send as their request body gets added as a JSON object. We also want to dynamically display all of these metrics. We can literally grab the data as JSON and make the keys table column headers in an html view. Adding new metrics can automatically be reflected in both the database and in our html views. We can query against new fields without changing schemas or business logic.
- film42 14y ago
  If it were up to me, I would create a schema for each metric and then if you need all on one page, create some nice /client/client_id/metrics/all route. Although if you're dealing with metrics, you probably want to have a good defined model of your data (schema), because statisticians want to know what they have to work with, and saying, "bunch 'o JSON" is not acceptable. Plus there's things like null values to deal with. Yuk. For my startup, we find the common denominator of social media data. For example, a tweet is very unique so it's itself. But then there's more generic types like photos. So then we look at photo services like flickr and instagram. We then evaluate the data available and look for common denominators again to hone in on as much similar data as possible. The result is a model of photo data from a wide range of sources, but are now unified. We could have just used instagram json and flickr json and tossed in the db, but because we defined a schema/ models to get data from, we can sleep soundly knowing the data we send to the frontend is fast and always valid.

hbbio 14y ago

In related news (here: https://news.ycombinator.com/item?id=5589593 https://news.ycombinator.com/item?id=5589593 ), the Opa framework which is based on Node.js just released support for both MongoDB and Postgres from the same application source.

andrewguenther 14y ago

Actually, Postgres does have a JSON datatype. The only extra thing it does is ensure the data is valid JSON, but it is still technically incorrect to say that there is no JSON type. http://www.postgresql.org/docs/devel/static/datatype-json.html http://www.postgresql.org/docs/devel/static/datatype-json.ht...

JohnDotAwesome 14y ago
Dang. Yeah - Definitely should have said there's not JSON type known to Node PG, which is actually incorrect as of this morning: https://github.com/brianc/node-postgres/pull/239 https://github.com/brianc/node-postgres/pull/239 Thanks for pointing out the error!
jvm 14y ago
> The only extra thing it does is ensure the data is valid JSON, Even that's actually not quite fair, it has special syntax for queries within the JSON (->> etc), and a bunch of functions: http://www.postgresql.org/docs/devel/static/functions-json.html http://www.postgresql.org/docs/devel/static/functions-json.h...
- andrewguenther 14y ago
  These functions can be run on datatypes other than JSON, but yes, you are correct.

audreyt 13y ago

The plv8x project ( https://github.com/clkao/plv8x https://github.com/clkao/plv8x ) adds convenient JSON and JS/LiveScript selector operators: https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-database https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-da... covers the basic usage and the motivation, as well as the ongoing PgREST work ( https://github.com/clkao/pgrest https://github.com/clkao/pgrest ) that builds a MongoLab REST API server that resides within the database, so they can be queried over a pg connection as well as through HTTP.