Обсуждение: [GENERAL] appropriate column for storing ipv4 address

I have to store/search some IP data in Postgres 9.6 and am second-guessing my storage options.

Would anyone mind giving this a quick look for me?

Right now I have two tables, and am just using cidr for both:

    create table tracked_ip_address (
        id SERIAL primary key,
        ip_address CIDR not null
    );

    create table tracked_ip_block (
        id SERIAL primary key,
        block_cidr CIDR not null,
        ownserhip_data TEXT
    );

The types of searching I'm doing:

    1. on tracked_ip_address, I'll search for neighboring ips.
        e.g.
         select * from tracked_ip_address where ip_address << '192.168'::CIDR;
         select * from tracked_ip_address where ip_address << '192.168.1'::CIDR;

    2. on tracked_ip_block, i search/join against the tracked_ip_address to show known ips in a block, or a known block
foran ip. 

i used cidr instead of inet for the ip_address because it saved me a cast on joins and appears to work the same.  was
thatthe right move?  is there a better option? 

thanks in advance.

/ jonathan

On 03/01/2017 08:39 AM, jonathan vanasco wrote:
>
> I have to store/search some IP data in Postgres 9.6 and am second-guessing my storage options.
>
> Would anyone mind giving this a quick look for me?
>
> Right now I have two tables, and am just using cidr for both:

Hi Jonathan,

CIDR seems like a better match to how people think about IPs, but
another option would be to use a custom range type on inet. I wrote a
blog post about that here, including how to use a GiST index to get fast
searches:

http://illuminatedcomputing.com/posts/2016/06/inet-range/

Maybe it will be useful for you! Or maybe there is already some built-in
way to treat cidr columns like ranges?

Paul

> On Mar 1, 2017, at 8:39 AM, jonathan vanasco <postgres@2xlp.com> wrote:
>
>
> I have to store/search some IP data in Postgres 9.6 and am second-guessing my storage options.
>
>
> The types of searching I'm doing:

[...]

>
>     2. on tracked_ip_block, i search/join against the tracked_ip_address to show known ips in a block, or a known
blockfor an ip. 
>
> i used cidr instead of inet for the ip_address because it saved me a cast on joins and appears to work the same.  was
thatthe right move?  is there a better option? 

If you're looking to do fast searches for "is this IP address in any of these CIDR blocks" you might want to look at
https://github.com/RhodiumToad/ip4ras a possible alternative. 

Cheers,
  Steve

> Maybe it will be useful for you! Or maybe there is already some built-in way
> to treat cidr columns like ranges?

There is GiST operator class since version 9.4 and SP-GiST operator
class on version 9.6:

CREATE INDEX ON tracked_ip_address USING gist (ip_address inet_ops);

CREATE INDEX ON tracked_ip_address USING spgist (ip_address);

Performance would change depending on the dataset.  I am more
confident from SP-GiST one.