Storage for multiple variable-length attributes in a single row - Mailing list pgsql-hackers

From Esteban Zimanyi
Subject Storage for multiple variable-length attributes in a single row
Date
Msg-id CAPqRbE5n+B6aZod1u+KrczQjC-fb4uqvuYBLmYA6xMrT72eF8Q@mail.gmail.com
Whole thread Raw
Responses Re: Storage for multiple variable-length attributes in a single row
List pgsql-hackers
Dear all

When ingesting mobility (IoT) data into MobilityDB
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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Support tab completion for upper character inputs in psql
Next
From: Tom Lane
Date:
Subject: Re: sqlsmith: ERROR: XX000: bogus varno: 2