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

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

• Release 9.4
• CREATE INDEX… USING VODKA

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

Автор: blackmaster

Источник