Thread: IPv4 addresses, unsigned integers, space
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?
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.
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
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.
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.
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.
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.