From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | making a pg store of 'multiple checkboxes' efficient |
Date: | 2007-04-18 07:39:29 |
Message-ID: | AD1A9176-7DC6-4409-9110-AC4481E47996@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2007-04-18 08:28:33 | Re: making a pg store of 'multiple checkboxes' efficient |
Previous Message | Thomas F. O'Connell | 2007-04-18 03:45:53 | Re: Status of Postgres 8.2.4 and pg_standby |