Re: [GENERAL] appropriate column for storing ipv4 address

2017-03-02 Thread Emre Hasegeli 
> 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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] appropriate column for storing ipv4 address

2017-03-01 Thread Steve Atkins 

> On Mar 1, 2017, at 8:39 AM, jonathan vanasco  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 block for 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 that the 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/ip4r as a 
possible alternative.

Cheers,
  Steve

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] appropriate column for storing ipv4 address

2017-03-01 Thread Paul Jungwirth 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] appropriate column for storing ipv4 address

2017-03-01 Thread jonathan vanasco 

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 for 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 that the right move?  is there a 
better option?

thanks in advance.

/ jonathan

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general