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 _______________________________________________ Kea-users mailing list [email protected] https://lists.isc.org/mailman/listinfo/kea-users
