PostgreSQL 9.4 Что нового?

Доброго времени суток! Не за горами релиз PostgreSQL 9.4 и нелишним будет пройтись по некоторым новшествам, привнесённым в этой версии. В статье будут рассмотрены (по большей части, касаемые клиентской модели):

• изменения в автообновляемых представлениях
• изменение в материализированных представлениях
• команда ALTER SYSTEM для установки конфигурационных параметров
• опция WITH ORDINALITY для функций, возращающих набор строк
• новые функции для агрегированных данных
• улучшения для индексов GIN и GiST
• улучшенный вывод команды EXPLAIN
• разогрев кэша
• триггеры для внешних таблиц
• изменения для json и новый тип данных jsonb

Изменения в автообновляемых представлениях

Автообновляемые представления (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. Синтаксически, он не имеет отличий от json, но данные хранятся в развёрнутом бинарном формате, что замедляет добавление новых данных, но обеспечивает высокую скорость их обработки. В общем случае, хранить 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 направлениях. Постепенно добавляются новые возможности, которые в следующих версиях улучшаются, что не может не радовать.

Полезные ссылки:

• Release 9.4
• CREATE INDEX… USING VODKA

Благодарю за внимание.