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

int4 vs varchar to store ip addr

From: Pomarede Nicolas <npomarede(at)corp(dot)free(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: int4 vs varchar to store ip addr
Date: 2007-01-29 16:22:22
Message-ID: Pine.LNX.4.64.0701291644110.15162@localhost (view raw, whole thread or download thread mbox)
Lists: pgsql-performance


I have an authorization table that associates 1 customer IP to a service 
IP to determine a TTL (used by a radius server).

table auth
  client varchar(15);
  service varchar(15);
  ttl int4;

client and service are both ip addr.

The number of distinct clients can be rather large (say around 4 million) 
and the number of distinct service around 1000.

table auth can contain between 10 M and 20 M lines.

there's a double index on ( client , service ).

Since I would like to maximize the chance to have the whole table cached 
by the OS (linux), I'd like to reduce the size of the table by replacing 
the varchar by another data type more suited to store ip addr.

I could use PG internal inet/cidr type to store the ip addrs, which would 
take 12 bytes per IP, thus gaining a few bytes per row.

Apart from gaining some bytes, would the btree index scan be faster with 
this data type compared to plain varchar ?

Also, in my case, I don't need the mask provided by inet/cidr ; is there a 
way to store an IPV4 addr directly into an INT4 but using the same syntax 
as varchar or inet/cidr (that is I could use '' for example), 
or should I create my own data type and develop the corresponding function 
to convert from a text input to an int4 storage ?

This would really reduce the size of the table, since it would need 3 int4 
for client/service/ttl and I guess index scan would be faster with int4 
data that with varchar(15) ?

Thanks for any input.



pgsql-performance by date

Next:From: Florian WeimerDate: 2007-01-29 16:26:42
Subject: Re: int4 vs varchar to store ip addr
Previous:From: RonDate: 2007-01-29 16:14:43
Subject: Re: Tuning

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