Re: Whither 1:1?

On Fri, Jun 1, 2018 at 1:25 PM, Guyren Howe <guyren(at)gmail(dot)com> wrote:

> On Jun 1, 2018, at 10:16 , Olivier Gautherot <olivier(at)gautherot(dot)net>
> wrote:
>
>
> You will get a benefit in terms of space only if the optional fields in
> the second table exist in a reduced number of instances - and the second
> table is significantly wider. This can make a difference on big tables but
> this gain may be offset by the cost of the join. In this perspective, I
> don’t think that there is a clear benefit or drawback: it should be
> evaluated on a case-by-case basis.
>
>
> It seems to me that people take time to catch up with modern hardware
> reality. SSDs reduce seek time to virtually zero. Surely, joins are now
> much, much cheaper. If so, I’m inclined to describe wide tables as a
> premature optimization.
>

Guyren, I'm only saying it is not a one-size-fits-all and we don't all have
last-generation hardware to run our systems on. I actually have a use-case
for the structure you describe, which supports optional fields in a table:
the main one is partitioned into 15GB monthly tables with 30+ millions
rows. The parallel table is used for devices that generate additional
information but are a small subset of the fleet. This way, I could prevent
the overhead of 150 columns across the board (the main table has around
30). By keeping things simple (I mean a single table), you get more bloat
but you can reduce the response time and may eventually be able to run on a
smaller, cheaper server. It is really a project-specific design issue.

Olivier Gautherot