Best datatype for store IP addresses in Vertica

May_1

What is the best data type for a column that stores IP addresses (which could be IPv4 or IPv6)? Comparison between VARBINARY and VARCHAR in terms of storage and search performance would be greatly appreciated.

[Deleted User]

If you're storing the IP address as a string, I think you'll find that the performance and storage footprint are very similar between VARBINARY and VARCHAR. I haven't personally compared the string form to the raw-binary form in the specific case of IPv6 addresses. Maybe others here have? It's interesting because the binary format is fixed at 128 bits, but there's a standard compact text notation for IPv6 that can potentially be much shorter. So it'll depend heavily on what your data looks like there, and how it interacts with Vertica's compression algorithms. If you had only IPv4 addresses, I would actually recommend an INTEGER type. Unfortunately, Vertica's biggest integer is only 64 bits; not large enough for an IPv6 address.

[Deleted User]

I know a Vertica customer who has been using BINARY(16) for those IPv6 values with good effect. There are built-in Vertica functions such as TO_HEX, HEX_TO_BINARY, etc. which can be handy. Be careful about loading those IPv6 fields. One example is to use the following in your COPY scripts: ..... column option ( destination_binary_ip format 'HEX', source_binary_ip format 'HEX') ....