pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes max size of varchar influence index size
4+ messages / 3 participants
[nested] [flat]
* Does max size of varchar influence index size
@ 2008-06-30 16:57 Franck Routier <franck.routier@axege.com>
2008-06-30 20:24 ` Re: Does max size of varchar influence index size Mark Roberts <mailing_lists@pandapocket.com>
0 siblings, 1 reply; 4+ messages in thread
From: Franck Routier @ 2008-06-30 16:57 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does max size of varchar influence index size
2008-06-30 16:57 Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
@ 2008-06-30 20:24 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-01 15:05 ` Re: Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Roberts @ 2008-06-30 20:24 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does max size of varchar influence index size
2008-06-30 16:57 Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
2008-06-30 20:24 ` Re: Does max size of varchar influence index size Mark Roberts <mailing_lists@pandapocket.com>
@ 2008-07-01 15:05 ` Franck Routier <franck.routier@axege.com>
2008-07-01 15:33 ` Re: Does max size of varchar influence index size Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Franck Routier @ 2008-07-01 15:05 UTC (permalink / raw)
To: pgsql-performance
Le lundi 30 juin 2008 à 13:24 -0700, Mark Roberts a écrit :
Hi Mark,
> Is there any particular reason that you're not using a surrogate key?
Well, human readability is the main reason, no standard way to handle
sequences between databases vendors being the second... (and also
problems when copying data between different instances of the database).
So surrogate keys could be a way, and I am considering this, but I'd
rather avoid it :)
Franck
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does max size of varchar influence index size
2008-06-30 16:57 Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
2008-06-30 20:24 ` Re: Does max size of varchar influence index size Mark Roberts <mailing_lists@pandapocket.com>
2008-07-01 15:05 ` Re: Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
@ 2008-07-01 15:33 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2008-07-01 15:33 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
Franck Routier wrote:
> Le lundi 30 juin 2008 à 13:24 -0700, Mark Roberts a écrit :
>
> Hi Mark,
>
>> Is there any particular reason that you're not using a surrogate key?
>
> Well, human readability is the main reason, no standard way to handle
> sequences between databases vendors being the second... (and also
> problems when copying data between different instances of the database).
>
> So surrogate keys could be a way, and I am considering this, but I'd
> rather avoid it :)
Might be worth looking at 8.3 - that can save you significant space with
short varchar's - the field-length is no longer fixed at 32 bits but can
adjust itself automatically. Apart from the overheads, you need the
space to store the text in each string, not the maximum possible.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-07-01 15:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-30 16:57 Does max size of varchar influence index size Franck Routier <franck.routier@axege.com>
2008-06-30 20:24 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-01 15:05 ` Franck Routier <franck.routier@axege.com>
2008-07-01 15:33 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox