PostgreSQL 9.4 Что нового?
Доброго времени суток! Не за горами релиз PostgreSQL 9.4 и нелишним будет пройтись по некоторым новшествам, привнесённым в этой версии. В статье будут рассмотрены (по большей части, касаемые клиентской модели):
Изменения в автообновляемых представленияхАвтообновляемые представления (autoupdatable views) — представления, над которыми можно выполнять DML-операции. Условия для таких представлений: только одна сущность (таблица или представление) в списке FROM отсутствие операторов WITH, DISTINCT, GROUP BY, HAVING, LIMIT и OFFSET отсутствие операций над множествами UNION, INTERSECT и EXCEPT к полям не применяются функции и операторы Эти представления были предоставлены в PostgreSQL 9.3. В версии 9.4 внесены некоторые дополнения к ним. Одно из них снимает ограничение, связанное с тем, что в этом представлении не может быть полей, к которым применяются функции или операторы (в 9.3, при наличии хотя бы одного такого поля поля, представление становится не автообновляемым). В 9.4 же, есть возможность работать с остальными полями. Проиллюстрируем это на примере.Создадим справочник книг и построим по нему простое автообновляемое представление:
CREATE TABLE book ( id serial NOT NULL, name text NOT NULL, author text NOT NULL, year integer NOT NULL,
CONSTRAINT pk_book_id PRIMARY KEY (id), CONSTRAINT uk_book UNIQUE (name, author) );
INSERT INTO book (name, author, year) VALUES ('Книга №1', 'Иванов И. И.', 2010), ('Книга №2', 'Иванов И. И.', 2011), ('Книга №3', 'Петров П. П.', 2012);
CREATE OR REPLACE VIEW vw_book AS SELECT b.id, b.name, b.author, b.year, (b.year >= extract (year FROM current_date) — 2) AS is_new FROM book b; Обновляем данные: — всё в порядке, поля доступно для обновления UPDATE vw_book SET name = 'Книга №10' WHERE name = 'Книга №1';
-- ошибка, поле is_new не обновляемое UPDATE vw_book SET is_new = false WHERE name = 'Книга №3';
-- проверяем, что изменилось SELECT * FROM vw_book b;
-- вот так можно посмотреть, какие колонки можно обновлять SELECT c.column_name, (is_updatable = 'YES') AS is_updatable FROM information_schema.columns c WHERE c.table_name = 'vw_book' ORDER BY ordinal_position; Для автообновляемых представлений стала доступна опция WITH CHECK OPTION. Смысл её в том, что, при INSERT и UPDATE в представление, будет добавлена проверка, не ограничиваются ли добавляемые или изменяемые данные условием WHERE в представлении. К примеру, у нас есть представление, в котором выбираются старые книги и необходимо запретить добавление новых книг через это представление: — обратите внимание, что базовой сущностью может быть другое автообновляемое представление CREATE OR REPLACE VIEW vw_book_archive AS SELECT b.id, b.name, b.author, b.year FROM vw_book b WHERE b.is_new = false WITH CHECK OPTION;
-- ошибка, так как значение поля year нарушает условие представления INSERT INTO vw_book_archive (name, author, year) VALUES ('Книга №100', 'Сидоров С. С.', 2014);
-- ошибки нет INSERT INTO vw_book_archive (name, author, year) VALUES ('Книга №100', 'Сидоров С. С.', 2010); Далее, представления, обьявленные с опцией security_barrier, теперь не перестают быть автообновляемыми. Рассмотрим security_barrier подробней.Добавим в книги поле, в котором будет находиться акционный код:
— немного почистим данные DELETE FROM book;
INSERT INTO book (id, name, author, year) VALUES (1, 'Книга №1', 'Иванов И. И.', 2010), (2, 'Книга №2', 'Иванов И. И.', 2011), (3, 'Книга №3', 'Петров П. П.', 2012);
-- добавление поля ALTER TABLE book ADD COLUMN promotion_code text;
-- генерация акционного кода UPDATE book SET promotion_code = 'CODE_' || id; Создадим представление, возращающее книги (исключив некую секретную книгу) и их акционные кода, а также функцию, выводящую через RAISE NOTICE название книги и её код: CREATE OR REPLACE VIEW vw_book_list AS SELECT b.* FROM book b WHERE b.name!= 'Книга №1';
CREATE OR REPLACE FUNCTION fn_book_promotion_code (p_name text, p_code text) RETURNS boolean AS $BODY$ BEGIN RAISE NOTICE 'Book »%» has code »%»', p_name, p_code; RETURN true; END $BODY$ LANGUAGE plpgsql COST 100; Сделаем выборку из этого представления (вызвав при этом fn_book_promotion_code для вывода акционного кода книги) и посмотрим на план запроса: SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code);
-- результат запроса ожидаем: — Книга №2 CODE_2 — Книга №3 CODE_3
-- вывод функции тоже: — NOTICE: Book «Книга №2» has code «CODE_2» — NOTICE: Book «Книга №3» has code «CODE_3»
EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code); — Seq Scan on book b (cost=0.00…1.79 rows=1 width=23) (actual time=0.185…0.217 rows=2 loops=1) Filter: ((name <> 'Книга №1':: text) AND fn_book_promotion_code (name, promotion_code)) Rows Removed by Filter: 1 Planning time: 0.064 ms Execution time: 0.229 ms Заметим, что строки читаются последовательно, в фильтре проверяется сперва соответствие имени книги, а затем вызывается функция fn_book_promotion_code. Попробуем теперь уменьшить стоимость вызова функций и понаблюдаем, что изменилось в результатах запроса и плане запроса: CREATE OR REPLACE FUNCTION fn_book_promotion_code (p_name text, p_code text) RETURNS boolean AS $BODY$ BEGIN RAISE NOTICE 'Book »%» has code »%»', p_name, p_code; RETURN true; END $BODY$ LANGUAGE plpgsql COST 0.01;
SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code);
-- результат запроса ожидаем: — Книга №2 CODE_2 — Книга №3 CODE_3
--, а вот вывод функции не очень: — NOTICE: Book «Книга №1» has code «CODE_1» — NOTICE: Book «Книга №2» has code «CODE_2» — NOTICE: Book «Книга №3» has code «CODE_3»
EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code); -- Seq Scan on book b (cost=0.00…1.04 rows=1 width=23) (actual time=0.215…0.240 rows=2 loops=1) Filter: (fn_book_promotion_code (name, promotion_code) AND (name <> 'Книга №1':: text)) Rows Removed by Filter: 1 Planning time: 0.064 ms Execution time: 0.254 ms Так как стоимость стоимость вызова функции мизерная, планировщик сперва вызывает ее, а затем уже проверяет имя книги. Что, соответсвенно, позволяет узнать код секретной книги. Для предотвращения такой ситуации, представление vw_book_list следует создать с опцией security_barrier: CREATE OR REPLACE VIEW vw_book_list WITH (security_barrier = true) AS SELECT b.* FROM book b WHERE b.name!= 'Книга №1';
SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code);
-- результат запроса: — Книга №2 CODE_2 — Книга №3 CODE_3
-- вывод функции: — NOTICE: Book «Книга №1» has code «CODE_1» — NOTICE: Book «Книга №2» has code «CODE_2» — NOTICE: Book «Книга №3» has code «CODE_3»
EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code (l.name, l.promotion_code); — Subquery Scan on l (cost=0.00…1.06 rows=1 width=23) (actual time=0.078…0.106 rows=2 loops=1) Filter: fn_book_promotion_code (l.name, l.promotion_code) → Seq Scan on book b (cost=0.00…1.04 rows=2 width=52) (actual time=0.009…0.010 rows=2 loops=1) Filter: (name <> 'Книга №1':: text) Rows Removed by Filter: 1 Planning time: 0.069 ms Execution time: 0.122 ms Теперь планировщик выделил фильтр с вызовом функции отдельным подзапросом. Представление vw_book_list остаётся автообновляемым: UPDATE vw_book_list SET promotion_code = 'CODE_555' WHERE name = 'Книга №2'; Изменения в материализированных представлениях Основной проблемой материализированных представлений, появившихся в PostgreSQL 9.3, было то, что в процессе обновления представления использовалась эксклюзивная (ACCESS EXCLUSIVE) блокировка, делающая невозможным запросы к представлению. В 9.4 для команды REFRESH MATERIALIZED VIEW добавлена опция CONCURRENTLY с которой обновление материализированного представления использует блокировку EXCLUSIVE, совместимую с конкурентными блокировками ACCESS SHARE, возникающими при запросах SELECT. За кадром, при таком обновлении, создаётся временная версия этого представления, затем происходит сравнение и, при различиях, выполняюся соответсвующие команды INSERT и DELETE. Такой подход требует создания UNIQUE INDEX по одному или нескольким полям материализированного представления. Посмотрим на это в действии: — таблица авторов CREATE TABLE author ( id serial NOT NULL, first_name text NOT NULL, last_name text NOT NULL,
CONSTRAINT pk_author_id PRIMARY KEY (id), CONSTRAINT uk_author_name UNIQUE (first_name, last_name) );
-- таблица публикаций CREATE TABLE publication ( id serial NOT NULL, author_id integer NOT NULL, name text NOT NULL,
CONSTRAINT pk_publication_id PRIMARY KEY (id), CONSTRAINT fk_publication_author_id FOREIGN KEY (author_id) REFERENCES author (id), CONSTRAINT uk_publication_name UNIQUE (author_id, name) );
-- наполнение данными INSERT INTO author (first_name, last_name) VALUES ('Иван', 'Иванов'); — сгенерирован id = 1 INSERT INTO author (first_name, last_name) VALUES ('Пётр', 'Петров'); — сгенерирован id = 2
INSERT INTO publication (author_id, name) VALUES (1, 'Публикация №' || generate_series (1, 1000000) || ')');
INSERT INTO publication (author_id, name) VALUES (2, 'Другая публикация'), (2, 'Еще одна публикация');
–- создание материализированного представления и индекс CREATE MATERIALIZED VIEW mvw_publication AS SELECT p.id, a.first_name || ' ' || a.last_name AS author_name, p.name FROM publication p INNER JOIN author a ON a.id = p.author_id;
CREATE UNIQUE INDEX idx_mvw_publication_id ON mvw_ publication (id);
-- обновление материализированного представления REFRESH MATERIALIZED VIEW CONCURRENTLY mvw_publication;
–- пока представление обновляется…
–- выборка данных из представления (в другом подключении) SELECT * FROM mvw_publication;
–- просмотр блокировок SELECT l.mode FROM pg_locks l INNER JOIN pg_class c ON c.oid = l.relation WHERE с.relname = 'mvw_publication'; Команда ALTER SYSTEM для установки конфигурационных параметров Эта команда позволяет менять параметры конфигурации сервера через SQL-запрос. В отличии от команд SET и set_config, действие которых распространяется на сессию (или транзакцию), изменение будет постоянным. По факту, параметр добавляется в файл $PGDATA/postgresql.auto.conf, который читается сервером (при старте или при получении сигнала SIGHUP) после чтения postgresql.conf. Примеры этой команды: — установка параметра ALTER SYSTEM SET log_min_duration_statement = '1min';
-- установка параметра ALTER SYSTEM SET log_min_duration_statement TO '2min'; — сброс параметра ALTER SYSTEM SET log_min_duration_statement TO DEFAULT; Опция WITH ORDINALITY для функций, возращающих набор строк Для функции unnest (и прочих функций, возвращающих набор строк), добавилась опция WITH ORDINALITY, которая выводит порядок строки. Также, в unnest теперь можно перечислить несколько массивов, каждый из которых будет отдельным столбцом: SELECT * FROM unnest ( ARRAY['cat', 'dog', 'mouse'], ARRAY['Tom', 'Jack', 'Lili'] ) WITH ORDINALITY AS t (cat, dog, mouse);
-- результат: — cat Tom 1 — dog Jack 2 — mouse Lili 3
-- аналогичный запрос через оконные функции SELECT *, row_number () OVER () AS i FROM unnest ( ARRAY['cat', 'dog', 'mouse'], ARRAY['Tom', 'Jack', 'Lili'] ) AS t (cat, dog, mouse); Новые функции для агрегированных данных К стандартным функциям-агрегатам (sum, avg, corr и т. д.), добавились функции для упорядоченных наборов и наборов гипотетических рядов: — исходные данные CREATE TABLE salary AS SELECT (random () * 100 + 2000):: int AS value FROM generate_series (1, 100);
-- наиболее часто встречающееся значение SELECT mode () WITHIN GROUP (ORDER BY value DESC) FROM salary;
-- первое значение, эквивалентное или превышающее положение в фракции SELECT percentile_disc (0.5) WITHIN GROUP (ORDER BY value) FROM salary;
-- значение, соответствующее положению в фракции (при необходимости с интерполяцией между двумя ближайшими значениями) SELECT percentile_cont (0.5) WITHIN GROUP (ORDER BY value) FROM salary;
-- относительный ранг гипотетического ряда (от 1 / n до 1) SELECT s.value, cume_dist (2026) WITHIN GROUP (ORDER BY value) FROM salary s GROUP BY s.value; Также появилась опция для фильтрации данных, которые будут попадать в агрегирующие функции: — средняя зарплата, среди зарплат выше 2050 SELECT avg (s.value) FILTER (WHERE s.value >= 2050) FROM salary s;
-- аналогичный запрос через CASE WHEN… SELECT avg (CASE WHEN s.value >= 2050 THEN s.value ELSE NULL END) FROM salary s; Улучшения индексов GIN и GiST Индексы GIN (с несколькими полями), используемые для полнотекстового поиска, теперь быстрее и занимают меньше места. Для GiST появилась поддержка типов inet и cidr: — справочник IP-адресов CREATE TABLE machine ( ip cidr );
INSERT INTO machine (ip) VALUES ('192.168.1.1':: cidr), ('192.168.1.10':: cidr), ('192.168.2.11':: cidr);
-- необходимо указать операторный класс CREATE INDEX idx_machine_ip ON machine USING GiST (ip inet_ops);
-- адреса с подсети 192.168.1.0/24 SELECT * FROM machine WHERE ip && '192.168.1.0/24':: cidr; Улучшенный вывод команды EXPLAIN В выводе команды EXPLAIN при наличии группировки отображается колонка, по которой группируются данные. К тому же, теперь выводится временя построения плана запроса (planning time): EXPLAIN ANALYZE SELECT s.value, count (*) FROM salary s GROUP BY s.value HAVING count (*) >= 2; -- HashAggregate (cost=2.75…3.60 rows=68 width=4) (actual time=0.045…0.053 rows=26 loops=1) Group Key: value Filter: (count (*) >= 2) Rows Removed by Filter: 42 → Seq Scan on salary s (cost=0.00…2.00 rows=100 width=4) (actual time=0.007…0.015 rows=100 loops=1) Planning time: 0.042 ms Execution time: 0.082 ms Если используется bitmap heap scan, то выводится сколько блоков совпало (exact), а сколько не хватает (lossy): — генерация множества данных INSERT INTO salary (value) SELECT (random () * 10000 + 1000):: int AS value FROM generate_series (1, 1000000);
CREATE INDEX idx_salary_value ON salary (value);
SET work_mem = '64kB';
EXPLAIN ANALYZE SELECT * FROM salary s WHERE s.value BETWEEN 2010 AND 2020; — Bitmap Heap Scan on salary s (cost=28.83…2739.72 rows=1210 width=4) (actual time=0.370…17.824 rows=1030 loops=1) Recheck Cond: ((value >= 2010) AND (value <= 2020)) Rows Removed by Index Recheck: 96457 Heap Blocks: exact=486 lossy=429 -> Bitmap Index Scan on idx_salary_value (cost=0.00…28.53 rows=1210 width=0) (actual time=0.286…0.286 rows=1030 loops=1) Index Cond: ((value >= 2010) AND (value <= 2020)) Planning time: 0.098 ms Execution time: 17.920 ms
SET work_mem = '32MB';
EXPLAIN ANALYZE SELECT * FROM salary s WHERE s.value BETWEEN 2010 AND 2020; — Bitmap Heap Scan on salary s (cost=28.83…2739.72 rows=1210 width=4) (actual time=0.283…1.214 rows=1030 loops=1) Recheck Cond: ((value >= 2010) AND (value <= 2020)) Heap Blocks: exact=915 -> Bitmap Index Scan on idx_salary_value (cost=0.00…28.53 rows=1210 width=0) (actual time=0.157…0.157 rows=1030 loops=1) Index Cond: ((value >= 2010) AND (value <= 2020)) Planning time: 0.076 ms Execution time: 1.269 ms Разогрев кэша В новом расширении pg_prewarm доступна одноимённая функция, с помощью которой необходимая сущность форсированно загружается в кэш (системный ОС или PostgreSQL). Посмотрим, как это происходит.Для начала, установим расширение и создадим тестовую таблицу:
CREATE EXTENSION pg_prewarm;
CREATE TABLE big AS SELECT array_to_string (array_agg (t.x), '') || '#' || generate_series (1, 10000) AS value FROM (SELECT 'a' || generate_series (1, 1000) AS x) t;
-- немного увеличим размер буфера ALTER SYSTEM SET shared_buffers = '256MB'; Теперь, остановим сервер PostgreSQL, сбросим кэши ОС на диск и запустим сервер снова (в вашей ОС команды могут быть иные): /etc/init.d/postgresql-9.4 stop sync /etc/init.d/postgresql-9.4 start Обратимся запросом к тестовой таблице, наблюдая, откуда выбираются данные: — первая попытка EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big; -- Seq Scan on big (cost=0.00…76047.00 rows=5000000 width=8) (actual time=0.013…448.978 rows=5000000 loops=1) Buffers: shared read=26047 Planning time: 0.081 ms Execution time: 689.083 ms
-- вторая попытка EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big; -- Seq Scan on big (cost=0.00…76047.00 rows=5000000 width=8) (actual time=0.044…449.973 rows=5000000 loops=1) Buffers: shared hit=32 read=26015 Planning time: 0.027 ms Execution time: 692.045 ms
-- третья попытка EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big; -- Seq Scan on big (cost=0.00…76047.00 rows=5000000 width=8) (actual time=0.044…449.973 rows=5000000 loops=1) Buffers: shared hit=32 read=26015 Planning time: 0.027 ms Execution time: 692.045 ms Наглядно видно, что так как в кэше еще ничего нет, данные читаются с диска (shared read), но с каждым последующим запросом кэш наполняется (shared hit).Снова остановим сервер PostgreSQL, сбросим кэш ОС и запустим сервер. И опять посмотрим на результат EXPLAIN, но перед этим заполнив кэш данными тестовой таблицы:
— загружено 26047 блоков SELECT pg_prewarm ('big');
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big; -- Seq Scan on big (cost=0.00…76047.00 rows=5000000 width=8) (actual time=0.007…407.269 rows=5000000 loops=1) Buffers: shared hit=26047 Planning time: 0.129 ms Execution time: 642.834 ms Все данные уже находятся в кэше.Триггеры для внешних таблиц В PostgreSQL 9.3 появилось расширение postgres_fdw, которое позволяет получать read/write доступ к таблицам, расположенным в другой БД — так называемым внешним таблицам (foreign tables). В 9.4 теперь возможно создавать триггеры к ним.Создадим внешню таблицу:
— в БД test0 CREATE TABLE city ( country text NOT NULL, name text NOT NULL,
CONSTRAINT uk_city_name UNIQUE (country, name) );
-- в БД test1 CREATE EXTENSION postgres_fdw;
-- добавление внешнего сервера CREATE SERVER fdb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'test0');
-- отображения пользователя CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS (password 'pwd');
-- создание внешней таблицы CREATE FOREIGN TABLE fdb_city (country text, name text) SERVER fdb_server OPTIONS (table_name 'city'); Создадим триггер, запрещающий добавление города с названием N/A и проверим его: — в БД test1 CREATE OR REPLACE FUNCTION tfn_city_change () RETURNS trigger AS $BODY$ BEGIN
IF (NEW.name = 'N/A') THEN RAISE EXCEPTION 'City with name «N/A» not allowed'; END IF;
RETURN NEW; END $BODY$ LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER tr_city_change BEFORE INSERT ON fdb_city FOR EACH ROW EXECUTE PROCEDURE tfn_city_change ();
-- запись добавляется INSERT INTO fdb_city (country, name) VALUES ('USA', 'New York');
--, а вот эта нет INSERT INTO fdb_city (country, name) VALUES ('USA', 'N/A'); Но в БД test0 этот триггер «не виден», что позволяет указывать любые названия городов: — в БД test0 — запись добавляется INSERT INTO city (country, name) VALUES ('Italy', 'N/A'); Изменения для json и новый тип данных jsonb Бесспорно, новый тип jsonb был самым ожидаемым нововеддением в PostgreSQL 9.4. Синтаксически, он не имеет отличий от jsonb, но данные хранятся в развёрнутом бинарном формате, что замедляет добавление новых данных, но обеспечивает высокую скорость их обработки. В общем случае, хранить JSON лучше в jsonb.Для jsonb есть возможность создавать индексы (GIN, btree и hash). В GIN для него есть два операторных класса:
стандартный (jsonb_ops) — поддерживает операторы @>, ?, ?& и ?| jsonb_path_ops — поддерживает оператор @> Хотя операторный класс jsonb_path_ops поддерживает всего лишь один оператор, он более производительный и, как правило, занимает меньше места для одних и тех же данных, чем jsonb_ops.Новые функции для работы с JSON включают в себя (для типа jsonb, соответственно, jsonb_*):
json_array_elements_text — разворачивание массива JSON в набор значений с типом text json_array_elements_text — свёртка значений в массив JSON json_object — построение объекта JSON из массива text json_typeof — информация о типе значения JSON Типы JSON и PostgreSQL имеют такое соотношение (эквивалента для null нет, так как NULL в PostgreSQL с другой семантикой): Тип JSON Тип PostgreSQL string text number numeric boolean boolean Попробуем поработать с JSON: — справочник книг CREATE TABLE book ( id serial NOT NULL, name text NOT NULL, params jsonb NOT NULL DEFAULT '{}' );
-- исходные данные INSERT INTO book (name, params) SELECT 'Книга #' || t.x, ('{ «pages»: ' || 500 + (t.x % 500) || CASE t.x % 1000 WHEN 0 THEN ', «gold_edition»: true' ELSE '' END || ' }'):: jsonb FROM (SELECT generate_series (1, 1000000) x) t;
-- сколько «золотых» изданий SELECT count (*) FROM book WHERE params @> '{ «gold_edition»: true }':: jsonb;
-- указано ли в книге количество страниц SELECT count (*) > 0 FROM book WHERE name = 'Книга #1' AND params? 'pages';
-- количество страниц в книге SELECT params → 'pages' FROM book WHERE name = 'Книга #11';
-- тип свойства «pages» SELECT jsonb_typeof (params → 'pages') FROM book WHERE name = 'Книга #11';
-- создание индекса CREATE INDEX idx_book_params ON book USING gin (params jsonb_path_ops); Операторы @>,? и некоторые другие специфичны для типа jsonb. Более подробное же рассмотрение jsonb и тестирование производительности тянет на отдельную статью.В статье остались не рассмотренными серверные улучшения (в частности, касаемые SSL, VACUUM и backgound_workers). Обратитесь к changelog-у, за более полной информацией. И в заключение хочется отметить, что PostgreSQL уверенно движется как в реляционном так и в NoSQL направлениях. Постепенно добавляются новые возможности, которые в следующих версиях улучшаются, что не может не радовать.
Полезные ссылки:
Благодарю за внимание.
