pg91

Эффективный поиск ближайших соседей и другие новые средства в PostgreSQL

Что планировалось

Совещание ведущих разработчиков PostgreSQL в мае 2010 года в Оттаве, Канада, выработало список приоритетных задач для PostgreSQL 9.1.

Ведущие разработчики PostgreSQL (Олег Бартунов снимает).

Josh Berkus ведет список приоритетных работ для PostgreSQL 9.1.

Что удалось реализовать

Список новых и наиболее важных разработок, вошедших в PostgreSQL 9.1 (Полный список изменений).

Функциональность

• Synchronous Replication - Синхронная репликация
• Per-column collation - задание правила сравнения строк для каждого атрибута
• Unlogged tables - возможность отказа журналирования транзакций (WAL) для таблицы. Это ускоряет запись в такие таблицы за счет потери возможности восстановления после сбоев и их репликации.

Новинки

• SSI (Serializable Snapshot Isolation) -
• KNN-GiST - возможность эффективного поиска (логарифмическая зависимость от размера таблицы) ближайших соседей, используя GiST индекс. Поддерживаются все встроенные типы данных. Например, для поиск 10 ближайших точке, к заданной, из 1 миллиона точек:

explain analyze select *, position <-> point(500,500) from test order by position <-> point(500,500) limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.77 rows=10 width=16) (actual time=0.164..0.475 rows=10 loops=1)
-> Index Scan using q on test (cost=0.00..76512.60 rows=1000000 width=16) (actual time=0.163..0.473 rows=10 loops=1)
Order By: ("position" <-> '(500,500)'::point)
Total runtime: 0.505 ms

• SE/Postgres
• wCTE (writable Common Table Expressions) - использование команд, которые изменяют данные (INSERT,UPDATE,DELETE), с оператором WITH. Это позволяет исполнять несколько разных операций в одном запросе, например: Получить сводку удаленных постов по пользователю и обновить статистику пользователей.

WITH deleted_posts AS (
DELETE FROM posts
WHERE created < now() - '6 months'::INTERVAL
RETURNING *
), deleted_per_user as (
SELECT user_id, count(*)
FROM deleted_posts
GROUP BY 1
)
UPDATE counts
SET posts_count = posts_count - d.count
FROM deleted_per_user d
WHERE d.user_id = counts.user_id;

Расширяемость

• Управление модулями
• SQL/MED