Обсуждение: Table Inheritance / VARCHAR search question

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

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/
> From each according to his ability. To each according to his ability to litigate.

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