another question I have is there a way to partition based on Greptime #general

another question I have, is there a way to partiti...

Wei

07/18/2025, 7:42 PM

another question I have, is there a way to partition based on a text column? below is an example of table definition,

Copy code

CREATE TABLE IF NOT EXISTS eth_counters(
 cluster STRING NULL,
 hostname STRING NULL,
 rx_bytes_phy BIGINT NULL,
 tx_bytes_phy BIGINT NULL,
 ts TIMESTAMP NOT NULL,
 TIME INDEX (ts),
 PRIMARY KEY (cluster, hostname)
)

Ning

07/19/2025, 1:35 AM

Yes, it's possible. You can use something like

hostname > 'a'

in partition rules. Note that the partition rules should cover all possible values of hostname

Wei

07/19/2025, 4:46 AM

does it support start with a letter, something like, '^a'?

Ning

07/19/2025, 5:54 AM

yes,

hostname >= 'a'

means starts with

. Note that the partition rule is range based, so you will use it like compare string

Wei

07/20/2025, 9:32 PM

thanks @Ning. Is there a plan to support using hashing just like in mysql? https://dev.mysql.com/doc/refman/8.4/en/partitioning-overview.html

Copy code

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

Wei

07/20/2025, 9:33 PM

this is much more flexible.

Wei

07/20/2025, 10:12 PM

I quickly ran into an issue, why greptimeDB makes its own decision, I am sure it won't happen?

Copy code

sdn=> CREATE TABLE IF NOT EXISTS ethtool_counters(
 clusters STRING NULL,
 hostname STRING NULL,
 pf STRING NULL,
 rx_bytes_phy BIGINT NULL,
 tx_bytes_phy BIGINT NULL,
 ts TIMESTAMP(9) NOT NULL,
 TIME INDEX (ts),
 PRIMARY KEY (clusters, hostname, pf)
)
PARTITION ON COLUMNS (clusters, pf) (
 clusters >= 'zone2' and pf == 'p0',
 clusters >= 'zone2' and pf == 'p1',
 clusters >= 'zone7' and pf == 'p0',
 clusters >= 'zone7' and pf == 'p1'
)
with(
 append_mode='true'
);
ERROR:  Checkpoint `clusters<zone2, pf<p0` is not covered
sdn=>

Wei

07/20/2025, 10:12 PM

what does clusters<zone2 mean here?

Ning

07/21/2025, 2:07 AM

it means you partition rule didn't cover the part of clusters < 'zone2' and pf < 'p0', you will need to define a partition with that. We have no plan for hash based partition because it's not good for query. But we do have plan for re-partition, so once it landed, you can update partition rules on existed tables. This will make it more flexible.

Wei

07/21/2025, 1:32 PM

thanks @Ning. so what does it mean, pf < 'p0'? since the ports on the compute is either p0 or p1, why I would need <?

Ning

07/21/2025, 2:15 PM

The partition has no domain knowledge, it just tries to cover all possible values. if there is only p0 and p1 for

pf

, you can use

pf <= 'p0'

and

pf > 'p0'

to cover them.

Wei

07/21/2025, 5:35 PM

thanks for clarification. it'd be good to let operator to decide what is the best to fit their needs.

Wei

07/21/2025, 9:14 PM

@Ning I am really struggling to define the rule for the partition, for instance, in this table, the location could be something like, beijing, tianjing, shanghai, dalian...... how am I defining the partition rule on column 'location'? Is the expression based on the order of alphabet?

Copy code

CREATE TABLE IF NOT EXISTS test(
 hostname STRING NULL,
 location STRING NULL,
 iface STRING NULL,
 rx_bytes_phy BIGINT NULL,
 tx_bytes_phy BIGINT NULL,
 ts TIMESTAMP(9) NOT NULL,
 TIME INDEX (ts),
 PRIMARY KEY (agent_tag_cluster, hostname, pf)
)
PARTITION ON COLUMNS (location ) (
 
)
with(
 append_mode='true'
);

Wei

07/21/2025, 9:19 PM

still thinking hashing is the easiest way to configure and understand. if hashing is costly, could you use something simple, for example, mod by number of data nodes? it doesn't have to be perfect distribution, batter than nothing.

Ruihang

07/22/2025, 3:55 AM

it would be easier to do this if you already know the value set, just sort them and generate the range should work:

Copy code

location <= 'beijing',
location > 'beijing' and location <= 'dalian',
location > 'dalian' and location <= 'shanghai',
location > 'shanghai' and location <= 'tianjin',
location > 'tianjin',

Ruihang

07/22/2025, 3:56 AM

here only uses lexicographical order, i.e., the same string ordering in python or SQL

Ruihang

07/22/2025, 3:57 AM

you can also further verify your data distribution by

select count(*), location from t group by location order by location

Wei

07/22/2025, 4:38 PM

thanks @Ruihang this is just a simple example. many cases, the number of string could be tens, if not a hundred. config will be tedious, how that could be faster than a modulus. any changes, will be a big reordering...

Ruihang

07/22/2025, 5:20 PM

you don’t need to list all candidates, just pick a few separation points is enough.

any changes, will be a big reordering

that’s the problem of modulus, not ranges

Wei

07/22/2025, 9:08 PM

okay, thanks. it'd be good to document it on the website, if I didn't miss it.

Open in Slack

Previous Next