Thread: [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.