4 ms·

I'm intrigued but skeptical of this bit: Jd is a columnar (column oriented) RDBMS. Most RDBMS systems are row oriented. Ages ago they fell into the trap of th

I'm intrigued but skeptical of this bit: Jd is a columnar (column oriented) RDBMS. Most RDBMS systems are row oriented. Ages ago they fell into the trap of thinking of tables as rows (records). You can see how this happened. The end user wants the record that has a first name, last name, license, make, model, color, and date. So a row was the unit of information and rows were stored sequentially on disk. Row orientation works for small amounts of data. But think about what happens when there are lots of rows and the user wants all rows where the license starts with 123 and the color is blue or black. In a naive system the application has to read every single byte of data from the disk. There are lots of bytes and reading from disk is, by orders of magnitude, the slowest part of the performance equation. To answer this simple question all the data had to be read from disk. This is a performance disaster and that is where decades of adding bandages and kludges started. Jd is columnar so the data is 'fully inverted'. This means all of the license numbers are stored together and sequentially on disk. The same for all the other columns. Think about the earlier query for license and color. Jd gets the license numbers from disk (a tiny fraction of the database) and generates a boolean mask of rows that match. It then gets the color column from disk (another small fraction of the data) and generates a boolean mask of matches and ANDS that with the other mask. It can now directly read just the rows from just the columns that are required in the result. Only a small fraction of the data is read. In J, columns used in queries are likely already in memory and the query runs at ram speed, not the sad and slow disk speed. Both scenarios above are simplified, but the point is strong and valid. The end user thinks in records, but the work to get those records is best organized by columns. Row oriented is slavishly tied to the design ideas of filing cabinets and manila folders. Column oriented embraces computers. A table column is a mapped file. What's the other side of this argument?

tormeh 5y ago
It all depends on access pattern. Do you tend to select entire rows? Use a row-oriented DB. Do you tend to select entire columns? A column-oriented database might be for you. That's it, really. None of the designs are superior, afaik.
- moonchild 5y ago
  You might be selecting entire rows, but you are probably not selecting all of the rows, and your selection criteria probably do not depend on all of the columns.
  - WJW 5y ago
    Yeah, row-oriented is good for WHERE queries and column-oriented is good for SUM (or other aggregation) queries.
- hoosieree 5y ago
  Just to add, because J is an array-oriented language, it makes some kinds of column-oriented access patterns easier. For example, it's trivial to sort one array by the values of another array: x /: y To me, it's much easier to read than the equivalent in NumPy: x[np.argsort(y)] Or get pairs of (unique value; count) from an array using the key operator (/.): (~.;#)/.~ y Column db's make sense for array-oriented languages, because there's much less of a mismatch compared to OOP with relational.
  - legalcorrection 5y ago
    All of that syntax is awful. Why not just x.sortBy(y) ? Did all of the advances in software legibility fail to make their way to the modern scientific computing world?
    - moonchild 5y ago
      https://www.jsoftware.com/papers/tot.htm https://www.jsoftware.com/papers/tot.htm
    - avmich 5y ago
      Hyperbolically, because you don't write math with variables in camel case. J traces its roots from a notation for math, used on whiteboards. That awful syntax you see - it's the same as in some formulas in, say, general relativity, only J is Turing complete and not a Turing tarpit. When you work on a formula, in case of J you have ability to execute it, and if you see it's wrong you can update the formula and try again. This could also be done in other languages, but in J (I mean, APL family of languages) it's more focused. In defense of J, I had a professional example of a problem which wasn't clearly specified, which needed some experimentation - that took, if I remember correctly, some 45 minutes of attempts in J, and then the prototype was re-written in C#, when if was already producing desired outcomes. Rewriting took somewhat longer.
cmrdporcupine 5y ago
Yeah, it's a simplification and one-sided. The general consensus as I understand it is: column-oriented indices/storage options are good for OLAP, large scale analytics, bulk data analysis. Row-oriented indices are suited more for OLTP, individual "record processing." Both are just techniques and there's nothing stopping a single db product from offering both.
- Semaphor 5y ago
  > Both are just techniques and there's nothing stopping a single db product from offering both. e.g. for MS SQL there are columnstore indexes.
- iamwil 5y ago
  Wouldn't column stores be better for the cache?
  - cmrdporcupine 5y ago
    I think the answer to that is just: it depends. Again comes back to usage patterns. Yes, if you're doing aggregation operations on a small number of columns then I expect locality of reference could be better with a column-store, rather than thrashing through row-retrievals one after another (and then just throwing them away after aggregating). But if you're frequently doing "look up this customer and others like them" and then using the bulk of the information there? I'd expect better cache behaviour out of row oriented storage. But these days it's so unclear what's happening inside the actual "black box" that is our hardware that it's hard to make generalizations.
deleted 5y ago
[deleted]
giaour 5y ago
In many OLTP systems, almost all work operates on multiple attributes of a single record. E.g., when logging a user in, an authentication system cares about multiple attributes of a single user record, not facts about the aggregate pool of users. Column oriented stores are extremely efficient for aggregate queries, but they make writes and single-row reads more expensive and are thus not suitable for every workload. There's an excellent overview in Martin Kleppmann's Designing Data Intensive Applications.
lostgame 5y ago
This seems...highly impractical for...90% of operations I'd be wanting to do with a DBMS. It kinda seems like a 'different way for different's sake' kinda solution? :/ I understand there must be a minority of operations that can benefit from this, but overall I can't imagine this being popular for most DB operations.
- vidarh 5y ago
  It tend to include a large proportion of the large, expensive reporting queries your business people want to do. Whether or not those kinds of queries dominates for your system will depend greatly on your system. You also need to reach a certain scale before the choice (either way) will affect you enough to matter. But when you reach that scale it can be the difference between reporting queries taking seconds vs. hours in some cases. For some systems you'll end up wanting both, and stream updates from the transaction focused db (row oriented) into a separate reporting database that uses a column store.
drc500free 5y ago
If you're using J, you are probably doing analytics and stats. That means you are looking for patterns in a handful of attributes across a large population - i.e. columnar. As others have said, row-based makes sense for most OLTP / app databases. You're probably not writing those products in J.
rileyphone 5y ago
A lot of modern, data-oriented ECS frameworks for game dev follow a similar philosophy, wherein components are stored in linear collections that optimize memory layout for caches and parallelism. Given how rarely you need 'SELECT *' this makes sense for a relational DB as well, though modern SQL DBs have a lot of sweat put in to their performance.

tormeh 5y ago

It all depends on access pattern. Do you tend to select entire rows? Use a row-oriented DB. Do you tend to select entire columns? A column-oriented database might be for you. That's it, really. None of the designs are superior, afaik.

moonchild 5y ago
You might be selecting entire rows, but you are probably not selecting all of the rows, and your selection criteria probably do not depend on all of the columns.
- WJW 5y ago
  Yeah, row-oriented is good for WHERE queries and column-oriented is good for SUM (or other aggregation) queries.
hoosieree 5y ago
Just to add, because J is an array-oriented language, it makes some kinds of column-oriented access patterns easier. For example, it's trivial to sort one array by the values of another array: x /: y To me, it's much easier to read than the equivalent in NumPy: x[np.argsort(y)] Or get pairs of (unique value; count) from an array using the key operator (/.): (~.;#)/.~ y Column db's make sense for array-oriented languages, because there's much less of a mismatch compared to OOP with relational.
- legalcorrection 5y ago
  All of that syntax is awful. Why not just x.sortBy(y) ? Did all of the advances in software legibility fail to make their way to the modern scientific computing world?
  - moonchild 5y ago
    https://www.jsoftware.com/papers/tot.htm https://www.jsoftware.com/papers/tot.htm
  - avmich 5y ago
    Hyperbolically, because you don't write math with variables in camel case. J traces its roots from a notation for math, used on whiteboards. That awful syntax you see - it's the same as in some formulas in, say, general relativity, only J is Turing complete and not a Turing tarpit. When you work on a formula, in case of J you have ability to execute it, and if you see it's wrong you can update the formula and try again. This could also be done in other languages, but in J (I mean, APL family of languages) it's more focused. In defense of J, I had a professional example of a problem which wasn't clearly specified, which needed some experimentation - that took, if I remember correctly, some 45 minutes of attempts in J, and then the prototype was re-written in C#, when if was already producing desired outcomes. Rewriting took somewhat longer.

cmrdporcupine 5y ago

Yeah, it's a simplification and one-sided. The general consensus as I understand it is: column-oriented indices/storage options are good for OLAP, large scale analytics, bulk data analysis. Row-oriented indices are suited more for OLTP, individual "record processing." Both are just techniques and there's nothing stopping a single db product from offering both.

Semaphor 5y ago
> Both are just techniques and there's nothing stopping a single db product from offering both. e.g. for MS SQL there are columnstore indexes.
iamwil 5y ago
Wouldn't column stores be better for the cache?
- cmrdporcupine 5y ago
  I think the answer to that is just: it depends. Again comes back to usage patterns. Yes, if you're doing aggregation operations on a small number of columns then I expect locality of reference could be better with a column-store, rather than thrashing through row-retrievals one after another (and then just throwing them away after aggregating). But if you're frequently doing "look up this customer and others like them" and then using the bulk of the information there? I'd expect better cache behaviour out of row oriented storage. But these days it's so unclear what's happening inside the actual "black box" that is our hardware that it's hard to make generalizations.

deleted 5y ago

[deleted]

giaour 5y ago

In many OLTP systems, almost all work operates on multiple attributes of a single record. E.g., when logging a user in, an authentication system cares about multiple attributes of a single user record, not facts about the aggregate pool of users. Column oriented stores are extremely efficient for aggregate queries, but they make writes and single-row reads more expensive and are thus not suitable for every workload. There's an excellent overview in Martin Kleppmann's Designing Data Intensive Applications.

lostgame 5y ago

This seems...highly impractical for...90% of operations I'd be wanting to do with a DBMS. It kinda seems like a 'different way for different's sake' kinda solution? :/ I understand there must be a minority of operations that can benefit from this, but overall I can't imagine this being popular for most DB operations.

vidarh 5y ago
It tend to include a large proportion of the large, expensive reporting queries your business people want to do. Whether or not those kinds of queries dominates for your system will depend greatly on your system. You also need to reach a certain scale before the choice (either way) will affect you enough to matter. But when you reach that scale it can be the difference between reporting queries taking seconds vs. hours in some cases. For some systems you'll end up wanting both, and stream updates from the transaction focused db (row oriented) into a separate reporting database that uses a column store.

drc500free 5y ago

If you're using J, you are probably doing analytics and stats. That means you are looking for patterns in a handful of attributes across a large population - i.e. columnar. As others have said, row-based makes sense for most OLTP / app databases. You're probably not writing those products in J.

rileyphone 5y ago

A lot of modern, data-oriented ECS frameworks for game dev follow a similar philosophy, wherein components are stored in linear collections that optimize memory layout for caches and parallelism. Given how rarely you need 'SELECT *' this makes sense for a relational DB as well, though modern SQL DBs have a lot of sweat put in to their performance.