On Mon, Jun 19, 2017 at 2:29 PM, Rob Nikander <rob.nikander@gmail.com> wrote:
> I'm wondering about the tradeoffs, specifically: is it possible to update
> one piece of a jsonb value without having to rewrite the entire field? There
> are cases where that data field was getting pretty big (500kb). Would you
> expect any obvious performance differences between these two options?
You are basically asking, what are the relative
advantages/disadvantages of document model vs data store? This is a
complicated discussion. Here are some tradeoffs:
*) Document is always read/written in bulk. Row data reads/writes are
more discrete (but generally postgres always reads/writes 8kb
minimum!)
*) for documents transaction tracking is for the entire document.
This is more efficient for storage but can have very serious
consequences if sub-portions of the document are updated under heavy
concurrency.
*) Documents are a pain if the structure changes in such a way so as
to require invalidation of all of them.
*) Documents can be a *real* pain if the data relationships change in
some fundamental way. This is a pain with traditional tables as
well, but relational type models tend to be the most flexible vs other
approaches.
Basically there is a reason why SQL and relational type systems won
the 'data wars' of the 1970's and 1980's. There are downsides to the
basic approach (especially performance due to joining) but the
simplicity and elegance of being able to model just about any problem
tends to compensate certain performance disadvantages.
Document style storage tends to move the database model out of the
database and into the application (which is a very poor tradeoff IMO)
and fall over when some of the initial assumptions with respect to the
document modeling discrete business units break down; you end up
storing the same information over and over in different documents
which causes all kinds of problems. They do tend to work well in low-
or no- update applications however.
merlin
