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

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


    Благодарю за внимание.
    Метки:
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 15
    • –4
      Отличные конкурентные приемущества. Теперь будем ждать поддержки json и jsonb в Node.js ORM-ках.
      • –1
        У меня построитель запросов knex.js, он уже умеет автоматом преобразовывать json в объект JS и наоборот, при выборке / обновлении соответственно.

        На его базе построена ORM Bookshelf.js, я думаю, там как раз идёт имплементация выборок по json, либо всё уже есть. Так или иначе, всегда есть raw query.
      • +3
        Когда уже появится возможность авторефреша материализованных представлений…
        • 0
          Можете пояснить про null подробнее в json и jsonb? Их нельзя хранить или какие то другие ограничения (при создании запросов к полям н-р)?
          • +1
            Можно хранить, но имеется ввиду, что null в JSON — не тот NULL, который в PostgreSQL.
            • 0
              Спасибо, посмотрел на примеры запросов, и понял в каком смысле.
          • 0
            >… Операторы @>,? и некоторые другие специфичны для типа jsonb

            Довольно сомнительное решение — ведь JSON — это просто структура, но состоящая из таких же простых типов, как и другие колонки таблицы — зачем им нужен новый набор крючков? Достаточно парсеру запросов определить, что работают над JSONb полем и СТАНДАРТНЫЕ операторы использовать в новом контексте — EXIST, >, <, = и т.п.
            • +1
              Мне кажется, что правильней именно отдельными операторами, чтобы не смешивать понятия. Так как EXISTS — это существование набора строк, < — меньше в сравнивании двух скаляров. Иначе прийдется в голове держать другой смысл для JSON-поля (и обращатся к структуре таблицы, чтобы посмотреть, что именно за операция и для чего применяется).
            • +1
              в последнем примере с security_barrier ноутис не лишний с секретной книгой?
              • 0
                Да, лишний, ошибка copy/paste. Исправил.
              • 0
                Здравствуйте, у меня почему-то не видит тип данных jsonb, не встречали такое случаем? Пробовал и в консоли, и через php (pdo_pgsql), и программки различные. Вот запрос:

                CREATE TABLE book ( id serial NOT NULL, params jsonb NOT NULL DEFAULT '{}');
                


                ERROR: type «jsonb» does not exist
                Версия: psql (9.4beta3, сервер 9.3.5)

                Если оставляю просто json — то соответственно таблица создается. Гугл мне отказывается отвечать.

                • 0
                  Вы точно с той БД, что нужно, соединяетесь (jsonb появился в 9.4)?
                  сервер 9.3.5
                • +1
                  Верно ли я понял, что на данное время существуют только возможность частичной выборки из json колонок (по имени ключей, индексам, пути), но изменение (alteration), допустим удаление/изменение отдельного элемента массива — нет?
                  • +1
                    Для формирования JSON из элемента, массива или записи предусмотрен ряд функций. Для установки некоторого поля в данных JSON подойдёт такая пользовательская функция:
                    Скрытый текст
                    CREATE OR REPLACE FUNCTION fn_set_json_key ( v_data json, v_key text, v_value anyelement )
                    RETURNS json AS
                    $BODY$
                      SELECT coalesce 
                        (
                          (
                            SELECT
                              (
                                '{' || string_agg ( to_json ( key ) || ':' || value, ',' ) || '}'
                              )
                            FROM
                              (
                                SELECT *
                                FROM json_each (  v_data )
                                WHERE key != v_key
                    
                                UNION ALL
                    
                                SELECT v_key, to_json ( v_value )
                              ) AS fields
                          ), '{}'
                        )::json;
                    $BODY$
                    LANGUAGE SQL IMMUTABLE;
                    


                    Для операций с отдельным элементом массива можно придумать что-то аналогичное.
                    • +1
                      Ок, благодарю. Имел ввиду простые универсальные встроенные средства, по аналогии sql update. То есть допустим нужно обновить часть данных в большом JSON, чтобы при этом не нужно было сначала вытянуть всё поле JSON, сделать какое-то единичное изменение, и потом записать назад весь JSON целиком.

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.