Storage for multiple variable-length attributes in a single row

From: Esteban Zimanyi <esteban(dot)zimanyi(at)ulb(dot)be>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: SAKR Mahmoud <mahmoud(dot)sakr(at)ulb(dot)be>
Subject: Storage for multiple variable-length attributes in a single row
Date: 2022-01-06 15:05:20
Message-ID: CAPqRbE5n+B6aZod1u+KrczQjC-fb4uqvuYBLmYA6xMrT72eF8Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear all

When ingesting mobility (IoT) data into MobilityDB
https://mobilitydb.com/
we transform very wide (2K attributes) car mobility data of high frequence
(every tenth of a second) from flat format (e.g. CSV) into MobilityDB
format in which there is a single record per trip and each of the signals
is transformed into a temporal attribute (tbool, tint, tfloat, ttext,
tgeompoint, tgeogpoint), which are temporal extensions of the corresponding
PostgreSQL/PostGIS base types (bool, int, float, text, geometry,
geography). All temporal types are stored using extended format, e.g.,
CREATE TYPE tfloat (
internallength = variable,
[...]
storage = extended,
alignment = double,
[...]
);

Given that each temporal value can be very wide (on average 30K
timestamped points/floats/text/... per trip) our first question is
* Is extended the right storage for this ?

Our second question is how all the 2K temporal attributes are stored, which
may be
* on a single table space
* in one table space per attribute
which in other words, relates to the question row vs column storage.

Many thanks for your insight

Esteban

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-01-06 15:24:09 Re: sqlsmith: ERROR: XX000: bogus varno: 2
Previous Message Tom Lane 2022-01-06 14:56:36 Re: Support tab completion for upper character inputs in psql