int4 vs varchar to store ip addr - Mailing list pgsql-performance

From Pomarede Nicolas
Subject int4 vs varchar to store ip addr
Date
Msg-id Pine.LNX.4.64.0701291644110.15162@localhost
Whole thread Raw
Responses Re: int4 vs varchar to store ip addr  (Florian Weimer <fweimer@bfk.de>)
Re: int4 vs varchar to store ip addr  (Cosimo Streppone <cosimo@streppone.it>)
List pgsql-performance

Hello,

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 '192.12.18.1' 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.


Nicolas

pgsql-performance by date:

Previous
From: Ron
Date:
Subject: Re: Tuning
Next
From: Florian Weimer
Date:
Subject: Re: int4 vs varchar to store ip addr