From: | droberts <david(dot)roberts(at)riverbed(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Best practices for aggregate table design |
Date: | 2015-10-06 18:53:22 |
Message-ID: | 1444157602064-5868967.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Okay, so is it safe to say I should use loosely use these guidelines when
deciding whether to model an attribute as a dimension
(type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ?
If you know the number of values for a dimension are fixed (e.g. boolean),
then creating a measure will have benefits of:
- reduced number of rows/storage
- better performance since less indexing/vacuuming
the drawbacks are:
-rigid structure, not very extensible over time (e.g. later realize I need
to also track 'internal' calls).
In my case, I'm now needing to add another measure 'encrypted=true/false',
so my table is starting to look like
month | city_id | state_id | total_calls_inbound | total_calls_outbound |
total_calls_inbound_encr | total_calls_outbound_encr |
getting a bit hairy but the alternative seems like it would start growing
too quickly in rows and more I/O for inserts.
month | city_id | state_id | encrypted | type | total_calls
2015-01 12 2 true, false, 56
2015-01 10 4 true, true, 147
2015-01 null null 201 17 218
--
View this message in context: http://postgresql.nabble.com/Best-practices-for-aggregate-table-design-tp5868940p5868967.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2015-10-06 19:01:27 | Re: [GENERAL] No funciona WITH con mas de 2 sentencias DML |
Previous Message | David G. Johnston | 2015-10-06 18:43:44 | Re: Best practices for aggregate table design |