4 ms·

With JSON support complete in 9.5, wouldn't that be a great way to handle hierarchies? The only downside I can think of is the 1 GB field size limit.

by rpedela 11y ago

orf 11y ago
Why would the 1GB field size limit be an issue?
- rpedela 11y ago
  If your hierarchy exceeds 1GB then you need to break it up somehow into multiple fields, rows, or tables depending on the specific use case.
  - jacobr1 11y ago
    If your hierarchy for a given node exceeds 1GB Then you almost certainly are using the wrong tool and should consider some kind of graph db.
    - rpedela 11y ago
      Hierarchy != graph (although it can) There are always better tools but that doesn't mean that it is "wrong". For example, S3 is generally a better tool than Postgres for storing files but there are several legitimate use cases where storing the files in the DB is better than S3.
netghost 11y ago
I think it depends on how your data is represented. If your data only exists in a single hierarchy, and you don't need to index other values for lookup, a JSON object might work fine.
saurik 11y ago
Some people, maybe you, have been confusing the updates that allow you to more easily manipulate jsonb data values, including being able to change values inside of them easily with SQL, with the ability to do in-place updates of parts of the field in the database. If you have a field in a row that is over the database page size (something around 8kB), it will get compressed to TOAST, and in any case any change to the content of that field will rewrite the row. If you have a field that is even megabytes large, much less a gigabyte large, that you are intending to update, you are in for a world of hurt. You also will not be able to index inside the value if you do this: usually you want to be able to efficiently say "I want parts of my tree that look liked this"; PostgreSQL is not going to be able to provide you any help, and is only going to be able to use an index to know "ok, your megatree row might match your query", and then it will have to load the entire value into memory so it can manipulate it with SQL and possibly return a sub-result.

With JSON support complete in 9.5, wouldn't that be a great way to handle hierarchies? The only downside I can think of is the 1 GB field size limit.

by rpedela 11y ago

With JSON support complete in 9.5, wouldn't that be a great way to handle hierarchies? The only downside I can think of is the 1 GB field size limit.

orf 11y ago

Why would the 1GB field size limit be an issue?

rpedela 11y ago
If your hierarchy exceeds 1GB then you need to break it up somehow into multiple fields, rows, or tables depending on the specific use case.
- jacobr1 11y ago
  If your hierarchy for a given node exceeds 1GB Then you almost certainly are using the wrong tool and should consider some kind of graph db.
  - rpedela 11y ago
    Hierarchy != graph (although it can) There are always better tools but that doesn't mean that it is "wrong". For example, S3 is generally a better tool than Postgres for storing files but there are several legitimate use cases where storing the files in the DB is better than S3.

netghost 11y ago

I think it depends on how your data is represented. If your data only exists in a single hierarchy, and you don't need to index other values for lookup, a JSON object might work fine.

saurik 11y ago

Some people, maybe you, have been confusing the updates that allow you to more easily manipulate jsonb data values, including being able to change values inside of them easily with SQL, with the ability to do in-place updates of parts of the field in the database. If you have a field in a row that is over the database page size (something around 8kB), it will get compressed to TOAST, and in any case any change to the content of that field will rewrite the row. If you have a field that is even megabytes large, much less a gigabyte large, that you are intending to update, you are in for a world of hurt. You also will not be able to index inside the value if you do this: usually you want to be able to efficiently say "I want parts of my tree that look liked this"; PostgreSQL is not going to be able to provide you any help, and is only going to be able to use an index to know "ok, your megatree row might match your query", and then it will have to load the entire value into memory so it can manipulate it with SQL and possibly return a sub-result.