CODEWITHSUNDEEP
postgresqlrelational-database
sambo
sambo

Reputation: 63

Is it practical to use one table for reading purpose only in a relational database?

I know this question would not be ideal in a real database world, however, I am building a web REST api to server a result that potentially need to join almost every table(i use normalization for sure).

So is it OK to do have one single table to hold the meta data used for reading API, but the table get updated as well when data updated in other tables? I am using PostgreSQL by the way.

Upvotes: 0

Views: 50

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

This is not very clear so I will state my understanding of the question and give you what I see are the tradeoffs.

First.... It sounds to me like you want to effectively materialize a metadata table and have it live-updated when other tables update. This is not really what the MATERIALIED VIEW support in PostgreSQL is for.

You can use a trigger to update the data whenever something changes. Because of the way PostgreSQL handles things, this leads to more disk and CPU activity, but will probably add more on the latter than the former. So if you hare heavily CPU-bound that will pose more problems than if you are I/O bound.

Using triggers in this way adds a fair bit of complexity to your database and may reduce write scaling a bit but if the data is seldom written but read frequently it may be a clear win.

So in answer to your question, yes it is practical in at least some cases. Whether it is practical in your case, that will be for you to decide.

Upvotes: 1

Related Questions