Rows vs. Columns: How different are they, really?

(spiraldb.com)

23 points | by eatonphil 4 days ago

5 comments

  • reticulates 9 minutes ago
    > Those consequences are the reason that most database systems are built for either transactions or analytics, and not both. [24]

    > [...]

    > [24] People have tried to build one system that does both in the past (HTAP, or hybrid transactional/analytical processing), but those attempts generally didn't work out. Some newer systems seem to be bridging the gap, however. CedarDB, the commercial version of Umbra (a research system from TU Munich), runs transactions and analytics on just one engine and one copy of the data. Databricks' Lakebase keeps Postgres (a row store) as the engine for transactional queries, but its storage layer rewrites the same data into columnar formats in the background, allowing an analytical engine to query it.

    The footnote says "those attempts generally didn't work out" but then mentions Lakebase and CedarDB as newer efforts. Can anyone speak to whether or not CedarDB, Lakebase (and any others?) are achieving the fabled transactions + analytics in one database? Should we expect, at some point in the near future, for transactions + analytics to be standard in all databases, or is combining them a niche / esoteric thing with many tradeoffs?

  • 4ndrewl 11 minutes ago
    "a database that keeps each record's details together can't take advantage of that: to read two columns, it has to read all five."

    My understanding is that modern databases will take advantage of that if you create an index of those fields - ie it'll read from the index, not the records.

    • kubanczyk 6 minutes ago
      Well, yes. One of the things an index can do is column-oriented storage.
  • kinleyd 24 minutes ago
    Great read! I do enjoy exploring both row and column data stores every now and then. Love the way I get my head bent.
  • j0shk0 1 hour ago
    Am I missing something or is this nothing more but AOS vs SOA ?
    • xnorswap 35 minutes ago
      Please introduce your acronyms!

      Array of Structures vs Structure of Arrays.

    • pdpi 54 minutes ago
      It's the same basic concept, yes. The details matter, though, and the fact that AoS/SoA is mostly about in-memory layout, whereas row/column store is about on-disk layout means that the performance compromises are very different. E.g. column store databases frequently use RLE for compression, but that's probably _way_ too expensive for SoA.
      • j0shk0 21 minutes ago
        Thank you!
        • OskarS 11 minutes ago
          One thing to add is that databases tend to take it further using compression. For instance, if you have a column that is all either A or B, you can use run-length encoding ("the next thousand entries are B"). Or you can do delta encoding: if you have some very large number that takes a lot of bits to store, but the deltas between them are quite small, you can save a lot of disk space and I/O by just storing the deltas.

          You can also combine these two: if you have a numeric id that increases by 1 every row, you can delta-encode that to just get a massive list of 1's, which you can then run-length encode to just be "the delta for the next million rows is 1", saving huge amounts of space.

          You generally don't do this with AOS/SOA, because you do want to be able to random-access your entries ("index" them, in database terms) and I/O performance is much less important. Databases have this issue too, which is why indexes tend to either not be available or much slower in column-oriented DBs.

          EDIT: ah, read the original commenter a bit too quickly, missed that they mentioned run-length encoding. Apologies.

  • krawcu 40 minutes ago
    I enjoyed reading this post