Re: Whither 1:1? - Mailing list pgsql-general
On Fri, Jun 1, 2018 at 1:25 PM, Guyren Howe <guyren@gmail.com> wrote:
On Jun 1, 2018, at 10:16 , Olivier Gautherot <olivier@gautherot.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
pgsql-general by date: