RE: Handling time series data with PostgreSQL

[Jayaram]

Dear All,

I'm Jayaram S, oracle DBA. Currently we are planning to develop a stock
market based application which deals 80% of data with time data. We are in
the process of choosing the right database for the requirement especially
for time series data. After all multiple investigations, I found PostgreSQL
with timescaleDB works better than other DBs.

But still I'm new to PGSQL and we wanted only open source technology to
handle our requirements. It will be helpful to me if anyone can suggest
implementing the time series concepts in PostgreSQL database.
It's better if I can get proper docs or links with explanation.

Thanks in advance.,

-- 




*Thanks & Regards,Jayaram S,Banglore.India.*

Re: Handling time series data with PostgreSQL

[Jayaram]

Hi Adalberto,

Awesome.!! Thanks for your reply.

So, Do we need the timescaleDB as mandatory to handle time series data? Is
there any way to handle hourly to days,months,yearly data with PGSQL alone
without timescale addon?
Ours is a new project and we are unsure about whether we should have both
timescaleDB and PGSQL or PGSQL alone is capable of handling this time
series data by tuning the right indexes.etc..

What we are planning is to start with PGSQL alone (For handling
hourly,daily,monthly,weekly,yearly calculations) and later when we add
seconds and minutes, we will include the timescaleDB. Is this the right
approach?
Is PGSQL alone capable of meeting this requirement?

Thank you in advance.

Regards,
Jayaram S.

On Wed, Oct 7, 2020 at 1:34 AM Adalberto Caccia  wrote:

> Hi,
> TimescaleDB as a Postgresql extension has been used in my firm for two
> years now, I've recently managed to upgrade it from pg10 to pg12 and from
> discrete VM's to Kubernetes as well.
> Frankly speaking, being new to TimescaleDB at that time, I've found it
> easy to manage, easy to scale (it's 100% compatible with pg replication,
> unfortunately not the logical one, yet...), easy to install, easy to
> upgrade... what else?
> From a developer's perspective, it just adds "superpowers" to ordinary PG
> tables, all under the hood. On disk, it features a "chunked" layout, where
> each chunk belongs to a definite "time" range; and of course the "time"
> column on which to index time data is just passed as a parameter to the
> call to TimescaleDB, for each table on which you need such power.
> At the moment, we're also using it for time aggregate calculations, but
> only for the coarse ones (30m --> 1h and 1h --> 1 day), while we're still
> handling the finer ones (1s --> 1m and so on) in Kafka+Flink, which is a
> common scenario for a streaming data platform, anyway.
>
> Regards,
> Adalberto
>
>
> Il giorno mar 6 ott 2020 alle ore 11:47 Jayaram  ha
> scritto:
>
>> Dear All,
>>
>> I'm Jayaram S, oracle DBA. Currently we are planning to develop a stock
>> market based application which deals 80% of data with time data. We are in
>> the process of choosing the right database for the requirement especially
>> for time series data. After all multiple investigations, I found PostgreSQL
>> with timescaleDB works better than other DBs.
>>
>> But still I'm new to PGSQL and we wanted only open source technology to
>> handle our requirements. It will be helpful to me if anyone can suggest
>> implementing the time series concepts in PostgreSQL database.
>> It's better if I can get proper docs or links with explanation.
>>
>> Thanks in advance.,
>>
>> --
>>
>>
>>
>>
>> *Thanks & Regards,Jayaram S,Banglore.India.*
>>
>

-- 




*Thanks & Regards,Jayaram S,Banglore.Mobile: 91-7760951366.*

Re: Handling time series data with PostgreSQL

[Jayaram]

Thank you Stephen, Mark for your inputs. I will go through the
documentation for time series data best practices in PGSQL.

Regards,
Jayaram S.

On Thu, Oct 8, 2020 at 1:57 AM Stephen Frost  wrote:

> Greetings,
>
> * Mark Johnson (remi9...@gmail.com) wrote:
> > I think the OP may be referring to Oracle's Temporal Validity feature.
>
> Perhaps, but that's not the only way to manage time series data.
>
> > [ ... ] In earlier releases of each DBMS we tried to accomplish
> > the same by adding pairs of timestamp columns to each table and then
> > writing our own code to handle row filtering.  Partitioning isn't needed.
> > Certainly partitioning by range could be used, but it would still require
> > some manual efforts.
>
> I've found that using the range data types can work quite will, with
> overlaps queries, to manage time-series data instead of using pairs of
> timestamp columns.  With range data types you can also create exclusion
> constraints to ensure that you don't end up introducing overlapping
> ranges.
>
> Either way require adjusting your queries though, no?  And inserting and
> maintaining the data..?  I can appreciate wanting to be standards
> compliant but this specific use-case doesn't really provide much
> justification for using this particular feature.  Perhaps there are
> better ones.
>
> Thanks,
>
> Stephen
>


-- 




*Thanks & Regards,Jayaram S,Banglore.Mobile: 91-7760951366.*