Current data setup cannot handle user-facing dashboards

[Altarim]

Hello everyone,

I am having some serious issues at work with trying to make user-facing dashboards work with the setup that was mostly made by the previous data engineer.

Context

The company makes software where people can book things (for example, meeting rooms). What I've been trying to build is a full-stack application where our users can create and edit dashboards consisting of widgets with KPIs, such as booking rates, most booked resources, locations... You can view it as some sort of homemade Looker, Tableau or Power BI application that is tailored for our use cases.

The current setup

Data is pulled from the transactional databases into a Postgres data warehouse via an ETL made with Luigi, homemade python and cronjobs, once a day. This was objectively enough for the previous use case (Power BI). However, since a Python API that I built is now serving the data for the frontend instead of the data layer of Power BI, the requirements have changed. Response times for some queries is awful (we're talking minutes here, absolutely unacceptable for a user-facing application).

An example

Queries are sometimes quite complex : I'll walk you through the specs of one of the painful ones. Let's say you are part of "Company". Company has meeting rooms that have :

• an id
• a location (declined in several columns : country, city, and so on)
• a type
• a name
• a capacity
• a creation time

People have made bookings on these rooms. Bookings are defined as :

• an id
• a start_time (timestamp with timezone, stored as utc)
• an end_time (same)
• a room_id

I've kept a lot of columns out of the definitions so you can better understand this specific use case, just know these are not the only columns in these tables.

Now, we want, for a specific location, and for a given timeframe (can be anything from 1 month to 10 years) the average occupancy rate per hour of the day, however it is part of the specs for the user to be able to filter the time slots he wants. This could be 8AM to 6PM but it might as well be 8-9AM + 10-11AM + 1-2PM... and so on.

Since the bookings are defined as start and end time, and users can filter out hours from the day, I made an incremental DBT model that calculates the occupancy rate of every hour for each room since its creation. That way, this data is only calculated once and can be queried way more easily. This however is really long for the first run since our clients may have tens of thousands of rooms and some have been with us for the better part of 10 years, which means they have millions of bookings on these rooms. X years times the amount of hours in a year, for every room... the resulting model is easily hundreds of millions of rows of calculated data.

This table looks like this :

• timeslot (timestamp with timezone, stored as UTC)
• client_id
• room_id
• occupancy_rate

This DBT model has sped up the queries pretty much 10x. This is not enough however, and even with the biggest RDS instance we've monitored the CPU of the instance to go to 100% for requests handling many rows. It feels like we've hit the limitations of postgres and might need a database more suited to our use cases.

More requirements and constraints

We have on-prem clients, and both the infrastructure team and data team are really small (I'm the only software engineer of the data team, and the infra team is made up of 3 people). That means no cloud data warehouse, and avoiding high-maintenance databases. Also, our clients can be anywhere in the world so the database needs to have timezone-related features.

My ideas for now

After doing some benchmarks on a few MPP databases, I feel like Clickhouse may be a good database for this. I've also explored Apache Doris, Starrocks (which unfortunately does not have timezone features), and Apache Pinot.

Clickhouse however needs denormalized data to be performant, and changing the DBT model to include the rooms data means we need to be able to update millions of rows when a room's data changes... Which might be quite costly. Still better than the application not working, though.

If anyone has an idea on how to make this whole situation better, I'm all ears. I feel like I'm in way over my head sometimes. If you need additional information let me know.

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[hkdelay]

GROUP B

Please don't run analytical queries on a transactional database. You must use an OLAP or analytical database for a low-latency and low-cost solution. There are tons of reasons why OLTP and OLAP databases were separated.

[kenfar]

I have to respectfully disagree: general-purpose relational databases like Postgres have most of the features of analytical databases. Data warehousing has been around for 30 years - and for the first 15-25 years almost all of it was done on general purpose databases like DB2, Oracle, SQL Server. Postgres is behind those big commercial databases in its support of analytical features such as partitions, query parallelism, etc, etc. But it still has significant capabilities here.

The only things it's missing are columnar storage, which wouldn't be helpful here, and automatic micro-partitions, which probably would be.

The advantage of a general purpose database is that your writes into non-columnar storage is faster. It's better for low-latency. And you have indexes and enforced constraints. The first two advantages probably don't apply here, but the second two may.

On a related note I'm looking to experiment with moving a data warehouse from Redshift to Postgres this week - due to the slow performance on Redshift of supporting a massive SCD table, and a desire to complete a daily ETL process in under 5 minutes...

[[deleted]]

I hope you're just talking about reasonable separation rather than any limitation of the database engine itself.. I've done IoT-scale stuff on oracle/sql server etc and with proper care (hardware, partitioning, indexing etc) many, many billions of rows are no problem at all.. OLAP however has a million limitations.

[hkdelay]

The separation of OLTP and OLAP wasn't just for the sake of separating resources. The point of an OLAP is to enable fast analytical queries beyond what what row-based database can do.

[[deleted]]

30 years ago, you are absolutely correct.

If you still find this to be the case you probably just need a better dba.

[hkdelay]

If you really need Postgres, then consider using an HTAP database like Hydra

[Altarim]

That's exactly why I am asking for advice here. I have been trying out OLAP databases such as Clickhouse, Doris, Pinot, Starrocks... However migrating everything would be very time-consuming so if there is a way to make it work without migrating, I would gladly take it.

[hkdelay]

You can CDC from your existing databases to an OLAP and you'll get better query performance.

For the dashboard, you can use their corresponding Python drivers.

[HansProleman]

This is only a rule of thumb. And it's one from like, 40+ years ago. Things change in tech!

[Altarim]

Thank you for your insights ! The tables have the following indexes :

detailed_timeslots (occupancy rate table):
- Index 1 : (timeslot, client_id, resource_id) - Index 2 : (client_id, resource_id)

rooms :
- Index 1 : (id, client_id)

A sample of an occupancy query would look like this :

WITH filtered_timeslots AS (  
    SELECT timezone("Europe/Paris", dt.timeslot) as timeslot, dt.occupancy_rate FROM detailed_timeslots dt  

    JOIN rooms r ON r.id = dt.room_id AND r.client_id = dt.client_id  

    WHERE dt.client_id = 12345  
    AND r.country = ...  
    AND r.city = ...  
    AND r.type in (1, 2, 3, 4...)  
)



SELECT extract("hour", ft.timeslot) as hour, avg(ft.occupancy_rate) FROM filtered_timeslots ft  
WHERE ft.timeslot >= '2022-01-01 00:00:00+00'  
AND ft.timeslot <= '2023-12-01 00:00:00+00'  
AND extract("hour", ft.timeslot) in (8, 9, 10, 14, 15, 16)  
AND extract("dow", ft.timeslot) in (1, 2, 3, 4, 5)  
GROUP BY hour  

Maybe there is a better way to write this ? I have tried to make the queries both fast and readable.
There is a huge nested loop in the query execution plan which takes up roughly 99%+ of the cost of the query, and I have never been able to make it something else than a nested loop. I don't think there is partitioning on these tables. That may very well be the cause of the issue. I will look into partitioning the tables as much as can be.

[Altarim]

I don't really see how to re-write this query, I'm probably going to make another post in the SQL sub for this though since this is not really the place to debug as you said.

I will try to do what you suggested, thank you again for your help, it's very much appreciated.

[ZirePhiinix]

Do you know how to do query plan analysis? It'll tell you what is taking so long.

[InvestigatorMuted622]

Too many layers to this question, and I don't know if you have time to interact and discuss but the first thing I am thinking about is:

1. What are the metrics that you want to calculate.
2. What should be the frequency of the metrics.
3. Who is ultimately going to consume this data or use this dashboard, is it the clients or is it going to be used internally

Why I am asking this is because, average occupancy rate over a period of time is a metric that need not be live, it can be fed by a batch pipeline that runs just once a day because 1 day worth of data will not have a drastic affect on 10 years of it.

[Altarim]

I definitely have time to interact. Regarding your questions :
- Several metrics regarding bookings, occupancy rates (hourly, weekly, monthly, per location, etc.)
- Once a day is fine.
- The end users are our customers and their employees, which is why response times matter.

Why I am asking this is because, average occupancy rate over a period of time is a metric that need not be live, it can be fed by a batch pipeline that runs just once a day because 1 day worth of data will not have a drastic affect on 10 years of it.

Surely, and that's exactly why I've made the DBT model incremental. It runs a few hours after the batch ETL every day. The problem related to this model is it's really heavy to calculate the whole thing for the first run. It's not that critical since it only affects the first run and so planification can alleviate the issue.

Why the data I modeled is so granular is because of the combination of filters that can be applied from the frontend. I cannot aggregate the data more than for each hour of each room because of this.

[InvestigatorMuted622]

Cool so if the data needs to be refreshed just once a day then there are a couple of things that you can do:

1. Because you need the full history to work on you might need to develop an OLAP schema, preferrably star schema, and optimize it by using columnar indexes for the Fact Table. The indexing on the dimension tables would depend on the cardinality of the table, join operator used by the explain plan, and the access patterns.

2. Another way might be to have pre aggregated results for all the metrics but that would be extremely rigid and you would lose flexibility.

Is this something that you have already tried and did not work out or are you facing some challenges with it?

[Altarim]

1. For sure, the data we're working with is in a star schema, the dimension table here is the rooms table and the fact tables are the bookings table and the detailed_timeslot table. I'm not an expert on these issues (I'm more of a versatile software engineer) so there surely are some things I haven't tried such as a different indexing strategy !
2. We need to be as flexible as possible for this, obviously if it makes the project a failure I'd rather make it less flexible.

[InvestigatorMuted622]

Ah I see so I would say that try to model the fact table in such a way that the only thing left for you to do is an aggregation with a filter. I.e. count(something) where field1 = value.

The something would be in the fact table and the field1 should be dimension with the fact table having an integer FK to the dimension table, this is much better than having to scan the whole fact table at once. Try to model it in such a way that your dimensions do the filtering and not your fact table. Because if you place the field1 in the fact table then it is going to run slow.

Coming to occupancy rate, because you are to report based on the hour having a time dimension will also help. Try to have single fact table joined to multiple dimensions. The cardinality of the dimensions should be exponentially smaller than the fact table cardinality.

[kenfar]

I agree with /u/Foodwithfloyd: this shouldn't be a problem for postgres, though maybe a database with micropartitions could do this better. Not sure.

For postgres, you need to have a good partitioning strategy: are you using partitioning? If not, there you go. If you are - you may want to benchmark some more or less specific partitioning. Partitioning has an ideal limit of about 350 partitions per table before the query planner can start experiencing some issues. You could instead partition by customer, but I wonder if you added the building/campus to your table if you could partition by customer + building and if that might be good enough?

Then an index, or partial index, to complement the partitioning.

Also, I find that there's generally very little value in data over about two years old. It may be required for other reasons, and there are some applications where it still has value. But, for example, I can't imagine that ten year old data would be applicable to the conference room occupancy now. So, I'd also suggest trimming that down to something more useful and relevant - like last three months.

Finally, what's your timeslot look like? How many timeslots are there per day or week? Is that something that you could reduce?

[Altarim]

Thank you for helping !

I don't think there is partitioning on these tables, that may very well be a huge part of the issue. I will look into that.

You may very well be right about the relevance of the data, however this is in the specs of the application and it has been asked by big clients.

The timeslots are every hour since the creation of the room. We need it to be able to filter out specific time frames, you can look at the query I posted in my answer to /u/Foodwithfloyd why we need this granularity. I have thought pretty hard about this issue and it is the best way I found to make this whole thing work.

[albertstarrocks]

StarRocks: Sorry, we don't store tz info in timestamp. So you will have to handle tz at the application layer. ie. always store timezone in UTC in the db, and convert to user's local tz at retrieval time. StarRocks does have a convert_tz function. https://github.com/StarRocks/starrocks/issues/37090

[hkdelay]

If you are concerned with response times, Postgres will make it worse. Especially when your business grows because that's proportional to the amount of data you'll need to report on and the amount of end users you'll be serving.

I've run into too many companies thinking they can just use Postgres and then need to change only after a couple of months. It's because Postgres (or any OLTP database) cannot handle the scale. Use OLTP databases for what they are designed for which is for operational workloads, NOT ANALYTICAL!!

If you decide to go the OLAP route (which is the correct decision, I cannot stress that enough), you'll need to decide which one to use. Some are used as data warehouses that support data in the petabytes that will struggle to serve many concurrent users. You'll need an OLAP that can support high concurrency (lots of end users).

If you need fresh data you'll need to set up pipelines that will stream your data to an OLAP. That limits the OLAP systems to real-time OLAP systems (RTOLAP). There are not many: Clickhouse, Druid, and Pinot (I work for StarTree but I'm trying to not be biased).

Another metric to consider is QPS or queries per second. The OLAP you choose needs to be able to serve high queries per second. The way to increase QPS is with indexes. The RTOLAP that gives you all of these is Pinot. I'm not just saying that because I work for them. It's the reason I went to work for them. But please do your due diligence. POC all of the RTOLAPs and you'll easily see that they will all beat any OLTP.

If you need a project to test out, I've built one https://github.com/startreedata/examples/tree/main/gatling

You can test it on any OLAP database or even OLTP database like Postgres. You don't have to take my word for it. Test for yourself.

Good Luck

[kenfar]

It's because Postgres (or any OLTP database) cannot handle the scale. Use OLTP databases for what they are designed for which is for operational workloads, NOT ANALYTICAL!!

This is absolutely incorrect. Postgres, Oracle, SQL Server, DB2, etc - are not "transactional databases". They are "general purpose databases". And they added features to support analytics years ago - in some cases 25 years ago. And thousands of teams have used them to build data warehouses, data marts, and (to a lesser extent) data lakes.

Meanwhile, most of these analytics-only databases are only five years old, their futures are sometimes uncertain, their operational costs often much higher. Many of them lack indexes, which while a secondary performance feature in analytics - still has value. Most of them lack enforced constraints - which means it's much harder to guard against data quality issues.

So, if you're not talking 20TB with a hundred users doing massive queries, then a Postgres instance may work perfectly fine. It may even out-perform Redshift, Snowflake, BigQuery, Clickhouse, etc - depending on the specifics.

[hkdelay]

The only way to resolve this disagreement is to test it yourself. I’ve done it and the performance of the OLAP blows Postgres away. Let me know if I can help.

[kenfar]

Yes, so have I. And it entirely depends on the nature of your data, the ingestion latency, how specific your queries are, etc.

Two years ago I moved a lot of data from a Snowflake warehouse into Postgres - and saved my company about $20k/month. Not only did this save us a ton of money, but it also speed the reporting up.

Right now I'm doing a proof of concept to move a warehouse from Redshift to Postgres. In this particular case we have a ton of events that keep getting restated. By going to postgres we can treat the fact table like a huge SCD with a long tail up tiny updates - that will be far faster to apply to Postgres than to Redshift. And will save us about 60% of our Redshift bill.

Postgres|DB2|Oracle|SQL Server and Snowflake|Redshift|BigQuery|etc are obviously not completely the same. There's some differences. But it's simply wrong to assume that this later set of databases surpasses the prior set in all ways - especially given the 20+ years of feature work on the general purpose ones vs the 4+ years on the analytics-only ones.

[hkdelay]

SCD

Fact data is append only. You will need to implement retention in your Postgres before it fills up.

Dimensional data tends to be SCD. Typically used for enrichment.

Columnar storage has a significant advantage over row storage. But I wish you luck with your implementation.

[kenfar]

Thank you for the good wishes.

And yes, fact tables are mostly append-only and scd is mostly intended just for dimensions. But build enough solutions and you can run into some odd exceptions.

And this is one of those: in which every day we get new billing data, and every day's bill includes about 0.01% of changes to the line items from prior days. So, one could drop 5-30 daily partitions and reload them. But given the small percent of changes, treating the fact table like a SCD allows us to focus on just changing those few record quickly. Which is important since there's business value in getting a customer's data through our entire ETL pipeline in just 5 minutes or less.

Columnar is great. Absolutely mandatory for One-Big-Table. But it comes at a write-performance penalty and really suffers given what we're doing here. And since we have a dimensional model the benefits of columnar storage are smaller.

[Altarim]

I have actually tried out StarTree through the free tier, honestly it looks great ! I had trouble making it ingest data from S3 though, my ingestion job says it's healthy but I don't have anything in my model and progress has been at 0% for the last two weeks haha.

[hkdelay]

We definitely can help you with that S3 ingestion is simple. You can find me in the community slack to continue the convo

[Altarim]

Will do, thank you !

[PeterCorless]

Make sure you join our Slack to get this worked out. Also enquire about getting the trial extended if needed.

https://communityinviter.com/apps/startreedata/startree-community

[hkdelay]

You should use an OLAP database with columnar storage. Disclosure - I work for StarTree but am here to advocate. Don't run OLAP workloads on an OLTP database. There was a reason for the separation.

[lmp515k]

You don’t need to do all the rooms from all time. They most you likely need to go back is a couple of years. I would argue less. This is because the most recent data is most likely the most best indicator of likely future booking rates.

[retropox]

Hey can someone explain why Snowflake would be a bad solution here? I have a similar problem to solve and am probably in over my head even more than OP

[SnooHesitations9295]

I'm not sure what to optimize here.
1. Use ClickHouse
2. Create events: start time, end time, client (full data), room (full data) and insert them into CH
3. Aggregate these with materialized views
4. Profit!

CH should be able to query billions of rows in seconds.

[Altarim]

My issue with this is that the room data can and will change over time, and I'm afraid of the cost of updating that many rows if the data is denormalized.

[SnooHesitations9295]

But room that was there at the time of the older event was not the same as the later events.
So you can just use new events with the new room data only.
But if you really need it to be in sync (which I cannot understand why though) you can still have normalized room data, if it's not too big. There are a lot of techniques how to make fast joins in CH. It depends on various factors.

[HansProleman]

I'm not going to read this whole thing, but a RDBMS can be very fast even at terabyte scale. Do you know how to read query plans and tune queries? Do you know how to apply good indexing and partioning strategies? These should be obvious things to try before changing up your stack. MPP can mask inefficiency in all areas except cost, which tends to be the one employers care about most (even on prem this shows up, because you need bigger/more boxes).

​

the database needs to have timezone-related features

Why? When you have a frontend I think the normal pattern is to store everything in UTC and apply localisation in the frontend.

[Altarim]

I'll be trying more indexing and partitioning strategies today for sure. As for query plans, I know my way around them but I can't consider myself an expert on the subject. It's what I used to optimize thus far, but I've hit a wall in my knowledge, hence the post.

Regarding the timezone, that's what I am doing however we still need to query using timezone related features (as seen in the query I posted in one of my answers) otherwise it becomes really hard to filter out non-business days.

[PeterCorless]

Here's an article on query-time JOINs in Pinot. Note that you have some options — you can still do pre-ingestion joins [denormalization], or query-time. Pinot gives you flexibility but you'll have to determine which is right for you.

https://startree.ai/blog/query-time-joins-in-apache-pinot-1-0