Re: Table Inheritance / VARCHAR search question - Mailing list pgsql-general

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