PolarDB:ip4r

Overview

ip4r is a third-party extension supported by PolarDB for PostgreSQL. It offers data types to store IPv4 or IPv6 addresses. Different from the inet/cidr data types offered by PostgreSQL, the ip4r extension supports index scans when a query uses the >>= (contains or equals) operator. In addition, the ip4r extension has the following advantages over the built-in network address types of PostgreSQL:

• Less semantic overload: It offers specialized data types for handling a netblock and a particular IP address.

• Lower overheads: PostgreSQL uses variable-length data types to store both IPv6 and IPv4 addresses. This leads to large overheads in use cases that involve IPv4 addresses only. The ip4r extension offers fixed-length data types tailored for IPv4 or IPv6 addresses.

Data type

ip4r provides a wide range of data types to store and represent IPv4 and IPv6 addresses:

Data types for single IP addresses and type conversion

The following three data types are used to store single IP addresses:

• ip4: Accepts input in the form of nnn.nnn.nnn.nnn. It stores an IP address as a 32-bit unsigned integer.

• ip6: Accepts input in the form of standard hexadecimal representation for an IPv6 address. It stores an IP address as two 64-bit values.

• ipaddress: Accepts input which is valid for either ip4 or ip6.

The following table describes type conversions for the preceding three types in ip4r.

Data types for netblocks and type conversion

The following three data types are used to store a netblock:

• ip4r: Used to store an IPv4 netblock, such as 192.0.2.100–192.0.2.200. Also accepts input in the form of CIDR blocks such as 192.0.2.0/24, which is equivalent to 192.0.2.0–192.0.2.255.

• ip6r: Used to store an IPv6 netblock, such as 2001::1234–2001::2000:0000. Also accepts input in the form of CIDR blocks such as 2001::/112, which is equivalent to 2001::–2001::ffff.

• iprange: Accepts input which is valid for either ip4r or ip6r.

The following table describes type conversions for the preceding three types in ip4r.

Usage

Create the ip4r extension

CREATE EXTENSION ip4r;

Create a test table and import data to the table

CREATE TABLE ipranges (r iprange, r4 ip4r, r6 ip6r);
INSERT INTO ipranges
    SELECT r, null, r
    FROM (
        SELECT ip6r(regexp_replace(ls, E'(....(?!$))', E'\\1:', 'g')::ip6,
               regexp_replace(substring(ls FOR n + 1) || substring(us FROM n + 2),
               E'(....(?!$))', E'\\1:', 'g')::ip6) AS r
        FROM (
            SELECT md5(i || ' lower 1') AS ls,
                   md5(i || ' upper 1') AS us,
                   (i % 11) + (i/11 % 11) + (i/121 % 11) AS n
            FROM generate_series(1,13310) i) s1) s2;

Create a GiST index

CREATE INDEX ipranges_r ON ipranges USING gist (r);

Use the >>= (contains or equals) operator

EXPLAIN (COSTS OFF) SELECT * FROM ipranges WHERE r >>= '5555::' ORDER BY r;
                     QUERY PLAN
-----------------------------------------------------
 Sort
   Sort Key: r
   ->  Bitmap Heap Scan on ipranges
         Recheck Cond: (r >>= '5555::'::iprange)
         ->  Bitmap Index Scan on ipranges_r
               Index Cond: (r >>= '5555::'::iprange)
(6 rows)

Remove the extension

DROP EXTENSION ip4r;