Experience with storing IP addresses in PostgreSQL

Description of the problem

More than once, our team in Karuna faced the challenge of storing and using IP addresses in a database. Let's assume that there is a typical task: you need to parse a huge number of address ranges (~ 300k) from a  known resource , and then determine the country by the client's IP address. It seems nothing special. This can be solved quite simply by any of the methods described below at low loads. But if we have thousands of users, or is our service a proxy in front of everyone else? In this case, you don't want to be a bottleneck and you have to fight for every fraction of a second.

A little about addressing

There are 2 types of network addressing

INET ( IP-) — , 1981 1993 . .

CIDR (Classless Inter-Domain Routing,  ) — IP-, .

 address/y,  y — . , /28 , 28 IP- , 4  — , .

,  192.168.5.0/24   192.168.5.1  192.168.5.254,  192.168.5.0 —  192.168.5.255 — .

inet cidr

PostgreSQL 2 IP- : inet  cidr.  inet/cidr.

 inet  , .  address/y.  y , 32 ( IPv4), .

 cidr  IPv4 ( IPv6).  address/y.  y  , (INET).

,  inet  ,  cidr . /8,  cidr , 24 , inet  . , 255.0.0.2/8  cidr .. 255.0.0.0  ( 2 ). 255.128.128.7/24, 255.255.255.255/31 — ,  inet  .

-?

(MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). IP-:

CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

(1.000.000) IP- :

DO
$$
DECLARE 
 i RECORD;
BEGIN
 FOR i IN 1..1000000 LOOP
  PERFORM country_id FROM ip_ranges WHERE ip_range >>= ‘{random_ip}’;
 end loop;
END;
$$
;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

.

ip4r

—  ip4r, .

, , PostgreSQL. . , .

 38 .

( ?)

 nginx,  geo , IP- .  docker-compose.yml:

version: '3.7'

services:
  web:
    image: nginx:latest
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf
      - ./GeoIP.dat:/var/geo/GeoIP.dat
      - ./geo.conf:/var/geo/geo.conf
    ports:
      - "8080:80"
    environment:
      - NGINX_PORT=80
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

 nginx:

http {
        ...
    geo $geo {
        default        NONE;
        include        /var/geo/geo.conf;
    }
    geoip_country /var/geo/GeoIP.dat;
        ...
    server {
        ...
        location / {
            ...
            add_header Geo-By-File $geo;
            add_header Geo-By-Binary $geoip_country_code;
        }
    }
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

,  $geo,  geo.conf :

128.0.0.0/1 US;
...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

 GeoIP.dat  , ($geoip_country_code).

, . , ( , , ..).

, PostgreSQL — . , .

, ( ). , .. , - .

, , ,  inet  cidr, .  ip4r  ~20 .