I have a large table (5M items current) that is projected to grow at
the rate of 2.5M a month looking at the current usage trends.
the table represents some core standardized user account attributes ,
while text heavy / unstandardized info lies in other tables.
my issue is this: i'm adding in a 'multiple checkboxes' style field,
and trying to weigh the options for db representation against one
another.
my main concern is speed - this is read heavy , but I am worried to
some degree about disk space (not sure where disk space fits in with
pg, when I used to use mysql the simplest schema change could
drastically effect the disk size though ).
that said , these are my current choices:
option a
bitwise operations
and/or operations to condense checkboxes into searchable field
pro:
super small
fits in 1 table
con:
could not find any docs on the speed of bitwise searches in pg
option b
secondary table with bools
create table extends( account_id , option_1_bool , option_2_bool )
pro:
1 join , fast search on bools
con:
PITA to maintain/extend
option c
mapping table
create table mapping ( account_id , option_id )
pro:
extensible
con:
slow speed - needs multiple joins , records all over
I'd personally lean towards option a or b . anyone have suggestions ?
thanks.
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
