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

On Mon, Feb 7, 2022 at 9:58 AM Esteban Zimanyi <estebanzimanyi(at)gmail(dot)com>
wrote:

>
> As suggested by David, this goes beyond the "traditional" usage of
> PostgreSQL. Therefore my questions are
> * What is the suggested strategy to splitting these 2K attributes into
> vertically partitioned tables where the tables are linked by the primary
> key (e.g. trip number in the example above). Are there any limitations/best
> practices in the number/size of TOASTED attributes that a table should
> contain.
> * In each partitioned table containing N TOASTED attributes, given the
> above requirements, are there any limitations/best practices in storing
> them using extended storage or an alternative one such as external.
>
>
Frankly, the best practice is "don't have that many columns". Since you
do, I posit that you are just going to have to make decisions (possibly
with experimentation) on your own. Or maybe ask around on a MobilityDB
forum what people using that tool and having these kinds of data structures
do. From a core PostgreSQL perspective you've already deviated from the
model structures that it was designed with in mind.
I'm really confused that you'd want the data value itself to contain a
timestamp that, on a per-row basis, should be the same timestamp that every
other value on the row has. Extracting the timestamp to it own column and
using simpler and atomic data types is how core PostgreSQL and the
relational model normalization recommend dealing with this situation. Then
you just break up the attributes of a similar nature into their own tables
based upon their shared nature. In almost all cases relying on "main"
storage.

David J.