Re: Table Inheritance / VARCHAR search question

On Tue, Sep 19, 2006 at 02:15:03PM -0400, Jonathan Vanasco wrote:
> Hi,
>
> I'm hoping someone on this list can save me some unnecessary
> benchmarking today

<snip>

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

I don't know about about mysql, but on postgres NULL fields take up
negligable space on disk. Also here there isn't really any space/speed
difference between text/char/varchar.

> 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

The inherited table will end up being option(a) and the parent table
will be empty. Not a good idea.

> 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

How often do you need the three other columns? It's not entirely clear
what the usage pattern in but if you're always going to be looking up
the table3 anyway, why split it out?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.