Where the open source database community meets: Use code PERCONA75 and secure your spot for Percona Live. Register

Downloads

Blog

Powering PostgreSQL 15 With Columnar Tables

December 14, 2022

Author

Robert Bernier

Insight for DBAs

PostgreSQL

Share this Post:

Investigating Columnar Tables in PostgreSQL with the Citus Columnar Extension

Prologue

This blog is the result of my investigation into column-wise tables. Hopefully, you will find it useful in your own deliberations.

When I started writing, this was meant to be a “good news” blurb. I was optimistic that I would have truly impressive numbers to share. As it turns out, while there is potential for significant advantage, a detailed testing regime of the Citus columnar store extension should be carried out before implementing it in your own environment.

The Missing Sizzle

Sizzle: A phrase used to show affirmation or approval. It references the sound one hears when cooking certain delicious foods, such as bacon or fajitas, and transfers those feelings to non-edible objects or ideas.

There is a lot to take in with every new version of PostgreSQL, and I am often impressed by each release. Nevertheless, there has been one long-standing feature that I have always felt was missing. This weakness is being addressed by the industry. Ironically, some of these implementations are themselves Postgres derivatives.

What is this sizzling feature?

Column-wise tables.

When to Consider Column-Wise Tables

Consider using the Citus columnar extension under one or more of the following conditions:

Table Attributes

The tables are wide, with many columns.
The table size is at least in the tens of gigabytes.
The typical row is byte-heavy.

System Considerations

System space is at a premium.
It is worth your while to manage space utilization closely.
OLAP is a major component of overall activity, with many different kinds of SELECT statements.
Insert performance is not a priority.
Indexes are not feasible, or you need to reduce reliance on them.

Creating expression indexes on columnar tables is faster by orders of magnitude than on heap tables.

Caveat: You cannot perform UPDATE or DELETE operations on a columnar table.

OLTP vs. OLAP

Let’s get back to basics. In the database world, there are essentially two types of database operations.

Online Transaction Processing, or OLTP

Online transaction processing applications have high throughput and are insert- or update-intensive. These applications are used concurrently by hundreds of users. The key goals of OLTP applications are availability, speed, concurrency, and recoverability.

Online Analytical Processing, or OLAP

Online analytical processing applications enable users to analyze multidimensional data interactively from multiple perspectives. OLAP consists of three basic analytical operations:

Consolidation, or roll-up
Drill-down
Slicing and dicing

About Columnar Tables

As an RDBMS, PostgreSQL is geared more toward OLTP than OLAP operations. It handles data manipulation, such as inserting, updating, and deleting, very well. While PostgreSQL is capable of performing OLAP workloads, it is not always as efficient for those use cases.

The primary reason is a common characteristic among most relational database systems: data is stored and processed as individual records, also known as row-wise processing.

Suppose you are performing a deep-dive analysis involving a table with 50 columns. Further, suppose your query only needs one column. In a row-oriented table, the query may still need to read data from all 50 columns per row to access that single column. If the query processes 100 million rows, that is not trivial.

Now consider reorienting the table as column-wise. In this case, the query only reads the needed column instead of all 50. The result is a lighter query that requires less I/O and less processing power, while achieving the same result in significantly less time.

Introducing the Citus Columnar Extension

As a general-purpose database management system, PostgreSQL can be reconfigured for different purposes. It is possible to enhance PostgreSQL with columnar tables by using the Citus columnar table extension.

The Citus columnar extension is one part of a larger set of capabilities. When fully implemented, Citus can create a scalable, distributed Postgres database system.

The Citus columnar extension feature set includes:

Highly Compressed Tables

Reduces storage requirements.
Reduces the I/O needed to scan the table.

Projection Pushdown

Only the columns included in the SELECT statement are returned, further reducing I/O.

Chunk Group Filtering

Allows queries to skip over whole groups of unrelated data without processing them.

The complete Citus feature set, except for the columnar storage component, is not covered in this blog. It includes:

Distributed tables
Reference tables
A distributed query engine that routes and parallelizes SELECT, DML, and other operations
Columnar storage, which:
- Compresses data
- Speeds up scans
- Supports fast projections
Querying from any node

Getting It

This is a bad-news, good-news, excellent-news situation.

Bad news: At the time of writing this blog, the only publicly available packages on the Postgres repositories that I could find were the cstore foreign data wrappers, which cover only version 12 and older versions of Postgres.

Good news: The Citus extension is available on the Citus Data download page.

Excellent news: As part of writing this blog, custom DEB and RPM packages were created for PostgreSQL 15 for CentOS 7, CentOS 8, Ubuntu 18.04, and Ubuntu 20.04. These are available for download from GitHub.

First-Time Installation

Assuming you opted for the easy way and are installing the packages made for this blog, follow these steps.

First, go to the PostgreSQL download page and configure your packaging system for your Linux distribution.

Then download the appropriate custom-made columnar package for your version of Linux:

<code class="language-bash"># Ubuntu 18.04
wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus-columnar_11.1-1-UBUNTU1804_amd64.deb

# Ubuntu 20.04
wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus-columnar_11.1-1-UBUNTU2004_amd64.deb

# CentOS 7
wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus_columnar-11.1-1-CENTOS7.x86_64.rpm

# CentOS 8
wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus_columnar-11.1-1-CENTOS8.x86_64.rpm
</code>

<code class="language-bash"># Ubuntu 18.04

wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus-columnar_11.1-1-UBUNTU1804_amd64.deb

# Ubuntu 20.04

wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus-columnar_11.1-1-UBUNTU2004_amd64.deb

# CentOS 7

wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus_columnar-11.1-1-CENTOS7.x86_64.rpm

# CentOS 8

wget https://github.com/rbernierZulu/columnar-tables-extension/blob/main/postgresql-15-citus_columnar-11.1-1-CENTOS8.x86_64.rpm

</code>

Install the package. All dependent packages, including the Postgres server, will be automatically installed onto your host. Pay attention to CentOS 7, which also requires the epel-release repository.

<code class="language-bash"># Ubuntu 18.04
apt update
apt-get install ./postgresql-15-citus-columnar_11.1-1-UBUNTU1804_amd64.deb

# Ubuntu 20.04
apt update
apt-get install ./postgresql-15-citus-columnar_11.1-1-UBUNTU2004_amd64.deb

# CentOS 7
# ATTENTION: the EPEL package must be installed beforehand.
yum install epel-release
yum install ./postgresql-15-citus_columnar-11.1-1-CENTOS7.x86_64.rpm

# CentOS 8
dnf -qy module disable postgresql
dnf install ./postgresql-15-citus_columnar-11.1-1-CENTOS8.x86_64.rpm
dnf check-update
</code>

<code class="language-bash"># Ubuntu 18.04

apt update

apt-get install ./postgresql-15-citus-columnar_11.1-1-UBUNTU1804_amd64.deb

# Ubuntu 20.04

apt update

apt-get install ./postgresql-15-citus-columnar_11.1-1-UBUNTU2004_amd64.deb

# CentOS 7

# ATTENTION: the EPEL package must be installed beforehand.

yum install epel-release

yum install ./postgresql-15-citus_columnar-11.1-1-CENTOS7.x86_64.rpm

# CentOS 8

dnf -qy module disable postgresql

dnf install ./postgresql-15-citus_columnar-11.1-1-CENTOS8.x86_64.rpm

dnf check-update

</code>

Initialize the Postgres data cluster and configure it appropriately for your Linux distribution so you can log in to Postgres.

A Working Example of Columnar Tables

What follows are the results of my analysis. Of course, there is always more that can be said, but this should give you an overview of the possibilities of this extension.

Installing the extension into your database is standard fare:

<code class="language-sql">-- Create your extension in the database.
CREATE DATABASE db01;
\c db01
CREATE EXTENSION citus_columnar;
</code>

<code class="language-sql">-- Create your extension in the database.

CREATE DATABASE db01;

\c db01

CREATE EXTENSION citus_columnar;

</code>

Here are two tables, one using the heap access method and one using the columnar access method. These will be used for the initial investigation. Notice that it took less time to create the regular heap table than the columnar table.

<code class="language-sql">DROP TABLE IF EXISTS t1, t2;

\timing

-- Time: 7628.612 ms (00:07.629)
CREATE TABLE IF NOT EXISTS t1(id, qty)
USING heap
AS
SELECT (random() * 10)::int, (random() * 1000)::int
FROM generate_series(1, 10e6);

-- Time: 15635.768 ms (00:15.636)
CREATE TABLE IF NOT EXISTS t2(id, qty)
USING columnar
AS
SELECT (random() * 10)::int, (random() * 1000)::int
FROM generate_series(1, 10e6);
</code>

<code class="language-sql">DROP TABLE IF EXISTS t1, t2;

\timing

-- Time: 7628.612 ms (00:07.629)

CREATE TABLE IF NOT EXISTS t1(id, qty)

USING heap

SELECT (random() * 10)::int, (random() * 1000)::int

FROM generate_series(1, 10e6);

-- Time: 15635.768 ms (00:15.636)

CREATE TABLE IF NOT EXISTS t2(id, qty)

USING columnar

SELECT (random() * 10)::int, (random() * 1000)::int

FROM generate_series(1, 10e6);

</code>

Table t1 uses the heap access method:

<code class="language-text">Table "public.t1"
Column |  Type   | Collation | Nullable | Default
-------+---------+-----------+----------+---------
id     | integer |           |          |
qty    | integer |           |          |
Access method: heap
</code>

<code class="language-text">Table "public.t1"

Column | Type | Collation | Nullable | Default

-------+---------+-----------+----------+---------

id | integer | | |

qty | integer | | |

Access method: heap

</code>

Table t2 uses the columnar access method:

<code class="language-text">Table "public.t2"
Column |  Type   | Collation | Nullable | Default
-------+---------+-----------+----------+---------
id     | integer |           |          |
qty    | integer |           |          |
Access method: columnar
</code>

<code class="language-text">Table "public.t2"

Column | Type | Collation | Nullable | Default

-------+---------+-----------+----------+---------

id | integer | | |

qty | integer | | |

Access method: columnar

</code>

The number of records is 10 million:

<code class="language-sql">WITH a(row_wise) AS (
    SELECT count(*) FROM t1
),
b(column_wise) AS (
    SELECT count(*) FROM t2
)
SELECT row_wise, column_wise
FROM a, b;
</code>

<code class="language-sql">WITH a(row_wise) AS (

SELECT count(*) FROM t1

b(column_wise) AS (

SELECT count(*) FROM t2

)

SELECT row_wise, column_wise

FROM a, b;

</code>

<code class="language-text">row_wise | column_wise
---------+-------------
9900001  | 9900001
</code>

<code class="language-text">row_wise | column_wise

---------+-------------

9900001 | 9900001

</code>

The columnar compression does a great job of shrinking the table:

<code class="language-text">List of relations
Schema | Name | Type  |  Owner   | Persistence | Access method | Size
-------+------+-------+----------+-------------+---------------+--------
public | t1   | table | postgres | permanent   | heap          | 346 MB
public | t2   | table | postgres | permanent   | columnar      | 27 MB
</code>

<code class="language-text">List of relations

-------+------+-------+----------+-------------+---------------+--------

</code>

SQL Statements, Preliminary

Let’s begin by comparing basic administration and SELECT statements for heap and columnar tables.

Examining the results, much of the performance indicates that columnar tables either perform similarly to heap tables or, in many cases, take more time executing the same operations.

Using a psql session, the following SQL statements were executed and examined for performance differences.

Creating Tables

<code class="language-sql">-- Heap table: 7.6s
CREATE TABLE IF NOT EXISTS t1(id, qty)
USING heap
AS
SELECT (random() * 10)::int, (random() * 1000)::int
FROM generate_series(1, 10e6);

-- Columnar table: 15.6s
CREATE TABLE IF NOT EXISTS t2(id, qty)
USING columnar
AS
SELECT (random() * 10)::int, (random() * 1000)::int
FROM generate_series(1, 10e6);
</code>

<code class="language-sql">-- Heap table: 7.6s

CREATE TABLE IF NOT EXISTS t1(id, qty)

USING heap

SELECT (random() * 10)::int, (random() * 1000)::int

FROM generate_series(1, 10e6);

-- Columnar table: 15.6s

CREATE TABLE IF NOT EXISTS t2(id, qty)

USING columnar

SELECT (random() * 10)::int, (random() * 1000)::int

FROM generate_series(1, 10e6);

</code>

Adding Five Million Records

<code class="language-sql">-- Heap table: 13.7s
DO
$$
BEGIN
    FOR i IN 5.1e6..10e6 LOOP
        INSERT INTO t1 VALUES ((random() * 10)::int, (random() * 1000)::int);
    END LOOP;
END
$$;

-- Columnar table: 18.5s
DO
$$
BEGIN
    FOR i IN 5.1e6..10e6 LOOP
        INSERT INTO t2 VALUES ((random() * 10)::int, (random() * 1000)::int);
    END LOOP;
END
$$;
</code>

<code class="language-sql">-- Heap table: 13.7s

BEGIN

FOR i IN 5.1e6..10e6 LOOP

INSERT INTO t1 VALUES ((random() * 10)::int, (random() * 1000)::int);

END LOOP;

END

$$;

-- Columnar table: 18.5s

BEGIN

FOR i IN 5.1e6..10e6 LOOP

INSERT INTO t2 VALUES ((random() * 10)::int, (random() * 1000)::int);

END LOOP;

END

$$;

</code>

Creating Indexes

<code class="language-sql">-- Heap table: 4.9s
CREATE INDEX ON t1(id);

-- Columnar table: 7.8s
CREATE INDEX ON t2(id);
</code>

<code class="language-sql">-- Heap table: 4.9s

CREATE INDEX ON t1(id);

-- Columnar table: 7.8s

CREATE INDEX ON t2(id);

</code>

SQL Statement Query Plans

Part One: 10 Million Records, Two-Column Tables

Using the table definitions above, the following metrics were generated with the runtime parameter max_parallel_workers_per_gather = 4.

For these two tables, there is no clear performance benefit from a columnar table over a regular heap table.

<code class="language-sql">-- Heap table: 742.411 ms
EXPLAIN ANALYZE SELECT id, qty FROM t1;

-- Columnar table: 914.096 ms
EXPLAIN ANALYZE SELECT id, qty FROM t2;

-- Heap table: 6441.425 ms
EXPLAIN ANALYZE SELECT id, qty FROM t1 ORDER BY random();

-- Columnar table: 5871.620 ms
EXPLAIN ANALYZE SELECT id, qty FROM t2 ORDER BY random();

-- Heap table: 329.562 ms
EXPLAIN ANALYZE SELECT sum(qty) FROM t1;

-- Columnar table: 902.614 ms
EXPLAIN ANALYZE SELECT sum(qty) FROM t2;

-- Heap table: 531.525 ms
EXPLAIN ANALYZE SELECT id, sum(qty) FROM t1 GROUP BY id;

-- Columnar table: 1602.756 ms
EXPLAIN ANALYZE SELECT id, sum(qty) FROM t2 GROUP BY id;
</code>

<code class="language-sql">-- Heap table: 742.411 ms

EXPLAIN ANALYZE SELECT id, qty FROM t1;

-- Columnar table: 914.096 ms

EXPLAIN ANALYZE SELECT id, qty FROM t2;

-- Heap table: 6441.425 ms

EXPLAIN ANALYZE SELECT id, qty FROM t1 ORDER BY random();

-- Columnar table: 5871.620 ms

EXPLAIN ANALYZE SELECT id, qty FROM t2 ORDER BY random();

-- Heap table: 329.562 ms

EXPLAIN ANALYZE SELECT sum(qty) FROM t1;

-- Columnar table: 902.614 ms

EXPLAIN ANALYZE SELECT sum(qty) FROM t2;

-- Heap table: 531.525 ms

EXPLAIN ANALYZE SELECT id, sum(qty) FROM t1 GROUP BY id;

-- Columnar table: 1602.756 ms

EXPLAIN ANALYZE SELECT id, sum(qty) FROM t2 GROUP BY id;

</code>

Part Two: Five Million Records, 100-Column Tables

To get a better idea of performance differences, a second set of tables was created at a greater scale. This time, while the number of records was halved, the number of columns was increased from two to 100.

Even though most of the columns are copies of one another, the compression achieved by the columnar table is remarkable. The default size was reduced by a factor of 752x.

<code class="language-text">Table "public.t[34]"
Column |  Type  | Collation | Nullable | Default
-------+--------+-----------+----------+-------------------------------------------------------------
c1     | bigint |           | not null | nextval('t1_c1_seq'::regclass)
c2     | bigint |           |          |
c3     | text   |           |          | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text
c4     | text   |           |          | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text
...
c98    | text   |           |          | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text
c99    | text   |           |          | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text
c100   | text   |           |          | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

Indexes:
    "t1_pkey" PRIMARY KEY, btree (c1)
    "t1_c2_idx" btree (c2)
</code>

<code class="language-text">Table "public.t[34]"

Column | Type | Collation | Nullable | Default

-------+--------+-----------+----------+-------------------------------------------------------------

c1 | bigint | | not null | nextval('t1_c1_seq'::regclass)

c2 | bigint | | |

c3 | text | | | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

c4 | text | | | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

...

c98 | text | | | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

c99 | text | | | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

c100 | text | | | 'aowjfa fawjfawofjawofjawoifawevvaerarpfjkaofvaweawe[OJARGOIAJOAFWF'::text

Indexes:

"t1_pkey" PRIMARY KEY, btree (c1)

"t1_c2_idx" btree (c2)

</code>

<code class="language-text">List of relations
Schema | Name | Type  |  Owner   | Persistence | Access method | Size
-------+------+-------+----------+-------------+---------------+-------
public | t3   | table | postgres | permanent   | heap          | 67 GB
public | t4   | table | postgres | permanent   | columnar      | 89 MB
</code>

<code class="language-text">List of relations

-------+------+-------+----------+-------------+---------------+-------

</code>

Examining the indexes shows them to be the same size:

<code class="language-text">List of relations
Schema | Name          | Type  |  Owner   | Table | Persistence | Access method | Size
-------+---------------+-------+----------+-------+-------------+---------------+--------
public | t3_c2_idx     | index | postgres | t3    | permanent   | btree         | 105 MB
public | t3_c2_idx1    | index | postgres | t3    | permanent   | btree         | 105 MB
public | t3_length_idx | index | postgres | t3    | permanent   | btree         | 33 MB
public | t3_pkey       | index | postgres | t3    | permanent   | btree         | 107 MB
public | t4_c2_idx     | index | postgres | t4    | permanent   | btree         | 105 MB
public | t4_length_idx | index | postgres | t4    | permanent   | btree         | 33 MB
public | t4_pkey       | index | postgres | t4    | permanent   | btree         | 107 MB
</code>

<code class="language-text">List of relations

-------+---------------+-------+----------+-------+-------------+---------------+--------

</code>

Unlike the first set of query plans, these clearly demonstrate a significant performance improvement.

Changing max_parallel_workers_per_gather did not appear to change performance much.

<code class="language-sql">-- Heap table without index
-- max_parallel_workers_per_gather = 4: 9.6s
-- max_parallel_workers_per_gather = 1: 8.7s
EXPLAIN ANALYZE SELECT sum(c2) FROM t3;

-- Columnar table without index
-- max_parallel_workers_per_gather = 4: 590.176 ms
-- max_parallel_workers_per_gather = 1: 596.459 ms
EXPLAIN ANALYZE SELECT sum(c2) FROM t4;

-- Heap table
-- max_parallel_workers_per_gather = 4: 10.4s
-- max_parallel_workers_per_gather = 1: 8.8s
EXPLAIN ANALYZE SELECT count(c3) FROM t3;

-- Columnar table
-- max_parallel_workers_per_gather = 4: 509.209 ms
-- max_parallel_workers_per_gather = 1: 541.452 ms
EXPLAIN ANALYZE SELECT count(c3) FROM t4;

-- Heap table
-- max_parallel_workers_per_gather = 4: 1m34s
-- max_parallel_workers_per_gather = 1: 1m17s
EXPLAIN ANALYZE SELECT max(length(c25)) FROM t3;

-- Columnar table
-- max_parallel_workers_per_gather = 4: 1.1s
-- max_parallel_workers_per_gather = 1: 1.2s
EXPLAIN ANALYZE SELECT max(length(c25)) FROM t4;

-- Heap table
-- max_parallel_workers_per_gather = 4: 1m33s
-- max_parallel_workers_per_gather = 1: 1m18s
EXPLAIN ANALYZE SELECT sum(length(c50)) FROM t3;

-- Columnar table
-- max_parallel_workers_per_gather = 4: 1.2s
-- max_parallel_workers_per_gather = 1: 1.2s
EXPLAIN ANALYZE SELECT sum(length(c50)) FROM t4;
</code>

<code class="language-sql">-- Heap table without index

-- max_parallel_workers_per_gather = 4: 9.6s

-- max_parallel_workers_per_gather = 1: 8.7s

EXPLAIN ANALYZE SELECT sum(c2) FROM t3;

-- Columnar table without index

-- max_parallel_workers_per_gather = 4: 590.176 ms

-- max_parallel_workers_per_gather = 1: 596.459 ms

EXPLAIN ANALYZE SELECT sum(c2) FROM t4;

-- Heap table

-- max_parallel_workers_per_gather = 4: 10.4s

-- max_parallel_workers_per_gather = 1: 8.8s

EXPLAIN ANALYZE SELECT count(c3) FROM t3;

-- Columnar table

-- max_parallel_workers_per_gather = 4: 509.209 ms

-- max_parallel_workers_per_gather = 1: 541.452 ms

EXPLAIN ANALYZE SELECT count(c3) FROM t4;

-- Heap table

-- max_parallel_workers_per_gather = 4: 1m34s

-- max_parallel_workers_per_gather = 1: 1m17s

EXPLAIN ANALYZE SELECT max(length(c25)) FROM t3;

-- Columnar table

-- max_parallel_workers_per_gather = 4: 1.1s

-- max_parallel_workers_per_gather = 1: 1.2s

EXPLAIN ANALYZE SELECT max(length(c25)) FROM t4;

-- Heap table

-- max_parallel_workers_per_gather = 4: 1m33s

-- max_parallel_workers_per_gather = 1: 1m18s

EXPLAIN ANALYZE SELECT sum(length(c50)) FROM t3;

-- Columnar table

-- max_parallel_workers_per_gather = 4: 1.2s

-- max_parallel_workers_per_gather = 1: 1.2s

EXPLAIN ANALYZE SELECT sum(length(c50)) FROM t4;

</code>

Working with Indexes

General observations: B-tree indexes are similar in size between heap and columnar tables. Overall, their performance also appears similar, although the columnar table’s index is somewhat slower. This is likely due to the extra processing required to decompress the table’s values.

Expression Indexes

Creating an expression index on a columnar table is significantly faster than creating one on a heap table:

<code class="language-sql">-- Heap table: 1m17s
CREATE INDEX ON t3(length(c90));

-- Columnar table: 14s
CREATE INDEX ON t4(length(c90));
</code>

<code class="language-sql">-- Heap table: 1m17s

CREATE INDEX ON t3(length(c90));

-- Columnar table: 14s

CREATE INDEX ON t4(length(c90));

</code>

Runtime Parameter: `max_parallel_workers_per_gather`

Index performance varies considerably on heap tables depending on the value set for max_parallel_workers_per_gather.

The following results highlight that, depending on the type of table used, this can become important when hardware resources and server costs are a consideration.

<code class="language-sql">-- Heap table using B-tree index
-- max_parallel_workers_per_gather = 4: 467.789 ms
-- max_parallel_workers_per_gather = 1: 748.939 ms
EXPLAIN ANALYZE SELECT sum(c2) FROM t3;

-- Columnar table using B-tree index
-- max_parallel_workers_per_gather = 4: 561.522 ms
-- max_parallel_workers_per_gather = 1: 599.629 ms
EXPLAIN ANALYZE SELECT sum(c2) FROM t4;

-- Heap table using expression index
-- max_parallel_workers_per_gather = 4: 1.614 ms
-- max_parallel_workers_per_gather = 1: 2.346 ms
EXPLAIN ANALYZE SELECT max(length(c90)) FROM t3;

-- Columnar table using expression index
-- max_parallel_workers_per_gather = 4: 31.980 ms
-- max_parallel_workers_per_gather = 1: 38.766 ms
EXPLAIN ANALYZE SELECT max(length(c90)) FROM t4;
</code>

<code class="language-sql">-- Heap table using B-tree index

-- max_parallel_workers_per_gather = 4: 467.789 ms

-- max_parallel_workers_per_gather = 1: 748.939 ms

EXPLAIN ANALYZE SELECT sum(c2) FROM t3;

-- Columnar table using B-tree index

-- max_parallel_workers_per_gather = 4: 561.522 ms

-- max_parallel_workers_per_gather = 1: 599.629 ms

EXPLAIN ANALYZE SELECT sum(c2) FROM t4;

-- Heap table using expression index

-- max_parallel_workers_per_gather = 4: 1.614 ms

-- max_parallel_workers_per_gather = 1: 2.346 ms

EXPLAIN ANALYZE SELECT max(length(c90)) FROM t3;

-- Columnar table using expression index

-- max_parallel_workers_per_gather = 4: 31.980 ms

-- max_parallel_workers_per_gather = 1: 38.766 ms

EXPLAIN ANALYZE SELECT max(length(c90)) FROM t4;

</code>

About Table Constraints and Access Methods

Overall, indexes, constraints, and access methods are still evolving, with many features still to be implemented.

Let’s start with a major issue: DELETE and UPDATE are not supported.

<code class="language-sql">-- Fails
DELETE FROM t2 WHERE id = 5;

ERROR: UPDATE and CTID scans not supported for ColumnarScan

-- Fails
UPDATE t2 SET id = 5;

ERROR: UPDATE and CTID scans not supported for ColumnarScan
</code>

<code class="language-sql">-- Fails

DELETE FROM t2 WHERE id = 5;

ERROR: UPDATE and CTID scans not supported for ColumnarScan

-- Fails

UPDATE t2 SET id = 5;

ERROR: UPDATE and CTID scans not supported for ColumnarScan

</code>

Creating indexes on a columnar table is restricted to B-tree indexes:

<code class="language-sql">-- Works
CREATE INDEX ON t2 USING btree (id);

-- Fails
CREATE INDEX ON t2 USING columnar (id);

ERROR: unsupported access method for the index on columnar table t2
</code>

<code class="language-sql">-- Works

CREATE INDEX ON t2 USING btree (id);

-- Fails

CREATE INDEX ON t2 USING columnar (id);

ERROR: unsupported access method for the index on columnar table t2

</code>

Creating foreign key constraints is not implemented:

<code class="language-sql">SELECT generate_series AS id
INTO t3
FROM generate_series(0, 15);

ALTER TABLE t3 ADD PRIMARY KEY(id);

-- Works for the standard table t1.
ALTER TABLE t1 ADD FOREIGN KEY(id) REFERENCES t3(id);

-- Fails with the columnar table t2.
ALTER TABLE t2 ADD FOREIGN KEY(id) REFERENCES t3(generate_series);

ERROR: Foreign keys and AFTER ROW triggers are not supported for columnar tables
HINT: Consider an AFTER STATEMENT trigger instead.

-- Works after converting table t2 from columnar to heap.
ALTER TABLE t2 SET ACCESS METHOD heap;
ALTER TABLE t2 ADD FOREIGN KEY(id) REFERENCES t3(generate_series);
ALTER TABLE t2 SET ACCESS METHOD columnar;
</code>

<code class="language-sql">SELECT generate_series AS id

INTO t3

FROM generate_series(0, 15);

ALTER TABLE t3 ADD PRIMARY KEY(id);

-- Works for the standard table t1.

ALTER TABLE t1 ADD FOREIGN KEY(id) REFERENCES t3(id);

-- Fails with the columnar table t2.

ALTER TABLE t2 ADD FOREIGN KEY(id) REFERENCES t3(generate_series);

ERROR: Foreign keys and AFTER ROW triggers are not supported for columnar tables

HINT: Consider an AFTER STATEMENT trigger instead.

-- Works after converting table t2 from columnar to heap.

ALTER TABLE t2 SET ACCESS METHOD heap;

ALTER TABLE t2 ADD FOREIGN KEY(id) REFERENCES t3(generate_series);

ALTER TABLE t2 SET ACCESS METHOD columnar;

</code>

Partitioning

Columnar tables can be used as partitions. A partitioned table can be made up of any combination of row and columnar partitions.

An excellent use case is insert-once, read-only table partitions. In this scenario, you can leverage both columnar compression and better-performing OLAP-style queries for very large tables.

Caveats

As of version 11.1, the Citus columnar extension has several limitations:

Creating a columnar table takes more time than creating a standard heap table.
Query performance is equal to or slower than heap tables when using smaller tables.
UPDATE and DELETE operations are not possible in a columnar table.
Indexes are limited to B-tree indexes as of version 10.0.
Logical replication is not supported.
Additional limitations are documented in the extension documentation.

Author’s note: Regarding the custom packages created for this blog, the entire Citus suite is designed to enhance Postgres as a distributed database solution. Only the columnar table component was covered in this blog. The complete feature set is part of this package install and should work, but it has not been tested here.

Percona and PostgreSQL work better together. Try Percona Distribution for PostgreSQL today.

Conclusion

Despite its current limitations, there are use cases where this extension can make a meaningful difference. It also speaks well of the extension’s future as the team continues development and adds new capabilities. Watch for updates on its GitHub source repository.

0 0 votes

Article Rating

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Artur

3 years ago

Due to the GDPR, I had to resign from CITUS. DELETE could be a killer feature.

Author

Robert Bernier

3 years ago

Reply to Artur

I agree; having the ability to modify existing data would be a significant plus. Perhaps adding another compression algorithm capable of working with chunks of compressed data is the way to go.