CREATE TABLE my_table(i INT8 DEFAULT '7');
INSERT INTO my_table VALUES(1);
-- convert to columnar
SELECT columnar.alter_table_set_access_method('my_table', 'columnar');
-- back to row
SELECT columnar.alter_table_set_access_method('my_table', 'heap');

Тип таблицы по умолчанию для базы данных по умолчанию - heap. Вы можете изменить тип таблицы по умолчанию, используя default_table_access_method:

SET default_table_access_method TO 'columnar';

Теперь для любой таблицы, которую вы создаете вручную с помощью CREATE TABLE, тип по умолчанию будет columnar.

Колоночные таблицы можно использовать как секции; и секционированная таблица может состоять из разных сочетаний секций, организованных как кучи или колонки.

CREATE TABLE parent(ts timestamptz, i int, n numeric, s text)
  PARTITION BY RANGE (ts);

-- columnar partition
CREATE TABLE p0 PARTITION OF parent
  FOR VALUES FROM ('2020-01-01') TO ('2020-02-01')
  USING COLUMNAR;
-- columnar partition
CREATE TABLE p1 PARTITION OF parent
  FOR VALUES FROM ('2020-02-01') TO ('2020-03-01')
  USING COLUMNAR;
-- row partition
CREATE TABLE p2 PARTITION OF parent
  FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

INSERT INTO parent VALUES ('2020-01-15', 10, 100, 'one thousand'); -- columnar
INSERT INTO parent VALUES ('2020-02-15', 20, 200, 'two thousand'); -- columnar
INSERT INTO parent VALUES ('2020-03-15', 30, 300, 'three thousand'); -- row

При выполнении операций с секционированной таблицей, состоящей из строк и колонок, обратите внимание на следующие особенности операций, которые поддерживаются для строк, но не поддерживаются для колонок (например, UPDATE, DELETE, блокировки строк и т.д.):

Обратите внимание, что Columnar поддерживает индексы btree и hash (и ограничения, которые требуют их использования), но не поддерживает индексы gist, gin, spgist и brin. По этой причине, если некоторые таблицы имеют колоночный формат и если индекс не поддерживается, невозможно создать индексы непосредственно на секционированной (родительской) таблице. В таком случае нужно создать индекс на отдельных таблицах heap. То же самое касается ограничений, которые требуют использование индексов, например:

CREATE INDEX p2_ts_idx ON p2 (ts);
CREATE UNIQUE INDEX p2_i_unique ON p2 (i);
ALTER TABLE p2 ADD UNIQUE (n);

Определение оптимальных размеров полос и блоков для колоночных таблиц в Tantor SE зависит от различных факторов, таких как тип данных, шаблоны запросов и характеристики оборудования. Вот некоторые рекомендации:

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

Выбор алгоритма сжатия для columnar таблиц в Tantor SE зависит от нескольких факторов, включая характер данных, требования к производительности, и технические характеристики оборудования. Вот несколько рекомендаций, которые могут помочь вам принять решение:

Важно отметить, что выбор алгоритма сжатия является компромиссом между производительностью (скоростью сжатия и распаковки) и дисковым пространством. Кроме того, эффективность каждого алгоритма сжатия может значительно зависеть от характера ваших данных. Поэтому рекомендуется провести некоторые тесты с реальными данными и запросами, чтобы определить наиболее подходящий алгоритм сжатия в вашей ситуации.

Time Series-данные характеризуются последовательной записью значений по возрастанию во времени. Для таких данных важна упорядоченность.

Создайте необходимые расширения:

create extension pg_columnar;
create extension plpython3u;

Создайте тестовую таблицу:

CREATE TABLE perf_columnar(
    id INT8,
    ts TIMESTAMPTZ,
    customer_id INT8,
    vendor_id INT8,
    name TEXT,
    description TEXT,
    value NUMERIC,
    quantity INT4
) USING columnar;

ALTER TABLE public.perf_columnar SET(
    columnar.compression = 'lz4',
    columnar.stripe_row_limit = 100000,
    columnar.chunk_group_row_limit = 10000
);

Создайте функцию для генерации случайного текста:

CREATE OR REPLACE FUNCTION random_words(n INT4) RETURNS TEXT LANGUAGE plpython3u AS $$
import random
t = ''
words = ['zero','one','two','three','four','five','six','seven','eight','nine','ten']
for i in range(0, n):
  if (i != 0):
    t += ' '
  r = random.randint(0, len(words) - 1)
  t += words[r]
return t
$$;

Сгенерируйте тестовые данные:

INSERT INTO perf_columnar
   SELECT
    g, -- id
    '2023-06-01'::timestamptz + ('1 minute'::interval * g), -- ts
    (random() * 1000000)::INT4, -- customer_id
    (random() * 100)::INT4, -- vendor_id
    random_words(7), -- name
    random_words(100), -- description
    (random() * 100000)::INT4/100.0, -- value
    (random() * 100)::INT4 -- quantity
   FROM generate_series(1,7500000) g;

Как видно из приведенного выше запроса, мы вставили данные, отсортированные по ts. Соберите статистику с помощью VACUUM (ANALYZE, VERBOSE):

VACUUM ANALYZE perf_columnar;

Создайте копию таблицы с другими параметрами:

CREATE TABLE perf_columnar2(LIKE perf_columnar) USING COLUMNAR;

ALTER TABLE public.perf_columnar2 SET(
    columnar.compression = 'zstd',
    columnar.stripe_row_limit = 10000,
    columnar.chunk_group_row_limit = 1000
);

INSERT INTO perf_columnar2 SELECT * FROM perf_columnar;

VACUUM ANALYZE perf_columnar2;

Проверьте размер получившихся таблиц:

test_db=# \dt+
                                        List of relations
 Schema |      Name      | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------------+-------+----------+-------------+---------------+---------+-------------
 public | perf_columnar  | table | postgres | permanent   | columnar      | 1886 MB | 
 public | perf_columnar2 | table | postgres | permanent   | columnar      | 850 MB  | 
(2 rows)

Как видно, таблица perf_columnar в два раза больше, чем perf_columnar2.

Проверьте параметры таблиц:

test_db=# select * from columnar.options;

    relation    | chunk_group_row_limit | stripe_row_limit | compression | compression_level 
----------------+-----------------------+------------------+-------------+-------------------
 perf_columnar  |                 10000 |           100000 | lz4         |                 3
 perf_columnar2 |                  1000 |            10000 | zstd        |                 3

(2 rows)

Выполните типичный запрос для чтения набора записей из таблицы perf_columnar за определенный промежуток времени:

explain (analyze, verbose, buffers) select ts from perf_columnar
where ts < '2023-06-01 10:00:00'::timestamp with time zone and
      ts > '2023-06-01 10:00:05'::timestamp with time zone;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar  (cost=0.00..401.12 rows=4 width=8) (actual time=18.838..49.001 rows=4 loops=1)
   Output: ts
   Filter: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 9996
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 749     <-----------
   Buffers: shared hit=3833 read=264                <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=52 read=7
 Planning Time: 12.789 ms
 Execution Time: 49.188 ms
(13 rows)

Выполните типичный запрос для чтения набора записей из таблицы perf_columnar2 за определенный промежуток времени:

explain (analyze, verbose, buffers) select ts from perf_columnar2
where ts < '2020-01-01 10:00:00'::timestamp with time zone and
      ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar2  (cost=0.00..17.95 rows=52 width=8) (actual time=5.175..58.532 rows=49 loops=1)
   Output: ts
   Filter: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 951
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 7499    <-----------
   Buffers: shared hit=40824 read=1                 <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=155
 Planning Time: 1.086 ms
 Execution Time: 58.717 ms
(13 rows)

Обратите внимание на Columnar Chunk Groups Removed by Filter: 749 в первом случае и 7499 во втором. Однако, количество прочитанных буферов во втором запросе гораздо выше.

Терминология для понимания поведения этих запросов:

Как мы видим, были отфильтрованы группы блоков 749/7499, что означает, что строки 7490000/7499000 были отфильтрованы без необходимости извлечения или распаковки данных. Только одна группа блоков (10 000 и 1 000 строк) требовала извлечения и распаковки, поэтому запрос занял всего несколько миллисекунд.

Но, как видно из планов запросов, в первом случае было использовано 30 МБ, а во втором - в десять раз больше - 319 МБ.

Columnar поддерживает индексы btree и hash (и ограничения, требующие их), но не поддерживает типы индексов, такие как gist, gin, spgist и brin.

Рассмотрите возможность создания индексов для таблиц perf_columnar и perf_columnar2:

create index test on perf_columnar2 (ts);
create index test_h on perf_columnar (ts);

Проверьте размер индексов:

test_db=# \di+
                                            List of relations

 Schema |  Name  | Type  |  Owner   |     Table      | Persistence | Access method |  Size  | Description 
--------+--------+-------+----------+----------------+-------------+---------------+--------+-------------
 public | test   | index | postgres | perf_columnar2 | permanent   | btree         | 161 MB | 
 public | test_h | index | postgres | perf_columnar  | permanent   | btree         | 161 MB | 
(2 rows)

Размер индексов одинаковый.

Отключите принудительное использование Custom Scan:

SET columnar.enable_custom_scan TO false;

Повторно выполните типичный запрос для чтения набора записей из таблицы perf_columnar для определенного временного интервала:

explain (analyze, verbose, buffers) select ts from perf_columnar
where ts < '2023-06-01 10:00:00'::timestamp with time zone and
      ts > '2023-06-01 10:00:05'::timestamp with time zone;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_h on public.perf_columnar  (cost=0.43..3217.48 rows=4 width=8) (actual time=402.144..402.204 rows=4 loops=1)
   Output: ts
   Index Cond: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=181 read=3232      <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=20 read=5
 Planning Time: 16.278 ms
 Execution Time: 402.386 ms
(9 rows)

Почти такое же количество буферов было просканировано, как при использовании Custom Scan.

Повторно выполните типичный запрос для чтения набора записей из таблицы perf_columnar2 для определенного временного интервала:

explain (analyze, verbose, buffers) select ts from perf_columnar2
where ts < '2020-01-01 10:00:00'::timestamp with time zone and
      ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test on public.perf_columnar2  (cost=0.43..153.05 rows=52 width=8) (actual time=14.620..14.821 rows=49 loops=1)
   Output: ts
   Index Cond: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=372 read=145     <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=97
 Planning Time: 0.813 ms
 Execution Time: 14.978 ms
(13 rows)

Для второй таблицы было просканировано гораздо меньше буферов, и запрос выполняется наиболее оптимальным образом.

Можно сделать следующие выводы: