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

[Shane Kerr]

Tomek and all,

Here are some more specific/detailed observations/questions about the
PostgreSQL schema for Kea.

I haven't looked at the relational model for the database or the SQL
statements that Kea actually uses and verified data plan used by the
server using EXPLAIN, but I figured I'd mention these now since they
seem straightforward.

------
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.

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.

------
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.

It's not as efficient as a 4-byte value, but 7 is better than 8? :)

(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.)

------
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.

----
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.)

Cheers,

--
Shane