PostgreSQL provides developers with numerous data types with specialized functions. In this article, we focus on the network address data type and show you how it works, when to use it, and how it can help you when storing IP addresses in your database.
IP addresses are the lingua franca of the internet giving everything a place, even if it isn't a specific place. By recording IP addresses you can, for example, determine patterns of behavior which may be malicious. Or you may want to efficiently store your network's layout. Whichever, PostgreSQL has the data types you need.
In this article, we'll take a look at Network Address data types in PostgreSQL, namely the INET (Internet Protocol) and CIDR (Classless Internet Domain Routing) data types to store IPv4 and IPv6 addresses. We'll cover the differences between how PostgreSQL stores INET and CIDR addresses, the input error checking capabilities that come out of the box, and some of the functions that are available for these data types.
Without further adieu, let's take a look at INET and CIDR data types.
INET and CIDR
CIDR and INET are the two data types that store IP addresses in PostgreSQL. The data types come with their own input error checking capabilities as well as their own operators and functions. It may be confusing trying to figure out when to use either the CIDR or INET data type in your PostgreSQL tables, so we'll go over some of the differences between them and when you should use one over the other.
If we're storing IPv4 or IPv6 host addresses, PostgreSQL recommends using the INET data type with an optional netmask. While it's possible to store addresses that represent a network using INET, like
192.10/14, PostgreSQL recommends using CIDR, which we'll discuss further below. For now, let's take a look at how INET data is stored and some of the problems we might run into.
To demonstrate what the INET data type does, we'll start off by creating a table called inet_test with address taking the INET data type.
CREATE TABLE inet_test ( address INET );
Now, let's insert some address values. The first value contains an IPv4 host address with a netmask of 24, second is just an IPv4 host address representing a single host, and the last is a host address with a random netmask value.
INSERT INTO inet_test (address) VALUES ('220.127.116.11/24'); INSERT INTO inet_test (address) VALUES ('18.104.22.168'); INSERT INTO inet_test (address) VALUES ('198.10/8');
SELECT statement, our table will give us the three values we've entered:
address ---------------- 22.214.171.124/24 126.96.36.199 188.8.131.52/8
Notice that the last address we inserted
198.10/8 has zeros added to the address. The INET data type will add the necessary zeros to the IPv4 host address to complete it when we append a netmask value. Without appending a netmask, we'd receive an error telling us that the address is not valid because it's incomplete:
ERROR: invalid input syntax for type inet: "198.10" LINE 1: INSERT INTO inet_test (address) VALUES ('198.10');
Another instance where we can get an error is if we're adding a netmask value that exceeds the number of bits allowed for that IPv4 host address. For example, if we enter another address
198.24/24, we'll get the following error:
INSERT INTO inet_test (address) VALUES ('198.24/24'); ERROR: invalid input syntax for type inet: "198.24/24" LINE 1: insert into inet_test (address) values ('198.24/24');
The last two examples, however, are cases where the INET data type is not recommended to be used. If we're storing IPv4 or IPv6 addresses representing a network, PostgreSQL recommends using the CIDR data type because it follows its own conventions and checks for errors a little differently. So, let's see what CIDR is all about ...
PostgreSQL recommends that the CIDR data type should be used when storing addresses that represent a network. Unlike INET, the CIDR data type checks whether there are nonzero bits to the right of the netmask on insertion. If there are, then it will give us an error and no values will be inserted.
Let's first look at an example of how the CIDR data type works. First, we'll create a table cidr_test with the address as the CIDR data type.
CREATE TABLE cidr_test ( address CIDR );
We'll then insert some sample network addresses with and without a netmask. If we don't enter the netmask bits, then the CIDR data type will revert to a classful network numbering system, which is an older numbering system that is not recommended to be used. However, for the sake of understanding the differences, we've put together addresses in pairs.
INSERT INTO cidr_test (address) VALUES ('192/8'); INSERT INTO cidr_test (address) VALUES ('192'); INSERT INTO cidr_test (address) VALUES ('192.168/16'); INSERT INTO cidr_test (address) VALUES ('192.168'); INSERT INTO cidr_test (address) VALUES ('192.168.10/24'); INSERT INTO cidr_test (address) VALUES ('192.168.10'); INSERT INTO cidr_test (address) VALUES ('192.168.100.128/25'); INSERT INTO cidr_test (address) VALUES ('192.168.100.128');
Selecting all the values that we've inserted into the database shows us some slight differences in how PostgreSQL has stored them:
address -------------------- 192.0.0.0/8 192.0.0.0/24 192.168.0.0/16 192.168.0.0/24 192.168.10.0/24 192.168.10.0/24 192.168.100.128/25 192.168.100.128/32
The stored addresses using CIDR values are quite compelling, but they don't really tell us much about how the network and broadcast addresses are conceived. To determine these addresses, we can turn to PostgreSQL's specialized functions for network address data types.
Network Address Functions
PostgreSQL has several network address functions that are available for INET and CIDR data types. Of particular interest for us are the
network functions since they provide the broadcast address, the IP address, the network netmask, and the network address of the INET and CIDR addresses we inserted. If PostgreSQL didn't have these functions, we'd have to manually figure them out, or we'd be dependent on an online resource that figures them out for us. Let's take a look at what these functions provide us by looking at our existing datasets.
SELECT address, host(address), broadcast(address), netmask(address), network(address) FROM inet_test;
This gives us:
address | host | broadcast | netmask | network ----------------+-------------+-------------------+-----------------+---------------- 184.108.40.206/24 | 220.127.116.11 | 18.104.22.168/24 | 255.255.255.0 | 22.214.171.124/24 126.96.36.199 | 188.8.131.52 | 184.108.40.206 | 255.255.255.255 | 220.127.116.11/32 18.104.22.168/8 | 22.214.171.124 | 126.96.36.199/8 | 255.0.0.0 | 188.8.131.52/8
From this dataset, the interesting values come from the first two addresses. For the first address
184.108.40.206/24, our addresses for the broadcast, netmask, and network addresses are different from those in the second address
220.127.116.11 represents a single host, which is shown via the
network function since it appends a netmask of 32 to the address in the
SELECT address, host(address), broadcast(address), netmask(address), network(address) FROM cidr_test;
This gives us:
address | host | broadcast | netmask | network --------------------+-----------------+--------------------+-----------------+-------------------- 192.0.0.0/8 | 192.0.0.0 | 18.104.22.168/8 | 255.0.0.0 | 192.0.0.0/8 192.0.0.0/24 | 192.0.0.0 | 22.214.171.124/24 | 255.255.255.0 | 192.0.0.0/24 192.168.0.0/16 | 192.168.0.0 | 192.168.255.255/16 | 255.255.0.0 | 192.168.0.0/16 192.168.0.0/24 | 192.168.0.0 | 192.168.0.255/24 | 255.255.255.0 | 192.168.0.0/24 192.168.10.0/24 | 192.168.10.0 | 192.168.10.255/24 | 255.255.255.0 | 192.168.10.0/24 192.168.10.0/24 | 192.168.10.0 | 192.168.10.255/24 | 255.255.255.0 | 192.168.10.0/24 192.168.100.128/25 | 192.168.100.128 | 192.168.100.255/25 | 255.255.255.128 | 192.168.100.128/25 192.168.100.128/32 | 192.168.100.128 | 192.168.100.128 | 255.255.255.255 | 192.168.100.128/32
Always make sure to include the CIDR netmask to the network addresses. Examining the output of our query above, we can see significant changes that have occurred in the broadcast and netmask fields where the addresses differ significantly. The exception is the third pair of addresses
192.168.10 where we seem to have gotten lucky since the addresses are translated the same using CIDR and the older classful system.
Indexing Network Addresses
So, what about query performance? We can set up indexes on INET and CIDR addresses using a Btree index by default. But to increase performance we can set up GINand GiST indexes on INET and CIDR columns using built-in operator classes for the indexes.
To set up a GiST index on INET or CIDR data, we'd write:
CREATE INDEX idx_name ON cidr_test USING GIST(address inet_ops);
This will index both INET and CIDR datatypes and most operators, excluding bitwise and addition and subtraction operators.
If we insert 5000 random addresses with random netmasks we can then test the difference between the Btree and GiST indexes. We are inserting this number of addresses in order to avoid PostgreSQL running a sequential scan over an indexed scan when using the
EXPLAIN command. Using a Btree index on the following query:
EXPLAIN SELECT * FROM cidr_test WHERE address <<= '154/8';
We will get the following result:
QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on cidr_test (cost=4.52..28.79 rows=9 width=7) Filter: ((address)::inet <<= '126.96.36.199/8'::inet) -> Bitmap Index Scan on cidr_test_address_idx (cost=0.00..4.52 rows=24 width=0) Index Cond: (((address)::inet >= '188.8.131.52/8'::inet) AND ((address)::inet <= '184.108.40.206'::inet))
If we drop the Btree index and use the GiST index we've created on the same query, we'd get something like:
QUERY PLAN ---------------------------------------------------------------------------- Bitmap Heap Scan on cidr_test (cost=4.22..22.18 rows=9 width=7) Recheck Cond: ((address)::inet <<= '220.127.116.11/8'::inet) -> Bitmap Index Scan on idx_gist_cidr (cost=0.00..4.21 rows=9 width=0) Index Cond: ((address)::inet <<= '18.104.22.168/8'::inet)
While it's not a significant difference in the query time, querying over more data may potentially save us seconds using the GiST over the Btree index.
Creating a GIN index is a little more tricky in that you can only set up an index on an array of values which have to either be all CIDR or INET addresses. Nonetheless, to set up these indexes we'd write:
CREATE INDEX idx_name_cidr ON cidr_test USING GIN(address_array _cidr_ops); -- OR CREATE INDEX idx_name_inet ON inet_test USING GIN(address_array _inet_ops);
It's been suggested that a more performative solution to indexing network addresses is the IP4R project, which extends the native PostgreSQL network address data types. It provides additional network address types, as well as additional support for GiST indexing.
Addressing What We Did
Understanding the different data types that PostgreSQL provides and how they work gives us tools that enable us to expand on how our data is represented and manipulated inside a database. With a new understanding of how the network address data type works, we can use INET and CIDR data types effectively and understand the various ways network address are interpreted and addressed in PostgreSQL.
If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at firstname.lastname@example.org. We're happy to hear from you.