StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>How do columnar databases work?</strong> The defining concept of a column-store is that the values of a table are stored contiguously by column. Thus the classic supplier table from CJ Date's supplier and parts database:</p> <pre><code>SNO STATUS CITY SNAME --- ------ ---- ----- S1 20 London Smith S2 10 Paris Jones S3 30 Paris Blake S4 20 London Clark S5 30 Athens Adams </code></pre> <p>would be stored on disk or in memory something like: </p> <pre><code>S1S2S3S4S5;2010302030;LondonParisParisLondonAthens;SmithJonesBlakeClarkAdams </code></pre> <p>This is in contrast to a traditional rowstore which would store the data more like this:</p> <pre><code>S120LondonSmith;S210ParisJones;S330ParisBlake;S420LondonClark;S530AthensAdams </code></pre> <p>From this simple concept flows all of the fundamental differences in performance, for better or worse, between a column-store and a row-store. For example, a column store will excel at doing aggregations like totals and averages, but inserting a single row can be expensive, while the inverse holds true for row-stores. This should be apparent from the above diagram.</p> <p><strong>How do they differ from relational databases?</strong> A relation database is a logical concept. A columnar database, or column-store, is a physical concept. Thus the two terms are not comparable in any meaningful way. Column- oriented DMBSs may be relational or not, just as row-oriented DBMS's may adhere more or less to relational principles. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload