Store/Retrieve time series data from PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

Requirement :- 
We need to retrieve latest health of around 1.5 million objects for a given time.

Implementation :-
We are storing hourly data of each object in single row. Given below is the schema :-
     
CREATE TABLE health_timeseries (
       mobid text NOT NULL,
       hour bigint NOT NULL,
       health real[]
     );

mobId - Object ID
hour - Epoch hour
health - Array of health values for a given hour of that object.

Each object has 2 hours of health data (i.e. 2 rows for each object) so total no. of rows is around 3 million.

With the above approach the query to retrieve the latest health of all objects for a given time duration is taking around    85 seconds. I have tried to increase the work_mem, effective_cache, shared_buffer to 4 GB of PostgreSQL but still there was no improvement in the query execution time.

Query :-
select distinct on (health_timeseries.mobid) mobid, health_timeseries.health, health_timeseries.hour from health_timeseries where hour >=(1505211054000/(3600*1000))-1 and hour <= 1505211054000/(3600*1000) ORDER BY health_timeseries.mobid DESC, health_timeseries.hour DESC;


Hardware Configuration of PostgreSQL VM :-
1. OS - Centos.
2. Postgresql version - 9.6.2
3. RAM - 16 GB RAM
4. CPU - 8 vCPU

Please let us know the hardware configuration of PostgreSQL for such huge dataset?

And also let us know if there is any better schema/query to retrieve this data?

Thanks and Regards
Subramaniam
    
      



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux