What are the intended uses of a columnar database?

user_454671 0
Viewed 179

I have a question about columnar databases such as Sybase. I understand columnar databases are very fast when your operation is restricted to single coulmns and doesn’t go across columns, i.e no row based filtering?

But most queries are a combination of both, filter some rows, then aggregate some columns.

So really, where do columnar databases shine?

sybase sql database
1 Answers

Columnar databases can definitely access data across different columns. By storing columns separately, they offer a few advantages not available in row-based storage:

  • They only need to read the columns accessed in a particular query.
  • They make it easy to add new columns to a table.
  • They allow an individual column to be compressed, using a compression algorithm optimal for that column.
  • They sometimes provide built-in indexing for each column.

All of these can be used to speed SELECT queries.

One big issue with columnar databases is inserting a new row (or deleting a row), because this requires touching all the columns. That makes ensuring ACID properties . . . trickier.

There are definitely some SELECT queries that may not perform as well in a columnar database as in other databases. But they do surprisingly well at increasing the performance of many queries.

user_11440350
Related