Postgres with columnar compression in Hyperscale (Citus) on Azure

Published 05-22-2021 09:40 AM 3,158 Views
Microsoft

Citus Columnar, one of the big new features in Citus 10, is now available in preview in Hyperscale (Citus) on our Azure Database for PostgreSQL managed service! You can check out the Citus 10 superpowers post to learn more about the rest of the Citus 10 goodness.

 

Citus is an open source extension to Postgres (not a fork) that enables horizontal scale, but offers other great features, too.

This post will walk you through how to create and use columnar tables in Hyperscale (Citus). You can also take a look at the columnar docs. Citus Columnar can be used with or without the scale-out features of Citus.

 

First some background: Postgres typically stores data using the heap access method, which is row-based storage. Row-based tables are good for transactional workloads, but can cause excessive IO for some analytic queries.

 

Columnar storage is a new way to store data in a Postgres table. Columnar groups data together by column instead of by row; and compresses the data, too. Arranging data by column tends to compress well, and it also means that queries can skip over columns they don’t need. Columnar dramatically reduces the IO needed to answer a typical analytic query—often by 10X!

 

Let's check it out!

 

Postgres-elephant-on-stack-of-books-blue-background-1920x1080.jpg

 

Quick Start for Columnar in Hyperscale (Citus)

 

The quickest way to get up and running with Citus Columnar is in the cloud, by selecting Hyperscale (Citus) when provisioning an Azure Database for PostgreSQL server. You can follow the quickstart guide in the Azure docs.

 

Make sure to select PostgreSQL server version 13 (if not selectable, check the box "Enable Preview Features" in East US):

 

Figure 1: Screenshot from the Azure Portal, showing the Preview features checkbox for Hyperscale (Citus), plus where to select the PostgreSQL version.Figure 1: Screenshot from the Azure Portal, showing the Preview features checkbox for Hyperscale (Citus), plus where to select the PostgreSQL version.

 

Next, configure the server group:

 

Figure 2: Screenshot from the Azure Portal, showing where to click the "Configure server group".Figure 2: Screenshot from the Azure Portal, showing where to click the "Configure server group".

 

For simplicity, choose the Basic Tier:

 

Figure 3: Screenshot from the Compute + storage screen, showing the Basic tier and Standard tier radio buttons. To use shard Postgres on a single node, choose Basic tier.Figure 3: Screenshot from the Compute + storage screen, showing the Basic tier and Standard tier radio buttons. To use shard Postgres on a single node, choose Basic tier.

 

Click Save, and fill out the rest of the required fields. Then move to the “Networking” tab and configure it. For simplicity, click “Add current client IP address”:

 

Figure 4: For firewall rules, add current client IP address.Figure 4: For firewall rules, add current client IP address.

 

Configure anything else you'd like, then click “Review and Create”, and then “Create”.

 

After the deployment finishes, click “Go to resource”, and copy the coordinator name (hostname).

 

I'll be using the psql client for these examples, to make use of the backslash commands (e.g. \d+, \timing on). If using another client, you can omit the commands beginning with \, which are just informational and not necessary for functionality.

psql -h $COORDINATOR_NAME citus citus
--
-- Like all Postgres extensions, citus needs to be enabled
-- for this database.
--
CREATE EXTENSION IF NOT EXISTS citus;

--
-- Make an ordinary table, which is row-based storage, and a
-- columnar table.
--
CREATE TABLE simple_row(i INT8);
CREATE TABLE simple_columnar(i INT8) USING columnar;

--
-- Columnar tables act like row tables
--
INSERT INTO simple_row SELECT generate_series(1,100000);
INSERT INTO simple_columnar SELECT generate_series(1,100000);
SELECT AVG(i) FROM simple_row;
SELECT AVG(i) FROM simple_columnar;

Notice the "Access Method" when describing the table in psql:

\d+ simple_row
                                Table "public.simple_row"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
 i      | bigint |           |          |         | plain   |              |
Access method: heap

\d+ simple_columnar
                             Table "public.simple_columnar"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
 i      | bigint |           |          |         | plain   |              |
Access method: columnar

The default Access Method is heap, which means it's a plain Postgres table, which is row-based storage. The columnar table has Access Method columnar.

 

Citus Columnar Video Demo

 

We made this video demo of Citus Columnar—using Citus open source—to help you to see:

  1. How the query on the row table was so slow that I needed to skip ahead, while the query on the columnar table finished quickly.
  2. Better visualizations of partitioning, that make it easy to see how columnar and partitioning work together.
  3. The cool turtle in the background!

 

Figure 5: Two-part video demo of using Columnar with Citus open source.Figure 5: Two-part video demo of using Columnar with Citus open source.

Watch how Citus 10 brings columnar to Postgres!

 

What are the Benefits of Columnar?

 

  • Compression reduces storage requirements
  • Compression reduces the IO needed to scan the table
  • Projection Pushdown means that queries can skip over the columns that they don’t need, further reducing IO
  • Chunk Group Filtering allows queries to skip over Chunk Groups of data if the metadata indicates that none of the data in the chunk group will match the predicate. In other words, for certain kinds of queries and data sets, it can skip past a lot of the data quickly, without even decompressing it!

All of these together mean faster queries and lower costs!

 

Let's See the Performance of Columnar in Hyperscale (Citus)

 

Here’s a microbenchmark to show off what columnar can do. This is a “columnar friendly” use case—a wide table, and a query that only reads a few of the columns.

 

This benchmark illustrates two benefits of columnar for PostgreSQL:

  • Reduced IO due to compression
  • Reduced IO because it skips over the columns not needed to answer the query

 

Configuration

 

  • Azure Database for PostgreSQL Hyperscale (Citus)
  • Citus v10.0.3
  • PostgreSQL version 13
  • 2 vCores
  • 128 GiB storage

 

Schema

CREATE TABLE perf_row(
  c00 int8, c01 int8, c02 int8, c03 int8, c04 int8, c05 int8, c06 int8, c07 int8, c08 int8, c09 int8,
  c10 int8, c11 int8, c12 int8, c13 int8, c14 int8, c15 int8, c16 int8, c17 int8, c18 int8, c19 int8,
  c20 int8, c21 int8, c22 int8, c23 int8, c24 int8, c25 int8, c26 int8, c27 int8, c28 int8, c29 int8,
  c30 int8, c31 int8, c32 int8, c33 int8, c34 int8, c35 int8, c36 int8, c37 int8, c38 int8, c39 int8,
  c40 int8, c41 int8, c42 int8, c43 int8, c44 int8, c45 int8, c46 int8, c47 int8, c48 int8, c49 int8,
  c50 int8, c51 int8, c52 int8, c53 int8, c54 int8, c55 int8, c56 int8, c57 int8, c58 int8, c59 int8,
  c60 int8, c61 int8, c62 int8, c63 int8, c64 int8, c65 int8, c66 int8, c67 int8, c68 int8, c69 int8,
  c70 int8, c71 int8, c72 int8, c73 int8, c74 int8, c75 int8, c76 int8, c77 int8, c78 int8, c79 int8,
  c80 int8, c81 int8, c82 int8, c83 int8, c84 int8, c85 int8, c86 int8, c87 int8, c88 int8, c89 int8,
  c90 int8, c91 int8, c92 int8, c93 int8, c94 int8, c95 int8, c96 int8, c97 int8, c98 int8, c99 int8
);

CREATE TABLE perf_columnar(LIKE perf_row) USING COLUMNAR;

Data Load

\timing on

INSERT INTO perf_row
  SELECT
    g % 00500, g % 01000, g % 01500, g % 02000, g % 02500, g % 03000, g % 03500, g % 04000, g % 04500, g % 05000,
    g % 05500, g % 06000, g % 06500, g % 07000, g % 07500, g % 08000, g % 08500, g % 09000, g % 09500, g % 10000,
    g % 10500, g % 11000, g % 11500, g % 12000, g % 12500, g % 13000, g % 13500, g % 14000, g % 14500, g % 15000,
    g % 15500, g % 16000, g % 16500, g % 17000, g % 17500, g % 18000, g % 18500, g % 19000, g % 19500, g % 20000,
    g % 20500, g % 21000, g % 21500, g % 22000, g % 22500, g % 23000, g % 23500, g % 24000, g % 24500, g % 25000,
    g % 25500, g % 26000, g % 26500, g % 27000, g % 27500, g % 28000, g % 28500, g % 29000, g % 29500, g % 30000,
    g % 30500, g % 31000, g % 31500, g % 32000, g % 32500, g % 33000, g % 33500, g % 34000, g % 34500, g % 35000,
    g % 35500, g % 36000, g % 36500, g % 37000, g % 37500, g % 38000, g % 38500, g % 39000, g % 39500, g % 40000,
    g % 40500, g % 41000, g % 41500, g % 42000, g % 42500, g % 43000, g % 43500, g % 44000, g % 44500, g % 45000,
    g % 45500, g % 46000, g % 46500, g % 47000, g % 47500, g % 48000, g % 48500, g % 49000, g % 49500, g % 50000
  FROM generate_series(1,50000000) g;

INSERT INTO perf_columnar
  SELECT
    g % 00500, g % 01000, g % 01500, g % 02000, g % 02500, g % 03000, g % 03500, g % 04000, g % 04500, g % 05000,
    g % 05500, g % 06000, g % 06500, g % 07000, g % 07500, g % 08000, g % 08500, g % 09000, g % 09500, g % 10000,
    g % 10500, g % 11000, g % 11500, g % 12000, g % 12500, g % 13000, g % 13500, g % 14000, g % 14500, g % 15000,
    g % 15500, g % 16000, g % 16500, g % 17000, g % 17500, g % 18000, g % 18500, g % 19000, g % 19500, g % 20000,
    g % 20500, g % 21000, g % 21500, g % 22000, g % 22500, g % 23000, g % 23500, g % 24000, g % 24500, g % 25000,
    g % 25500, g % 26000, g % 26500, g % 27000, g % 27500, g % 28000, g % 28500, g % 29000, g % 29500, g % 30000,
    g % 30500, g % 31000, g % 31500, g % 32000, g % 32500, g % 33000, g % 33500, g % 34000, g % 34500, g % 35000,
    g % 35500, g % 36000, g % 36500, g % 37000, g % 37500, g % 38000, g % 38500, g % 39000, g % 39500, g % 40000,
    g % 40500, g % 41000, g % 41500, g % 42000, g % 42500, g % 43000, g % 43500, g % 44000, g % 44500, g % 45000,
    g % 45500, g % 46000, g % 46500, g % 47000, g % 47500, g % 48000, g % 48500, g % 49000, g % 49500, g % 50000
  FROM generate_series(1,50000000) g;

VACUUM (FREEZE, ANALYZE) perf_row;
VACUUM (FREEZE, ANALYZE) perf_columnar;

-- checkpoint if superuser; otherwise wait for system to settle
CHECKPOINT; CHECKPOINT;

The row table loaded in 2274s, while the columnar table loaded in 1111s for a speedup of 2X. Load speed is not always better with columnar, but columnar does have the advantage when the system is IO-bound.

 

Compression Ratio

 

For this data, you can see a compression ratio of about 7X when using Columnar.

SELECT pg_total_relation_size('perf_row')::numeric/
       pg_total_relation_size('perf_columnar') AS compression_ratio;
 compression_ratio  
--------------------
 6.7394916300510456
(1 row)

(Note: the compression ratio varies depending on the version of the compression library. Earlier versions of zstd showed a better compression ratio of better than 8X. I have filed an issue with the zstd project in case it's a bug that can be fixed.)

 

We can also get some information from VACUUM VERBOSE:

VACUUM VERBOSE perf_columnar;
INFO:  statistics for "perf_columnar":
storage id: 10000000003
total file size: 6754770944, total data size: 6733594337
compression rate: 6.03x
total row count: 50000000, stripe count: 334, average rows per stripe: 149700
chunk count: 500000, containing data for dropped columns: 0, zstd compressed: 500000

VACUUM

Notice that there are 334 stripes. Stripes are the unit of a data load/write. By default, each stripe can hold up to 150,000 tuples.

By default, data is compressed with zstd compression. The compression rate calculated by VACUUM VERBOSE is slightly different than what we saw above, because it considers only the average compression ratio of the data, and doesn’t account for metadata (like visibility information).

 

Queries

 

Now let's run a couple SQL queries. We will use EXPLAIN ANALYZE so that we can see the details in addition to the overall runtime. Timings are taken from the median of three consecutive runs.

 

Notice that only a 3 out of 100 columns are necessary to answer this query.

--
-- Parallel query actually slows down the query on the row table in this example, so disable it.
-- Columnar doesn't support parallel query.
--
SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, BUFFERS) SELECT c00, SUM(c29), AVG(c71) FROM perf_row GROUP BY c00;
                                                            QUERY PLAN                                                                                                       
----------------------------------------------------------------------------------------------------------------------------------                                           
 HashAggregate  (cost=6430556.07..6430563.57 rows=500 width=72) (actual time=449720.051..449720.570 rows=500 loops=1)                                                        
   Group Key: c00                                                                     
   Batches: 1  Memory Usage: 169kB                                                                                                                                           
   Buffers: shared hit=32 read=5555524
   I/O Timings: read=411407.314                                                                                                                                              
   ->  Seq Scan on perf_row  (cost=0.00..6055556.04 rows=50000004 width=24) (actual time=0.136..429496.825 rows=50000000 loops=1)                                            
         Buffers: shared hit=32 read=5555524                                                                                                                                 
         I/O Timings: read=411407.314                                                 
 Planning:                             
   Buffers: shared hit=4      
 Planning Time: 0.156 ms                                                                                                                                                     
 JIT:                                                                                 
   Functions: 7                                                                                                                                                              
   Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                               
   Timing: Generation 1.852 ms, Inlining 21.516 ms, Optimization 138.386 ms, Emission 67.720 ms, Total 229.474 ms                                                            
 Execution Time: 449722.605 ms                                                                                                                                               
(16 rows)                                                                                                                                                                    

EXPLAIN (ANALYZE, BUFFERS) SELECT c00, SUM(c29), AVG(c71) FROM perf_columnar GROUP BY c00;
                                                                      QUERY PLAN                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=399659.16..399662.16 rows=200 width=72) (actual time=20688.895..20689.190 rows=500 loops=1)
   Group Key: c00
   Batches: 1  Memory Usage: 185kB
   Buffers: shared hit=109765
   ->  Custom Scan (ColumnarScan) on perf_columnar  (cost=0.00..24659.16 rows=50000000 width=24) (actual time=6.285..10261.578 rows=50000000 loops=1)
         Columnar Chunk Groups Removed by Filter: 0
         Buffers: shared hit=109765
 Planning:
   Buffers: shared hit=20
 Planning Time: 0.682 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.349 ms, Inlining 0.000 ms, Optimization 0.234 ms, Emission 4.110 ms, Total 5.692 ms
 Execution Time: 20690.686 ms
(15 rows)

We see a dramatic difference in the overall buffers read, which results in a dramatic difference in the runtime:

 

Storage Columnar Speedup
Row 450s  
Columnar 21s 21X

 

What are the Limitations?

 

These limitations are not set in stone, and we look forward to working on them in the future:

  • No UPDATE or DELETE support
  • No index support
  • No logical replication or logical decoding support
  • See more limitations in the columnar README

This means that, as of Citus 10 in Hyperscale (Citus), columnar should be used for append-only tables that are used for analytic queries. Even if UPDATE/DELETE are supported in the future, UPDATE & DELETE will not be as efficient as they are on row-based storage, so columnar is not a good fit for many transactional workloads. However, you can pick and choose columnar where it works best, and use row tables where they work best, to get the benefits of both.

 

Hybrid Columnar and Row tables with Range Partitioning

 

A useful way to take advantage of Citus Columnar is when you combine it with native range partitioning. Using columnar with partitioning helps to overcome the limitation on updates and deletes, by using a mix of row and columnar partitions within the same partitioned table.

Normally, range partitioning is used for time-based partitioning. Often, you have one or two recent “active” partitions that are still being updated, and then many older partitions that are rarely updated but still queried. In this case the one or two active partitions can be row-based storage to allow updates, and the older partitions can be converted to columnar storage to benefit from compression and scan speed.

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

CREATE TABLE events_2021_jan PARTITION OF events
  FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

CREATE TABLE events_2021_feb PARTITION OF events
  FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

INSERT INTO events SELECT
    '2021-01-01'::timestamptz + '0.45 seconds'::interval * g,
    g,
    g*pi(),
    'number: ' || g::text
    FROM generate_series(1,10000000) g;

VACUUM (FREEZE, ANALYZE) events_2021_feb;

Later, when you're ready to “columnarize” the older January partition, you can use this Citus-provided function to convert the access method from row to columnar storage.

SELECT alter_table_set_access_method('events_2021_jan', 'columnar');
VACUUM (FREEZE, ANALYZE) events_2021_jan;

-- checkpoint if superuser; otherwise wait for system to settle
CHECKPOINT; CHECKPOINT;

Now you can see that the January partition is columnar, and the February partition is row storage (heap).

\d+ events_2021_jan
                                       Table "public.events_2021_jan"
 Column |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 ts     | timestamp with time zone |           |          |         | plain    |              |
 i      | integer                  |           |          |         | plain    |              |
 n      | numeric                  |           |          |         | main     |              |
 s      | text                     |           |          |         | extended |              |
Partition of: events FOR VALUES FROM ('2021-01-01 00:00:00+00') TO ('2021-02-01 00:00:00+00')
Partition constraint: ((ts IS NOT NULL) AND (ts >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (ts < '2021-02-01 00:00:00+00'::timestamp with time zone))
Access method: columnar

\d+ events_2021_feb
                                      Table "public.events_2021_feb"
 Column |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 ts     | timestamp with time zone |           |          |         | plain    |              |
 i      | integer                  |           |          |         | plain    |              |
 n      | numeric                  |           |          |         | main     |              |
 s      | text                     |           |          |         | extended |              |
Partition of: events FOR VALUES FROM ('2021-02-01 00:00:00+00') TO ('2021-03-01 00:00:00+00')
Partition constraint: ((ts IS NOT NULL) AND (ts >= '2021-02-01 00:00:00+00'::timestamp with time zone) AND (ts < '2021-03-01 00:00:00+00'::timestamp with time zone))
Access method: heap

And they each have about half the rows. But even though the January partition has more rows, it’s much smaller due to columnar compression:

SELECT COUNT(*) FROM events; -- parent table scans both partitions
  count
----------
 10000000
(1 row)

SELECT COUNT(*) FROM events_2021_jan;
  count
---------
 5951999
(1 row)

SELECT COUNT(*) FROM events_2021_feb;
  count
---------
 4048001
(1 row)

SELECT pg_size_pretty(pg_relation_size('events_2021_jan'));
 pg_size_pretty
----------------
 69 MB
(1 row)

SELECT pg_size_pretty(pg_relation_size('events_2021_feb'));
 pg_size_pretty
----------------
 264 MB
(1 row)

 

What is Chunk Group Filtering?

 

Let’s run a query for a particular hour within the January partition of the events table.

EXPLAIN (ANALYZE,BUFFERS)
SELECT SUM(n)
FROM events_2021_jan
  WHERE ts >= '2021-01-11 01:00'::timestamptz AND
        ts < '2021-01-11 02:00'::timestamptz;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4438.09..4438.10 rows=1 width=32) (actual time=8.356..8.357 rows=1 loops=1)
   Buffers: shared hit=2922 read=2
   ->  Custom Scan (ColumnarScan) on events_2021_jan  (cost=0.00..4418.55 rows=7815 width=11) (actual time=2.998..7.703 rows=8000 loops=1)
         Filter: ((ts >= '2021-01-11 01:00:00+00'::timestamp with time zone) AND (ts < '2021-01-11 02:00:00+00'::timestamp with time zone))
         Rows Removed by Filter: 12000
         Columnar Chunk Groups Removed by Filter: 594
         Buffers: shared hit=2922 read=2
 Planning:
   Buffers: shared hit=27 dirtied=2
 Planning Time: 0.233 ms
 Execution Time: 8.380 ms
(11 rows)

Notice Columnar Chunk Groups Removed by Filter: 594.

 

First, we need some terminology to understand this:

 

  • Stripe: all loads into a columnar table are broken into stripes of 150000 rows (by default). The larger a stripe, the more sequential access when reading a given column.
  • Chunk Group: Stripes are broken down further into Chunk Groups of 10000 rows (by default).
  • Chunk: Each Chunk Group consists of one Chunk for each column. A Chunk is the unit of compression, and the min/max is tracked for each chunk to enable Chunk Group Filtering.
  • Chunk Group Filtering: When a query’s WHERE clause can't possibly match any of the tuples in a Chunk, and we know that by the min/max values of the chunk, then Chunk Group Filtering will simply skip over the whole Chunk Group without decompressing any of the Chunks in the Chunk Group.

You can see above that 594 Chunk Groups were filtered out, which means that 5931999 rows were filtered out without needing to fetch or decompress the data. Only 2 Chunk Groups (20000 rows) needed to be actually fetched and decompressed, which is why the query took only milliseconds.

 

How is Citus Columnar connected to Citus?

 

Citus Columnar is a new feature that we've introduced in Citus 10. Now that Citus 10 is available in preview in Hyperscale (Citus), as long as you've turned on the preview features, you can now create Postgres tables with Hyperscale (Citus) with the new USING columnar syntax, and you're ready to go (of course, read the docs, too!).

 

Citus is known for its ability to scale Postgres. Importantly, you can use Columnar with or without the Citus scale-out features. Columnar is a great complement to typical Citus use cases, but you can pick and choose whether to use Citus Columnar on a single node, or as part of a distributed Citus cluster.

 

You can mix and match:

  • columnar and row tables
  • columnar and row partitions of the same table
  • local columnar tables and distributed columnar tables; you can mix local and distributed tables on a single node with Basic tier—as well as on a distributed Hyperscale (Citus) cluster

 

What about cstore_fdw?

 

If you've heard of the cstore_fdw extension that my teammates at Citus created a number of years ago, you can think of Citus Columnar as the next generation of cstore_fdw. If using cstore_fdw, consider migrating to Citus Columnar.

 

cstore_fdw achieved the core benefits of columnar in terms of performance; but Citus Columnar goes much further in terms of integration and feature compatibility.

 

Citus Columnar works with:

  • ROLLBACK
  • Write-ahead logging (WAL)
  • Phsyical Replication
  • pg_upgrade

and also provides a more seamless user experience, similar to ordinary Postgres row tables.

 

Citus Columnar was able to accomplish this better experience by using the Table Access Method API, new in PostgreSQL version 12. Using this new extension API allows tighter integration into Postgres while still being a pure extension.

 

Try out Citus Columnar for your analytics workloads

 

The dramatic compression and scan speed improvements offered by Columnar in Hyperscale (Citus) allow you to do more with less. Take advantage by identifying large, append-only tables and evaluate whether columnar will improve performance or reduce costs.

Columnar storage may also allow you to keep data in Postgres for longer, rather than forcing you to archive older data, where it can't be queried efficiently.

 

If you want to dive even deeper into learning about Citus Columnar, I recommend:

 

Try out Citus 10—open source or in the cloud

 

 

Finally, special thanks to Hadi Moshayedi, who was the original author of cstore_fdw (the mature basis of Citus columnar), and is the co-author of Citus 10 columnar!

1 Comment

Thank you @j-davis for Sharing this awesome blogpost :cool:

%3CLINGO-SUB%20id%3D%22lingo-sub-2378842%22%20slang%3D%22en-US%22%3ERe%3A%20Postgres%20with%20columnar%20compression%20in%20Hyperscale%20(Citus)%20on%20Azure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378842%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728581%22%20target%3D%22_blank%22%3E%40j-davis%3C%2FA%3E%26nbsp%3Bfor%20Sharing%20this%20awesome%20blogpost%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2F%40B71AFCCE02F5853FE57A20BD4B04EADD%2Fimages%2Femoticons%2Fcool_40x40.gif%22%20alt%3D%22%3Acool%3A%22%20title%3D%22%3Acool%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373870%22%20slang%3D%22en-US%22%3EPostgres%20with%20columnar%20compression%20in%20Hyperscale%20(Citus)%20on%20Azure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373870%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22markdown-body%22%3E%0A%3CP%3ECitus%20Columnar%2C%20one%20of%20the%20big%20new%20features%20in%20Citus%2010%2C%20is%20now%20available%20in%20preview%20in%20Hyperscale%20(Citus)%20on%20our%20Azure%20Database%20for%20PostgreSQL%20managed%20service!%20You%20can%20check%20out%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%2010%20superpowers%20post%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fnew-postgres-superpowers-in-hyperscale-citus-with-citus-10%2Fba-p%2F2335404%22%20target%3D%22_blank%22%3ECitus%2010%20superpowers%20post%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20learn%20more%20about%20the%20rest%20of%20the%20Citus%2010%20goodness.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECitus%20is%20an%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%2010%20github%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcitus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eopen%20source%20extension%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20Postgres%20(not%20a%20fork)%20that%20enables%20horizontal%20scale%2C%20but%20offers%20other%20great%20features%2C%20too.%3C%2FP%3E%0A%3CP%3EThis%20post%20will%20walk%20you%20through%20how%20to%20create%20and%20use%20columnar%20tables%20in%20Hyperscale%20(Citus).%20You%20can%20also%20take%20a%20look%20at%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22columnar%20docs%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpostgresql%2Fconcepts-hyperscale-columnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecolumnar%20docs%3C%2FA%3E.%20Citus%20Columnar%20can%20be%20used%20with%20or%20without%20the%20scale-out%20features%20of%20Citus.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20some%20background%3A%20Postgres%20typically%20stores%20data%20using%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Eheap%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eaccess%20method%2C%20which%20is%20row-based%20storage.%20Row-based%20tables%20are%20good%20for%20transactional%20workloads%2C%20but%20can%20cause%20excessive%20IO%20for%20some%20analytic%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EColumnar%20storage%20is%20a%20new%20way%20to%20store%20data%20in%20a%20Postgres%20table.%20Columnar%20groups%20data%20together%20by%20column%20instead%20of%20by%20row%3B%20and%20compresses%20the%20data%2C%20too.%20Arranging%20data%20by%20column%20tends%20to%20compress%20well%2C%20and%20it%20also%20means%20that%20queries%20can%20skip%20over%20columns%20they%20don%E2%80%99t%20need.%20Columnar%20dramatically%20reduces%20the%20IO%20needed%20to%20answer%20a%20typical%20analytic%20query%E2%80%94often%20by%2010X!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20check%20it%20out!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Postgres-elephant-on-stack-of-books-blue-background-1920x1080.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282517iEFFDB3052BC4954A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Postgres-elephant-on-stack-of-books-blue-background-1920x1080.jpg%22%20alt%3D%22Postgres-elephant-on-stack-of-books-blue-background-1920x1080.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--465001231%22%20id%3D%22toc-hId--465142476%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-2022511602%22%20id%3D%22toc-hId-2022370357%22%3E%3CA%20id%3D%22user-content-quick-start-for-columnar-in-hyperscale-citus%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23quick-start-for-columnar-in-hyperscale-citus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EQuick%20Start%20for%20Columnar%20in%20Hyperscale%20(Citus)%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20quickest%20way%20to%20get%20up%20and%20running%20with%20Citus%20Columnar%20is%20in%20the%20cloud%2C%20by%20selecting%20Hyperscale%20(Citus)%20when%20provisioning%20an%20Azure%20Database%20for%20PostgreSQL%20server.%20You%20can%20follow%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Hyperscale%20(Citus)%20quickstart%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpostgresql%2Fquickstart-create-hyperscale-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Equickstart%20guide%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%20the%20Azure%20docs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20to%20select%20PostgreSQL%20server%20version%2013%20(if%20not%20selectable%2C%20check%20the%20box%20%22Enable%20Preview%20Features%22%20in%20East%20US)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Figure1-screenshot-of-Azure-portal-enabling-Postgres-v13.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282515i358BB87957BA15DA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Figure1-screenshot-of-Azure-portal-enabling-Postgres-v13.png%22%20alt%3D%22Figure%201%3A%20Screenshot%20from%20the%20Azure%20Portal%2C%20showing%20the%20Preview%20features%20checkbox%20for%20Hyperscale%20(Citus)%2C%20plus%20where%20to%20select%20the%20PostgreSQL%20version.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFigure%201%3A%20Screenshot%20from%20the%20Azure%20Portal%2C%20showing%20the%20Preview%20features%20checkbox%20for%20Hyperscale%20(Citus)%2C%20plus%20where%20to%20select%20the%20PostgreSQL%20version.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20configure%20the%20server%20group%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Figure2-screenshot-Azure-portal-how-to-configure-Hyperscale-Citus-server-group.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282518iD6DF3F8F0A77BA9F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Figure2-screenshot-Azure-portal-how-to-configure-Hyperscale-Citus-server-group.png%22%20alt%3D%22Figure%202%3A%20Screenshot%20from%20the%20Azure%20Portal%2C%20showing%20where%20to%20click%20the%20%26quot%3BConfigure%20server%20group%26quot%3B.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFigure%202%3A%20Screenshot%20from%20the%20Azure%20Portal%2C%20showing%20where%20to%20click%20the%20%22Configure%20server%20group%22.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20simplicity%2C%20choose%20the%20Basic%20Tier%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Figure3-screenshot-Azure-portal-selecting-Hyperscale-Citus-basic-tier-or-standard-tier.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282519i7D3CAC56FA3E4BAD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Figure3-screenshot-Azure-portal-selecting-Hyperscale-Citus-basic-tier-or-standard-tier.png%22%20alt%3D%22Figure%203%3A%20Screenshot%20from%20the%20Compute%20%2B%20storage%20screen%2C%20showing%20the%20Basic%20tier%20and%20Standard%20tier%20radio%20buttons.%20To%20use%20shard%20Postgres%20on%20a%20single%20node%2C%20choose%20Basic%20tier.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFigure%203%3A%20Screenshot%20from%20the%20Compute%20%2B%20storage%20screen%2C%20showing%20the%20Basic%20tier%20and%20Standard%20tier%20radio%20buttons.%20To%20use%20shard%20Postgres%20on%20a%20single%20node%2C%20choose%20Basic%20tier.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Save%2C%20and%20fill%20out%20the%20rest%20of%20the%20required%20fields.%20Then%20move%20to%20the%20%E2%80%9CNetworking%E2%80%9D%20tab%20and%20configure%20it.%20For%20simplicity%2C%20click%20%E2%80%9CAdd%20current%20client%20IP%20address%E2%80%9D%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Figure4-screenshot-firewall-rules-adding-current-IP-address.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282520iAE294EA1974D418D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Figure4-screenshot-firewall-rules-adding-current-IP-address.png%22%20alt%3D%22Figure%204%3A%20For%20firewall%20rules%2C%20add%20current%20client%20IP%20address.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFigure%204%3A%20For%20firewall%20rules%2C%20add%20current%20client%20IP%20address.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConfigure%20anything%20else%20you'd%20like%2C%20then%20click%20%E2%80%9CReview%20and%20Create%E2%80%9D%2C%20and%20then%20%E2%80%9CCreate%E2%80%9D.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20the%20deployment%20finishes%2C%20click%20%E2%80%9CGo%20to%20resource%E2%80%9D%2C%20and%20copy%20the%20coordinator%20name%20(hostname).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'll%20be%20using%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Epsql%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eclient%20for%20these%20examples%2C%20to%20make%20use%20of%20the%20backslash%20commands%20(e.g.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3E%5Cd%2B%3C%2FCODE%3E%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3E%5Ctiming%20on%3C%2FCODE%3E).%20If%20using%20another%20client%2C%20you%20can%20omit%20the%20commands%20beginning%20with%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3E%5C%3C%2FCODE%3E%2C%20which%20are%20just%20informational%20and%20not%20necessary%20for%20functionality.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Epsql%20-h%20%24COORDINATOR_NAME%20citus%20citus%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--%0A--%20Like%20all%20Postgres%20extensions%2C%20citus%20needs%20to%20be%20enabled%0A--%20for%20this%20database.%0A--%0ACREATE%20EXTENSION%20IF%20NOT%20EXISTS%20citus%3B%0A%0A--%0A--%20Make%20an%20ordinary%20table%2C%20which%20is%20row-based%20storage%2C%20and%20a%0A--%20columnar%20table.%0A--%0ACREATE%20TABLE%20simple_row(i%20INT8)%3B%0ACREATE%20TABLE%20simple_columnar(i%20INT8)%20USING%20columnar%3B%0A%0A--%0A--%20Columnar%20tables%20act%20like%20row%20tables%0A--%0AINSERT%20INTO%20simple_row%20SELECT%20generate_series(1%2C100000)%3B%0AINSERT%20INTO%20simple_columnar%20SELECT%20generate_series(1%2C100000)%3B%0ASELECT%20AVG(i)%20FROM%20simple_row%3B%0ASELECT%20AVG(i)%20FROM%20simple_columnar%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENotice%20the%20%22Access%20Method%22%20when%20describing%20the%20table%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Epsql%3C%2FCODE%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%5Cd%2B%20simple_row%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table%20%22public.simple_row%22%0A%20Column%20%7C%20%20Type%20%20%7C%20Collation%20%7C%20Nullable%20%7C%20Default%20%7C%20Storage%20%7C%20Stats%20target%20%7C%20Description%0A--------%2B--------%2B-----------%2B----------%2B---------%2B---------%2B--------------%2B-------------%0A%20i%20%20%20%20%20%20%7C%20bigint%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0AAccess%20method%3A%20heap%0A%0A%5Cd%2B%20simple_columnar%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table%20%22public.simple_columnar%22%0A%20Column%20%7C%20%20Type%20%20%7C%20Collation%20%7C%20Nullable%20%7C%20Default%20%7C%20Storage%20%7C%20Stats%20target%20%7C%20Description%0A--------%2B--------%2B-----------%2B----------%2B---------%2B---------%2B--------------%2B-------------%0A%20i%20%20%20%20%20%20%7C%20bigint%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0AAccess%20method%3A%20columnar%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20default%20Access%20Method%20is%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Eheap%3C%2FCODE%3E%2C%20which%20means%20it's%20a%20plain%20Postgres%20table%2C%20which%20is%20row-based%20storage.%20The%20columnar%20table%20has%20Access%20Method%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Ecolumnar%3C%2FCODE%3E.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-215057139%22%20id%3D%22toc-hId-214915894%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1592397324%22%20id%3D%22toc-hId--1592538569%22%3E%3CA%20id%3D%22user-content-citus-columnar-video-demo%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23citus-columnar-video-demo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ECitus%20Columnar%20Video%20Demo%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20made%20this%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22video%20demo%20of%20Citus%20Columnar%22%20href%3D%22https%3A%2F%2Fyoutu.be%2FSS7jcq9fTnw%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Evideo%20demo%20of%20Citus%20Columnar%3C%2FA%3E%E2%80%94using%20Citus%20open%20source%E2%80%94to%20help%20you%20to%20see%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EHow%20the%20query%20on%20the%20row%20table%20was%20so%20slow%20that%20I%20needed%20to%20skip%20ahead%2C%20while%20the%20query%20on%20the%20columnar%20table%20finished%20quickly.%3C%2FLI%3E%0A%3CLI%3EBetter%20visualizations%20of%20partitioning%2C%20that%20make%20it%20easy%20to%20see%20how%20columnar%20and%20partitioning%20work%20together.%3C%2FLI%3E%0A%3CLI%3EThe%20cool%20turtle%20in%20the%20background!%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20title%3D%22Citus%2010%20brings%20Columnar%20to%20Postgres%20video%22%20href%3D%22https%3A%2F%2Fyoutu.be%2FSS7jcq9fTnw%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Figure5-youtube-thumbnail-Citus10-Brings-Columnar-to-Postgres-video-demo-with-smiling-engineer.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282521i5BD9F1E04D18E799%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Figure5-youtube-thumbnail-Citus10-Brings-Columnar-to-Postgres-video-demo-with-smiling-engineer.jpg%22%20alt%3D%22Figure%205%3A%20Two-part%20video%20demo%20of%20using%20Columnar%20with%20Citus%20open%20source.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFigure%205%3A%20Two-part%20video%20demo%20of%20using%20Columnar%20with%20Citus%20open%20source.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EWatch%20how%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%2010%20brings%20columnar%20to%20Postgres%20blog%20post%22%20href%3D%22https%3A%2F%2Fyoutu.be%2FSS7jcq9fTnw%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECitus%2010%20brings%20columnar%20to%20Postgres%3C%2FA%3E!%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-895115509%22%20id%3D%22toc-hId-894974264%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--912338954%22%20id%3D%22toc-hId--912480199%22%3E%3CA%20id%3D%22user-content-what-are-the-benefits-of-columnar%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23what-are-the-benefits-of-columnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EWhat%20are%20the%20Benefits%20of%20Columnar%3F%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3ECompression%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ereduces%20storage%20requirements%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3ECompression%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ereduces%20the%20IO%20needed%20to%20scan%20the%20table%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EProjection%20Pushdown%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Emeans%20that%20queries%20can%20skip%20over%20the%20columns%20that%20they%20don%E2%80%99t%20need%2C%20further%20reducing%20IO%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EChunk%20Group%20Filtering%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eallows%20queries%20to%20skip%20over%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EChunk%20Groups%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20data%20if%20the%20metadata%20indicates%20that%20none%20of%20the%20data%20in%20the%20chunk%20group%20will%20match%20the%20predicate.%20In%20other%20words%2C%20for%20certain%20kinds%20of%20queries%20and%20data%20sets%2C%20it%20can%20skip%20past%20a%20lot%20of%20the%20data%20quickly%2C%20without%20even%20decompressing%20it!%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EAll%20of%20these%20together%20mean%20faster%20queries%20and%20lower%20costs!%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1575173879%22%20id%3D%22toc-hId-1575032634%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--232280584%22%20id%3D%22toc-hId--232421829%22%3E%3CA%20id%3D%22user-content-lets-see-the-performance-of-columnar-in-hyperscale-citus%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23lets-see-the-performance-of-columnar-in-hyperscale-citus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ELet's%20See%20the%20Performance%20of%20Columnar%20in%20Hyperscale%20(Citus)%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%E2%80%99s%20a%20microbenchmark%20to%20show%20off%20what%20columnar%20can%20do.%20This%20is%20a%20%E2%80%9Ccolumnar%20friendly%E2%80%9D%20use%20case%E2%80%94a%20wide%20table%2C%20and%20a%20query%20that%20only%20reads%20a%20few%20of%20the%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20benchmark%20illustrates%20two%20benefits%20of%20columnar%20for%20PostgreSQL%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EReduced%20IO%20due%20to%20compression%3C%2FLI%3E%0A%3CLI%3EReduced%20IO%20because%20it%20skips%20over%20the%20columns%20not%20needed%20to%20answer%20the%20query%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH3%20id%3D%22toc-hId-458280890%22%20id%3D%22toc-hId-458139645%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId--1349173573%22%20id%3D%22toc-hId--1349314818%22%3E%3CA%20id%3D%22user-content-configuration%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23configuration%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EConfiguration%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAzure%20Database%20for%20PostgreSQL%20Hyperscale%20(Citus)%3C%2FLI%3E%0A%3CLI%3ECitus%20v10.0.3%3C%2FLI%3E%0A%3CLI%3EPostgreSQL%20version%2013%3C%2FLI%3E%0A%3CLI%3E2%20vCores%3C%2FLI%3E%0A%3CLI%3E128%20GiB%20storage%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH3%20id%3D%22toc-hId-443175781%22%20id%3D%22toc-hId-443034536%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId--1364278682%22%20id%3D%22toc-hId--1364419927%22%3E%3CA%20id%3D%22user-content-schema%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23schema%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ESchema%3C%2FH3%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20perf_row(%0A%20%20c00%20int8%2C%20c01%20int8%2C%20c02%20int8%2C%20c03%20int8%2C%20c04%20int8%2C%20c05%20int8%2C%20c06%20int8%2C%20c07%20int8%2C%20c08%20int8%2C%20c09%20int8%2C%0A%20%20c10%20int8%2C%20c11%20int8%2C%20c12%20int8%2C%20c13%20int8%2C%20c14%20int8%2C%20c15%20int8%2C%20c16%20int8%2C%20c17%20int8%2C%20c18%20int8%2C%20c19%20int8%2C%0A%20%20c20%20int8%2C%20c21%20int8%2C%20c22%20int8%2C%20c23%20int8%2C%20c24%20int8%2C%20c25%20int8%2C%20c26%20int8%2C%20c27%20int8%2C%20c28%20int8%2C%20c29%20int8%2C%0A%20%20c30%20int8%2C%20c31%20int8%2C%20c32%20int8%2C%20c33%20int8%2C%20c34%20int8%2C%20c35%20int8%2C%20c36%20int8%2C%20c37%20int8%2C%20c38%20int8%2C%20c39%20int8%2C%0A%20%20c40%20int8%2C%20c41%20int8%2C%20c42%20int8%2C%20c43%20int8%2C%20c44%20int8%2C%20c45%20int8%2C%20c46%20int8%2C%20c47%20int8%2C%20c48%20int8%2C%20c49%20int8%2C%0A%20%20c50%20int8%2C%20c51%20int8%2C%20c52%20int8%2C%20c53%20int8%2C%20c54%20int8%2C%20c55%20int8%2C%20c56%20int8%2C%20c57%20int8%2C%20c58%20int8%2C%20c59%20int8%2C%0A%20%20c60%20int8%2C%20c61%20int8%2C%20c62%20int8%2C%20c63%20int8%2C%20c64%20int8%2C%20c65%20int8%2C%20c66%20int8%2C%20c67%20int8%2C%20c68%20int8%2C%20c69%20int8%2C%0A%20%20c70%20int8%2C%20c71%20int8%2C%20c72%20int8%2C%20c73%20int8%2C%20c74%20int8%2C%20c75%20int8%2C%20c76%20int8%2C%20c77%20int8%2C%20c78%20int8%2C%20c79%20int8%2C%0A%20%20c80%20int8%2C%20c81%20int8%2C%20c82%20int8%2C%20c83%20int8%2C%20c84%20int8%2C%20c85%20int8%2C%20c86%20int8%2C%20c87%20int8%2C%20c88%20int8%2C%20c89%20int8%2C%0A%20%20c90%20int8%2C%20c91%20int8%2C%20c92%20int8%2C%20c93%20int8%2C%20c94%20int8%2C%20c95%20int8%2C%20c96%20int8%2C%20c97%20int8%2C%20c98%20int8%2C%20c99%20int8%0A)%3B%0A%0ACREATE%20TABLE%20perf_columnar(LIKE%20perf_row)%20USING%20COLUMNAR%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CH3%20id%3D%22toc-hId-1123234151%22%20id%3D%22toc-hId-1123092906%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId--684220312%22%20id%3D%22toc-hId--684361557%22%3E%3CA%20id%3D%22user-content-data-load%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23data-load%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EData%20Load%3C%2FH3%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%5Ctiming%20on%0A%0AINSERT%20INTO%20perf_row%0A%20%20SELECT%0A%20%20%20%20g%20%25%2000500%2C%20g%20%25%2001000%2C%20g%20%25%2001500%2C%20g%20%25%2002000%2C%20g%20%25%2002500%2C%20g%20%25%2003000%2C%20g%20%25%2003500%2C%20g%20%25%2004000%2C%20g%20%25%2004500%2C%20g%20%25%2005000%2C%0A%20%20%20%20g%20%25%2005500%2C%20g%20%25%2006000%2C%20g%20%25%2006500%2C%20g%20%25%2007000%2C%20g%20%25%2007500%2C%20g%20%25%2008000%2C%20g%20%25%2008500%2C%20g%20%25%2009000%2C%20g%20%25%2009500%2C%20g%20%25%2010000%2C%0A%20%20%20%20g%20%25%2010500%2C%20g%20%25%2011000%2C%20g%20%25%2011500%2C%20g%20%25%2012000%2C%20g%20%25%2012500%2C%20g%20%25%2013000%2C%20g%20%25%2013500%2C%20g%20%25%2014000%2C%20g%20%25%2014500%2C%20g%20%25%2015000%2C%0A%20%20%20%20g%20%25%2015500%2C%20g%20%25%2016000%2C%20g%20%25%2016500%2C%20g%20%25%2017000%2C%20g%20%25%2017500%2C%20g%20%25%2018000%2C%20g%20%25%2018500%2C%20g%20%25%2019000%2C%20g%20%25%2019500%2C%20g%20%25%2020000%2C%0A%20%20%20%20g%20%25%2020500%2C%20g%20%25%2021000%2C%20g%20%25%2021500%2C%20g%20%25%2022000%2C%20g%20%25%2022500%2C%20g%20%25%2023000%2C%20g%20%25%2023500%2C%20g%20%25%2024000%2C%20g%20%25%2024500%2C%20g%20%25%2025000%2C%0A%20%20%20%20g%20%25%2025500%2C%20g%20%25%2026000%2C%20g%20%25%2026500%2C%20g%20%25%2027000%2C%20g%20%25%2027500%2C%20g%20%25%2028000%2C%20g%20%25%2028500%2C%20g%20%25%2029000%2C%20g%20%25%2029500%2C%20g%20%25%2030000%2C%0A%20%20%20%20g%20%25%2030500%2C%20g%20%25%2031000%2C%20g%20%25%2031500%2C%20g%20%25%2032000%2C%20g%20%25%2032500%2C%20g%20%25%2033000%2C%20g%20%25%2033500%2C%20g%20%25%2034000%2C%20g%20%25%2034500%2C%20g%20%25%2035000%2C%0A%20%20%20%20g%20%25%2035500%2C%20g%20%25%2036000%2C%20g%20%25%2036500%2C%20g%20%25%2037000%2C%20g%20%25%2037500%2C%20g%20%25%2038000%2C%20g%20%25%2038500%2C%20g%20%25%2039000%2C%20g%20%25%2039500%2C%20g%20%25%2040000%2C%0A%20%20%20%20g%20%25%2040500%2C%20g%20%25%2041000%2C%20g%20%25%2041500%2C%20g%20%25%2042000%2C%20g%20%25%2042500%2C%20g%20%25%2043000%2C%20g%20%25%2043500%2C%20g%20%25%2044000%2C%20g%20%25%2044500%2C%20g%20%25%2045000%2C%0A%20%20%20%20g%20%25%2045500%2C%20g%20%25%2046000%2C%20g%20%25%2046500%2C%20g%20%25%2047000%2C%20g%20%25%2047500%2C%20g%20%25%2048000%2C%20g%20%25%2048500%2C%20g%20%25%2049000%2C%20g%20%25%2049500%2C%20g%20%25%2050000%0A%20%20FROM%20generate_series(1%2C50000000)%20g%3B%0A%0AINSERT%20INTO%20perf_columnar%0A%20%20SELECT%0A%20%20%20%20g%20%25%2000500%2C%20g%20%25%2001000%2C%20g%20%25%2001500%2C%20g%20%25%2002000%2C%20g%20%25%2002500%2C%20g%20%25%2003000%2C%20g%20%25%2003500%2C%20g%20%25%2004000%2C%20g%20%25%2004500%2C%20g%20%25%2005000%2C%0A%20%20%20%20g%20%25%2005500%2C%20g%20%25%2006000%2C%20g%20%25%2006500%2C%20g%20%25%2007000%2C%20g%20%25%2007500%2C%20g%20%25%2008000%2C%20g%20%25%2008500%2C%20g%20%25%2009000%2C%20g%20%25%2009500%2C%20g%20%25%2010000%2C%0A%20%20%20%20g%20%25%2010500%2C%20g%20%25%2011000%2C%20g%20%25%2011500%2C%20g%20%25%2012000%2C%20g%20%25%2012500%2C%20g%20%25%2013000%2C%20g%20%25%2013500%2C%20g%20%25%2014000%2C%20g%20%25%2014500%2C%20g%20%25%2015000%2C%0A%20%20%20%20g%20%25%2015500%2C%20g%20%25%2016000%2C%20g%20%25%2016500%2C%20g%20%25%2017000%2C%20g%20%25%2017500%2C%20g%20%25%2018000%2C%20g%20%25%2018500%2C%20g%20%25%2019000%2C%20g%20%25%2019500%2C%20g%20%25%2020000%2C%0A%20%20%20%20g%20%25%2020500%2C%20g%20%25%2021000%2C%20g%20%25%2021500%2C%20g%20%25%2022000%2C%20g%20%25%2022500%2C%20g%20%25%2023000%2C%20g%20%25%2023500%2C%20g%20%25%2024000%2C%20g%20%25%2024500%2C%20g%20%25%2025000%2C%0A%20%20%20%20g%20%25%2025500%2C%20g%20%25%2026000%2C%20g%20%25%2026500%2C%20g%20%25%2027000%2C%20g%20%25%2027500%2C%20g%20%25%2028000%2C%20g%20%25%2028500%2C%20g%20%25%2029000%2C%20g%20%25%2029500%2C%20g%20%25%2030000%2C%0A%20%20%20%20g%20%25%2030500%2C%20g%20%25%2031000%2C%20g%20%25%2031500%2C%20g%20%25%2032000%2C%20g%20%25%2032500%2C%20g%20%25%2033000%2C%20g%20%25%2033500%2C%20g%20%25%2034000%2C%20g%20%25%2034500%2C%20g%20%25%2035000%2C%0A%20%20%20%20g%20%25%2035500%2C%20g%20%25%2036000%2C%20g%20%25%2036500%2C%20g%20%25%2037000%2C%20g%20%25%2037500%2C%20g%20%25%2038000%2C%20g%20%25%2038500%2C%20g%20%25%2039000%2C%20g%20%25%2039500%2C%20g%20%25%2040000%2C%0A%20%20%20%20g%20%25%2040500%2C%20g%20%25%2041000%2C%20g%20%25%2041500%2C%20g%20%25%2042000%2C%20g%20%25%2042500%2C%20g%20%25%2043000%2C%20g%20%25%2043500%2C%20g%20%25%2044000%2C%20g%20%25%2044500%2C%20g%20%25%2045000%2C%0A%20%20%20%20g%20%25%2045500%2C%20g%20%25%2046000%2C%20g%20%25%2046500%2C%20g%20%25%2047000%2C%20g%20%25%2047500%2C%20g%20%25%2048000%2C%20g%20%25%2048500%2C%20g%20%25%2049000%2C%20g%20%25%2049500%2C%20g%20%25%2050000%0A%20%20FROM%20generate_series(1%2C50000000)%20g%3B%0A%0AVACUUM%20(FREEZE%2C%20ANALYZE)%20perf_row%3B%0AVACUUM%20(FREEZE%2C%20ANALYZE)%20perf_columnar%3B%0A%0A--%20checkpoint%20if%20superuser%3B%20otherwise%20wait%20for%20system%20to%20settle%0ACHECKPOINT%3B%20CHECKPOINT%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20row%20table%20loaded%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E2274s%3C%2FSTRONG%3E%2C%20while%20the%20columnar%20table%20loaded%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E1111s%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Efor%20a%20speedup%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E2X%3C%2FSTRONG%3E.%20Load%20speed%20is%20not%20always%20better%20with%20columnar%2C%20but%20columnar%20does%20have%20the%20advantage%20when%20the%20system%20is%20IO-bound.%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1803292521%22%20id%3D%22toc-hId-1803151276%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId--4161942%22%20id%3D%22toc-hId--4303187%22%3E%3CA%20id%3D%22user-content-compression-ratio%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23compression-ratio%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ECompression%20Ratio%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20data%2C%20you%20can%20see%20a%20compression%20ratio%20of%20about%207X%20when%20using%20Columnar.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20pg_total_relation_size('perf_row')%3A%3Anumeric%2F%0A%20%20%20%20%20%20%20pg_total_relation_size('perf_columnar')%20AS%20compression_ratio%3B%0A%20compression_ratio%20%20%0A--------------------%0A%206.7394916300510456%0A(1%20row)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CDIV%20class%3D%22highlight%20highlight-source-sql%20position-relative%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CEM%3E(Note%3A%20the%20compression%20ratio%20varies%20depending%20on%20the%20version%20of%20the%20compression%20library.%20Earlier%20versions%20of%20zstd%20showed%20a%20better%20compression%20ratio%20of%20better%20than%208X.%20I%20have%20filed%20an%20%3CA%20title%3D%22zstd%20compression%20ratio%20regression%20issue%22%20href%3D%22https%3A%2F%2Fgithub.com%2Ffacebook%2Fzstd%2Fissues%2F2651%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eissue%3C%2FA%3E%20with%20the%20zstd%20project%20in%20case%20it's%20a%20bug%20that%20can%20be%20fixed.)%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20can%20also%20get%20some%20information%20from%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EVACUUM%20VERBOSE%3C%2FCODE%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EVACUUM%20VERBOSE%20perf_columnar%3B%0AINFO%3A%20%20statistics%20for%20%22perf_columnar%22%3A%0Astorage%20id%3A%2010000000003%0Atotal%20file%20size%3A%206754770944%2C%20total%20data%20size%3A%206733594337%0Acompression%20rate%3A%206.03x%0Atotal%20row%20count%3A%2050000000%2C%20stripe%20count%3A%20334%2C%20average%20rows%20per%20stripe%3A%20149700%0Achunk%20count%3A%20500000%2C%20containing%20data%20for%20dropped%20columns%3A%200%2C%20zstd%20compressed%3A%20500000%0A%0AVACUUM%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENotice%20that%20there%20are%20334%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Estripes%3C%2FEM%3E.%20Stripes%20are%20the%20unit%20of%20a%20data%20load%2Fwrite.%20By%20default%2C%20each%20stripe%20can%20hold%20up%20to%20150%2C000%20tuples.%3C%2FP%3E%0A%3CP%3EBy%20default%2C%20data%20is%20compressed%20with%20zstd%20compression.%20The%20compression%20rate%20calculated%20by%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EVACUUM%20VERBOSE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20slightly%20different%20than%20what%20we%20saw%20above%2C%20because%20it%20considers%20only%20the%20average%20compression%20ratio%20of%20the%20data%2C%20and%20doesn%E2%80%99t%20account%20for%20metadata%20(like%20visibility%20information).%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--1811616405%22%20id%3D%22toc-hId--1811757650%22%3E%26nbsp%3B%3C%2FH3%3E%0A%3CH3%20id%3D%22toc-hId-675896428%22%20id%3D%22toc-hId-675755183%22%3E%3CA%20id%3D%22user-content-queries%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23queries%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EQueries%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20let's%20run%20a%20couple%20SQL%20queries.%20We%20will%20use%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EEXPLAIN%20ANALYZE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eso%20that%20we%20can%20see%20the%20details%20in%20addition%20to%20the%20overall%20runtime.%20Timings%20are%20taken%20from%20the%20median%20of%20three%20consecutive%20runs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENotice%20that%20only%20a%203%20out%20of%20100%20columns%20are%20necessary%20to%20answer%20this%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--%0A--%20Parallel%20query%20actually%20slows%20down%20the%20query%20on%20the%20row%20table%20in%20this%20example%2C%20so%20disable%20it.%0A--%20Columnar%20doesn't%20support%20parallel%20query.%0A--%0ASET%20max_parallel_workers_per_gather%20%3D%200%3B%0A%0AEXPLAIN%20(ANALYZE%2C%20BUFFERS)%20SELECT%20c00%2C%20SUM(c29)%2C%20AVG(c71)%20FROM%20perf_row%20GROUP%20BY%20c00%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20QUERY%20PLAN%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A----------------------------------------------------------------------------------------------------------------------------------%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20HashAggregate%20%20(cost%3D6430556.07..6430563.57%20rows%3D500%20width%3D72)%20(actual%20time%3D449720.051..449720.570%20rows%3D500%20loops%3D1)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Group%20Key%3A%20c00%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Batches%3A%201%20%20Memory%20Usage%3A%20169kB%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Buffers%3A%20shared%20hit%3D32%20read%3D5555524%0A%20%20%20I%2FO%20Timings%3A%20read%3D411407.314%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20-%26gt%3B%20%20Seq%20Scan%20on%20perf_row%20%20(cost%3D0.00..6055556.04%20rows%3D50000004%20width%3D24)%20(actual%20time%3D0.136..429496.825%20rows%3D50000000%20loops%3D1)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20Buffers%3A%20shared%20hit%3D32%20read%3D5555524%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20I%2FO%20Timings%3A%20read%3D411407.314%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20Planning%3A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Buffers%3A%20shared%20hit%3D4%20%20%20%20%20%20%0A%20Planning%20Time%3A%200.156%20ms%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20JIT%3A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Functions%3A%207%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Options%3A%20Inlining%20true%2C%20Optimization%20true%2C%20Expressions%20true%2C%20Deforming%20true%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20Timing%3A%20Generation%201.852%20ms%2C%20Inlining%2021.516%20ms%2C%20Optimization%20138.386%20ms%2C%20Emission%2067.720%20ms%2C%20Total%20229.474%20ms%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20Execution%20Time%3A%20449722.605%20ms%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A(16%20rows)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%0AEXPLAIN%20(ANALYZE%2C%20BUFFERS)%20SELECT%20c00%2C%20SUM(c29)%2C%20AVG(c71)%20FROM%20perf_columnar%20GROUP%20BY%20c00%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20QUERY%20PLAN%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A------------------------------------------------------------------------------------------------------------------------------------------------------%0A%20HashAggregate%20%20(cost%3D399659.16..399662.16%20rows%3D200%20width%3D72)%20(actual%20time%3D20688.895..20689.190%20rows%3D500%20loops%3D1)%0A%20%20%20Group%20Key%3A%20c00%0A%20%20%20Batches%3A%201%20%20Memory%20Usage%3A%20185kB%0A%20%20%20Buffers%3A%20shared%20hit%3D109765%0A%20%20%20-%26gt%3B%20%20Custom%20Scan%20(ColumnarScan)%20on%20perf_columnar%20%20(cost%3D0.00..24659.16%20rows%3D50000000%20width%3D24)%20(actual%20time%3D6.285..10261.578%20rows%3D50000000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20Columnar%20Chunk%20Groups%20Removed%20by%20Filter%3A%200%0A%20%20%20%20%20%20%20%20%20Buffers%3A%20shared%20hit%3D109765%0A%20Planning%3A%0A%20%20%20Buffers%3A%20shared%20hit%3D20%0A%20Planning%20Time%3A%200.682%20ms%0A%20JIT%3A%0A%20%20%20Functions%3A%206%0A%20%20%20Options%3A%20Inlining%20false%2C%20Optimization%20false%2C%20Expressions%20true%2C%20Deforming%20true%0A%20%20%20Timing%3A%20Generation%201.349%20ms%2C%20Inlining%200.000%20ms%2C%20Optimization%200.234%20ms%2C%20Emission%204.110%20ms%2C%20Total%205.692%20ms%0A%20Execution%20Time%3A%2020690.686%20ms%0A(15%20rows)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20see%20a%20dramatic%20difference%20in%20the%20overall%20buffers%20read%2C%20which%20results%20in%20a%20dramatic%20difference%20in%20the%20runtime%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20role%3D%22table%22%3E%0A%3CTHEAD%3E%0A%3CTR%3E%0A%3CTH%3EStorage%3C%2FTH%3E%0A%3CTH%3EColumnar%3C%2FTH%3E%0A%3CTH%3ESpeedup%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3C%2FTHEAD%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3ERow%3C%2FTD%3E%0A%3CTD%3E450s%3C%2FTD%3E%0A%3CTD%3E%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EColumnar%3C%2FTD%3E%0A%3CTD%3E21s%3C%2FTD%3E%0A%3CTD%3E%3CSTRONG%3E21X%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CH2%20id%3D%22toc-hId--1260640754%22%20id%3D%22toc-hId--1260781999%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1226872079%22%20id%3D%22toc-hId-1226730834%22%3E%3CA%20id%3D%22user-content-what-are-the-limitations%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23what-are-the-limitations%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EWhat%20are%20the%20Limitations%3F%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20limitations%20are%20not%20set%20in%20stone%2C%20and%20we%20look%20forward%20to%20working%20on%20them%20in%20the%20future%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ENo%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EUPDATE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eor%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EDELETE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esupport%3C%2FLI%3E%0A%3CLI%3ENo%20index%20support%3C%2FLI%3E%0A%3CLI%3ENo%20logical%20replication%20or%20logical%20decoding%20support%3C%2FLI%3E%0A%3CLI%3ESee%20more%20limitations%20in%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22columnar%20README%20on%20github%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcitus%2Ftree%2Fmaster%2Fsrc%2Fbackend%2Fcolumnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecolumnar%20README%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EThis%20means%20that%2C%20as%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%2010%20in%20Hyperscale%20(Citus)%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fnew-postgres-superpowers-in-hyperscale-citus-with-citus-10%2Fba-p%2F2335404%22%20target%3D%22_blank%22%3ECitus%2010%20in%20Hyperscale%20(Citus)%3C%2FA%3E%2C%20columnar%20should%20be%20used%20for%20append-only%20tables%20that%20are%20used%20for%20analytic%20queries.%20Even%20if%20UPDATE%2FDELETE%20are%20supported%20in%20the%20future%2C%20UPDATE%20%26amp%3B%20DELETE%20will%20not%20be%20as%20efficient%20as%20they%20are%20on%20row-based%20storage%2C%20so%20columnar%20is%20not%20a%20good%20fit%20for%20many%20transactional%20workloads.%20However%2C%20you%20can%20pick%20and%20choose%20columnar%20where%20it%20works%20best%2C%20and%20use%20row%20tables%20where%20they%20work%20best%2C%20to%20get%20the%20benefits%20of%20both.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-117579557%22%20id%3D%22toc-hId-117438312%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1689874906%22%20id%3D%22toc-hId--1690016151%22%3E%3CA%20id%3D%22user-content-hybrid-columnar-and-row-tables-with-range-partitioning%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23hybrid-columnar-and-row-tables-with-range-partitioning%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EHybrid%20Columnar%20and%20Row%20tables%20with%20Range%20Partitioning%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20useful%20way%20to%20take%20advantage%20of%20Citus%20Columnar%20is%20when%20you%20combine%20it%20with%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22native%20range%20partitioning%20postgres%20documentation%22%20href%3D%22https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fddl-partitioning.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Enative%20range%20partitioning%3C%2FA%3E.%20Using%20columnar%20with%20partitioning%20helps%20to%20overcome%20the%20limitation%20on%20updates%20and%20deletes%2C%20by%20using%20a%20mix%20of%20row%20and%20columnar%20partitions%20within%20the%20same%20partitioned%20table.%3C%2FP%3E%0A%3CP%3ENormally%2C%20range%20partitioning%20is%20used%20for%20time-based%20partitioning.%20Often%2C%20you%20have%20one%20or%20two%20recent%20%E2%80%9Cactive%E2%80%9D%20partitions%20that%20are%20still%20being%20updated%2C%20and%20then%20many%20older%20partitions%20that%20are%20rarely%20updated%20but%20still%20queried.%20In%20this%20case%20the%20one%20or%20two%20active%20partitions%20can%20be%20row-based%20storage%20to%20allow%20updates%2C%20and%20the%20older%20partitions%20can%20be%20converted%20to%20columnar%20storage%20to%20benefit%20from%20compression%20and%20scan%20speed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20events(ts%20timestamptz%2C%20i%20int%2C%20n%20numeric%2C%20s%20text)%0A%20%20PARTITION%20BY%20RANGE%20(ts)%3B%0A%0ACREATE%20TABLE%20events_2021_jan%20PARTITION%20OF%20events%0A%20%20FOR%20VALUES%20FROM%20('2021-01-01')%20TO%20('2021-02-01')%3B%0A%0ACREATE%20TABLE%20events_2021_feb%20PARTITION%20OF%20events%0A%20%20FOR%20VALUES%20FROM%20('2021-02-01')%20TO%20('2021-03-01')%3B%0A%0AINSERT%20INTO%20events%20SELECT%0A%20%20%20%20'2021-01-01'%3A%3Atimestamptz%20%2B%20'0.45%20seconds'%3A%3Ainterval%20*%20g%2C%0A%20%20%20%20g%2C%0A%20%20%20%20g*pi()%2C%0A%20%20%20%20'number%3A%20'%20%7C%7C%20g%3A%3Atext%0A%20%20%20%20FROM%20generate_series(1%2C10000000)%20g%3B%0A%0AVACUUM%20(FREEZE%2C%20ANALYZE)%20events_2021_feb%3B%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CDIV%20class%3D%22highlight%20highlight-source-sql%20position-relative%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELater%2C%20when%20you're%20ready%20to%20%E2%80%9Ccolumnarize%E2%80%9D%20the%20older%20January%20partition%2C%20you%20can%20use%20this%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus-provided%20alter_table_set_access_method%20function%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fcitus-tips-for-postgres-how-to-alter-distribution-key-shard%2Fba-p%2F2311470%22%20target%3D%22_blank%22%3ECitus-provided%20function%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20convert%20the%20access%20method%20from%20row%20to%20columnar%20storage.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20alter_table_set_access_method('events_2021_jan'%2C%20'columnar')%3B%0AVACUUM%20(FREEZE%2C%20ANALYZE)%20events_2021_jan%3B%0A%0A--%20checkpoint%20if%20superuser%3B%20otherwise%20wait%20for%20system%20to%20settle%0ACHECKPOINT%3B%20CHECKPOINT%3B%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you%20can%20see%20that%20the%20January%20partition%20is%20columnar%2C%20and%20the%20February%20partition%20is%20row%20storage%20(%3CCODE%3Eheap%3C%2FCODE%3E).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%5Cd%2B%20events_2021_jan%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table%20%22public.events_2021_jan%22%0A%20Column%20%7C%20%20%20%20%20%20%20%20%20%20%20Type%20%20%20%20%20%20%20%20%20%20%20%7C%20Collation%20%7C%20Nullable%20%7C%20Default%20%7C%20Storage%20%20%7C%20Stats%20target%20%7C%20Description%0A--------%2B--------------------------%2B-----------%2B----------%2B---------%2B----------%2B--------------%2B-------------%0A%20ts%20%20%20%20%20%7C%20timestamp%20with%20time%20zone%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20i%20%20%20%20%20%20%7C%20integer%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20n%20%20%20%20%20%20%7C%20numeric%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20main%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20s%20%20%20%20%20%20%7C%20text%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20extended%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0APartition%20of%3A%20events%20FOR%20VALUES%20FROM%20('2021-01-01%2000%3A00%3A00%2B00')%20TO%20('2021-02-01%2000%3A00%3A00%2B00')%0APartition%20constraint%3A%20((ts%20IS%20NOT%20NULL)%20AND%20(ts%20%26gt%3B%3D%20'2021-01-01%2000%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone)%20AND%20(ts%20%26lt%3B%20'2021-02-01%2000%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone))%0AAccess%20method%3A%20columnar%0A%0A%5Cd%2B%20events_2021_feb%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table%20%22public.events_2021_feb%22%0A%20Column%20%7C%20%20%20%20%20%20%20%20%20%20%20Type%20%20%20%20%20%20%20%20%20%20%20%7C%20Collation%20%7C%20Nullable%20%7C%20Default%20%7C%20Storage%20%20%7C%20Stats%20target%20%7C%20Description%0A--------%2B--------------------------%2B-----------%2B----------%2B---------%2B----------%2B--------------%2B-------------%0A%20ts%20%20%20%20%20%7C%20timestamp%20with%20time%20zone%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20i%20%20%20%20%20%20%7C%20integer%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20plain%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20n%20%20%20%20%20%20%7C%20numeric%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20main%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0A%20s%20%20%20%20%20%20%7C%20text%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%20%7C%20%20%20%20%20%20%20%20%20%7C%20extended%20%7C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7C%0APartition%20of%3A%20events%20FOR%20VALUES%20FROM%20('2021-02-01%2000%3A00%3A00%2B00')%20TO%20('2021-03-01%2000%3A00%3A00%2B00')%0APartition%20constraint%3A%20((ts%20IS%20NOT%20NULL)%20AND%20(ts%20%26gt%3B%3D%20'2021-02-01%2000%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone)%20AND%20(ts%20%26lt%3B%20'2021-03-01%2000%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone))%0AAccess%20method%3A%20heap%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20they%20each%20have%20about%20half%20the%20rows.%20But%20even%20though%20the%20January%20partition%20has%20more%20rows%2C%20it%E2%80%99s%20much%20smaller%20due%20to%20columnar%20compression%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20COUNT(*)%20FROM%20events%3B%20--%20parent%20table%20scans%20both%20partitions%0A%20%20count%0A----------%0A%2010000000%0A(1%20row)%0A%0ASELECT%20COUNT(*)%20FROM%20events_2021_jan%3B%0A%20%20count%0A---------%0A%205951999%0A(1%20row)%0A%0ASELECT%20COUNT(*)%20FROM%20events_2021_feb%3B%0A%20%20count%0A---------%0A%204048001%0A(1%20row)%0A%0ASELECT%20pg_size_pretty(pg_relation_size('events_2021_jan'))%3B%0A%20pg_size_pretty%0A----------------%0A%2069%20MB%0A(1%20row)%0A%0ASELECT%20pg_size_pretty(pg_relation_size('events_2021_feb'))%3B%0A%20pg_size_pretty%0A----------------%0A%20264%20MB%0A(1%20row)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId-797637927%22%20id%3D%22toc-hId-797496682%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1009816536%22%20id%3D%22toc-hId--1009957781%22%3E%3CA%20id%3D%22user-content-what-is-chunk-group-filtering%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23what-is-chunk-group-filtering%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EWhat%20is%20Chunk%20Group%20Filtering%3F%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20run%20a%20query%20for%20a%20particular%20hour%20within%20the%20January%20partition%20of%20the%20events%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EEXPLAIN%20(ANALYZE%2CBUFFERS)%0ASELECT%20SUM(n)%0AFROM%20events_2021_jan%0A%20%20WHERE%20ts%20%26gt%3B%3D%20'2021-01-11%2001%3A00'%3A%3Atimestamptz%20AND%0A%20%20%20%20%20%20%20%20ts%20%26lt%3B%20'2021-01-11%2002%3A00'%3A%3Atimestamptz%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20QUERY%20PLAN%0A--------------------------------------------------------------------------------------------------------------------------------------------%0A%20Aggregate%20%20(cost%3D4438.09..4438.10%20rows%3D1%20width%3D32)%20(actual%20time%3D8.356..8.357%20rows%3D1%20loops%3D1)%0A%20%20%20Buffers%3A%20shared%20hit%3D2922%20read%3D2%0A%20%20%20-%26gt%3B%20%20Custom%20Scan%20(ColumnarScan)%20on%20events_2021_jan%20%20(cost%3D0.00..4418.55%20rows%3D7815%20width%3D11)%20(actual%20time%3D2.998..7.703%20rows%3D8000%20loops%3D1)%0A%20%20%20%20%20%20%20%20%20Filter%3A%20((ts%20%26gt%3B%3D%20'2021-01-11%2001%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone)%20AND%20(ts%20%26lt%3B%20'2021-01-11%2002%3A00%3A00%2B00'%3A%3Atimestamp%20with%20time%20zone))%0A%20%20%20%20%20%20%20%20%20Rows%20Removed%20by%20Filter%3A%2012000%0A%20%20%20%20%20%20%20%20%20Columnar%20Chunk%20Groups%20Removed%20by%20Filter%3A%20594%0A%20%20%20%20%20%20%20%20%20Buffers%3A%20shared%20hit%3D2922%20read%3D2%0A%20Planning%3A%0A%20%20%20Buffers%3A%20shared%20hit%3D27%20dirtied%3D2%0A%20Planning%20Time%3A%200.233%20ms%0A%20Execution%20Time%3A%208.380%20ms%0A(11%20rows)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENotice%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EColumnar%20Chunk%20Groups%20Removed%20by%20Filter%3A%20594%3C%2FCODE%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20we%20need%20some%20terminology%20to%20understand%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EStripe%3C%2FSTRONG%3E%3A%20all%20loads%20into%20a%20columnar%20table%20are%20broken%20into%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Estripes%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20150000%20rows%20(by%20default).%20The%20larger%20a%20stripe%2C%20the%20more%20sequential%20access%20when%20reading%20a%20given%20column.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EChunk%20Group%3C%2FSTRONG%3E%3A%20Stripes%20are%20broken%20down%20further%20into%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EChunk%20Groups%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%2010000%20rows%20(by%20default).%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EChunk%3C%2FSTRONG%3E%3A%20Each%20Chunk%20Group%20consists%20of%20one%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EChunk%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Efor%20each%20column.%20A%20Chunk%20is%20the%20unit%20of%20compression%2C%20and%20the%20min%2Fmax%20is%20tracked%20for%20each%20chunk%20to%20enable%20Chunk%20Group%20Filtering.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EChunk%20Group%20Filtering%3C%2FSTRONG%3E%3A%20When%20a%20query%E2%80%99s%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EWHERE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eclause%20can't%20possibly%20match%20any%20of%20the%20tuples%20in%20a%20Chunk%2C%20and%20we%20know%20that%20by%20the%20min%2Fmax%20values%20of%20the%20chunk%2C%20then%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EChunk%20Group%20Filtering%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ewill%20simply%20skip%20over%20the%20whole%20Chunk%20Group%20without%20decompressing%20any%20of%20the%20Chunks%20in%20the%20Chunk%20Group.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EYou%20can%20see%20above%20that%20594%20Chunk%20Groups%20were%20filtered%20out%2C%20which%20means%20that%205931999%20rows%20were%20filtered%20out%20without%20needing%20to%20fetch%20or%20decompress%20the%20data.%20Only%202%20Chunk%20Groups%20(20000%20rows)%20needed%20to%20be%20actually%20fetched%20and%20decompressed%2C%20which%20is%20why%20the%20query%20took%20only%20milliseconds.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1477696297%22%20id%3D%22toc-hId-1477555052%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--329758166%22%20id%3D%22toc-hId--329899411%22%3E%3CA%20id%3D%22user-content-how-is-citus-columnar-connected-to-citus%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23how-is-citus-columnar-connected-to-citus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EHow%20is%20Citus%20Columnar%20connected%20to%20Citus%3F%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECitus%20Columnar%20is%20a%20new%20feature%20that%20we've%20introduced%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%20github%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcitus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECitus%2010%3C%2FA%3E.%20Now%20that%20Citus%2010%20is%20available%20in%20preview%20in%20Hyperscale%20(Citus)%2C%20as%20long%20as%20you've%20turned%20on%20the%20preview%20features%2C%20you%20can%20now%20create%20Postgres%20tables%20with%20Hyperscale%20(Citus)%20with%20the%20new%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EUSING%20columnar%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esyntax%2C%20and%20you're%20ready%20to%20go%20(of%20course%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Citus%20Columnar%20documentation%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpostgresql%2Fconcepts-hyperscale-columnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eread%20the%20docs%3C%2FA%3E%2C%20too!).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECitus%20is%20known%20for%20its%20ability%20to%20scale%20Postgres.%20Importantly%2C%20you%20can%20use%20Columnar%20with%20or%20without%20the%20Citus%20scale-out%20features.%20Columnar%20is%20a%20great%20complement%20to%20typical%20Citus%20use%20cases%2C%20but%20you%20can%20pick%20and%20choose%20whether%20to%20use%20Citus%20Columnar%20on%20a%20single%20node%2C%20or%20as%20part%20of%20a%20distributed%20Citus%20cluster.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20mix%20and%20match%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3Ecolumnar%20and%20row%20tables%3C%2FLI%3E%0A%3CLI%3Ecolumnar%20and%20row%20partitions%20of%20the%20same%20table%3C%2FLI%3E%0A%3CLI%3Elocal%20columnar%20tables%20and%20distributed%20columnar%20tables%3B%20you%20can%20mix%20local%20and%20distributed%20tables%20on%20a%20single%20node%20with%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fsharding-postgres-with-basic-tier-in-hyperscale-citus-how-why%2Fba-p%2F2275672%22%20target%3D%22_blank%22%3EBasic%20tier%3C%2FA%3E%E2%80%94as%20well%20as%20on%20a%20distributed%20Hyperscale%20(Citus)%20cluster%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId--2137212629%22%20id%3D%22toc-hId--2137353874%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-350300204%22%20id%3D%22toc-hId-350158959%22%3E%3CA%20id%3D%22user-content-what-about-cstore_fdw%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23what-about-cstore_fdw%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EWhat%20about%20cstore_fdw%3F%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you've%20heard%20of%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22cstore_fdw%20github%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore_fdw%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecstore_fdw%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eextension%20that%20my%20teammates%20at%20Citus%20created%20a%20number%20of%20years%20ago%2C%20you%20can%20think%20of%20Citus%20Columnar%20as%20the%20next%20generation%20of%20cstore_fdw.%20If%20using%20cstore_fdw%2C%20consider%20migrating%20to%20Citus%20Columnar.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecstore_fdw%20achieved%20the%20core%20benefits%20of%20columnar%20in%20terms%20of%20performance%3B%20but%20Citus%20Columnar%20goes%20much%20further%20in%20terms%20of%20integration%20and%20feature%20compatibility.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECitus%20Columnar%20works%20with%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CCODE%3EROLLBACK%3C%2FCODE%3E%3C%2FLI%3E%0A%3CLI%3EWrite-ahead%20logging%20(WAL)%3C%2FLI%3E%0A%3CLI%3EPhsyical%20Replication%3C%2FLI%3E%0A%3CLI%3Epg_upgrade%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3Eand%20also%20provides%20a%20more%20seamless%20user%20experience%2C%20similar%20to%20ordinary%20Postgres%20row%20tables.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECitus%20Columnar%20was%20able%20to%20accomplish%20this%20better%20experience%20by%20using%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Table%20Access%20Method%20API%20postgres%20documentation%22%20href%3D%22https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Ftableam.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ETable%20Access%20Method%20API%3C%2FA%3E%2C%20new%20in%20PostgreSQL%20version%2012.%20Using%20this%20new%20extension%20API%20allows%20tighter%20integration%20into%20Postgres%20while%20still%20being%20a%20pure%20extension.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1457154259%22%20id%3D%22toc-hId--1457295504%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1030358574%22%20id%3D%22toc-hId-1030217329%22%3E%3CA%20id%3D%22user-content-try-out-citus-columnar-for-your-analytics-workloads%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23try-out-citus-columnar-for-your-analytics-workloads%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ETry%20out%20Citus%20Columnar%20for%20your%20analytics%20workloads%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20dramatic%20compression%20and%20scan%20speed%20improvements%20offered%20by%20Columnar%20in%20Hyperscale%20(Citus)%20allow%20you%20to%20do%20more%20with%20less.%20Take%20advantage%20by%20identifying%20large%2C%20append-only%20tables%20and%20evaluate%20whether%20columnar%20will%20improve%20performance%20or%20reduce%20costs.%3C%2FP%3E%0A%3CP%3EColumnar%20storage%20may%20also%20allow%20you%20to%20keep%20data%20in%20Postgres%20for%20longer%2C%20rather%20than%20forcing%20you%20to%20archive%20older%20data%2C%20where%20it%20can't%20be%20queried%20efficiently.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20dive%20even%20deeper%20into%20learning%20about%20Citus%20Columnar%2C%20I%20recommend%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3E%3CA%20title%3D%22Columnar%20README%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcitus%2Ftree%2Fmaster%2Fsrc%2Fbackend%2Fcolumnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EColumnar%20README%3C%2FA%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eavailable%20on%20the%20Citus%20GitHub%20repo.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3E%3CA%20title%3D%22Hyperscale%20(Citus)%20docs%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpostgresql%2Fconcepts-hyperscale-columnar%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHyperscale%20(Citus)%20docs%3C%2FA%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eabout%20columnar%20storage.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3E%3CA%20title%3D%22Video%20Demo%20of%20Columnar%22%20href%3D%22https%3A%2F%2Fyoutu.be%2FSS7jcq9fTnw%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EVideo%20Demo%20of%20Columnar%3C%2FA%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EShort%20video%20to%20explain%20the%20what%2C%20why%2C%20and%20how%20to%20use%20columnar%20storage%20with%20Citus%20and%20Postgres.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3E%3CA%20title%3D%22Citus%2010%20on%20Hyperscale%20(Citus)%20blog%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fnew-postgres-superpowers-in-hyperscale-citus-with-citus-10%2Fba-p%2F2335404%22%20target%3D%22_blank%22%3ECitus%2010%20on%20Hyperscale%20(Citus)%20blog%3C%2FA%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3ELots%20of%20good%20examples%2C%20and%20information%20about%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22Hyperscale%20(Citus)%20basic%20tier%20blog%20post%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fsharding-postgres-with-basic-tier-in-hyperscale-citus-how-why%2Fba-p%2F2275672%22%20target%3D%22_blank%22%3EBasic%20tier%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eas%20well.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId--78933948%22%20id%3D%22toc-hId--79075193%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1886388411%22%20id%3D%22toc-hId--1886529656%22%3E%3CA%20id%3D%22user-content-try-out-citus-10open-source-or-in-the-cloud%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fcitusdata%2Fcstore2%2Fwiki%2FColumnar-Blog-May-2021%23try-out-citus-10open-source-or-in-the-cloud%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ETry%20out%20Citus%2010%E2%80%94open%20source%20or%20in%20the%20cloud%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20title%3D%22Hyperscale%20(Citus)%20quickstart%20for%20Basic%20tier%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpostgresql%2Fquickstart-create-hyperscale-basic-tier%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHyperscale%20(Citus)%20quickstart%20for%20Basic%20tier%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20title%3D%22Citus%20open%20source%20download%22%20href%3D%22https%3A%2F%2Fwww.citusdata.com%2Fdownload%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECitus%20open%20source%20download%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EFinally%2C%20special%20thanks%20to%20Hadi%20Moshayedi%2C%20who%20was%20the%20original%20author%20of%20cstore_fdw%20(the%20mature%20basis%20of%20Citus%20columnar)%2C%20and%20is%20the%20co-author%20of%20Citus%2010%20columnar!%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%20id%3D%22wiki-footer%22%20class%3D%22mt-5%20Link--muted%20wiki-footer%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2373870%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20use%20the%20columnar%20feature%20in%20Hyperscale%20(Citus)%20to%20compress%20Postgres%20tables%2C%20reduce%20IO%2C%20%26amp%3B%20speed%20up%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Postgres-elephant-on-stack-of-books-blue-background-1200x630.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282512i0E05937362EA6AEE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Postgres-elephant-on-stack-of-books-blue-background-1200x630.jpg%22%20alt%3D%22Postgres-elephant-on-stack-of-books-blue-background-1200x630.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2373870%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Database%20for%20PostgreSQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECitus%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOpen%20Source%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPostgreSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎May 25 2021 09:17 AM
Updated by: