So I have about 250k items x 2000 days (2010 to 2019) ~= 500 million records.

For each item I have variable number of fields. To begin with we have 50 fields defined for each item but in the future we want the ability to add more fields.

With that said, we thought we could pack all of the fields of each item in a JSON blob. Postgres allows us to query the JSON blobs. We did some prototyping and it took around 3 seconds for the QC queries on about 1 year of data with a smaller subset of items.

Since we are in Prototyping stage, I was thinking of building a Prototype using HBase. Since writing a spark job to bulk load data into it would take atleast me a couple of days I wanted to check online if HBase is a good option for this problem.

Also are there any other RDBMS solutions we should consider for this problem. MongoDB is a no-go because of some bureaucratic reasons.

PS: our QC queries are mostly, given a date get me all the items. Given an item get me all the data available for that item on all dates. Occasionally get me all the data for a particular field for a particular item.

  • 2
    What kind of query are those? What kind indexes did you create on the JSONB column? Did you consider partitioning? Did the queries use parallel execution? – a_horse_with_no_name Feb 13 at 21:30
  • Depending on what queries you plan to run, there are many different designs (besides a one json to gather and rule them all) and indexing you can test, as @a_horse_with_no_name mentions. (partitioning, partial indexes, brin and bloom indexes, ..) – ypercubeᵀᴹ Feb 13 at 21:44
  • Do people use HBase for a use case like this? Like what is the threshold at which a RDBMS problem becomes a Big data problem? – Aditya Feb 13 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.