Columnar database

A columnar database is a database that saves data column by column, instead of row by row. It is also called a column store, or a column-oriented database. A question about one column, such as "What is the total price of all orders?", then reads only that column from the disk, instead of every row in full. This makes questions across billions of rows fast, which is why most data warehouses store their data this way.

Picture a table of orders. Each row is one order. The columns are the order number, the date, the customer, the city, the product, the quantity and the price. A row store, such as the usual relational database, keeps each row's values together on the disk. That suits an app, which usually wants one whole order at a time. A column store keeps all the order numbers together, then all the dates, then all the cities, and so on.

Here is how a question runs on a column store. Suppose the table has 20 columns and 100 crore rows. A question that adds up the price for each city needs only two columns: city and price. The column store reads those two columns and skips the other 18. So it reads about a tenth of the data that a row store would read. Two more things make it faster. First, the values in one column are all of one kind, and they often repeat, so they compress well. A city column that says "Pune" a thousand times in a row can be stored as "Pune, 1,000 times". Less data on the disk means less to read. Second, the processor can add up a whole block of prices in one step, instead of one row at a time.

The cost is on the writing side. Saving one new order means adding a value to each of the 20 columns, which are stored in separate places. Changing one order means finding its value in each column. So column stores are slow at an app's many small changes. They usually take in new rows in large groups. They are used for analysis, the OLAP side of OLTP and OLAP, rather than for recording each sale as it happens.

Take, for example, a retail chain that wants its total sales by state for every month of the last five years. In a column store, the query reads only the date, state and amount columns, and finishes in seconds.

Consider a news website that wants to know, every few seconds, which articles readers are clicking right now. A real-time analytics database is a column store built for questions like this, over clicks that arrive nonstop.

For instance, a data lakehouse keeps its tables as files in a columnar file format, such as Parquet, so that several different engines can read the same files.

How is a columnar database different from a row store? A row store is fast at reading or changing one whole record. A column store is fast at adding up one field across millions of records. Some databases keep both. An HTAP database records each transaction in rows and keeps a column copy for questions.