pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinet versus text for ip addresses
2+ messages / 2 participants
[nested] [flat]
* inet versus text for ip addresses
@ 2003-07-05 00:08 Dan Langille <dan@langille.org>
0 siblings, 1 reply; 2+ messages in thread
From: Dan Langille @ 2003-07-05 00:08 UTC (permalink / raw)
To: pgsql-sql
The PostgreSQL inet datatype stores an holds an IP host address, and
optionally the identity of the subnet it is in, all in one field.
This requires 12 bytes.
Using my "random" data of approximately 8000 IP addresses collected
during previous polls, I've found the average length of an IP address
is 13.1 bytes. An integer requires 4 bytes.
First question: Why not store an option to store just an IP address?
That should require less than the 12 bytes for inet.
On to the real question:
The existing tables are:
create table recount_ips
(
ipid serial not null,
ipaddress inet not null,
primary key (ipid)
);
create unique index recount_ips_ip_address on recount_ips
(ipaddress);
create table recount_iptopolls
(
pollid integer not null,
ipid integer not null,
primary key (pollid, ipid)
);
alter table recount_iptopolls
add foreign key (pollid)
references recount_polls (pollid) on update restrict on delete
restrict;
alter table recount_iptopolls
add foreign key (ipid)
references recount_ips (ipid) on update restrict on delete
restrict;
I think a better solution is one table:
create table recount_iptopolls
(
pollid integer not null,
ipaddress inet not null,
primary key (pollid, ipaddress)
);
alter table recount_iptopolls
add foreign key (pollid)
references recount_polls (pollid) on update restrict on delete
restrict;
It removes a table and the associated primary key, and removed a
foreign key from the modified recount_iptopolls table.
Comments?
--
Dan Langille : http://www.langille.org/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: inet versus text for ip addresses
@ 2003-07-21 03:47 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2003-07-21 03:47 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
Dan Langille wrote:
> The PostgreSQL inet datatype stores an holds an IP host address, and
> optionally the identity of the subnet it is in, all in one field.
> This requires 12 bytes.
>
> Using my "random" data of approximately 8000 IP addresses collected
> during previous polls, I've found the average length of an IP address
> is 13.1 bytes. An integer requires 4 bytes.
>
> First question: Why not store an option to store just an IP address?
> That should require less than the 12 bytes for inet.
We store inet and cidr in similar structures, and they are of variable
length (4 byte overhead):
/*
* This is the internal storage format for IP addresses
* (both INET and CIDR datatypes):
*/
typedef struct
{
unsigned char family;
unsigned char bits;
unsigned char type;
union
{
unsigned int ipv4_addr; /* network byte order */
/* add IPV6 address type here */
} addr;
} inet_struct;
/*
* Both INET and CIDR addresses are represented within Postgres as varlena
* objects, ie, there is a varlena header (basically a length word) in front
* of the struct type depicted above.
*
* Although these types are variable-length, the maximum length
* is pretty short, so we make no provision for TOASTing them.
*/
typedef struct varlena inet;
In 7.4, we support IPv6, so they will be even larger.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-21 03:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-05 00:08 inet versus text for ip addresses Dan Langille <dan@langille.org>
2003-07-21 03:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
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