4 ms·

I'm really failing to understand obsession with unstructured types in Postgres? Why can't you just use native types for hierarchical structures? Is it really t

by zapov 13y ago

I'm really failing to understand obsession with unstructured types in Postgres? Why can't you just use native types for hierarchical structures? Is it really that hard for people to maintain database changes?

dragonwriter 13y ago
The article here seems to pretty clearly lay out usage consideration in selection between the types at issue (neither of which is really "unstructured".)
ketralnis 13y ago
I'm really failing to understand obsession with deriding people for solving problems that you personally don't have. But yes, for some people it can be difficult to do this. It slows development, it makes programmers have to do sysadmin work (which in a two-person company without a specialised sysadmin can be difficult). As another use-case, there was a point at which Twitter would take days or weeks to implement even minor schema changes across their entire MySQL cluster without impacting production. And some people are solving problems that just do have unstructured or semi-structured data, rather than your assumption here that it's just a proxy for avoiding schema changes
- zapov 13y ago
  Ok, I admit my comment is a little overboard. Comparing Mysql migration to Postgres is not really helping this discussion. Postgres has transaction DDL with many schema change operations being O(1). Most of the time your data has structure. Only parts of it are usually semi-structured. And yeah, I had this problem too, until I automated it and don't have it anymore. So I guess I'm a little annoyed when I see that schema change automation didn't really took off and people accepted JSON as a storage medium.
- jakejake 13y ago
  Avoiding DB work strikes me as not the greatest reason to store JSON in your DB. But I find there are good reasons to do it. The main one for me is when the data fields are configurable by the user. If I didn't want to be constrained by a schemas I'd just go with a NoSQL solution. Nothing wrong with that either.
  - tensor 13y ago
    Postgres can make a pretty nice "nosql" solution too. Either via hstore, json, or bytea. That said, nothing frees you from a schema. Not having it explicit in the database just means you have it encoded into your application code.
- philwelch 13y ago
  > it makes programmers have to do sysadmin work (which in a two-person company without a specialised sysadmin can be difficult) If you're a two-person company without a specialized sysadmin, programmers having to do sysadmin work should be inevitable, normal, and expected, not "difficult".
ishbits 13y ago
I used hstore in an existing database for metrics. It allowed me to add different metrics in the code, rather than having to update the database for new metrics, then a metrics to hstore layer that more or less "streamed" them into the database. I find it quite awesome for that case. Sometimes there is a need to unstructured data, and if you are already using Postgres, hstore is a great alternative to adding another database backend to your stack.
kaonashi 13y ago
Depending on the application, hstore can see real performance increases. http://blog.artlogic.com/2012/10/29/performance-of-custom-field-schemas/ http://blog.artlogic.com/2012/10/29/performance-of-custom-fi...

by zapov 13y ago

dragonwriter 13y ago

The article here seems to pretty clearly lay out usage consideration in selection between the types at issue (neither of which is really "unstructured".)

ketralnis 13y ago

I'm really failing to understand obsession with deriding people for solving problems that you personally don't have. But yes, for some people it can be difficult to do this. It slows development, it makes programmers have to do sysadmin work (which in a two-person company without a specialised sysadmin can be difficult). As another use-case, there was a point at which Twitter would take days or weeks to implement even minor schema changes across their entire MySQL cluster without impacting production. And some people are solving problems that just do have unstructured or semi-structured data, rather than your assumption here that it's just a proxy for avoiding schema changes

zapov 13y ago
Ok, I admit my comment is a little overboard. Comparing Mysql migration to Postgres is not really helping this discussion. Postgres has transaction DDL with many schema change operations being O(1). Most of the time your data has structure. Only parts of it are usually semi-structured. And yeah, I had this problem too, until I automated it and don't have it anymore. So I guess I'm a little annoyed when I see that schema change automation didn't really took off and people accepted JSON as a storage medium.
jakejake 13y ago
Avoiding DB work strikes me as not the greatest reason to store JSON in your DB. But I find there are good reasons to do it. The main one for me is when the data fields are configurable by the user. If I didn't want to be constrained by a schemas I'd just go with a NoSQL solution. Nothing wrong with that either.
- tensor 13y ago
  Postgres can make a pretty nice "nosql" solution too. Either via hstore, json, or bytea. That said, nothing frees you from a schema. Not having it explicit in the database just means you have it encoded into your application code.
philwelch 13y ago
> it makes programmers have to do sysadmin work (which in a two-person company without a specialised sysadmin can be difficult) If you're a two-person company without a specialized sysadmin, programmers having to do sysadmin work should be inevitable, normal, and expected, not "difficult".

ishbits 13y ago

I used hstore in an existing database for metrics. It allowed me to add different metrics in the code, rather than having to update the database for new metrics, then a metrics to hstore layer that more or less "streamed" them into the database. I find it quite awesome for that case. Sometimes there is a need to unstructured data, and if you are already using Postgres, hstore is a great alternative to adding another database backend to your stack.

kaonashi 13y ago

Depending on the application, hstore can see real performance increases. http://blog.artlogic.com/2012/10/29/performance-of-custom-field-schemas/ http://blog.artlogic.com/2012/10/29/performance-of-custom-fi...