Thread: IPv4 addresses, unsigned integers, space

IPv4 addresses, unsigned integers, space

From
Florian Weimer
Date:
If I switched from signed integers to unsigned integers (and from INET
to "real" IPv4 addresses, consisting of the relevant 32 bits only) I
think I could save about 25% of my table size.

Does PostgreSQL already implement these data types?  I don't think so.
If I succeed in implementing them, would you accept a patch?

Re: IPv4 addresses, unsigned integers, space

From
Bruno Wolff III
Date:
On Tue, Jul 15, 2003 at 12:59:34 +0200,
  Florian Weimer <fw@deneb.enyo.de> wrote:
> If I switched from signed integers to unsigned integers (and from INET
> to "real" IPv4 addresses, consisting of the relevant 32 bits only) I
> think I could save about 25% of my table size.
>
> Does PostgreSQL already implement these data types?  I don't think so.
> If I succeed in implementing them, would you accept a patch?

You can have unsigned integers using a domain with a check constraint.

Re: IPv4 addresses, unsigned integers, space

From
Tom Lane
Date:
Florian Weimer <fw@deneb.enyo.de> writes:
> If I switched from signed integers to unsigned integers (and from INET
> to "real" IPv4 addresses, consisting of the relevant 32 bits only) I
> think I could save about 25% of my table size.

> Does PostgreSQL already implement these data types?  I don't think so.
> If I succeed in implementing them, would you accept a patch?

I doubt you will find any enthusiasm for a dumbed-down INET type,
considering that IPv6 capability will be increasingly necessary in
the future.

As for unsigned ints, I have no objection to 'em in principle, but
in practice we have more than enough problems already deducing the
appropriate type for a numeric constant.  Unless you've got a super
new solution to that set of problems, adding unsigned ints to the
numeric hierarchy is going to be unmanageable.

            regards, tom lane

Re: IPv4 addresses, unsigned integers, space

From
Florian Weimer
Date:
Bruno Wolff III <bruno@wolff.to> writes:

>> Does PostgreSQL already implement these data types?  I don't think so.
>> If I succeed in implementing them, would you accept a patch?
>
> You can have unsigned integers using a domain with a check constraint.

They take twice as much storage as necessary.

Re: IPv4 addresses, unsigned integers, space

From
Bruno Wolff III
Date:
On Tue, Jul 15, 2003 at 22:12:13 +0200,
  Florian Weimer <fw@deneb.enyo.de> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
> >> Does PostgreSQL already implement these data types?  I don't think so.
> >> If I succeed in implementing them, would you accept a patch?
> >
> > You can have unsigned integers using a domain with a check constraint.
>
> They take twice as much storage as necessary.

This depends on the range of numbers you need. If you specifically need
integers greater than 2^31-1 and don't need any greater than 2^32-1, then
that is true. In most cases there won't be a per item space penalty.

Re: IPv4 addresses, unsigned integers, space

From
Jim Crate
Date:
on 7/15/03, Florian Weimer <fw@deneb.enyo.de> wrote:

>If I switched from signed integers to unsigned integers (and from INET
>to "real" IPv4 addresses, consisting of the relevant 32 bits only) I
>think I could save about 25% of my table size.

Why do you need unsigned ints to hold IP addresses?  Signed ints are also 32
bits wide and hold IPv4 addresses just fine.  What difference does it make if IP
addresses with a class A higher than 127 appear as negative numbers?

Here's a couple of convenience function that convert between integer and dotted
notation.  These functions work fine with the signed integers we have in
PostgreSQL.

<http://www.deepskytech.com/downloads/misc/ip_functions.txt>

--
Jim Crate
Deep Sky Technologies, Inc.

Re: IPv4 addresses, unsigned integers, space

From
Florian Weimer
Date:
Jim Crate <jcrate@deepskytech.com> writes:

> on 7/15/03, Florian Weimer <fw@deneb.enyo.de> wrote:
>
>>If I switched from signed integers to unsigned integers (and from INET
>>to "real" IPv4 addresses, consisting of the relevant 32 bits only) I
>>think I could save about 25% of my table size.
>
> Why do you need unsigned ints to hold IP addresses?

This is a misunderstanding.  I could use both space-conservative IP
addresses and unsigned integers.

> What difference does it make if IP addresses with a class A higher
> than 127 appear as negative numbers?

The mapping does not preserve ordering if not done carefully.