0

Good Morning,

I have a client doing scientific research. The current database (Azure postgres) design is 3NF but very few transactions are happening. There are several "pipelines" that add new data to the tables and scientists might make an update to some data or "discard" some data as erroneous but overall very little is happening transaction wise.

However they do have a "reporting" aspect to their work and that is what the scientists are doing most of the day. Looking at the data (genetics stuff) and seeing if these mutations have been seen before or what are start and stop markers etc.

For example one of their "reporting" or data analysis web pages calls a view. That view takes 4+ minutes to run. When I look at that view it's basically gathering all the data from the different 3NF tables and flattening it out to 1NF to then display.

So here are my thoughts / questions:

Could 3NF and 1NF tables exists in the same db and same schema? I know that you could literally do it but would that be wise / problematic? Is it some sort of "anti-pattern" to mix and match like that?

1a. If you did that would you modify the pipelines to put the new incoming data right into the 1NF tables or would you still let the pipeline insert to 3NF and then have a trigger or ETL process etc. do the 1NF table updates?

1b The thought here is that if a table were to exists in 1NF then we could just do a scan from that table rather than all the joins, cte, sub queries etc. that are currently in the long running view.

Should I migrate the whole operation to 1NF with the understanding that the few transactions there are will be slower but %90 of everything else (sp, vw, fx, etc) will be less complex and faster.

2a. If I did migrate everything to 1NF would you still have "staging" tables that the pipeline writes the new incoming data to then an ETL process loads the new data into the 1NF tables?

Overall I'm wanting to plan a long term solutions. Sure I can cut this 4 minute view down in time but long term what should we be thinking about especially as the volume of data continues to grow. (%20 increase last year alone).

TIA

2
  • Sorry, but the question is too unfocused (many questions in one), and it is also not quite clear. For one, you seem to use "1NF" and "3NF" with a quite unusual meaning. Moreover, the answer will depend on the individual case. Using a data model that is not ideal for the task can work fairly OK, but it can also be a catastrophe. So it is impossible to answer your questions in the general form in which they are put. Commented Jul 4 at 7:07
  • @LaurenzAlbe Thanks for responding. As I learn more about the db (just started) I understand how to ask better questions. Again thank you for responding.
    – GPGVM
    Commented Jul 4 at 14:11

2 Answers 2

0

What you describe is basic data warehouse architecture, where you have both normalized transaction data, and from that load tables that are modeled for easy consumption and fast query performance.

In Lakehouse architectures this idea is usually referred to as a "Medalion Architecture": https://learn.microsoft.com/en-us/azure/databricks/lakehouse/medallion

But whether you use a Lakehouse or a database the design is the same. The consumption tier of the solution is often modeled for consumption using Dimensional Modeling, which is generally more useful and efficient than producing wide denormalized tables.

-2

Your question is a classic example of the generic question:

  • ‘A’ doesn’t work. Should I replace it with ‘B’?

Who knows? It depends entirely on why ‘A’ does not work how you expect.

  • If ‘A’ doesn’t work because of a simple fixable problem therein, you should probably just fix that problem;

  • Conversely, if ‘A’ doesn’t work because of a fundamentally unfixable problem therein, you could try ‘B’ instead - as long as you understand that ‘B’ might be worse, so you’d have wasted your time by changing to ‘B’, and might even have to waste more time by changing back to ‘A’!

So before you change anything, you should determine unequivocally why your current queries are slow.

My bet is, one or more of the following:

  • Your database isn’t properly normalised;

  • The tables don’t have proper primary keys, and/or are not properly indexed;

  • Your queries are written inefficiently.

Say you have queries that run unexpectedly slowly:

  • Pick the simplest, slowest such query. (For example, pick a 10 line query that runs 50 times slower than you think it should, in preference to a 1,000 line query that runs 5% slower than you think it should.)

  • Find out why that query runs slowly. Ask someone more experienced if necessary. Do not proceed until you know why!

  • Then proceed accordingly.

HTH

0

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.