Table Inheritance / VARCHAR search question

I would say that splitting the data will work ok if ( and only if ) you
can remove some duplication of data and therefore reduce disk usage. If
it won't, it'll not really save you anything, and it may increase disk
space with the additional db overheads of another set of table meta
information, indexes on the new table etc etc.

I have a similar width table with 7.5M rows ( no nulls ) and I get
reasonable search times running on a 2GHz AMD64 with 2GB ram.

Varchars on disk ( if I remember correctly ) take little or no space when
they are null. ( A pg developer may need to comment for postgres ) It
definitely is for Oracle ( and a good reason to rebuild tables regularly
if you have a lot of inserts / updates and deletes going on ).

Cheers

Simon

pgsql-general-owner@postgresql.org wrote on 20/09/2006 06:15:03 a.m.:

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

****************************************************************************
Statement of confidentiality: This e-mail message and any accompanying
attachments may contain information that is IN-CONFIDENCE and subject to
legal privilege.
If you are not the intended recipient, do not read, use, disseminate,
distribute or copy this message or attachments.
If you have received this message in error, please notify the sender
immediately and delete this message.
****************************************************************************

*************************************************************************************
This e-mail message has been scanned for Viruses and Content and cleared
by the Ministry of Health's Content and Virus Filtering Gateway
*************************************************************************************

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@svana.org> http://svana.org/kleptog/