How to Use ClickHouse: The Beginner's Guide (First Docker Run, the ORDER BY Rule, First Query, and the Ports Lesson)

CodexFuse Team · 2026-10-31T17:24:33.749Z · 13 min read

Learn how to use ClickHouse: run it in Docker, create your first MergeTree table, write your first query, and secure the ports in one beginner guide.

What is ClickHouse, exactly?

Learning how to use ClickHouse starts with understanding what it is: an open-source, column-oriented database management system built for online analytical processing (OLAP). That is the kind of workload where you scan millions or billions of rows to power dashboards, reports, and real-time analytics — and where a traditional row-based database starts timing out.

The short definition: ClickHouse is an open-source columnar database that generates analytical reports using SQL queries in real time. It stores data by column instead of by row. If a table has fifty columns and your query only aggregates one of them, ClickHouse reads just that one column. That single design choice is why queries that take thirty seconds in a row store can finish in under a second here.

If you are brand new to self-hosting databases, the fastest way to follow this clickhouse tutorial is to run everything in containers — our Docker beginner guide covers the Docker setup you will need in the first section. You will also want a server to run it on; our what is VPS hosting guide explains the kind of machine this works best on.

How do you use ClickHouse for the first time?

You start a server, open the ClickHouse client, create a database, create a MergeTree table with an ORDER BY key, insert some rows, and run a SELECT with an aggregation. The whole arc takes about fifteen minutes, and this clickhouse tutorial for beginners walks through every step.

1. Start the server. Run the official Docker image with the HTTP port (8123) and the native client port (9000) published to your machine.
2. Open the client. Connect with clickhouse-client from inside the container.
3. Create a database. Run CREATE DATABASE analytics to give your first project its own home.
4. Create a MergeTree table. Define your columns and pick an ORDER BY key — this decides how data is sorted on disk.
5. Insert sample rows. A few thousand rows are enough to feel how the engine behaves.
6. Run your first query. A SELECT with GROUP BY shows you the speed ClickHouse is famous for.
7. Lock down the ports. Bind 8123 and 9000 to localhost and set a real password before the server ever faces the internet.

How do you install ClickHouse with Docker?

The official Docker image is the fastest clickhouse install for a beginner. It is maintained by ClickHouse Inc. and built from the official packages, so the version inside the container is a genuine release — at the time of writing, the latest stable is 26.10.2, released 2026-10-24.

docker run -d \
  --name clickhouse-server \
  --ulimit nofile=262144:262144 \
  -p 127.0.0.1:8123:8123 \
  -p 127.0.0.1:9000:9000 \
  -e CLICKHOUSE_USER=analytics \
  -e CLICKHOUSE_PASSWORD=pick-a-strong-password \
  -e CLICKHOUSE_DB=analytics \
  clickhouse/clickhouse-server:26.10

Three details in that command matter. First, --ulimit nofile=262144:262144 raises the open-file limit, because ClickHouse keeps one file per column in every data part and the default limit is too low. Second, the ports are bound to 127.0.0.1 so the database is reachable only from your own machine — the ports lesson in the final section explains why this is non-negotiable. Third, the CLICKHOUSE_USER, CLICKHOUSE_PASSWORD, and CLICKHOUSE_DB environment variables create your database and a working user on first start.

To keep your data when the container restarts, mount a volume at /var/lib/clickhouse — that is the folder where the server stores everything. Add -v clickhouse-data:/var/lib/clickhouse to the command above and your tables survive container recreation.

Check that it is running with docker ps, then open the native client:

docker exec -it clickhouse-server clickhouse-client --user analytics --password

You will be prompted for the password you set. If the client greets you, your first clickhouse docker deployment is alive.

How do you install ClickHouse on Ubuntu?

A clickhouse ubuntu install has two honest paths. The quick-install script downloads the official binaries and is the route most independent beginner guides use:

curl https://clickhouse.com | sh
sudo ./clickhouse install
./clickhouse server

That script detects your operating system, downloads the matching binary, and the install step registers the server so it starts on boot. Run ./clickhouse client to connect.

The second path is the official package repository at packages.clickhouse.com, which ships Debian and Ubuntu packages for servers you plan to keep long-term. Either way, the client, the ports, and the table engines work identically — the rest of this guide does not care which install method you chose.

What is the MergeTree engine and the ORDER BY rule?

Every ClickHouse table declares an engine, and MergeTree is the one you will use first. It is the general-purpose engine optimized for large tables that are written once and read many times. The ORDER BY rule is the single most important thing a beginner must learn: a MergeTree table must declare an ORDER BY clause, and ClickHouse physically sorts the data on disk in that order.

This is not a suggestion the way ORDER BY in a query is. It is the primary index. When you later filter on the ORDER BY columns, ClickHouse can skip whole chunks of data without reading them — that skipping is where the famous query speed comes from.

CREATE TABLE analytics.page_views (
  event_id UInt32,
  user_id UInt32,
  page String,
  country String,
  event_time DateTime
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

Notice the clickhouse mergetree anatomy: column names with ClickHouse data types (UInt32, String, DateTime), the engine, and the ORDER BY tuple. A good rule of thumb for your first tables: put the timestamp column first in the ORDER BY key, because almost every analytics query filters by a time range.

How do you write your first ClickHouse query?

Your clickhouse first query is ordinary SQL. Switch to your database, insert a handful of rows, and aggregate them:

USE analytics;

INSERT INTO page_views (event_id, user_id, page, country, event_time) VALUES
  (1, 101, '/pricing', 'US', '2026-10-31 10:00:00'),
  (2, 102, '/pricing', 'US', '2026-10-31 10:01:00'),
  (3, 101, '/docs', 'PK', '2026-10-31 10:02:00');

SELECT page, count() AS views, countDistinct(user_id) AS visitors
FROM page_views
WHERE event_time >= '2026-10-31 00:00:00'
GROUP BY page
ORDER BY views DESC;

Two ClickHouse-isms show up here. count() and countDistinct() are the aggregation workhorses — the engine computes them in parallel across data parts. And because the table is sorted by event_time on disk, the WHERE filter on the time range skips every data part outside that range without reading it.

The HTTP interface on port 8123 gives you a second way to run the same query without the client — handy for scripts and health checks:

curl 'http://localhost:8123/?user=analytics&password=pick-a-strong-password' \
  --data-binary "SELECT version()"

How do you design a ClickHouse table so it stays fast?

Pick an ORDER BY key that matches your filters, use the smallest data type that fits, and prefer low-cardinality encodings for repeated strings. Beginners usually get the first one wrong and the other two never matter — so start with the key.

Design the ORDER BY key around the questions you will actually ask. A web-analytics table gets (event_time, user_id) because every dashboard filters by time and drills down by user. A logs table gets (service, event_time) because every investigation starts with a service name. The wrong key does not corrupt anything; it just means queries scan more data than they need to.

Beyond the key: store timestamps as DateTime (not strings), use UInt32 instead of UInt64 when the range fits, and mark repeated short strings like country as LowCardinality(String) — ClickHouse then stores each distinct value once and references it by a tiny integer.

ClickHouse vs PostgreSQL: when should you use each?

Use ClickHouse when the question is "what happened across millions of rows," and keep PostgreSQL when the question is "what is the current state of this one record." They are not rivals — the standard production pattern is to use them together, with Postgres holding your application data and ClickHouse holding your analytics.

This clickhouse vs postgres decision table is the one I wish someone had handed me on day one:

Where does our DuckDB beginner guide fit? DuckDB is the embedded analytical engine — one file, no server. ClickHouse is the server OLAP: it is what you reach for when the data outgrows a single file or when multiple services need to query the same warehouse at once.

How do you load data into ClickHouse with dbt?

ClickHouse stores the data; dbt transforms it. The pairing is one of the most common beginner pipelines: raw events land in a staging table, and dbt models turn them into the clean tables your dashboards read.

The arc is short. Install dbt with the ClickHouse adapter, point your profiles.yml at the host and port (8123 for HTTP), and write your first model as plain SQL with a {{ ref() }} to the staging table. dbt materializes it as a view or table inside ClickHouse, and from there your GROUP BY queries run against clean data instead of raw events.

If dbt is new to you, work through our dbt beginner guide first — the model/tests/docs workflow is identical; only the warehouse at the bottom changes.

Should you self-host ClickHouse or use ClickHouse Cloud?

A clickhouse for beginners setup means one container, one database, and the ports bound to localhost. To clickhouse self host on your own server means running the official Docker image on a VPS you control instead of paying for the managed service — you get full control, predictable cost, and the learning this guide gives you.

The clickhouse cloud vs self hosted decision comes down to operations skill versus budget. Self-hosting costs a VPS and an hour of setup, and you own the ports lesson below. ClickHouse Cloud (the managed service) costs per usage and removes the scaling, backup, and upgrade work. For learning and for side projects, self-host. For a production pipeline your team depends on, price the cloud before you commit to running the cluster yourself.

What is the ports lesson every beginner must learn?

Never expose ClickHouse's ports to the internet without authentication. Port 8123 speaks HTTP and port 9000 speaks the native protocol, and a fresh container listens on all interfaces inside Docker — so if you publish -p 8123:8123 instead of -p 127.0.0.1:8123:8123, your database is one port scan away from the whole internet.

The official image already nudges you toward safety: the default user has no network access until you set a password through CLICKHOUSE_PASSWORD or the related environment variables. Treat that as a starting point, not a finished job. The complete beginner checklist is: bind both ports to 127.0.0.1, set a strong CLICKHOUSE_PASSWORD, create a dedicated user instead of using default for applications, and put a firewall in front of the host.

7 beginner mistakes that break a ClickHouse install

Sources

• ClickHouse GitHub repository — latest stable v26.10.2 released 2026-10-24; LTS v26.3.60.2 released 2026-10-23; ~50,691 stars; Apache License 2.0 (GitHub API, fetched 2026-10-31).
• ClickHouse official documentation, "Install ClickHouse using Docker" — https://clickhouse.com/docs/get-started/setup/self-managed/docker (official image, --ulimit nofile=262144:262144, ports 8123/9000, CLICKHOUSE_DB/USER/PASSWORD env vars, default-user network-access rule, /var/lib/clickhouse persistence; fetched 2026-10-31).
• Docker Hub, clickhouse official image — https://hub.docker.com/_/clickhouse (maintained by ClickHouse Inc.; docker exec -it <name> clickhouse-client; HTTP interface via port 8123; fetched 2026-10-31).
• KDnuggets, "A Beginner's Guide to ClickHouse Database" — https://www.kdnuggets.com/a-beginners-guide-to-clickhouse-database (curl quick-install arc, native client workflow, column-store explanation).
• DEV Community, "Day 5 of 100 Days of ClickHouse: Writing Your First Query" — https://dev.to/kanishga_subramani_49ad73/day-5-of-100-days-of-clickhouser-writing-your-first-query-5bkf (ORDER BY requirement on MergeTree, first-table/first-query arc).
• Layerbase, "Getting Started with ClickHouse" — https://layerbase.com/blog/getting-started-with-clickhouse (ORDER BY defines the primary index and on-disk sort; columnar + vectorized execution explanation).
• Norvilis DevBlog, "A Beginner's Guide to ClickHouse in Ruby on Rails" — https://norvilis.com/a-beginner-s-guide-to-clickhouse-in-ruby-on-rails/ (columns-not-rows explanation; use ClickHouse alongside Postgres, not instead of it).
• Web Coding Center, "Running ClickHouse in Docker" — https://www.webcodingcenter.com/analytical-sql/clickhouse-for-real-time-analytics--running-clickhouse-in-docker.html (127.0.0.1 port binding, nofile rationale, ports 8123/9000 roles).
• Tinybird, "Step-by-step self-host ClickHouse" — https://www.tinybird.co/blog/step-by-step-self-host-clickhouse (Docker → Helm self-host arc).
• InsightsTitan, "Getting Started with ClickHouse: Your First Queries and Schema Design" — https://insightstitan.com/blog/893/getting-started-with-clickhouse-your-first-queries-and-schema-design/ (first queries + schema-design arc).

Frequently Asked Questions

Is ClickHouse free to use?

Yes. ClickHouse is open source under the Apache License 2.0, which means you can run it on your own servers — including in production — for free. The paid offering is ClickHouse Cloud, the managed service; the software itself costs nothing.

Can ClickHouse replace PostgreSQL?

No, and that is not its job. PostgreSQL is a transactional row store built for frequent updates and complex joins; ClickHouse is an analytical column store built for scanning billions of rows fast. The standard pattern is to use them together: Postgres for application state, ClickHouse for analytics.

How much RAM does ClickHouse need for a beginner setup?

A learning setup runs comfortably on a small VPS with a few gigabytes of RAM — the container idles at a few hundred megabytes. Production sizing depends on your data volume and query concurrency, but you should not let hardware stop you from starting.

What is the difference between ClickHouse and DuckDB?

DuckDB is an embedded analytical database — a single file with no server, perfect for local analysis. ClickHouse is a server database built for shared, always-on analytics at scale. Learn DuckDB for personal data work; learn ClickHouse when services and dashboards need the same warehouse.

Do I need to learn a new query language?

No. ClickHouse speaks a SQL dialect that will feel familiar if you know PostgreSQL or MySQL — SELECT, WHERE, GROUP BY, and JOINs all work. The new concepts are table engines, the ORDER BY sort key, and column data types, and this guide covered all three.