DEMO - Citus 10 brings columnar to Postgres

No video

DEMO - Citus 10 brings columnar to Postgres

Рет қаралды 6,103

Күн бұрын

In this Citus open source demo, you’ll learn how to use the Citus 10 Columnar feature for Postgres to achieve 3x-10x compression ratios, reduce IOPs, and deliver faster query responses-particularly for data where your Postgres queries can skip unneeded columns.
Citus is an open source extension to Postgres that transforms Postgres into a distributed database. New as of Citus 10, you can now use columnar storage in Citus, both for single-node Citus as well as for distributed Citus database clusters. With Citus 10, you can also mix and match both columnar and row tables. Using Citus columnar in combination with native Postgres range partitioning features is especially powerful, as you can compress older data that won’t be changing while still able to update and delete your “hot” tables.
The Citus 10 Columnar demo covers how to create a columnar table, the type of significant performance and compression improvements you might gain, and how to use Citus columnar in combination with native Postgres range partitioning features.
Demo given by Jeff Davis, a Principal Software Engineer at Microsoft and Postgres committer.
Video bookmarks:
► 0:32 What is Columnar?
► 2:01 Demo begins
► 5:41 What is Postgres range partitioning
► 7:05 Columnar with range partitioning demo
📌 Let’s connect:
Twitter - Citus Data, / citusdata
Twitter - AzureDBPostgres, / azuredbpostgres
🔔 Subscribe to the Citus monthly technical newsletter:
aka.ms/citus-n...
✅ Learn more:
Blog post about Citus 10 Columnar: aka.ms/citus-c...
Citus open source GitHub repo: github.com/cit...
Azure Database for PostgreSQL Blog: aka.ms/azure-p...
Hyperscale (Citus) on Azure Database for PostgreSQL docs: aka.ms/docs-hy...
#PostgreSQL #Databases #AzureDBPostgres

Пікірлер: 9

@grupotreinar 3 жыл бұрын

great job

@bhuwanpandit1967 2 жыл бұрын

How do we index on columnar access method table, like first I created a columnar table, and then queried data, which took about 10 sec to run for 44000000 rows, after adding index I get the same time, and when I use the EXPLAIN on query that doesn't show that index is being used, can anyone guide me here??

@meekee7553 2 жыл бұрын

Same issue here

@alvaromseixas 2 жыл бұрын

Any idea when you plan to support sharding in several nodes for columnar deployments?

@lauej Жыл бұрын

Is there any development planned to getting GiST and SPGiST indexes to work on columnar? Without those indexes, the 6-10 times compression is not worth for usecases that require r-trees such as temporal and spatio-temporal warehouses.

@Ged 3 жыл бұрын

Yeah, but will there be a JeffCon 2021?!?

@mullida 3 жыл бұрын

I've tested this now. Top marks for effort but ,in real world multi-core server ,I was shocked to find it slower than heap tables. The reason appears to be that parallel query is not abled on columnar where it is on the origtal table. I can see this being beneficial in cases which are heavily used and low on memory perhaps. I'm keen to keep trying it.anyway 😀. .. Is there any chance parallelism can be used in future versions ?

@clairegiordano4636 3 жыл бұрын

Just saw your question, Claire here from the Citus open source team. For parallelizing queries, we usually recommend using the Citus `create_distributed_table` function. Native PG parallel query is not on the immediate road map AFAIK. Given we already have another way to parallelize queries with Citus (and you can now shard Postgres on a single Citus node too), adding parallel query support to Citus columnar is not currently as high on the list as, say, adding index support or update/delete support (or other Citus things.)