Search Postgresql Archives

Re: Table Inheritance / VARCHAR search question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux