Querying Partitions

CrateDB
1

Hello

Please excuse my ignorance and I’m sure I’m missing something, but regarding creating partitions for a table how do I use them in a query ?

i.e. I’ve got a column in the table that is partitioned for months (ts_month)
In my queries do I have to use this column in order for the right partitions to be searched ?
e.g. ts_month = 6

Many thanks in advance
David.

2

Hi @djbestenergy,

if the partition key is included in the WHERE clause of a SELECT query, the query planner will ensure that only relevant shards are searched. Otherwise, if no WHERE clause on the partition key is specified, all partitons are returned by default.

For performance reasons (especially on big tables), it is recommended to always include the partition key in SELECT queries as part of the WHERE clause, to prevent all partitions from being scanned (unless that is intended).

In most cases, the partition key is derived from another column using a generated column, as in this example:

CREATE TABLE "doc"."partition_test" (
   "ts" TIMESTAMP WITH TIME ZONE,
   "ts_month" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('month', "ts"),
   "value" INTEGER
)
PARTITIONED BY ("ts_month");

In that case, you can also run queries with a WHERE condition on ts. The query planner detects that ts_month can be derived from ts. So a query like SELECT * FROM doc.partition_test WHERE ts = '2022-07-15' will also be executed efficiently on relevant partitions only. Specifying ts_month explicitly is not needed.

There are some more details in this part of the documentation:

3 Likes