6 ms·

I know nothing about J and JSoftware, but this reads like an Aprils fools joke. Is it? > In a naive system the application has to read every single byte of dat

by jve 5y ago

I know nothing about J and JSoftware, but this reads like an Aprils fools joke. Is it? > In a naive system the application has to read every single byte of data from the disk. ... 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. .... Think about the earlier query for license and color. Jd gets the license numbers from disk (a tiny fraction of the database) Ofcourse that data has to be read from disk. Well, for simple or aggregate queries he may gain performance. Moreover, as other commenter has commented, you can organize data in columns in MSSQL too for aggregations: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/in... > columns used in queries are likely already in memory and the query runs at ram speed, not the sad and slow disk speed. ... Jd performance is affected primarily by ram. Lots of ram allows lots of rows Any other RDBMS can have sensible indexes that satisfy your queries. And, surprise, your data also lives in RAM once you read it. > You can backup a database or a table with standard host shell scripts, file copy commands, and tools such as tar/gzip/zip.... If you understand backing up file folders, then you pretty much understand backing up Jd databases. And... throw data consistency out of the window? I'm reading and I'm "not getting" the selling point - why is this better? Okay, I read that things are files. SQLite is also a file if physical format is a concern.

OskarS 5y ago
> Ofcourse that data has to be read from disk. Well, for simple or aggregate queries he may gain performance. Lets say you want to access 2 columns out of 100 in a particular table. In a row-oriented database, you have to read the full rows off the disk, which means that you have to read 98 pieces of data off the disk that you have no use for, a total waste of I/O. In a columnar database, you don't have to do that, you just read off the relevant columns. This is VERY similar to the "array of structs"/"struct of arrays" argument in gamedev (and related high performance fields), it's the same kind of tradeoff: slightly more complicated data layout traded in for much more efficient reads. In addition: if you have a columnar database, you can employ compression in a much more efficient manner. If you have 10 million rows with the same (or very similar) data in a column, you can compress that to a fraction of the size. This messes with indexes, but it's often worth it because it VASTLY speeds up aggregate calculations. Row-based and column-based databases have different tradeoffs and advantages, and it's not quite as clear-cut as the article makes it seem. But it's certainly no April fools joke: columnar databases (for many tasks, particularly aggregates) can vastly outperform row-oriented databases. This is why Google BigQuery is columnar, for instance. Another good example is kdb+ (which this is clearly based off of), which is widely used in places which value quick time-series aggregates (Wall Street, being the obvious example). The article is a bit over the top and one-sided, but it doesn't say anything that is particularly controversial. You might wanna read up on columnar database systems: https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS
- 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 ago
    Yeah, 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 ago
      Hybrids 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)
michelpp 5y ago
> I know nothing about J and JSoftware, but this reads like an Aprils fools joke. Is it? To me it reads as being colored by a very specific tool bias. For example: >> The key difference between Jd and most other database systems is that Jd comes with a fully integrated and mature programming language. Most major database systems come with a fully integrated and mature programming language. >> Row orientation works for small amounts of data. It works for a different data access pattern. Row vs column is a tradeoff spectrum. Data size is just one dimension of the analysis. >> Row oriented is slavishly tied to the design ideas of filing cabinets and manila folders. Column oriented embraces computers. Pretty hyperbolic.
- moonchild 5y ago
  > Most major database systems come with a fully integrated and mature programming language. Like ... pl/[pg]sql? Not exactly a joy to write.
  - nostoc 5y ago
    Still is a fully integrated and mature programming language though. And I do believe you wouldn't have any issue finding people who think the same of J.
    - moonchild 5y ago
      How integrated? Most of jd is written in j. It is also expected that the app performing—or at least handling—the queries be written in j. And regarding maturity—j has libraries, debugger, etc.

by jve 5y ago

OskarS 5y ago

> Ofcourse that data has to be read from disk. Well, for simple or aggregate queries he may gain performance. Lets say you want to access 2 columns out of 100 in a particular table. In a row-oriented database, you have to read the full rows off the disk, which means that you have to read 98 pieces of data off the disk that you have no use for, a total waste of I/O. In a columnar database, you don't have to do that, you just read off the relevant columns. This is VERY similar to the "array of structs"/"struct of arrays" argument in gamedev (and related high performance fields), it's the same kind of tradeoff: slightly more complicated data layout traded in for much more efficient reads. In addition: if you have a columnar database, you can employ compression in a much more efficient manner. If you have 10 million rows with the same (or very similar) data in a column, you can compress that to a fraction of the size. This messes with indexes, but it's often worth it because it VASTLY speeds up aggregate calculations. Row-based and column-based databases have different tradeoffs and advantages, and it's not quite as clear-cut as the article makes it seem. But it's certainly no April fools joke: columnar databases (for many tasks, particularly aggregates) can vastly outperform row-oriented databases. This is why Google BigQuery is columnar, for instance. Another good example is kdb+ (which this is clearly based off of), which is widely used in places which value quick time-series aggregates (Wall Street, being the obvious example). The article is a bit over the top and one-sided, but it doesn't say anything that is particularly controversial. You might wanna read up on columnar database systems: https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS

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 ago
  Yeah, 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 ago
    Hybrids 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)

michelpp 5y ago

> I know nothing about J and JSoftware, but this reads like an Aprils fools joke. Is it? To me it reads as being colored by a very specific tool bias. For example: >> The key difference between Jd and most other database systems is that Jd comes with a fully integrated and mature programming language. Most major database systems come with a fully integrated and mature programming language. >> Row orientation works for small amounts of data. It works for a different data access pattern. Row vs column is a tradeoff spectrum. Data size is just one dimension of the analysis. >> Row oriented is slavishly tied to the design ideas of filing cabinets and manila folders. Column oriented embraces computers. Pretty hyperbolic.

moonchild 5y ago
> Most major database systems come with a fully integrated and mature programming language. Like ... pl/[pg]sql? Not exactly a joy to write.
- nostoc 5y ago
  Still is a fully integrated and mature programming language though. And I do believe you wouldn't have any issue finding people who think the same of J.
  - moonchild 5y ago
    How integrated? Most of jd is written in j. It is also expected that the app performing—or at least handling—the queries be written in j. And regarding maturity—j has libraries, debugger, etc.