What's new in PostgreSQL 9.3

Talk about new features in PostgreSQL 9.3

Transcript

1. Что нового в PostgreSQL 9.3 Севостьянов Николай
2. Что нового в PostgreSQL 9.3? • Материализованные представления • Индексы для регулярных выражений • Поддержка записи в Foreign Data Wrappers • Дополнительные операторы и функции для JSON
3. Материализованные представления • Это что-то среднее между представлением и таблицей – CREATE MATERIALIZED VIEW name AS query • Обновление происходит по требованию – REFRESH MATERIALIZED VIEW name • Эффективная замена представлению, если запросы на нём стали медленными
4. Обычное представление =# CREATE VIEW test1 AS -# SELECT 1 AS a WHERE pg_sleep(0.2) IS NOT NULL; CREATE VIEW Time: 36.741 ms =# SELECT * FROM test1; a --- 1 (1 row) Time: 201.314 ms
5. Материализованное представление =# CREATE MATERIALIZED VIEW test2 AS -# SELECT 1 AS a WHERE pg_sleep(0.2) IS NOT NULL; SELECT 1 Time: 271.105 ms =# SELECT * FROM test2; a --- 1 (1 row) Time: 0.554 ms
6. Индексы для регулярных выражений • pg_trgm – “Поиск с опечатками” на основе триграмм • Поддерживает GiST и GIN индексы • Ускоряет поиск подстроки (LIKE) • Позволяет использовать индекс для поиска по регулярному выражению, для которого можно составить список триграмм
7. Без индекса =# EXPLAIN ANALYZE SELECT * FROM companies WHERE _data ~ 'a{3}(b|c)? (d|f)?'; QUERY PLAN -------------------------------------------------------------------------- Seq Scan on companies (cost=0.00..67437.41 rows=108 width=367) (actual t Filter: (_data ~ 'a{3}(b|c)?(d|f)?'::text) Rows Removed by Filter: 1080200 Total runtime: 4444.170 ms (4 rows)
8. Используя GiST индекс =# EXPLAIN ANALYZE SELECT * FROM companies WHERE _data ~ 'a{3}(b|c)? (d|f)?'; QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on companies (cost=45.25..464.10 rows=108 width=365) (a Recheck Cond: (_data ~ 'a{3}(b|c)?(d|f)?'::text) Rows Removed by Index Recheck: 205 -> Bitmap Index Scan on idx_companies__data_key_gist_trgm (cost=0.00. Index Cond: (_data ~ 'a{3}(b|c)?(d|f)?'::text) Total runtime: 511.177 ms (6 rows)
9. Используя GIN индекс =# EXPLAIN ANALYZE SELECT * FROM companies WHERE _data ~ 'a{3}(b|c)? (d|f)?'; QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on companies (cost=116.84..535.69 rows=108 width=367) ( Recheck Cond: (_data ~ 'a{3}(b|c)?(d|f)?'::text) Rows Removed by Index Recheck: 205 -> Bitmap Index Scan on idx_companies__data_key_trgm (cost=0.00..116. Index Cond: (_data ~ 'a{3}(b|c)?(d|f)?'::text) Total runtime: 8.295 ms (6 rows)
10. Запись в Foreign Data Wrappers • Есть FDW модули для CouchDB, MongoDB, Redis, MySQL, но только на чтение • postgres_fdw будет в 9.3 и будет поддерживать запись • FDW легко использовать • Поддержка “фич” зависит от источника
11. Подключим таблицу =# CREATE SERVER pg_remote FOREIGN DATA WRAPPER postgres_fdw -# OPTIONS (dbname 'remote_db', host '192.168.11.48'); CREATE SERVER =# CREATE USER MAPPING FOR CURRENT_USER SERVER pg_remote -# OPTIONS (user 'remote_user', password '123'); CREATE USER MAPPING =# CREATE FOREIGN TABLE remote_table (id int4 not null, some_number int4) -# SERVER pg_remote OPTIONS (table_name 'some_table'); CREATE FOREIGN TABLE
12. Заполним таблицу данными =# INSERT INTO remote_table SELECT id, (random()*100)::int4 AS some_number -# FROM (SELECT * FROM generate_series(1, 1000) AS id) AS x; INSERT 0 1000 =# SELECT * FROM remote_table ORDER BY id OFFSET 998; id | some_number ------+------------- 999 | 49 1000 | 79 (2 rows)
13. Проверим перезапись данных =# BEGIN; BEGIN =# DELETE FROM remote_table WHERE id = 1000; DELETE 1 =# UPDATE remote_table SET some_number = 0 WHERE id = 999; UPDATE 1 =# SELECT * FROM remote_table ORDER BY id OFFSET 998; id | some_number -----+------------- 999 | 0 (1 row)
14. А теперь вернём всё обратно =# ROLLBACK; ROLLBACK =# SELECT * FROM remote_table ORDER BY id OFFSET 998; id | some_number ------+------------- 999 | 49 1000 | 79 (2 rows) -- да, postgres_fdw поддерживает транзанкции
15. JSON в 9.3 можно готовить проще • Для генерации: json_agg, to_json, hstore_to_json_loose • Для обработки: json_populate_recordset, json_array_elements, json_each и многие другие • Доступ к данным по ключу: ->> и #>> • Всегда можно сделать больше используя PL/V8JS Все функции имеют два варианта: – Возвращающие тип json – Другие возвращают тип text
16. Сгенерируем JSON =# WITH z AS (SELECT x, to_json(now()) AS y FROM generate_series(1,2) x) -# SELECT json_agg(z) FROM z; [{"x":1,"y":"2013-07-09 23:19:27.706936+03"}, {"x":2,"y":"2013-07-09 23:19:27.706936+03"}] =# WITH x AS (SELECT hstore('a', '123') || hstore('b', 't') AS h) -# SELECT h, hstore_to_json_loose(h) FROM x; h | hstore_to_json_loose ----------------------+---------------------- "a"=>"123", "b"=>"t" | {"a": 123, "b": true}
17. Или же если JSON уже есть =# SELECT * FROM t; [{"id":1,"val":"abc"},{"id":2,"val":"bcd"}] [{"id":3,"val":"123"},{"id":4,"val":"234"}] =# SELECT * FROM json_populate_recordset(NULL::test, -# (SELECT json_agg(y) FROM (SELECT json_array_elements(j) AS y FROM t) z)); id | val ----+----- 1 | abc 2 | bcd 3 | 123 4 | 234 (4 rows)
18. Получим доступ к данным =# SELECT '{"a":[1,2,3]}'::json #>> '{a,2}'; 3 =# SELECT '{"a":[1,2,3]}'::json ->> 'a'; [1,2,3] =# CREATE INDEX idx_int ON companies -# (((_data #>> '{meta,highrise,id}')::int)); CREATE INDEX =# CREATE INDEX idx_text ON companies ((_data ->> 'name')); CREATE INDEX
19. А как на самом деле? Кто: Christophe Pettus Откуда: PostgreSQL Experts, Inc Когда: PgDay FOSDEM 2013 Говорил, что еще не всё идеально, но местами лучше, чем у MongoDB :) Сейчас посмотрим на пару его слайдов
24. Николай Севостьянов email: nick.sevostjanov@quoteroller.com skype: nikolai.sevostjanov Спасибо за внимание