Large database results in long data loading on frontend

Sven · 2024-11-10T14:58:39.130Z

What is a good way to sort and or filter data from a database table that is pretty large? Right now it takes a lot of time. When a user goes to the page that will show this data paginated it queries the database but the method i use now takes pretty long. Is there any efficient sort/filter algoritm i should use with postgres in order to reduce this time?

K

kapa.ai•11/10/24, 2:58 PM

Hi @Sven. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

K

kapa.ai•11/10/24, 2:58 PM

----------------------
To address the issue of long data loading times for large database tables in Wasp, there are a few approaches you can consider:

Pagination: Implement server-side pagination to limit the amount of data fetched and transferred at once. This can significantly reduce query time and improve frontend performance.
Indexing: Ensure that your PostgreSQL database has proper indexes on the columns you're filtering and sorting by. This can dramatically speed up query execution times.
Optimized Queries: Write efficient SQL queries, possibly using Prisma's query optimization features. Wasp uses Prisma as its ORM, which provides various optimization techniques Prisma Configuration (https://wasp-lang.dev/docs/data-model/backends#prisma-configuration).
Caching: Implement caching strategies to store frequently accessed data and reduce database load.
Asynchronous Loading: Use asynchronous loading techniques on the frontend to improve perceived performance.
Database Extensions: Consider using PostgreSQL extensions for specific optimization needs. Wasp allows you to enable these through configuration PostgreSQL Extensions (https://wasp-lang.dev/docs/data-model/backends#postgresql-extensions).
Query Invalidation: Be mindful of Wasp's automatic query invalidation feature, which can cause unnecessary updates. While convenient, it may lead to performance issues in large apps Automatic Query Invalidation (https://wasp-lang.dev/docs/tutorial/actions#invoking-the-action-on-the-client).

K

kapa.ai•11/10/24, 2:58 PM

For more specific optimizations, you might need to analyze your particular use case and database schema. If you're still experiencing issues after implementing these strategies, you may want to consider more advanced techniques or consult with database optimization experts.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------

K

kapa.ai•11/10/24, 2:58 PM

Sorry @Sven, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:

Perhaps try again in 15mins?

M

miho•11/11/24, 11:00 AM

Are you filtering the data on the server or on the client?

Mmiho Are you filtering the data on the server or on the client?

S

SvenOP•11/11/24, 1:21 PM

Server

M

miho•11/11/24, 2:26 PM

This is more a general DB question than a Wasp related question. If you are filtering on the server, that's a good first step. Now, if you are fetching all the data and then filtering in JS - that's bad. If you are writing filtering queries with Prisma - that's good. And if you are doing that - then database indexes will be helpful for you. I'd research that direction

Mmiho This is more a general DB question than a Wasp related question. If you are filt...

S

SvenOP•11/11/24, 6:07 PM

I'm working on a feature that involves querying daily event data and aggregating metrics over a specified timeframe (e.g., last 30 days or 90 days) for each event. Based on these aggregates, I need to filter and sort the events according to certain thresholds, such as total revenue or average price within the selected timeframe. Here’s a more detailed breakdown of my issue:

Data Structure:

 Events table

Events table

 Events table

Events table: Stores information about each event, like its name and category.

EventData table

EventData table

EventData table

EventData table: Contains daily metrics for each event, including:

totalRevenue

totalRevenue

totalRevenue

totalRevenue: Total revenue generated by the event on a given day.

totalTicketsSold

totalTicketsSold

totalTicketsSold

totalTicketsSold: Total tickets sold on that day.

avgTicketPrice

avgTicketPrice

avgTicketPrice

avgTicketPrice: Average ticket price for that day.

eventDate

eventDate

eventDate

eventDate: The date of the event metrics.

The Events table has a relation to

 EventData

EventData

 EventData

EventData, where

EventData

EventData

EventData

EventData holds the daily details.

Query for all events within a given timeframe (e.g., last 30 or 90 days).
For each event, I need to aggregate the

totalRevenue

totalRevenue

totalRevenue

totalRevenue,

totalTicketsSold

totalTicketsSold

totalTicketsSold

totalTicketsSold, and

 avgTicketPrice

avgTicketPrice

 avgTicketPrice

avgTicketPriceover the timeframe.

Apply filters on these aggregated values. For example:
Only retrieve events where the sum of

totalRevenue

totalRevenue

totalRevenue

totalRevenue in the timeframe is between a specified minimum and maximum.

Similarly, apply filters for

totalTicketsSold

totalTicketsSold

totalTicketsSold

totalTicketsSold and

avgTicketPrice

avgTicketPrice

avgTicketPrice

avgTicketPrice (where avgticketprice is average price over that timeframe) based on specified thresholds.
Finally, sort the results based on one of these aggregate fields (e.g., sort by

totalRevenue

totalRevenue

totalRevenue

totalRevenuein descending order).

Challanges im facing
Filtering on Aggregated Data: I need to filter events based on the sum or average of fields in EventData over the specified timeframe. For example, filtering events with a total revenue between 1000 and 5000 within the last 30 days.

Sorting: I want to sort the filtered results based on these aggregated values (e.g., total revenue or average ticket price).

S

SvenOP•11/11/24, 7:00 PM

-----------------------

S

SvenOP•11/11/24, 7:00 PM

I know this is not really wasp related but i know you guys maybe can give some input or recomendations

M

miho•11/11/24, 7:19 PM

Using specialised databases would help here i.e. they are called time series DBs. Stuff like https://www.timescale.com/ or even going with https://clickhouse.com/ would be nice.

I'm not an expert, but if you are dealing aggregates - either you calculate them and save them in the DB under some fields or you calculate them on the fly (but your DB needs to be fit for that so it's fast enough)

PostgreSQL ++ for time series and events | Timescale

Engineered to handle demanding workloads, like time series, vector, events, and analytics data. Built on PostgreSQL, with expert support at no extra charge.

ClickHouse

Fast Open-Source OLAP DBMS - ClickHouse

ClickHouse is a fast open-source column-oriented database management system that allows generating analytical data reports in real-time using SQL queries

Mmiho Using specialised databases would help here i.e. they are called time series DBs...

S

SvenOP•11/12/24, 8:55 AM

Thanks, someone else recommended me this. Is this suitable for wasp? And if so how would i implement this?

The best approach would be to use TypedSQL as Prisma can't handle all the requirements you've listed. You can create a new SQL file in the prisma/sql directory, for example, getEventMetrics.sql:

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

Generate Prisma Client with the SQL flag:

prisma generate --sql

prisma generate --sql

prisma generate --sql

prisma generate --sql

you can use the generated TypedSQL function in your TypeScript code:

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

M

miho•11/12/24, 12:40 PM

Wasp doesn't support TypedSQL - we'll probably work on it in the future, but it's not a priority atm.

The good thing, TypedSQL is just fancy raw SQL which you can use with Wasp without trouble https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/raw-queries

Thanks, someone else recommended me this. Is this suitable for wasp? And if so how would i implement this?

Wasp is just a Node.js server, so you'd do it in any way you see fit to connect a Node.js app with one of those apps. I'd probably setup things in the

server.setupFn

server.setupFn

server.setupFn

server.setupFn function and import the DB client for e.g. Clickhouse from there in the rest of the server.

Raw queries | Prisma Documentation

Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API.

Mmiho Wasp doesn't support TypedSQL - we'll probably work on it in the future, but it'...

S

SvenOP•11/12/24, 2:10 PM

What do you mean with server.serupFn i dont understand fron there

SSven What do you mean with server.serupFn i dont understand fron there

M

miho•11/12/24, 3:31 PM

https://wasp-lang.dev/docs/project/server-config

Server Config | Wasp

You can configure the behavior of the server via the server field of app declaration:

Mmiho Wasp doesn't support TypedSQL - we'll probably work on it in the future, but it'...

S

SvenOP•11/12/24, 3:49 PM

So i need to do this setup in the server in order to use raw SQL?

Mmiho Using specialised databases would help here i.e. they are called time series DBs...

S

SvenOP•11/13/24, 8:55 PM

I think calculating them and saving under some fields is best option for me, Would a cronjob be best?

S

SvenOP•11/13/24, 9:13 PM

So also lets say i add a new model in my schema.prisma and migrated it in dev version, what happens when i deploy then? what is best practise

S

SvenOP•11/13/24, 9:41 PM

@kapa.ai

job dataJob {
  executor: PgBoss,
  perform: {
    fn: import { dataJobfn } from "@src/workers/dataJob"
  },
  entities: [Data,Aggregates,Prods],

  schedule: {
    cron: "40 22 * * *",
  }
}

job dataJob {
  executor: PgBoss,
  perform: {
    fn: import { dataJobfn } from "@src/workers/dataJob"
  },
  entities: [Data,Aggregates,Prods],

  schedule: {
    cron: "40 22 * * *",
  }
}

job dataJob {
  executor: PgBoss,
  perform: {
    fn: import { dataJobfn } from "@src/workers/dataJob"
  },
  entities: [Data,Aggregates,Prods],

  schedule: {
    cron: "40 22 * * *",
  }
}

job dataJob {
  executor: PgBoss,
  perform: {
    fn: import { dataJobfn } from "@src/workers/dataJob"
  },
  entities: [Data,Aggregates,Prods],

  schedule: {
    cron: "40 22 * * *",
  }
}

This job does not run on 22:40 of my local time

K

kapa.ai•11/13/24, 9:41 PM

Let me check the knowledge base for you again. One moment! :books:

K

kapa.ai•11/13/24, 9:42 PM

----------------------
Based on the information provided in the knowledge sources, I can explain why your job might not be running at 22:40 local time as expected.

The cron expression in your job declaration is set to run at 22:40 (10:40 PM) every day. However, it's important to note that the time zone for cron jobs in Wasp (which uses pg-boss) is typically based on the server's time zone, not your local time zone.

According to the pg-boss documentation (https://wasp-lang.dev/docs/advanced/jobs#pg-boss-considerations), Wasp uses pg-boss for job scheduling. The cron expression is interpreted based on the server's time zone where your application is running.

To address this issue, you have a few options:

Adjust the cron expression to match your desired local time, taking into account the time difference between your local time and the server time.
If you're running this locally for development, ensure your server's time zone is set correctly.
For production environments, you might need to configure the server's time zone to match your desired execution time.
Alternatively, you could adjust your job logic to account for time zone differences if needed.

If you need more precise control over the execution time or need to handle multiple time zones, you might need to implement additional logic within your

dataJobfn

dataJobfn

dataJobfn

dataJobfn to manage these time-related complexities.

Remember, when deploying to production, the behavior might differ based on the hosting environment's time zone settings. Always test your scheduled jobs in an environment that closely matches your production setup to ensure they run at the expected times.
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------

K

kapa.ai•11/13/24, 9:42 PM

Sorry @Sven, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:

Perhaps try again in 15mins?

S

SvenOP•11/14/24, 10:33 AM

Fixed, no datalost just deployed to fly

S

SvenOP•11/14/24, 10:33 AM

also my job didnt run because i had scheduled beow entities

S

SvenOP•11/14/24, 10:34 AM

has to be other way around

S

SvenOP•11/14/24, 10:40 AM

well to be honest somethimes i notice that it just does not run

S

SvenOP•11/14/24, 10:40 AM

even with

     cron: "* * * * *",

cron: "* * * * *",

     cron: "* * * * *",

cron: "* * * * *",

SSven Fixed, no datalost just deployed to fly

N

NEROX•11/14/24, 11:15 AM

did you acomplished at the end?

S

SvenOP•11/14/24, 11:16 AM

Yes

SSven Yes

N

NEROX•11/14/24, 11:17 AM

how did you NOT lost data?

N

NEROX•11/14/24, 11:17 AM

in prod

S

SvenOP•11/14/24, 11:17 AM

Wait

S

SvenOP•11/14/24, 11:18 AM

I just added column to a table

S

SvenOP•11/14/24, 11:18 AM

Then did wasp db migrate-dev

S

SvenOP•11/14/24, 11:18 AM

And then wasp deploy fly deploy

S

SvenOP•11/14/24, 11:18 AM

Fly migrates these changes to prod

S

SvenOP•11/14/24, 11:18 AM

And i didnt loose data

S

SvenOP•11/14/24, 11:19 AM

And if i would fly makes snapshot of database to go back

N

NEROX•11/14/24, 11:19 AM

fly has snapshot option?!?

N

NEROX•11/14/24, 11:19 AM

omg

S

SvenOP•11/14/24, 11:20 AM

It does ir daily

N

NEROX•11/14/24, 11:22 AM

in wich section can you see that?

NNEROX in wich section can you see that?

S

SvenOP•11/14/24, 12:12 PM

https://fly.io/docs/postgres/managing/backup-and-restore/#listing-snapshots

Fly

Backup, Restores, & Snapshots

Documentation and guides from the team at Fly.io.

Large database results in long data loading on frontend

Similar Threads

Similar Threads

Similar Threads