4 ms·
> slightly more complicated data layout traded in for much more efficient reads. Depending on read patterns. The classic example is address. Sure, you can sto
by brianwawok 5y ago
> slightly more complicated data layout traded in for much more efficient reads.
Depending on read patterns. The classic example is address. Sure, you can store an address as column. Name here, city there, street 1 there, street 2 there. How useful is 1/5th of an address, and how often are you pulling it like that? For something like address that you generally read all or none, you generally are better served by a row oriented database.
You also have FKs to kind of do this in a row oriented database. If some part of the data is not read nearly as much as another, it can be a foreign key sitting in another table.
- OskarS 5y agoYeah, exactly: there are tradeoffs to both models, neither is strictly superior. You would never want to do aggregates on addresses anyway, so that advantage is out the door. You do, however, want to very easily index a table of addresses, so you could quickly look them up for a particular user, which a columnar database is (arguably) worse at. BigQuery, in particular, does not use indexes at all. (EDIT: I guess you do might want to do aggregates on addresses, actually. "How many customers do we have in NYC?", that kinda thing.)
- vidarh 5y agoHybrids are straightforward enough. A "simple" way of achieving that is to support using the indexes to directly answer queries, as quite a few databases do. Now an index on a single column is also a columnar store of the contents of that column, yet you still have the full row to query if you need lots of data from individual rows. A more sophisticated option would be to reduce duplication of column data. (EDIT: How well a usually row-oriented database optimises this, is another question, and will differ by database)