How do we compile analytics from millions of rows in a PostgreSQL table?
We pull order data from multiple CRM's and need to compile the data for reporting and each CRM has it's own orders table. We compile these tables into a compiled_orders table in 24 hour increments.
Our current implementation uses SQL Views to aggregate results and SUM the columns
CREATE OR REPLACE VIEW crm1_sql_views AS SELECT account_id , name , COUNT(*) AS order_count , SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS approved_count , SUM(CASE WHEN status = 0 THEN total ELSE 0 END) AS approved_total FROM crm1_orders WHERE AND is_test = false GROUP BY account_id , name ;
We select the data we want from this view. The issue that we are running into is that a query like this pulls all the order data for a client into memory. If a client has 20M orders, it becomes extremely slow, and sometimes the query results are larger than the available memory/cache.
How do we incrementally/consistently/quickly take 20M records in a table and compile it into another table?
Increasing hardware is one solution, but we feel that is not the correct solution right now. We looked at materialized views, but since each CRM has it's own tables, it would have major maintenance implications every time we added a new CRM to our offering.
The goal is for our end users to answer questions like: - How many orders did we receive last week/month/year? - What weekday do I receive the most orders?
What technologies/methodologies/terms do we need to look at and research?
- Data Pipelines
- "Big Data" tools