0

I am designing a database api. I have two options:

  1. Have a complex aggregation that queries the data as desired or,
  2. Create a materialized view that logs all data changes in an easy to query manner and potentially makes querying this data faster than querying data from option 1.

I was wondering is having many writes an anti-pattern in database design and administration? Because if not I would love to use option 2.

I am using a no-SQL database; MongoDB.

1

There are no free lunches. If there is work to be done it has to be done at some point. The question is when is it best for the system as a whole to perform the work. This depends on the total workload and access patterns.

For write-mostly / read-seldom patterns, such as logs, it makes sense to optimize for write throughput. Since most data is never read it would be a waste of resources to perform additional processing to optimize for future reads which likely will never happen.

For write-once / read-many patterns, such as e-commerce, it makes sense to do the additional work once on write rather than perform it redundantly for every read.

There are many gradations between these two extremes. Perhaps the initial write is latency sensitive? It may be better to enqueue a request to perform the additional processing asynchronously. And so on.

Be aware that duplicating values (and aggregates do duplicate data) introduces risks. There will be a finite latency between the detailed row being written and the aggregates being updated. Other processes may be sensitive to this and report inconsistent results. The aggregates can become a concurrency bottleneck as they are touched may many activities, by definition. Every program ever written against either source has to consider the existence of the other values, too.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.