Please find the below as a problem scenario:
Current DB design: I'm having 40 tables (currently each table is having 5 billion records) and these records are not having the same result which means each table consists of unique records in their own table format. Each table has a primary key (pk) "timestamp" which is in UTC with timezone in ISO format. Around 1000 records are getting inserted in each respective tables for every 5 min.
Implementation: Now I have to get the most recent timestamp data from every table. I tried using for every table
select * from table_name where timestamp = (select timestamp from table_name order by timestamp desc limit 1)
which returns the most recent records but it took some time to get the results. After this query, I tried usingselect * from table_name where timestamp = (select max(timestamp) from table_name)
but eventually this also took some to get the data.
So, How to minimize the query time to get the data from the database? (all the design and query are welcome)?
Thank you.
FYI, Im using Python3.6 and psycopg2