Re: handling time series data

Thanks for the responses.

So, it seems what I am trying to do isn't out of the norm. I will get the
column store driver a try. Are there any plans to have this natively
support in Postgresql? That would be a great "killer" feature.

On Tue, Feb 2, 2016 at 7:53 PM, Brent Wood <Brent(dot)Wood(at)niwa(dot)co(dot)nz> wrote:

> Hi Keith,
>
> This documents a time series database we manage with Postgis, from a
> research vessel. We use partitions & clustered indexes, as well as a
> "minute identifier" (of sorts) to allow various intervals to be rapidly
> identified and extracted. It works well for us, with a Mapserver
> application and other tools to provide interactive access, as well as SQL
> queries. We are up to over 600,000,000 records now, and it is still quite
> responsive.
>
> The new block indexes in PG9.5 might have some application for such
> databases as well, but I think that while they may be smaller/faster but
> also more limited than our approach.
>
> See: ftp://ftp.niwa.co.nz/os2020/ipy-caml/DAS_database.pdf
>
> Cheers
>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI: +64 (4) 3860529
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |
> www.niwa.co.nz
> [image: NIWA] <http://www.niwa.co.nz>
> To ensure compliance with legal requirements and to maintain cyber
> security standards, NIWA's IT systems are subject to ongoing monitoring,
> activity logging and auditing. This monitoring and auditing service may be
> provided by third parties. Such third parties can access information
> transmitted to, processed by and stored on NIWA's IT systems.
> ________________________________________
> From: pgsql-general-owner(at)postgresql(dot)org <
> pgsql-general-owner(at)postgresql(dot)org> on behalf of Keith Brown <
> keith6014(at)gmail(dot)com>
> Sent: Wednesday, February 3, 2016 12:04:14 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] handling time series data
>
>
> By reading this,
>
> http://www.postgresql.org/message-id/7e41ba8f0908191624g4501b5f7mcbe29ad2c8139acc@mail.gmail.com
> ,
> I was wondering if anything has changed on the postgresql front.
>
> I have a large timeseries (2TB worth of uncompressed data). I will be
> doing some queries which change at times. Should I stick with my
> current approach which is a series of csv files or would it be better
> to load it into Postgresql and use its TOAST features (which will give
> me some sort of compression)
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>