Skip site navigation (1) Skip section navigation (2)

Re: Does max size of varchar influence index size

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: Franck Routier <franck(dot)routier(at)axege(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Does max size of varchar influence index size
Date: 2008-06-30 20:24:54
Message-ID: 1214857494.6049.76.camel@localhost (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
On Mon, 2008-06-30 at 18:57 +0200, Franck Routier wrote:
> Hi,
> 
> I have problems with my database becoming huge in size (around 150 GB
> right now, and 2/3 for only three tables, each having around 30 millions
> tuples. Space is spent mainly on indices.).
> 
> I have a lot of multi-column varchar primary keys (natural keys), and
> lot of foreign keys on these tables and thus a lot of indices.
> 
> When using VARCHAR, we defaulted to VARCHAR(32) (because on _some_ of
> the identifiers, we have to apply md5).
> 
> We assumed that using VARCHAR(32) but having values at most 4 characters
> long (for example) wouldn't influence indices size, ie it would be the
> same as using VARCHAR(4) to keep the example.
> 
> Now I really doubt if we were right :)
> 
> So, what should we expect ? And are there other factors influencing
> indices size ?
> 
> Thanks,
> Franck

Is there any particular reason that you're not using a surrogate key?  I
found that switching from natural to surrogate keys in a similar
situation made the indexes not only smaller, but faster.

It really only became an issue after our individual tables got larger
than 20-25G, but I think we got lucky and headed the issue off at the
pass.

I think it should be fairly trivial* to set up a test case using
pg_total_relation_size() to determine whether your suspicions are
correct.

-Mark

* It may not be as trivial as I say, or I'd have done it in the 5
minutes it took to write this email.


In response to

Responses

pgsql-performance by date

Next:From: John BeaverDate: 2008-07-01 00:37:58
Subject: Re: sequence scan problem
Previous:From: Alvaro HerreraDate: 2008-06-30 19:04:19
Subject: Re: VACUUM ANALYZE blocking both reads and writes to atable

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group