9 ms·

FYI you can store unstructured data in PostgreSQL (and query it) with the introduction of hstore. So knock one more reason to use MongoDB instead of PostgreSQL

by gfodor 14y ago

FYI you can store unstructured data in PostgreSQL (and query it) with the introduction of hstore. So knock one more reason to use MongoDB instead of PostgreSQL off your list. (Disclaimer: the length of my list to use MongoDB has always been a constant that is less than one.) http://www.postgresql.org/docs/9.1/static/hstore.html http://www.postgresql.org/docs/9.1/static/hstore.html

mrkurt 14y ago
Wow, hstore really isn't a great alternative to an actual document DB. The "better" Postgres option would be a JSON type and functional indexes.
- shawn-butler 14y ago
  There is a JSON type but it just validates content. HSTORE can be fully indexed (gIST and GIN). Just have to roll your own object graphs for nesting if that's what you need to do. I swear I have typed this exact same comment previously. Deja vu, maybe
  - mrkurt 14y ago
    JSON type gives you some typed values within the doc, multi-level nesting, etc. You can add functional indexes (http://www.postgresql.org/docs/9.1/static/indexes-expressional.html http://www.postgresql.org/docs/9.1/static/indexes-expression...) to index specific attributes within the JSON, do legit sorts over values, reasonable array queries, etc. It seems much, much closer to what Mongo does than anything you can do with hstore.
    - shawn-butler 14y ago
      I think you just restated my comment. Do you believe expression indexes do not apply to HSTORE? I consider both HSTORE (key/value) and the current JSON type and record functions are just intermediate steps to a fuller API [0]. [0]: http://www.postgresql.org/message-id/50EC971C.3040003@dunslane.net http://www.postgresql.org/message-id/50EC971C.3040003@dunsla...
  - aaronblohowiak 14y ago
    http://news.ycombinator.com/item?id=4764834 http://news.ycombinator.com/item?id=4764834
- deleted 14y ago
  [deleted]
- vog 14y ago
  > JSON type and functional indexes Those "Indexes on Expressions" are really a great feature that can also be combined with XML (not just JSON) and any other types. I recommend everyone to have a look at those: http://www.postgresql.org/docs/9.2/static/indexes-expressional.html http://www.postgresql.org/docs/9.2/static/indexes-expression...
  - arethuza 14y ago
    Is there any way in those expressions to parse JSON and perform arbitrary calculations - i.e. like CouchDB views?
    - zurn 14y ago
      Sure, you can write server side procedures in f.ex. javascript that do arbitrary things with the json.
      - vog 14y ago
        To be fair, one should note that "only" the languages C, Python, Perl and Tcl [1] are officially supported by PostgreSQL. Also, there are 3rd-party bindings for other languages such as Java, PHP, R, Ruby, Scheme, sh: http://www.postgresql.org/docs/9.2/interactive/external-pl.html http://www.postgresql.org/docs/9.2/interactive/external-pl.h... However, the PostgreSQL documentation doesn't mention JavaScript support anywhere. Are you sure there exists mature PL/JavaScript binding for PostgreSQL? If so, their docs should be updated. [1] There's also "pgSQL", but that's a special-purpose language you won't find outside the database world. I don't recommend learning it unless you have strange requirements that make PL/pgSQL a perfect fit. For normale usage, use PL/Python or PL/Perl. In simple cases, use SQL directly.
        zurn 14y ago
        Yes, #1 hit when you Google for postgresql JavaScript: https://code.google.com/p/plv8js/ https://code.google.com/p/plv8js/ I can't vouch for any particular maturity level but seems to have active users and it's been around a few years already.
taligent 14y ago
> So knock one more reason to use MongoDB instead of PostgreSQL off your list. One of the reasons MongoDB is so popular is because it is an fantastic database for developers. As a Java developer I can deal in my code with sets, hashmaps, embedded structures and have it effectively map 1-1 in the database. It's akin to an object database meaning you can focus higher up in the stack. With the SQL ORMs you can't avoid having to deal with the ER model.
- gfodor 14y ago
  Of course, the problem with that approach is you don't have anything enforcing any sort of data integrity below the application. In my experience most of the time you actually can put down on paper a schema and a set of rules the data should obey without too much fear of it changing dramatically. The nice thing about hstore is it allows you the flexibility to introduce unstructured data in just the places where a schema is unknowable or not worth the complexity. MongoDB et all basically are built around the assumption that a schema is never worth the complexity. It's a bold claim that contradicts many decades worth of database research.
  - burntsushi 14y ago
    > MongoDB et all basically are built around the assumption that a schema is never worth the complexity. It's a bold claim that contradicts many decades worth of database research. Unless MongoDB et al are saying "always use MongoDB et al and never an RDBMS", then I'm not sure how you arrived at the conclusion that "the schema is never worth the complexity." If anything, the appropriate assumption is, "schemas aren't always worth the complexity." When they are, you use an RDBMS. When they aren't, you don't bother with the data integrity constraints.
    - gfodor 14y ago
      The "right tool for the job" mantra often cited whereby you run N different data stores for different use cases heavily discounts the true implication of running multiple data stores: you have to run multiple data stores. You have more ways to get burned by your lack of expertise. You need more eyeballs for the same amount of confidence in your system since those will probably need to be different types of experts. You need to know how to monitor them and tune them. Discussion about which data store to use for a given use case becomes a constant drag on discussions. There is less consistency in modeling since you have to work with multiple paradigms. Your software needs to be built to be able to deal with multiple data stores. All your export/import/backup/etc software efforts that are 1-to-1 with each data store need to be multiplied. The bottom line is if you drop in a second data store because you have a few fields in your database that are a pain to model with a schema, you are doing yourself a disservice compared to just doing ALTER COLUMN foo hstore. My colleague mcfunley wrote an article about this blind spot when people talk about these issues: http://mcfunley.com/why-mongodb-never-worked-out-at-etsy http://mcfunley.com/why-mongodb-never-worked-out-at-etsy
vog 14y ago
There are also JSON and XML types in PostgreSQL. It also supports an easy transition from PostgreSQL native, record and array types to JSON, and it supports a wide range of the SQL XML standard. However, there's still something missing. PostgreSQL lacks support for XQuery. It lacks support for querying parts of JSON structure, although there's an external extension for that. And it lacks decoding JSON into native/record/array types. So for a pure document store, PostgreSQL would require you to add lots of userdefined functions for handling your JSON structures. However, PostgreSQL is great for hybrid approaches, when you separate your documents into content and metadata, i.e. free-structured JSON and hard-structured native types.

by gfodor 14y ago

mrkurt 14y ago

Wow, hstore really isn't a great alternative to an actual document DB. The "better" Postgres option would be a JSON type and functional indexes.

shawn-butler 14y ago
There is a JSON type but it just validates content. HSTORE can be fully indexed (gIST and GIN). Just have to roll your own object graphs for nesting if that's what you need to do. I swear I have typed this exact same comment previously. Deja vu, maybe
- mrkurt 14y ago
  JSON type gives you some typed values within the doc, multi-level nesting, etc. You can add functional indexes (http://www.postgresql.org/docs/9.1/static/indexes-expressional.html http://www.postgresql.org/docs/9.1/static/indexes-expression...) to index specific attributes within the JSON, do legit sorts over values, reasonable array queries, etc. It seems much, much closer to what Mongo does than anything you can do with hstore.
  - shawn-butler 14y ago
    I think you just restated my comment. Do you believe expression indexes do not apply to HSTORE? I consider both HSTORE (key/value) and the current JSON type and record functions are just intermediate steps to a fuller API [0]. [0]: http://www.postgresql.org/message-id/50EC971C.3040003@dunslane.net http://www.postgresql.org/message-id/50EC971C.3040003@dunsla...
- aaronblohowiak 14y ago
  http://news.ycombinator.com/item?id=4764834 http://news.ycombinator.com/item?id=4764834
deleted 14y ago
[deleted]
vog 14y ago
> JSON type and functional indexes Those "Indexes on Expressions" are really a great feature that can also be combined with XML (not just JSON) and any other types. I recommend everyone to have a look at those: http://www.postgresql.org/docs/9.2/static/indexes-expressional.html http://www.postgresql.org/docs/9.2/static/indexes-expression...
- arethuza 14y ago
  Is there any way in those expressions to parse JSON and perform arbitrary calculations - i.e. like CouchDB views?
  - zurn 14y ago
    Sure, you can write server side procedures in f.ex. javascript that do arbitrary things with the json.
    - vog 14y ago
      To be fair, one should note that "only" the languages C, Python, Perl and Tcl [1] are officially supported by PostgreSQL. Also, there are 3rd-party bindings for other languages such as Java, PHP, R, Ruby, Scheme, sh: http://www.postgresql.org/docs/9.2/interactive/external-pl.html http://www.postgresql.org/docs/9.2/interactive/external-pl.h... However, the PostgreSQL documentation doesn't mention JavaScript support anywhere. Are you sure there exists mature PL/JavaScript binding for PostgreSQL? If so, their docs should be updated. [1] There's also "pgSQL", but that's a special-purpose language you won't find outside the database world. I don't recommend learning it unless you have strange requirements that make PL/pgSQL a perfect fit. For normale usage, use PL/Python or PL/Perl. In simple cases, use SQL directly.
      - zurn 14y ago
        Yes, #1 hit when you Google for postgresql JavaScript: https://code.google.com/p/plv8js/ https://code.google.com/p/plv8js/ I can't vouch for any particular maturity level but seems to have active users and it's been around a few years already.

taligent 14y ago

> So knock one more reason to use MongoDB instead of PostgreSQL off your list. One of the reasons MongoDB is so popular is because it is an fantastic database for developers. As a Java developer I can deal in my code with sets, hashmaps, embedded structures and have it effectively map 1-1 in the database. It's akin to an object database meaning you can focus higher up in the stack. With the SQL ORMs you can't avoid having to deal with the ER model.

gfodor 14y ago
Of course, the problem with that approach is you don't have anything enforcing any sort of data integrity below the application. In my experience most of the time you actually can put down on paper a schema and a set of rules the data should obey without too much fear of it changing dramatically. The nice thing about hstore is it allows you the flexibility to introduce unstructured data in just the places where a schema is unknowable or not worth the complexity. MongoDB et all basically are built around the assumption that a schema is never worth the complexity. It's a bold claim that contradicts many decades worth of database research.
- burntsushi 14y ago
  > MongoDB et all basically are built around the assumption that a schema is never worth the complexity. It's a bold claim that contradicts many decades worth of database research. Unless MongoDB et al are saying "always use MongoDB et al and never an RDBMS", then I'm not sure how you arrived at the conclusion that "the schema is never worth the complexity." If anything, the appropriate assumption is, "schemas aren't always worth the complexity." When they are, you use an RDBMS. When they aren't, you don't bother with the data integrity constraints.
  - gfodor 14y ago
    The "right tool for the job" mantra often cited whereby you run N different data stores for different use cases heavily discounts the true implication of running multiple data stores: you have to run multiple data stores. You have more ways to get burned by your lack of expertise. You need more eyeballs for the same amount of confidence in your system since those will probably need to be different types of experts. You need to know how to monitor them and tune them. Discussion about which data store to use for a given use case becomes a constant drag on discussions. There is less consistency in modeling since you have to work with multiple paradigms. Your software needs to be built to be able to deal with multiple data stores. All your export/import/backup/etc software efforts that are 1-to-1 with each data store need to be multiplied. The bottom line is if you drop in a second data store because you have a few fields in your database that are a pain to model with a schema, you are doing yourself a disservice compared to just doing ALTER COLUMN foo hstore. My colleague mcfunley wrote an article about this blind spot when people talk about these issues: http://mcfunley.com/why-mongodb-never-worked-out-at-etsy http://mcfunley.com/why-mongodb-never-worked-out-at-etsy

vog 14y ago

There are also JSON and XML types in PostgreSQL. It also supports an easy transition from PostgreSQL native, record and array types to JSON, and it supports a wide range of the SQL XML standard. However, there's still something missing. PostgreSQL lacks support for XQuery. It lacks support for querying parts of JSON structure, although there's an external extension for that. And it lacks decoding JSON into native/record/array types. So for a pure document store, PostgreSQL would require you to add lots of userdefined functions for handling your JSON structures. However, PostgreSQL is great for hybrid approaches, when you separate your documents into content and metadata, i.e. free-structured JSON and hard-structured native types.