making a pg store of 'multiple checkboxes' efficient
От | Jonathan Vanasco |
---|---|
Тема | making a pg store of 'multiple checkboxes' efficient |
Дата | |
Msg-id | AD1A9176-7DC6-4409-9110-AC4481E47996@2xlp.com обсуждение исходный текст |
Ответы |
Re: making a pg store of 'multiple checkboxes' efficient
|
Список | 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 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
В списке pgsql-general по дате отправления: