4 ms·
Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few
by ako 3y ago
Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few columns but from a large number of records , e.g. to aggregate it to calculate an average, it needs to read less database blocks from disk, so lower io costs. Same for in memory or cached, the data your interested in is easier accessible for lower performance cost.
This is especially useful for reporting/analytical purposes where you want to aggregate over data from many records.
- cpursley 3y agoThanks. So if I understand correctly, this is basically like ignoring all the other columns (that you're not interested in)? And do you run them in conjunction with your existing Postgres, install it as a plugin, or switch your entire db to something like Hydra?
- ako 3y agoUsually you choose the type of database based on your use case. Reporting databases like data warehouses, benefit from using a column oriented database, whereas transactional use cases use a row oriented database. Modern databases allow you to choose per table.
- beagle3 3y agoYes, the downside being that if you need all the columns of one specific record (row), it will be spread over many column blocks. As a rule of the thumb, if most of your queries are aggregations over few columns of many records (OLAP), column based would be a better choice - whereas if you need to access every column/field of a small number of records (OLTP), record/row based would be a better choice.
- adammarples 3y agoAnd in addition, each column, or even partition of a column, can store statistics about itself like min max avg count etc. That way if you look up max(column) you only have to read the meta data on the partitions on that column, it's very fast. And for analytics this is pretty common.
- onlyrealcuzzo 3y agoNot an expert on the traditional database. Do they also have a benefit similar to the compression that comes from record re-ordering in column databases? It's not just that you need to read a smaller section of disk to scan the same amount of interesting data - it's that that data also takes up considerably less space than it otherwise would.
- coatue 3y agoHi, hydra cofounder here. catching up on the thread since we were submitted by the community! Yes, it depends on the data, but we've observed customers with a 10X data compression over Postgres (heap) tables although ~5-7X is more common.