making a pg store of 'multiple checkboxes' efficient

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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -

Responses

Browse pgsql-general by date

  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