RDS or Object DB for 99.99% static data of 25 million rows in one table

Question

I have important data that consists of approx 25 million rows and will grow year on year. This dates back to 1995 and is split into yearly CSV files.

Example: 1995.csv, 1996.csv and so on up to 2016.csv.

The data is of UK addresses with the Postcode being the primary identifier.

Let's say I perform a search on SW19 2EA.

I need the database to query the data and return the result set that is available for that given postcode. This will return data that contains information such as the address, postcode, property type and so on.

The problem I have at the moment is querying 25 million rows in a single table is fairly heavy and has a hefty delay. That being said, I'm only just learning advanced SQL and my configuration is probably not up to scratch.

We have 2x 128 GB RAM servers both running dual Xeon x5's on 10 Gbit dedicated connections. We assume the severs are of no issue whatsoever, and therefore are unsure what the best solution would be.

Do we store all the databases into a single table and do some research on the best configurations or do we store them in an object database?

The data will not change apart from new data being added in 2017 that is for the 2017 year. All other years are static and are never changed.

My initial thought was to store each data set in its own table according to each CSV file, so, for example, 1 table for 1995 data, 1 table for 1996 data and so on. The question is, whenever a search query is performed, is it possible to have multiple workers query each table at the same time, so that, let's say, 10 tables are being queried concurrently as opposed to one giant 25-million-row table?

Could you use the same use case on an object database? I'm also curious if it's possible to cache the entire database, it being static data. On the other hand, would this significantly reduce the query delay of around 20 seconds that I currently experience on the single 25-million-row table?

Example of CSV data structure:

"3E0330EF-67CA-8D89-E050-A8C062052140","112000","2006-05-22 00:00","MK13 7QS","F","N","L","HOME RIDINGS HOUSE","13","FLINTERGILL COURT","HEELANDS","MILTON KEYNES","MILTON KEYNES","MILTON KEYNES","A","A"

I have been using Laravel with MariaDB backend and been importing the CSV files into MariaDB. Then I have been simply using a query like this:

Select * from history where `postcode` = 'MK13 7QS';

and in Laravel terms:

$postcode = 'MK13 7QS';
History::where('postcode', '=', $postcode)->get();

Database Layout is not perfectly set up and the data types can certainly be tweaked to make them more ideal for the dataset.

Currently I have just used varchar on every column apart from the date column, as I have only been playing about with things to try and get a more suitable setup and configuration working for production.

I'm hoping to get some much appreciated advice from a newcomer point of view.

Answer 1

You will keep the data 'forever', correct? That is, there is no 'purging'.

Is there potentially a different entry for each year? Or multiple entries for each year?

Why not have a single, non-partitioned, InnoDB, table with the PRIMARY KEY starting with the postcode. Perhaps you want the address and DATETIME as other columns in the PRIMARY KEY.

PRIMARY KEY(postcode, address, as_of)

That would let you get an ordered history of who lived at $pc, $addr:

SELECT * FROM tbl
    WHERE postcode = $pc
      AND address = $addr
    ORDER BY as_of;

It would be very fast (much less than 1 second), regardless of hardware. (With ENGINE=CSV, you are looking at minutes or hours for any query.)

If you envision other queries, let's see them.