Which accounts reporting tool is ideal for Time bound, Dynamic Queries and Aggregates

Question

Problem:

We are looking to find a good open source tool in terms of a database that is great at the following;

We use Spring boot and Microservice architecture

• Good with aggregate in i.e sum, mean, average and subtraction
• Shading capabilities whereby you are able to archive data
• Able to do time bound queries efficiently
• A NoSQL database for unstructured data

The type of queries would be the general Accounting reports such as;

1. Trial balance
2. General Ledger
3. Cash Flow
4. Balance Sheet

We have considered mongo which has the following capablitlies;

1. NoSQL database
2. Shading capabilities

Monogo fails at doing aggregate queries in a simple manner and for those who have used mongo you find that you are writing the block of code to get simple aggregate and timebound queries while using spring aggregate.

Kindly share your thought and engine, will greatly appreciate.

Answer 1

PostgreSQL would be a good choice. It addresses your requirements in following ways:

1. As a relational database it performs generally well in aggregation queries

2. There are several ways how to address the shading/archiving data requirement

   • if the data volume is low just use another column to mark archived data
   • use another table with same schema and copy the records (manually or with a trigger)
   • use built-in partitioning feature

3. Indexes allow to perform time bound queries efficiently.

4. Provides json column type for unstructured data