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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Storage for multiple variable-length attributes in a single row
Дата
Msg-id CAKFQuwYbRydgj2T0jV0Y+efKqamK9Og7o4ONDQn-sdO32c=wQg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Storage for multiple variable-length attributes in a single row  (Esteban Zimanyi <estebanzimanyi@gmail.com>)
Ответы Re: Storage for multiple variable-length attributes in a single row  (Julien Rouhaud <rjuju123@gmail.com>)
Re: Storage for multiple variable-length attributes in a single row  (Esteban Zimanyi <estebanzimanyi@gmail.com>)
Список pgsql-hackers
On Mon, Feb 7, 2022 at 9:58 AM Esteban Zimanyi <estebanzimanyi@gmail.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.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: [PATCH v2] use has_privs_for_role for predefined roles
Следующее
От: Julien Rouhaud
Дата:
Сообщение: Re: Storage for multiple variable-length attributes in a single row