Schema Design

--populate table from existing table

INSERT INTO posts_v3 SELECT * FROM posts_v2

0 rows in set. Elapsed: 158.074 sec. Processed 59.82 million rows, 76.21 GB (378.42 thousand rows/s., 482.14 MB/s.)
Peak memory usage: 6.41 GiB.

Our previous query improves the query response time by over 3x:

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v3
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

10 rows in set. Elapsed: 0.020 sec. Processed 290.09 thousand rows, 21.03 MB (14.65 million rows/s., 1.06 GB/s.)
```

For users interested in the compression improvements achieved by using specific types and appropriate ordering keys, see [Compression in ClickHouse](/docs/data-compression/compression-in-clickhouse). If you need to further improve compression we also recommend the section [Choosing the right column compression codec](/docs/data-compression/compression-in-clickhouse#choosing-the-right-column-compression-codec).

## Next: Data Modeling Techniques[​](#next-data-modeling-techniques "Direct link to Next: Data Modeling Techniques")

Until now, we've migrated only a single table. While this has allowed us to introduce some core ClickHouse concepts, most schemas are unfortunately not this simple.

In the other guides listed below, we will explore a number of techniques to restructure our wider schema for optimal ClickHouse querying. Throughout this process we aim for `Posts` to remain our central table through which most analytical queries are performed. While other tables can still be queried in isolation, we assume most analytics want to be performed in the context of `posts`.

> Through this section, we use optimized variants of our other tables. While we provide the schemas for these, for the sake of brevity we omit the decisions made. These are based on the rules described earlier and we leave inferring the decisions to the reader.

The following approaches all aim to minimize the need to use JOINs to optimize reads and improve query performance. While JOINs are fully supported in ClickHouse, we recommend they're used sparingly (2 to 3 tables in a JOIN query is fine) to achieve optimal performance.

> ClickHouse has no notion of foreign keys. This doesn't prohibit joins but means referential integrity is left to the user to manage at an application level. In OLAP systems like ClickHouse, data integrity is often managed at the application level or during the data ingestion process rather than being enforced by the database itself where it incurs a significant overhead. This approach allows for more flexibility and faster data insertion. This aligns with ClickHouse's focus on speed and scalability of read and insert queries with very large datasets.

In order to minimize the use of Joins at query time, users have several tools/approaches:

- [**Denormalizing data**](/docs/data-modeling/denormalization) - Denormalize data by combining tables and using complex types for non 1:1 relationships. This often involves moving any joins from query time to insert time.
- [**Dictionaries**](/docs/dictionary) - A ClickHouse specific feature for handling direct joins and key value lookups.
- [**Incremental Materialized Views**](/docs/materialized-view/incremental-materialized-view) - A ClickHouse feature for shifting the cost of a computation from query time to insert time, including the ability to incrementally compute aggregate values.
- [**Refreshable Materialized Views**](/docs/materialized-view/refreshable-materialized-view) - Similar to materialized views used in other database products, this allows the results of a query to be periodically computed and the result cached.

We explore each of these approaches in each guide, highlighting when each is appropriate with an example showing how it can be applied to solving questions for the Stack Overflow dataset.

- [Stack Overflow dataset](#stack-overflow-dataset)- [Establish initial schema](#establish-initial-schema)- [Optimizing Types](#optimizing-types)- [Choosing an ordering key](#choosing-an-ordering-key)
        - [Example](#example)- [Next: Data Modeling Techniques](#next-data-modeling-techniques)

Was this page helpful?