Single table having large no of rows I am about to start a project and currently working on data design. While I am done in defining schema of db, I am just wondering to ask from experts.

I have only one table in which I am going to insert rows (no update) and from same table I am going to query the data set. Rows contains different columns. Columns for which I am going to query are kept in separate columns and the data which is needed to give as ouput will store in single column as JSON data.

I have experience of MySQL and I am looking for some other options to get fast query. My db size could contains billion of records.

I have seen many options like Cassandra, dynomodb etc but all these options make me confuse to decide on which db I could chose. I have also seen BIG query Postgre SQL.

I am looking for open source and where I could avoid any monetary expenses. I am looking for querying large datasets and want response to be max of 500ms.

Thanks in advance for help and suggestions.

Regards, 
Harpinder
Whatever you do, if you go down the RDBMS route, I recommend that you choose PostgreSQL over MySQL - it is a far more sophisticated product and has lots more functionality. 


Check constraints
Window (analytic) functions
CTEs - Common Table Expressions
Proper set operators - INTERSECT, EXCEPT as well as UNION (which MySQL does have)
Far superior support for JSON


As for response time, that depends on far more than your RDBMS server. 

Your disk config (RAID &amp;c) will be very critical in that domain, as will your server's RAM and CPU, although most database operations are HDD-bound. 

It will depend on your queries also (obviously). If you are doing point SELECTs on PRIMARY KEYs, then 1/2s is not unrealistic - but if you are doing analytic work (GROUP BY, HAVING, range scans, subselects... the list goes on), then there's no way anybody can tell you in advance what your response time will be.

If you are always going to be performing the same query or small range of queries against your data, then a NoSQL solution might be the way to go - but SQL's ability to "slice and dice" data is beyond compare - plus, you never know when new requirements will arise. 

Your question might be a bit broad for this forum - visit the help centre, take the tour and check out the "help us to help you" blog. p.s. welcome to the forum.