Mirae Asset Financial Services: faster BI reports for sales support

Result

The Sales Support BI Solution analyzed sales performance reports directly on the operational PostgreSQL, so the reports were slow. When only the analytics queries of the reports moved to a GritDB analytics system, the report query finished in 8.8 seconds even with 50 times the data.

1 Reports were slow

Before · analytics on the operational PostgreSQL

The backend read whole tables and analyzed them in memory. PostgreSQL stores data by row, so it reads whole rows even when it aggregates only a few columns.

The report query on 200,000 rows took 229 seconds. It got slower and used more memory as the data grew.

A GritDB analytics system addedPostgreSQL built-in replication

2 GritDB runs the analytics queries

GritDB analytics system · columnar analytics engine

Stored by column · dark columns: columns the report reads

GritDB runs the analytics queries of the reports. It reads only the columns it needs and splits the aggregation across many CPU cores. The backend receives only the analytics results and builds the final result.

Changes in the operational PostgreSQL arrive through PostgreSQL built-in replication.

Same report query50 times the data (200,000 → 10 million rows)

3 8.8 seconds on 50 times the data

After · Sales Support BI reports

Existing PostgreSQL · 200,000 rows

229 s

GritDB · 10 million rows

8.8 s

Both times are the time the database took to run the report query. The GritDB side has 50 times the data.

Problems and improvements

1. Problem

   The backend read whole tables and analyzed them in memory. As the data grew, responses got slower and the backend used a lot of memory.

   Improved with GritDB

   GritDB runs the analytics queries, and the backend receives only the analytics results to build the final result. This removed the excess memory use.

2. Problem

   The report queries themselves were also slow. The existing PostgreSQL stores data by row, so even a query that aggregates only a few columns reads whole rows. The report query on 200,000 rows took 229 seconds.

   Improved with GritDB

   GritDB runs analytics queries in its columnar analytics engine. It reads only the columns it needs and splits the work across many CPU cores. It finished the report query on 10 million rows, 50 times the data, in 8.8 seconds.

Setup

Before: analytics on the operational PostgreSQL

Operational PostgreSQLStores rows

Reads whole tables

Sales Support BIBackend analyzes in memory → report screen

After: a GritDB analytics system added

Operational PostgreSQLBusiness transactions

PostgreSQL built-in replication

GritDB analytics systemRuns analytics queries · columnar analytics engine

Analytics results only

Sales Support BIBackend builds the final result → report screen

• The operational PostgreSQL keeps handling business transactions and sends its changes to the GritDB analytics system through PostgreSQL built-in replication (publication · subscription).
• The Sales Support BI backend sends the analytics queries of its reports to the GritDB analytics system and builds the final result from the analytics results only.
• Validation: we timed the same report query with the customer's real data (existing PostgreSQL 200,000 rows · GritDB 10 million rows).

The setup of the two engines is in Architecture. If you want the same validation, reach us through Contact sales.

The Mirae Asset Financial Services logo is a trademark of Mirae Asset Financial Services or its affiliates. Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.