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
