Tantor SE standard heap data storage method works well for an OLTP workload:

Columnar tables are best suited for analytic or data warehouse workloads, where the following benefits will be useful compared to the heap method:

There are a number of reasons for this:

Aggregates (COUNT, SUM, AVG), WHERE clauses, bulk INSERTs, UPDATE, DELETE and others.

Frequent large updates, small transactions.

Hydra Columnar supports both row and columnar tables, and any storage format has tradeoffs.

Columnar is not best for:

Where columnar falls short, traditional heap tables can often help. On Hydra Columnar you can mix both!

Try Incremental Materialized views.

Materialized views are precomputed database tables that store the results of a query. Hydra Columnar offers automatic updates to materialized views based on changes in the underlying base tables. This approach eliminates the need to recalculate the entire view, resulting in improved query performance and without time-consuming recomputation.

Please see our section about when to use row and columnar tables.

Create need to have columnar enabled by running the query below as superuser:

CREATE EXTENSION IF NOT EXISTS pg_columnar;

Once installed, you will not need superuser to utilize columnar tables.

Creating a columnar table, specifying the USING columnar option:

CREATE TABLE my_columnar_table
(
    id INT,
    i1 INT,
    i2 INT8,
    n NUMERIC,
    t TEXT
) USING columnar;

Insert the data into the table and read it as usual (subject to the restrictions listed above).

To view internal statistics for a table, use VACUUM VERBOSE. Note that VACUUM (without FULL) is much faster on a columnar table because it only scans the metadata and not the actual data.

Set table options using columnar.alter_columnar_table_set:

SELECT columnar.alter_columnar_table_set(
  'my_columnar_table',
  compression => 'none',
  stripe_row_limit => 10000
);

For tables following options are available:

You can view the options of all tables using:

SELECT * from columnar.options;

Or a specific table:

SELECT * FROM columnar.options
WHERE regclass = 'my_columnar_table'::regclass;

You can reset one or more table options to their default (or current values using SET) using columnar.alter_columnar_table_reset:

SELECT columnar.alter_columnar_table_reset(
  'my_columnar_table',
  chunk_group_row_limit => true
);

Set general columnar options using SET:

SET columnar.compression TO 'none';
SET columnar.enable_parallel_execution TO false;
SET columnar.compression TO default;

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');

The default table type for the default database is heap. You can change this using default_table_access_method:

SET default_table_access_method TO 'columnar';

For any table you create manually with CREATE TABLE the default type is columnar.

Columnar tables can be used as partitions; and a partitioned table can consist of any combination of heap and columnar sections.

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

When performing operations on a partitioned table consisting of rows and columns, note the following behaviors of operations that are supported for rows but not for columns (for example, UPDATE, DELETE, row locks, etc.):

Note that Columnar supports btree and hash indexes (and the restrictions that require them), but does not support gist, gin, spgist and brin indexes. For this reason, if some tables are in columnar format and if an index is not supported, it is not possible to create indexes directly on a partitioned (parent) table. In such a case, you need to create an index on the individual heap tables. Likewise for constraints that require indexes, for example:

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

Install plpython3u before running the test:

CREATE EXTENSION plpython3u;

Scheme:

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

CREATE TABLE perf_columnar(LIKE perf_row) USING COLUMNAR;

Functions for data generation:

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 data using random_words function:

INSERT INTO perf_row
   SELECT
    g, -- id
    '2020-01-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,75000000) g;

INSERT INTO perf_columnar SELECT * FROM perf_row;

Check compression level:

=> SELECT pg_total_relation_size('perf_row')::numeric/pg_total_relation_size('perf_columnar')
AS compression_ratio;

 compression_ratio
--------------------
 5.3958044063457513
(1 row)

The overall compression ratio of a columnar table compared to the same data stored in heap storage is 5.4X.

=> VACUUM VERBOSE perf_columnar;
INFO:  statistics for "perf_columnar":
storage id: 10000000000
total file size: 8761368576, total data size: 8734266196
compression rate: 5.01x
total row count: 75000000, stripe count: 500, average rows per stripe: 150000
chunk count: 60000, containing data for dropped columns: 0, zstd compressed: 60000

VACUUM VERBOSE reports a lower compression ratio because it only averages the compression ratio of individual chunks and does not disregard column format metadata savings.

System characteristics:

Workload queries:

-- OFFSET 1000 so that no rows are returned, and we collect only timings

SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;

Benchmarks were run on a c6a.4xlarge (16 vCPU, 32 GB RAM) with 500 GB of GP2 storage. Results in seconds, smaller is better.

Figure F.1. Performance

Query 0 is 512X faster:

SELECT COUNT(*) FROM hits;

Query 2 is 283X faster:

SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;

Postgres COUNTs are slow? Not anymore! Hydra Columnar parallelizes and vectorizes aggregates (COUNT, SUM, AVG) to deliver the analytic speed you’ve always wanted on Postgres.

Filtering (WHERE clauses):

Query 1 is 1,412X faster:

SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;

Filters on Hydra’s columnar storage resulted in a 1,412X performance improvement.

Review Clickbench for comprehensive results and the list of 42 queries tested.

This benchmark represents typical workload in the following areas: clickstream and traffic analysis, web analytics, machine-generated data, structured logs, and events data.

Figure F.2. Query Speed

For other continuous benchmark results, see BENCHMARKS.

Columnar storage is a key part of the data warehouse, but why is that? In this article we will review what columnar storage is and why it’s such a key part of the data warehouse.

| a | b | c | d |
| a | b | c | d |
| a | b | c | d |

| a | a | a |
| b | b | b |
| c | c | c |
| d | d | d |

To maximize performance of SELECT queries, columnar tables should have a maximum of data in every stripe. Like an INSERT, each transaction with an UPDATE query will write a new stripe. To maximize the size of these stripes, update as much data in a single transaction as possible. Alternatively, you can run VACUUM on the table which will combine the most recent stripes into a combined stripe of maximum size.

Each updates or deletes query will lock the table, meaning multiple UPDATE or DELETE queries on the same table will be executed serially (one at a time). UPDATE queries rewrite any rows that are modified, and thus are relatively slow. DELETE queries only modify metadata and thus complete quite quickly.

The columnar store provides multiple methods to vacuum and clean up tables. Among these are the standard VACUUM and VACUUM FULL functionality, but also provided are UDFs, or User Defined Functions that help for incremental vacuuming of large tables or tables with many holes.

SELECT columnar.vacuum('mytable', 25);

SELECT columnar.vacuum_full();

SELECT columnar.vacuum_full('public');
SELECT columnar.vacuum_full(sleep_time => 0.5);
SELECT columnar.vacuum_full(stripe_count => 1000);

For terms used in this section, please refer to Section 13.2.

Hydra Columnar updates and deletes will meet the isolation level requested for your current transaction (the default is READ COMMITTED). Keep in mind that an UPDATE query is implemented as a DELETE followed by an INSERT. As new data that is inserted in one transaction can appear in a second transaction in READ COMMITTED, this can affect concurrent transactions even if the first transaction was an UPDATE. While this satisfies READ COMMITTED, it may result in unexpected behavior. This is also possible in heap (row-based) tables, but heap tables contain additional metadata that limit the impact of this case.

For stronger isolation guarantees, REPEATABLE READ is recommended. In this isolation level, your transaction will be cancelled if it references data that has been modified by another transaction. In this case, your application should be prepared to retry the transaction.

The columnar store makes extensive use of compression, allowing for your data to be stored efficiently on disk and in memory as compressed data.

While this is very helpful, in some circumstances a long running analytical query can retrieve and decompress the same columns on a recurring basis. In order to counter that, a column caching mechanism is available to store uncompressed data in-memory, up to a specified amount of memory per worker.

These uncompressed columns are then available for the lifetime of a SELECT query, and are managed by the columnar store directly. NOTE that this cache is not used for UPDATE, DELETE, or INSERT, and are released after the SELECT query is complete. #### Enabling the Cache

Caching is be enabled or disabled with a GUC: columnar.enable_column_cache. For queries where the cache is not helpful, you may find slightly better performance by disabling the cache.

-- enable the cache
set columnar.enable_column_cache = 't';

-- disable the cache
set columnar.enable_column_cache = 'f';

In addition, the cache size can be set, with a default of 200MB per process.

set columnar.column_cache_size = '2000MB';

This size can range between 20MB and 20000MB, and is consumed by each parallel process. This means that if you have 8 parallel processes and set the cache size to 2000MB, then a query can consume up to 8 * 2000000 bytes, or up to 16GB of RAM, so it is very important to choose a cache size that is appropriate for your workload.

Vacuuming tables will optimize tables that have had many inserts, updates, or deletes. There are three levels of vacuum on columnar tables:

There are a couple of things you can do to make sure you’re making the most of materialized views:

Documentation in this section is courtesy pg_ivm.

pg_ivm provides a way to make materialized views up-to-date where only incremental changes are computed and applied on views rather than recomputing the contents from scratch as REFRESH MATERIALIZED VIEW does. This process is called “incremental view maintenance” (or IVM), and it can update materialized views more efficiently than recomputation when only small parts of the view are changed.

There are two approaches with regard to timing of view maintenance: immediate and deferred. In immediate maintenance, views are updated in the same transaction that its base table is modified. In deferred maintenance, views are updated after the transaction is committed, for example, when the view is accessed, as a response to user command like REFRESH MATERIALIZED VIEW, or periodically in background, and so on. pg_ivm provides a kind of immediate maintenance, in which materialized views are updated immediately in AFTER triggers when a base table is modified.

To begin using pg_ivm, you must first enable it on your database:

CREATE EXTENSION IF NOT EXISTS pg_ivm;

We call a materialized view supporting IVM an Incrementally Maintainable Materialized View (IMMV). To create an IMMV, you have to call create_immv function with a relation name and a view definition query. For example:

SELECT create_immv('sales_test', 'SELECT * FROM sales');

creates an IMMV with name ‘sales_test’ defined as SELECT * FROM sales. This corresponds to the following command to create a normal materialized view:

CREATE MATERIALIZED VIEW sales_test AS SELECT * FROM sales;

When an IMMV is created, some triggers are automatically created so that the view’s contents are immediately updated when its base tables are modified.

postgres=# SELECT create_immv('m', 'SELECT * FROM t0');
NOTICE:  could not create an index on immv "m" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv
-------------
           3
(1 row)

postgres=# SELECT * FROM m;
 i
---
 1
 2
 3
(3 rows)

postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECT * FROM m; -- automatically updated
 i
---
 1
 2
 3
 4
(4 rows)

SET default_table_access_method = 'columnar';
SET max_parallel_workers = 1;
SELECT create_immv('sales_test', 'SELECT * FROM sales');

SET max_parallel_workers = DEFAULT;
SET default_table_access_method = DEFAULT;

create_immv(immv_name text, view_definition text) RETURNS bigint

refresh_immv(immv_name text, with_data bool) RETURNS bigint

get_immv_def(immv regclass) RETURNS text

test=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.aid, b.bid, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
SELECT 10000000

test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 9.052 ms

test=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00:20.576)

test=# SELECT create_immv('immv',
        'SELECT a.aid, b.bid, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv
-------------
    10000000
(1 row)

test=# UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;
UPDATE 1
Time: 15.448 ms

test=# SELECT * FROM immv WHERE aid = 1;
 aid | bid | abalance | bbalance
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

test=# DROP INDEX immv_index;
DROP INDEX

test=# UPDATE pgbench_accounts SET abalance = 9876 WHERE aid = 1;
UPDATE 1
Time: 3224.741 ms (00:03.225)

Determining the optimal stripe and chunk sizes for columnar tables in Tantor SE depends on various factors, such as the nature of the data, query patterns, and hardware characteristics. Here are some recommendations:

Ultimately, the optimal stripe and chunk sizes depend on the unique characteristics of your environment, data, and query patterns.

The choice of compression algorithm for columnar tables in Tantor SE depends on several factors, including the nature of the data, performance requirements, and hardware specifications. Here are some recommendations that may help you make a decision:

It is important to note that choosing a compression algorithm is a trade-off between performance (compression and decompression speed) and disk space. Also, the efficiency of each compression algorithm can greatly depend on the nature of your data. Therefore, it is recommended to conduct some tests with your real data and queries to determine the most suitable compression algorithm for your situation.

Time Series data is characterized by the sequential recording of values in increasing time order. For such data, orderliness is important.

Create the necessary extensions:

create extension pg_columnar;
create extension plpython3u;

Create a test table:

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

SELECT columnar.alter_columnar_table_set(
    'public.perf_columnar',
    compression => 'lz4',
    stripe_row_limit => 100000,
    chunk_group_row_limit => 10000
); 

Create a function to generate random text:

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
$$;

Generate test data:

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;

As seen from the query above, we have inserted data sorted by ts. Gather statistics using VACUUM (ANALYZE, VERBOSE):

VACUUM ANALYZE perf_columnar;

Create a copy of the table with different parameters:

CREATE TABLE perf_columnar2(LIKE perf_columnar) USING COLUMNAR;

SELECT columnar.alter_columnar_table_set(
    'public.perf_columnar2',
    compression => 'zstd',
    stripe_row_limit => 10000,
    chunk_group_row_limit => 1000
);

INSERT INTO perf_columnar2 SELECT * FROM perf_columnar;

VACUUM ANALYZE perf_columnar2;

Check the size of the resulting tables:

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)

As can be seen, the table perf_columnar is twice as large as perf_columnar2.

Check the options of the tables:

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)

Execute a typical query to read a set of records from the perf_columnar table for a specific time interval:

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)

Execute a typical query to read a set of records from the perf_columnar2 table for a specific time interval:

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)

Note Columnar Chunk Groups Removed by Filter: 749 in the first case and 7499 in the second. However, the number of read buffers in the second query is much higher.

Terminology for understanding the behavior of these queries:

As can be seen, 749/7499 chunk groups were filtered, meaning that 7490000/7499000 rows were filtered without the need to extract or unpack the data. Only 1 chunk group (10,000 and 1,000 rows) needed to be extracted and unpacked, so the query took only milliseconds.

But as seen from the query plans, in the first case, 30 MB was used, while in the second, ten times more - 319 MB.

Columnar supports btree and hash indexes (and constraints that require them), but does not support index types like gist, gin, spgist, and brin.

Consider creating indexes for the tables perf_columnar and perf_columnar2:

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

Check the size of the indexes:

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)

The sizes of the indexes are the same.

Disable the forced use of Custom Scan:

SET columnar.enable_custom_scan TO false;

Re-execute the typical query to read a set of records from the perf_columnar table for a specific time interval:

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)

Nearly the same number of buffers were scanned as when using Custom Scan.

Re-execute the typical query to read a set of records from the perf_columnar2 table for a specific time interval:

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)

For the second table, far fewer buffers were scanned, and the query is executed in the most optimal manner.

The following conclusions can be drawn: