Pull to refresh

Быстрое определение местоположения по ip в postgresql

PostgreSQL *
В этом топике я хочу рассказать о задаче, очень часто встречающейся в веб-проектах — определение местоположения по ip-адресу. Начну с того, что для того, чтобы определить местоположение пользователя — нужна некая geoip база. Приведу здесь два популярных бесплатных варианта:
IpGeoBase — очень хорошая бесплатная база, но, к сожалению, только по российским ip-адресам.
MaxMind — огромная база по ip-адресам всех стран. Предоставляют бесплатную lite-версию базы. Точность базы по российским ip-адресам не настолько хороша, как у IpGeoBase. Также предоставляют некое API для работы со своей базой, которое позволяет производить выборки очень быстро.

Допустим вы скачали эти базы и залили их в таблицы вашей БД Postgresql (сама закачка — несколько out of scope, если у кого-то возникнет желание — я могу в будущем рассказать о том, что такое команда COPY и с чем ее едят). В общем случае вы получите таблицу такой структуры:

startip endip location_id
2130706433 2130706433 1

Здесь:
startip — это начало блока ip-адресов в формате long
endip — конец блока ip-адресов в формате long
location_id — идентификатор локации (город, регион, страна и т.д., maxmind даже координаты содержит).



Решение №1


Первый раз столкнувшись с этим, я просто на автомате решил делать следующим образом:
1. Создаем индекс на (startip, endip)
2. Создаем простенькую функцию для преобразование ip-адреса в BIGINT:
CREATE OR REPLACE FUNCTION "public"."extract_long_from_ip" (ip text)
RETURNS bigint AS
$body$
SELECT (((elements[1]::bigint * 256) + elements[2]::bigint) * 256 + elements[3]::bigint) * 256 + elements[4]::bigint
FROM (
SELECT string_to_array($1, '.') as elements
) t;
$body$ LANGUAGE 'sql' IMMUTABLE;

3. Вуаля:
SELECT location_id
FROM geo.ip_blocks
WHERE extract_long_from_ip('93.158.134.8') BETWEEN startip AND endip;

И все так просто? — спросите вы. А вот ни разу и не так. Этот запрос отработает правильно, но очень медленно. Все дело в том, что postgresql не умеет использовать индекс в запросах вида «SOMETHING BETWEEN X AND Y». Если местоположение нужно определять быстро (а у меня был как раз такой случай) — seq scan всей таблицы просто никуда не годится.

И что теперь? Переносить определение местоположения по ip в приложение? Вовсе не обязательно — поиск можно значительно ускорить. Для этого воспользуемся замечательным модулем — ip4r.

Установка


su -c 'yum install postgresql-ip4r'
или
sudo apt-get install postgresql-8.3-ip4r
или просто скачиваем с сайта.

Ищем в директории contrib файл ip4r.sql и устанавливаем в нужную нам базу:
psql -U user -f "...../contrib/ip4r.sql" database

Структура таблицы


Модуль ip4r предоставляет два новых типа: ip4 и ip4r. Первый соответствует IPv4 адресу. Второй — некоему интервалу IPv4 адресов. Тип ip4r нам интересен особенно — дело в том, что он индексируемый.

Изменим нашу таблицу:
ALTER TABLE geo.ip_blocks ADD COLUMN ip_range ip4r;
UPDATE geo.ip_blocks SET ip_range = ip4r(startip::ip4, endip::ip4);
ALTER TABLE geo.ipblocks DROP COLUMN startip;
ALTER TABLE geo.ipblocks DROP COLUMN endip;

Создаем индекс:
CREATE INDEX ip_blocks_idx ON geo.ip_blocks USING gist (ip_range);

Вот и все


Используем вот такой вот простенький запрос и получаем ускорение в сотни раз:
SELECT location_id FROM geo.ip_blocks WHERE ip_range >>= '93.158.134.8'::ip4;


UPD:
Все, конечно, сильно зависит от железа и загрузки базы. Но, для примера, у меня получились такие результаты:
NOTICE: 100 queries without ip4r 00:00:14.988
NOTICE: 100 queries with ip4r 00:00:00.008


UPD2:
В комментариях подсказали еще одно решение для быстрого поиска по ip.
Не используется ip4r, создается индекс по startip и используем небольшой финт в запросе:
CREATE INDEX ip_blocks_idx ON geo.ip_blocks USING btree(startip);
SELECT CASE WHEN extract_long_from_ip('93.158.134.8') <= endip
THEN location_id
ELSE NULL END AS location_id
FROM geo.ip_blocks
WHERE startip <= extract_long_from_ip('93.158.134.8')
ORDER BY startip DESC
LIMIT 1;


UPD3:
Из комментариев — еще одно место где можно взять geoip базу. За основу взят maxmind lite, но неплохо переработан + поставляется формате SQL базы или CSV. Там же хорошее описание того, как правильно с этой базой работать.
http://ipinfodb.com/ip_database.php
Tags:
Hubs:
Total votes 47: ↑41 and ↓6 +35
Views 13K
Comments Comments 29