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 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
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
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 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
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