SQL has never been good at handling dynamic columns — it can easily process tables with a fixed number of columns, but dynamic
column handling has always been a challenge. (Dynamic columns are often used to implement user-defined fields.) One approach has been to
define columns for every possible value that might be stored in a row; this leads to sparse tables containing mostly NULL
values. Because Postgres stores NULL values in a single
bit
, there is little overhead to this approach. However, it is hard to call these dynamic columns since every new column requires an
ALTER TABLE … ADD COLUMN command.

Another approach is to use entity/attribute/value layout,
where each value is stored in its own row. This obviously increases storage requirements, has
performance challenges, and adds
complexity to SQL queries.

Fortunately, Postgres has various options to efficiently handle dynamic columns. The simplest solution is
arrays which allow multiple values of the same data type to be stored in a
single field. This assumes that the values are independent, or that their ordinal position in the array is sufficiently meaningful.

Continue Reading »