[Kea-users] PostgreSQL schema observations (was Host reservations in PostgreSQL)

[Tomek Mrugalski]

Hi Shane!
Sorry it took me so long to finally get round to it, but here are my
answers. Please note that I'm not a DB expert by any stretch of its
definition, so I'd love if others, more experienced DB aficionados could
share their opinions.

On 04.01.2016 15:43, Shane Kerr wrote:
> Here are some more specific/detailed observations/questions about the
> PostgreSQL schema for Kea.

> IPv6 addresses are stored as VARCHAR(39). 
> 
> In PostgreSQL probably the inet type should be used for this. It will
> consume 19 bytes per row instead of 39, and you can do comparisons and
> lots of other operations on the values:
> 
> http://www.postgresql.org/docs/current/static/datatype-net-types.html
> 
> To be complete, perhaps adding a constraint that checks that
> family(address) is 6 would be useful.
Seems good to me. One aspect of the determination whether to use a
feature like this is how widely it is supported. I just checked that
it's supported all the way back in PostgreSQL 9.1, so we should be cool
here.

> Note that one could also get rid of the prefix_len column in PostgreSQL
> because the inet type includes the prefix, so you'd actually save 21
> bytes per row.
Cool. I'm not really that concerned with bytes saving. Memory is getting
cheaper and cheaper and on small embedded devices you typically have
only a handful of leases. What's more important is that it should
be easy to access by sysadmins. We can sometimes save a byte or two by
using binary format, but that would make the DB much more difficult to
access. Anyway, I played around a bit with inet type and it seems to be
easy to use and intuitive.

> ------
> Likewise for IPv4, addresses are stored as BIGINT. I guess this is
> because PostgreSQL has chosen not to implement UNSIGNED types, and the
> MySQL schema uses UNSIGNED INT as the data type for this.
> 
> In PostgreSQL probably the inet type should be used for this. It's 7
> bytes, and actually stores IPv4 addresses.
> 
> http://www.postgresql.org/docs/current/static/datatype-net-types.html
> 
> To be complete, perhaps adding a constraint that checks that
> family(address) is 4 and masklen(address) is 32 would be useful.
Seems good.

> It's not as efficient as a 4-byte value, but 7 is better than 8? :)
It's also misaligned with everything except a single byte, so I have a
hunch that this gain is rather virtual. :)

> (Note that one could also use a normal INT for these values if the
> extra 3 bytes were important. It would mean using negative values in
> the database, but that's not a show-stopper.)
I suppose David Carlier, who sent the initial PostgreSQL patch (we
remember and are grateful!) and whoever reviewed it, simply used the
BIGINT type, because it was the easiest way. I'm ok with replacing this
with inet.

Please keep in mind that we'll need to provide upgrade scripts for
existing databases. If BIGINT can be automatically migrated to inet by
ALTER table, then it's cool. If not, the upgrade script will have to
slightly more clever about it.

> ------
> Another use of BIGINT is for valid_lifetime in lease4. This reflects a
> DHCP field so it has to be BIGINT. It might be worthwhile to restrict
> the allowed values with a constraint to make sure that it is always
> between 0 and 0xffffffff.
Agree.

> ----
> Yet another use of BIGINT is for subnet_id. Do we really expect more
> than 2^32 subnets? Can we maybe use INT for this where it appears? (It
> is UNSIGNED INT in the MySQL, and my guess is that it was blindly
> converted to BIGINT for PostgreSQL.)
Nope. 32 bits is enough. This is what Kea is using internally.

Tomek