On Mon, Jun 19, 2017 at 2:29 PM, Rob Nikander <rob.nikander@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
