1

An application frequently executes the following prepared statement in SQL:

SELECT AVG(StockUnits)
FROM PRODUCT;

I'm currently exploring the most suitable storage model for the database architecture. After some research, the term "Decomposition Storage Model" has come up, but could anyone help me understand why the "Decomposition Storage Model" might be particularly well-suited for handling the mentioned SQL statement? Any advice, explanations, or pointers to relevant resources would be highly appreciated.

1 Answer 1

2
SELECT AVG(StockUnits) FROM PRODUCT;

If the problem is a dumb query, the solution is to not do the dumb query. It's like count(*) on a huge table. Who needs that result? I mean, if at least there was a WHERE...

I looked up "Decomposition Storage Model" and found "Vertically partitions an n-attribute relation into columns, each of which is accessed only when queries need it." So basically it means columnar storage like clickhouse.

Yes it will make the query you don't need to do faster, but it won't fix the fact you shouldn't be doing the query.

You should design a database according to queries that need to be executed, not according to queries that should not be executed.

If your database supports column storage, you can use that. If it supports index-only scans, you can fake it by adding an index on StockUnits. So when it wants avg(StockUnits) it will only have to read the index. Maybe you can even make it a useful index by adding a column.

If someone insists, a better solution is to do the query once in a while, like every day at 4AM, and cache the results somewhere.

If someone really insists it's important to have an accurate result in real time, the solution would be a trigger that updates a count stored in another table. In this case it's an average, so you need a row count and a total for the column in question. So you need a trigger on insert,update,delete on table product, which updates the row count and the total stock. This usually ends up with the "one row lock to rule them all" which may cause some performance issues since that essentially serializes all writes to the table in question. A solution may be to keep counts by category or other method of grouping products.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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