From: | Subramaniam C <subramaniam31784(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Store/Retrieve time series data from PostgreSQL |
Date: | 2017-09-14 11:51:20 |
Message-ID: | CAL=06WmUGXv2Td30XaT9mHYSWJCVombq85+z2Th+0UPs7sOKsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | vinny | 2017-09-14 12:03:20 | Re: Store/Retrieve time series data from PostgreSQL |
Previous Message | David Rowley | 2017-09-13 21:57:54 | Re: query of partitioned object doesnt use index in qa |