Table Inheritance / VARCHAR search question

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

Responses

Browse pgsql-general by date

  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