Re: Storage for multiple variable-length attributes in a single row

From: Esteban Zimanyi <estebanzimanyi(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: SAKR Mahmoud <mahmoud(dot)sakr(at)ulb(dot)be>
Subject: Re: Storage for multiple variable-length attributes in a single row
Date: 2022-02-07 17:42:47
Message-ID: CAPqRbE4zytQv7T1OWPh2nKgbs7eGoCwCJAP6gqFnBO14jemymA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear David

There are two approaches for storing temporal information in a relational
database, explored since the 1980s following the work of Richard Snodgrass
http://www2.cs.arizona.edu/~rts/publications.html
tuple-timestamping vs attribute-timestamping. The SQL standard used the
tuple-timestamping approach, but in MobilityDB we decided to use the
attribute-timestamping approach. As you rightly pointed out,
tuple-timestamping follows the traditional relational normalization theory.

The main advantage of the attribute timestamping for mobility data is that
we need only to store the changes of values for a temporal attribute. In
the example of gear for a car, even if we receive high-frequency
observations, there will be very little gear changes for a trip, while
there will be much more position changes. Therefore on MobilityDB we only
store the change of values (e.g., no change of position will be stored
during a red light or traffic jam), which constitutes a huge lossless
compression with respect to the raw format storing every observation in a
single row. We have experimented 450% lossless compression for real IoT
data.

In addition, MobilityDB does all temporal operations and allows to
determine the value of any temporal attribute at any timestamp (e.g., using
linear interpolation between observations for speed or GPS position),
independently of the actual stored observations.

I hope this clarifies things a little.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2022-02-07 18:26:32 Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations
Previous Message Joshua Brindle 2022-02-07 17:23:28 Re: [PATCH v2] use has_privs_for_role for predefined roles