In our company, we have a relatively big data structure, with terabytes of historic information stored, and there are lots of different views, editors, and reports for all the data. With the years, there have been several "hands" involved in the underlying code for data retrieval and storage, which we've found out that have taken different considerations between different views (for example) that lead to non-consistent reports, but this also has been happening for configuration GUIs.

We think we might implement our own API on top of the basic services, so every part of the system only has a single point of interaction with certain data, although this may seem simple enough, we've discussed some matters that might trouble us in such implementation, like for example the performance of massive reports, where thousands of records should be analyzed with lots of possible data aggregations, filters and alike.

Obviously, we can easily build an API for basic CRUD operations, but what about massive operations for viewing data on-screen, or for building Excel reports? What should be done in these scenarios? Any best practices to consider? Design patterns perhaps?

  • 3
    This is too vague to be answerable. Try to narrow it down to a specific aspect. Feb 7, 2017 at 17:09
  • Since you say the data is 'historic', would exporting it into something like Elasticsearch be an option? This would possibly solve many performance problems regarding aggregations and filters. Feb 8, 2017 at 12:55
  • @thorstenmüller just read about ElasticSearch at aws.amazon.com/es/elasticsearch-service but I guess not DB oriented, but files oriented, am I right? Over 95% of our data is, in fact, historic data, but we are getting new tons of it every second, so if ElasticSearch would be in fact an option I guess we should setup some kind of pipeline to ingest incoming new data. Feb 8, 2017 at 13:18
  • It is document oriented. But this detail shouldn't worry you that much. I used it for statistics (and complex filtering, searching, aggregating) over millions of records. Mostly sales data over longer time periods (like sum of sales per quarter, week... by region etc). Not only was it faster than the SQL database, it also was much nicer to get complex aggregation. (All with Ruby). Setup is super easy, some nice tools like Kibana for ad hoc queries. You could get a simple trial setup running in a day or so. Also super easy to get a distributed system running. Feb 8, 2017 at 13:47

3 Answers 3


When an API covers a specific part of the data, it doesn't necessarily need to be limited to CRUD operations on this data. Doing this wouldn't bring anything useful in terms of data abstraction, and will have an important impact on the performances.

Such API should instead mimic the business cases in relation to the underlying data. This means that it can provide features such as aggregation, pagination, transformation of data into a form which is more representative of a business logic, etc.

However, introducing too much computation/processing in the API creates a risk of moving too much logic from the upper layers—the services which call the API—to the API itself. A rule of thumb is that it makes sense to put the logic in the API if the same logic is used by many clients; if only one client is using a specific logic, this must be a sign that the logic could have been implemented directly in the client.

In general, when the API is well designed, you won't have too much doubts about where to put a specific piece of logic. You can, for instance, look at popular APIs and the ways they are designed. For example, Amazon S3's API is rather abstract when it comes to the underlying data format: by looking at the S3's API, you would have no clue about data sources (do they use MongoDB? Or maybe PostgreSQL? Or maybe a mix of twenty different data sources?) or even the architecture (is there one server which keeps all the data? Or maybe a hundred? Or thousands and thousands of servers interacting together?) At the same time, S3's API contains exclusively the logic needed to work with the storage and retrieval of chunks of data: ACLs, caching, automatic removal, etc., but doesn't contain anything which belongs somewhere else: for instance, S3 doesn't make any difference between storing video files for a video streaming site, or storing descriptions of products for a product management system.


It happens when you have system running for long time and you gather huge information, I think below steps would help you out.

  1. You need to have new server on which you need to store the database.
  2. Once you done with new server for database now use cdn to fetch the data.
  3. To have better execution time use language which have better I/O operation such as Node JS

I hope above points however they are in brief not in detail but it will give you some idea.

  • Thanks for the ideas, but our main issue is not the DB performance actually, but how a single API exposure would clog the throughput between the API and the above layers. About your suggestions, what's a cdn in terms of DB? Feb 8, 2017 at 14:42
  • for cdn I am reffering to have call from totally different server, so it will not load your server where you are showing the data. So basically you have have execution strength increased by 80%. CDN will work as in different call for your website and however if you api it will use same server to call
    – JiteshNK
    Feb 8, 2017 at 15:01
  • Ok I get at...a slave replica perhaps Feb 8, 2017 at 15:22

In big data scenarius there are actually two databases

  • one writable database for dayly busininess with less than a million datarows and
  • an analytical read-only-database with the terabytes of historic information for report generation

Periodically (i.e. once a day) data is transfered from the writable database to the readonly report database

  • disadvantage: the content of the historic database is 24 hours old
  • advantage you have good performance on the writable database because it is not occupied by the reports

design suggestion: create 2 sperate api-s:

  • one for the big-data-reports and
  • one for the dayly business
  • Suggestion considered, but I still have the API issue, and how to properly plan it for massive / bulk / report oriented operations Feb 8, 2017 at 17:01
  • if your question is about database design which can efecctively query big data you should have a look at the Snowflake database schema. In my opinion you cannot create an efficient java-api for big data without knowing the domain details
    – k3b
    Feb 8, 2017 at 17:18
  • the question is not about the db design itself, it's regarding the API. The domain is an already working and proven structure for about 8 years, but it's about time to make the services around it more organized. Nevertheless I will have a look at Snowflake to what you mean. Feb 8, 2017 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.