# What are the basic optimization techniques/parameters for a time series table in this example?

**URL:** <https://community.cratedb.com/t/what-are-the-basic-optimization-techniques-parameters-for-a-time-series-table-in-this-example/848>\
**Category:** CrateDB\
**Created:** [October 7, 2021, 11:17am UTC](https://community.cratedb.com/t/what-are-the-basic-optimization-techniques-parameters-for-a-time-series-table-in-this-example/848 "2021-10-07T11:17:21Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Emre\_Sevinc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/emre_sevinc/32/375_2.png) [@Emre\_Sevinc](https://community.cratedb.com/u/Emre_Sevinc)\
**Post date:** [October 7, 2021, 11:17am UTC](https://community.cratedb.com/t/what-are-the-basic-optimization-techniques-parameters-for-a-time-series-table-in-this-example/848/1 "2021-10-07T11:17:21Z")

</div>

Hello,

I’m aware of [Optimizing storage for historic time-series data](https://community.cratedb.com/t/optimizing-storage-for-historic-time-series-data/762), but in this specific question, I’m not interested data retention or moving old (cold) data to another table, etc.

I want to understand better what kind of basic optimizations/techniques should be used when creating a time series table with the following structure, stored on a **3-node CrateDB** cluster, **each node** with **64 GB RAM** , **16 CPU cores** and **500 GB** storage:

```auto
CREATE TABLE IF NOT EXISTS emre.test_table_001 (
  time timestamptz NOT NULL,
  location_id text NOT NULL,
  device_id text NOT NULL, 
  sensor_reading_01 double precision,
  sensor_reading_02 double precision,
  ...
  sensor_reading_50 double precision,
);

```

Combination of `(time, location_id, device_id)` is **unique**.

Let’s assume that:

- this table stores sensor readings from various devices identified by `device_id`, residing at a geographical location identified by a `location_id`.
- the sensor data is sent to a .NET application, and this .NET application INSERTs data into this table every few seconds.
- other .NET applications SELECT from this table to show various dashboards to users, whenever users request these via a web application.
- a user is generally interested in the time series data for **1 or more devices** in **a single location** in a given web page request.
- currently there are about ~80 devices scattered to ~50 different locations, sending sensor readings every second.
- ‘ **hot data** ’ should be available for about 3 months, meaning, currently hot data is about **100 million rows** , but as new locations and devices are added, the amount of ‘ **hot data** ’ is estimated to be on the order **a few billion rows**.

Based on these assumptions and the ‘naive’ table CREATE command above, what would CrateDB experts say, with respect to the following topics and table parameters:

- Indexing: as far as I understand CrateDB creates indexes automatically, but coming from a PostgreSQL background, I wonder if I should also create some other indexes manually based on the conditions specified in WHERE queries in .NET applications?
- CLUSTERED parameter as described in [CREATE TABLE](https://crate.io/docs/crate/reference/en/4.6/sql/statements/create-table.html#clustered)
- PARTITIONED BY parameter as described in [CREATE TABLE](https://crate.io/docs/crate/reference/en/4.6/sql/statements/create-table.html#partitioned-by)
- number-of-routing-shards, as described in [CREATE TABLE](https://crate.io/docs/crate/reference/en/4.6/sql/statements/create-table.html#number-of-routing-shards)