# Partioning question

**URL:** <https://community.cratedb.com/t/partioning-question/1332>\
**Category:** CrateDB\
**Created:** [January 16, 2023, 4:10pm UTC](https://community.cratedb.com/t/partioning-question/1332 "2023-01-16T16:10:54Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [January 16, 2023, 4:10pm UTC](https://community.cratedb.com/t/partioning-question/1332/1 "2023-01-16T16:10:54Z")

</div>

Hello all, me again

OK at the moment I’ve got partitioning structure where a the month number is stored in the tables and this the partitioning column.

```auto
CREATE TABLE IF NOT EXISTS "doc"."v3_safe_1526595589" (
   "ts" TIMESTAMP WITH TIME ZONE,
...
...
   "dmax3" REAL,
   "id" VARCHAR(40) NOT NULL,
   "roundts" TIMESTAMP WITH TIME ZONE NOT NULL,
   "roundts_month" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('month', "roundts") NOT NULL,
   PRIMARY KEY ("roundts", "roundts_month", "id")
)
CLUSTERED BY ("id") INTO 3 SHARDS
PARTITIONED BY ("roundts_month")
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'best_compression',
   column_policy = 'dynamic',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

```

The only problem is the data is projected to be at least 1.4TB a month in size and this will steadily increase.

I’m looking to break this down more and so was wondering if there is the option of bi-weekly, as I think week number could cause more problems ( 3 shards per table ) .  
I know the interval for date\_trunc is limited to “weekly”, but is there some sorcery I can use to do bi-weekly ?

Of course, if I’m being ridiculous, please suggest away…

Many thanks  
David.

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [January 16, 2023, 4:20pm UTC](https://community.cratedb.com/t/partioning-question/1332/2 "2023-01-16T16:20:37Z")

</div>

Hi David,  
You could use something like

```sql
date_bin('2 weeks'::INTERVAL, roundts,'2023-01-01T00:00:00Z'::TIMESTAMP)

```

but if you are going to partitions over smaller periods of time please keep in mind the total number of shards in the cluster and the size of those against the recommendations in [Sharding and Partitioning Guide for Time Series Data - Tutorials - CrateDB Community](https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737)

I hope this helps.

---

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [January 16, 2023, 4:44pm UTC](https://community.cratedb.com/t/partioning-question/1332/3 "2023-01-16T16:44:43Z")

</div>

Many thanks Hernan,

This is what I was thinking - that I didn’t want to use week numbers as this would increase the shards quite rapidly. We’re looking to swap data over 60 days to cold storage, which would help, but I’ll work through the guide

Best regards

---

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [January 16, 2023, 5:11pm UTC](https://community.cratedb.com/t/partioning-question/1332/4 "2023-01-16T17:11:36Z")

</div>

OK I’ll give this a test to see how it goes.

Just as a side issue, we’re using PHP and with the PDO driver it really does not like “:” in the SQL as its considered a parameter thatn will be passed in from the PHP code, so for the above I’ve used

`date_bin( CAST ( '2 weeks' AS INTERVAL), roundts, 1672531200000)`

Thanks again.