Search Postgresql Archives

Re: Best practices for aggregate table design

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux