# Shard allocation filtering

**Shard allocation filters** allow you to control where a table’s primary shards and replicas are stored, based on **custom attributes** assigned to nodes.

This feature can be used to:

* Allocate specific tables to specific groups of nodes.
* Separate workloads by hardware type, location, or role.
* Restrict data to certain zones or storage types.

> **Note:**\
> Per-table shard allocation filtering works **in conjunction** with Cluster-Level Allocation.\
> See Custom Attributes for details on how to assign attributes to nodes.

***

### How It Works

1. Assign attributes to nodes (e.g., `storage=ssd`, `zone=zoneA`).
2. Configure allocation settings for a table using `routing.allocation.*` parameters.
3. CrateDB schedules shards according to the filters.

***

### Allocation Settings

The following **dynamic settings** can be defined:

* **When creating a table** → control where shards are initially placed.
* **When altering a table** → move shards to a different set of nodes.

| Setting                                  | Description                                                                                            |
| ---------------------------------------- | ------------------------------------------------------------------------------------------------------ |
| `routing.allocation.include.{attribute}` | Allocate shards to nodes where `{attribute}` has **at least one** of the given comma-separated values. |
| `routing.allocation.require.{attribute}` | Allocate shards to nodes where `{attribute}` has **all** of the given comma-separated values.          |
| `routing.allocation.exclude.{attribute}` | Allocate shards to nodes where `{attribute}` has **none** of the given comma-separated values.         |

**Example:**

```sql
ALTER TABLE my_table
SET (
    routing.allocation.require.storage = 'ssd',
    routing.allocation.exclude.zone = 'zoneA'
);
```

This will:

* Require nodes to have `storage=ssd`
* Exclude nodes in `zone=zoneA`

> **Note:**\
> These settings can be combined. In the above example, both conditions must be met.

***

### Special Attributes

CrateDB also supports the following **built-in attributes**:

| Attribute     | Matches…                                      |
| ------------- | --------------------------------------------- |
| `_name`       | Node name                                     |
| `_host_ip`    | Host IP address (IP associated with hostname) |
| `_publish_ip` | Publish IP address                            |
| `_ip`         | Either `_host_ip` or `_publish_ip`            |
| `_host`       | Hostname                                      |

***

### Step-by-Step Example: Pin a Table to SSD Nodes

**Scenario:**\
You have a mixed cluster — some nodes have SSD storage, others HDD. You want a table to only store shards on SSD-backed nodes.

***

#### 1. Assign a Custom Attribute to Nodes

On each SSD node, set an attribute in `crate.yml`:

```yaml
node.attr.storage: ssd
```

Restart the node for the change to take effect.

You can verify attributes via:

```sql
SELECT name, attributes FROM sys.nodes;
```

***

#### 2. Create a Table with Allocation Rules

```sql
CREATE TABLE customer_orders (
    id INT PRIMARY KEY,
    order_date TIMESTAMP,
    amount DOUBLE
)
CLUSTERED INTO 4 SHARDS
WITH (
    routing.allocation.require.storage = 'ssd'
);
```

This ensures **only SSD nodes** will host the table’s shards.

***

#### 3. Verify Shard Placement

Use the `sys.shards` table:

```sql
SELECT table_name, shard_id, node['name'], node['attributes']
FROM sys.shards
WHERE table_name = 'customer_orders';
```

You should see that **all shards** are assigned to nodes with `storage=ssd`.
