# Implementing Hot and Cold Data Storage Tiering - Example

+ The placement labels on TiKV nodes are required
+ Use `PLACEMENT POLICY` clause in the `PARTITION` definition

```7
SHOW PLACEMENT LABELS;
+---------------------+-----------------+
| Key                 | Values          |
+---------------------+-----------------+
| ~!@storage-performance@!~ | ["high", "low"] |
+---------------------+-----------------+

CREATE PLACEMENT POLICY ~!@hot@!~
CONSTRAINTS="[+storage-performance=~!@high@!~]";

CREATE PLACEMENT POLICY ~!@cold@!~
CONSTRAINTS="[+storage-performance=~!@low@!~]";

CREATE TABLE test.orders (order_id INT, item VARCHAR(50), order_date DATE,
  PRIMARY KEY (order_date, order_id))
PARTITION BY RANGE( YEAR(order_date) ) (
  PARTITION p10 VALUES LESS THAN (2010) ~!@PLACEMENT POLICY=cold@!~,
  PARTITION p15 VALUES LESS THAN (2015) ~!@PLACEMENT POLICY=cold@!~,
  PARTITION pm VALUES LESS THAN MAXVALUE ~!@PLACEMENT POLICY=hot@!~
);

CREATE TABLE test.orders_log (order_id INT, item VARCHAR(50), order_date DATE, 
  PRIMARY KEY (order_date, order_id))
PARTITION BY RANGE( YEAR(order_date) ) (
  PARTITION p10 VALUES LESS THAN (2010) ~!@PLACEMENT POLICY=cold@!~,
  PARTITION p15 VALUES LESS THAN (2015) ~!@PLACEMENT POLICY=cold@!~,
  PARTITION pm VALUES LESS THAN MAXVALUE ~!@PLACEMENT POLICY=cold@!~
);
```

![`smart`](PlacementRuleColdHot_en_001_20230422.png)

------------------------------------------------------------------------------
Placement Rules is a feature that allows you to specify where data is stored in a TiDB using SQL statements. 

Assuming that you already created a placement label with two values for your TiKV nodes. The `KEY` is `storage-performance`, the `VALUE` are `high` for those TiKV nodes with high-end storage devices (expensive, for hot data) attached; `low` for those TiKV nodes with low-end storage devices (inexpensive, for cold data) attached. 

As depicted on the slide, you can use the placement rules to separate cold data and hot data to with table partitions to improve the hardware cost efficiency.

The course TiDB Administration (Self-Hosted) includes additional details and hands-on labs on placement policies.