Sometimes there are seemingly obvious concepts that are difficult to explain to other dbas (more junior) or just whoever is in charge of certain data architecture.

For instance -- when should a view become a table? (and this is a loaded question).

For me, the answer is more intuition -- but that can get you in trouble when talking to other developers.

For instance if there is giant fact/ transaction table of a 50 million records, fairly large, and you need to modify the "format" of a few fields for ingestion into external software, a view seems ideal because the calculations are quick during retrieval (that is needed anyway) --- and doubling the storage of a behemoth table has a large cost.

On the other side of the coin --- maybe there is a critical reference table. It contains details of 1,000 operational headquarters with dimensional data and calculations --- only 1,000 records though.

The view itself contains complicated joins, queries, calculations that might take 30 minutes to compute/ pull the view. HOWEVER, the underlying data barely changes day to day. Maybe 1% of records are changed overnight, and that's it.

And this view is referenced maybe 20 times throughout the day, causing 20 x 30 minutes of computation time and wait time for analysts. (in my opinion calculating the exact same thing over and over is a waste of hardware)

In this case, of course the view should be a table or materialized view. "But but the storage!" the contrarian might say. But the storage is minimal; maybe 0.01% of the database.

How can you explain these concepts to lay persons?

For some reason there seems to a penny-pinching mentality when it comes to database storage in this organization --- without holding that up against the cost of CPU/ memory (which is often overburdened) not to mention actual wait time (labor/ productivity costs).

Is there an established framework or vocabulary when it comes to decisions such as "table vs. view?" ? What metrics would determine what is ultimately more "cost efficient" ?

  • Storage is cheap compared to the additional CPU/Memory required to support the inefficient path. That said, additional CPU and Memory can cover up quite a large number of sins. Nov 18, 2022 at 19:27

1 Answer 1


There are two intractable problems here.

  1. The problem of quantifying and comparing costs and benefits. Eg how do you compare the cost of additional storage against the benefit of faster query execution? What about the cost of extra complexity? Even if you could measure them, they all have different units of measure and don't convert to a common currency.

  2. The problem that the costs and benefits are divided among multiple stakeholders. Each stakeholder looks at their costs and their benefits. So why should the DBAs do something to make the users life easier?

So while you can quantify the options, you can't calculate an optimal solution. And that's why typically this comes down to leadership who must decide and enforce a policy regarding how the system should be optimized.

See generally Software Product Management and Stakeholder Analysis.

  • Thanks for the thoughts --- I believe even if difficult, there can be established (I thought there might already be a framework in the field) the rough costs of various decisions. Storage (and backup, and labor associated) -- should have a rough quantifiable cost. But yes such "deep in the weeds" analysis should be done by the DBA/ data architect. --- Leadership is often tech illiterate, unless you mean the IT or DevOps leadership who may or may not have a strong understanding of database architecture. But for databases -- there should be a cost measure, and productivity measure - roughly
    – user45867
    Nov 18, 2022 at 15:22
  • I understand that realistically, it might be too complex or burdensome to try to 'optimize the hell' out of every database decision. However, I guess the idea --- to do a few case studies/ analyses on a few different architectures ... to generate a few generalized rules of thumb. Again, such as "when should something be a table vs. a view" -- apart from intuition, I mean.
    – user45867
    Nov 18, 2022 at 15:23

Your Answer

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

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