From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Table Inheritance / VARCHAR search question |
Date: | 2006-09-19 18:15:03 |
Message-ID: | 0640DDC8-CF6F-4095-BA2F-6D1E1CD86E06@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I'm hoping someone on this list can save me some unnecessary
benchmarking today
I have the following table in my system
BIGSERIAL , INT , INT, VARCHAR(32)
There are currently 1M records , it will grow to be much much
bigger. It's used as a search/dispatch table, and gets the most
traffic on my entire app.
I'm working on some new functionality, which will require the same 3
colums as above but with 3 new VARCHAR(32) columns
BIGSERIAL , INT , INT, +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32)
ie, the new function shares the same serial and the the 2 INT columns
I'm trying to get this to work efficiently on speed and on disk space.
i've figured that my options are:
a) one table with everything in it
pro:
simple
possible con:
when i had something similar in mysql 4 years ago, i had to make
all the varchars chars , because speed was awful. under this system,
80% of the 3 new VARCHAR fields will always be null, so that disk
waste will be noticable. thats only IF there is a speed issue with
VARCHAR searching.
b) keep current table, create new table that inherits and has the 3
new fields
pro: simple
possible con:
i can't find any documentation on how an inherit works behind the
scenes. is the data cloned into the new table? is there a join on
every search? if this is constantly doing a join behind the scenes,
thats probably not going to work for me
c) move to a 3 table structure
table1- serial
table2 - current table, bigserial is not bigint
table3- bigint + 3 varchars
pro:
obviously will work
con:
a lot of restructuring
i was going to have both table share a seqeunce, but then i
remembered that the id is foreign keyed by other tables
if anyone can offer a suggestion, i'd be greatly appreciative
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2006-09-19 18:15:22 | Load a csv file into a pgsql table |
Previous Message | Marc Evans | 2006-09-19 18:12:53 | Re: Odd behavior observed |