6 ms·

For complicated reasons I was involved in a successful project to develop an open-source, Oracle-SQL-compatible, transactional-integrity-preserving, extensible

by rodw 8y ago

For complicated reasons I was involved in a successful project to develop an open-source, Oracle-SQL-compatible, transactional-integrity-preserving, extensible database designed for in-memory performance in Java. In the process of this development "suddenly" the reasons for a whole bunch of performance and syntax oddities in databases like Oracle and PostgreSQL became very clear. I was shocked to learn that was 15 years ago when I looked up the link to share, but if you are interested in the topic of "how to implement a database" it may be worth a look. For what it is worth it was listed (not by me) on the C2 Wiki on the "Programs to Read" Page, where it was described as "[A] database written in Java with good unit tests and ShortMethods." [1] Both statements are true, for a complete working example of a production database (it supported a commercial product for at least 10 years) it is actually a pretty accessible and well documented code-base. The project is called AxionDB and can be found at [2]. [1] http://wiki.c2.com/?ProgramsToRead http://wiki.c2.com/?ProgramsToRead [2] http://axion.tigris.org/source/browse/axion/ http://axion.tigris.org/source/browse/axion/

skunkworker 8y ago
Yup, a good way to understand why RDBMS are they way they are is to try to make your own in whatever language you choose. I've done this on a very elementary scale making a built in DB for a past project, at first it was single threaded, but when you start considering indexing and the datas structures necessary, it makes you understand and appreciate the man hours that have gone into projects like SQLite, PostgreSQL etc. At first you think, oh I can just write my own database, but when you start getting into areas and the CAP theorem starts to create problems, you really understand why anything past a local machine only ephemeral KV store is asking for trouble.
- rodw 8y ago
  Exactly. We sort of backed into this project by starting with HSQL (http://hsqldb.org/ http://hsqldb.org/) a low-end pure-Java "database" that was popular at the time. The more we used HSQL the more it became clear that it was more like a SQL-parser wrapping a simple key/value store than a full-on ACID database. We created AxionDB precisely because we needed the kinds of capabilities you mention and at the time HSQL did not provide them and wasn't remotely architected to support them. To be honest though, creating a moderately robust RDBMS from "scratch" turned out not to be the most ambitious or complex part of the overarching project that spawned AxionDB. The harder part was trying to use Java's primitive, built-in HTML-renderer to create something approximating a fully featured browser. The effort and complexity behind something like Gecko, WebKit, Edge, Blink, etc. is very easy to underestimate. It's a hard problem, made much harder by having to tackle the kinds of content you find "in the wild. Frankly building a database was a much more straightforward problem than that.
sethammons 8y ago
Could you provide some examples of oddities?
- rodw 8y ago
  "Oddities" might be overstating it, but I remember a number of times a light went off and I though "oh, so _that's_ why Oracle does X". It was the same kind of information you might deduce from looking at a "query plan" from `EXPLAIN` or similar, and the kind of thing you might deduce from a careful review of topics like transaction isolation levels. It's been a very long time so I'm not sure I can name many specific examples, but IIRC some of the topics that became very clear were things like: * the importance of the order in which JOINs and WHEREs are applied (to limit the number of rows being accessed per step) * the relationship between columns that are selected and those that appear in WHERE and ORDER BY clauses. * the value of tables with a small number of columns (limiting data that must be read per row) * the cost/complexity of variable-width columns (VARCHAR vs a fixed-length string), again because of the time and complexity required to do something like "skip ahead three rows" in a data file * the behavior of CLOB/BLOB types (stored external to the "main" table content) * the various types of transaction isolation levels and the conditions in which it becomes hard or impossible to guarantee isolation without table or row locking * etc. The references at http://axion.tigris.org/readings.html http://axion.tigris.org/readings.html describe some of the theoretical concepts we seemed to think were important or useful at the time. Also, there's a post at http://heyrod.com/articles/radio-blog/pleasures-of-profiling.html http://heyrod.com/articles/radio-blog/pleasures-of-profiling... that describes a session of performance-tuning on the index implementation that gets into some of the nitty-gritty implementation topics. (But a lot of the issues addressed there were an artifact of Java's primitive vs. object representation, which has been reduced by things like generics.)
Denzel 8y ago
Do you have any more details on “the reasons” for the performance and syntax oddities?
- rodw 8y ago
  Hi Denzel. I replied to a very similar question at https://news.ycombinator.com/item?id=18562007 https://news.ycombinator.com/item?id=18562007 so rather than repeating myself I'll just point you to that.
emmanueloga_ 8y ago
On the subject of readable database source code: * LMDB is legendary for its performance, but it is source code is kind of hard to read. Found there's a pretty decent port to Java which looks a lot more readable [1]. * LMDB doesn't support SQL. But there's this SQL implementation: Apache Calcite [2]. Creating a DB using llmdbjava + Calcite sound like an interesting project. 1: https://github.com/lmdbjava/lmdbjava/tree/master/src/main/java/org/lmdbjava https://github.com/lmdbjava/lmdbjava/tree/master/src/main/ja... 2: https://calcite.apache.org/ https://calcite.apache.org/
- snaky 8y ago
  Maybe it would be better to start with https://github.com/leo-yuriev/libmdbx https://github.com/leo-yuriev/libmdbx
  - emmanueloga_ 8y ago
    This looks super interesting! Although it still looks more complicated to understand than lmdbjava. I feel any C codebase will be more difficult to follow since there's gonna be a lot more code related to memory management that happens automatically in a GC language like java.
    - snaky 8y ago
      JVM and .NET ports - https://github.com/castortech/mdbxjni https://github.com/castortech/mdbxjni and https://github.com/wangjia184/mdbx.NET https://github.com/wangjia184/mdbx.NET are listed there as well.
      - sorokod 8y ago
        Wrt JVM, not a port, but a JNI interface.
emmanueloga_ 8y ago
Aside: found about your GraphViz cookbook on your site [1]. Looks super useful. Is it coming any time soon? 1: http://heyrod.com/projects/gv-cookbook.html http://heyrod.com/projects/gv-cookbook.html

by rodw 8y ago

skunkworker 8y ago

Yup, a good way to understand why RDBMS are they way they are is to try to make your own in whatever language you choose. I've done this on a very elementary scale making a built in DB for a past project, at first it was single threaded, but when you start considering indexing and the datas structures necessary, it makes you understand and appreciate the man hours that have gone into projects like SQLite, PostgreSQL etc. At first you think, oh I can just write my own database, but when you start getting into areas and the CAP theorem starts to create problems, you really understand why anything past a local machine only ephemeral KV store is asking for trouble.

rodw 8y ago
Exactly. We sort of backed into this project by starting with HSQL (http://hsqldb.org/ http://hsqldb.org/) a low-end pure-Java "database" that was popular at the time. The more we used HSQL the more it became clear that it was more like a SQL-parser wrapping a simple key/value store than a full-on ACID database. We created AxionDB precisely because we needed the kinds of capabilities you mention and at the time HSQL did not provide them and wasn't remotely architected to support them. To be honest though, creating a moderately robust RDBMS from "scratch" turned out not to be the most ambitious or complex part of the overarching project that spawned AxionDB. The harder part was trying to use Java's primitive, built-in HTML-renderer to create something approximating a fully featured browser. The effort and complexity behind something like Gecko, WebKit, Edge, Blink, etc. is very easy to underestimate. It's a hard problem, made much harder by having to tackle the kinds of content you find "in the wild. Frankly building a database was a much more straightforward problem than that.

sethammons 8y ago

Could you provide some examples of oddities?

rodw 8y ago
"Oddities" might be overstating it, but I remember a number of times a light went off and I though "oh, so _that's_ why Oracle does X". It was the same kind of information you might deduce from looking at a "query plan" from `EXPLAIN` or similar, and the kind of thing you might deduce from a careful review of topics like transaction isolation levels. It's been a very long time so I'm not sure I can name many specific examples, but IIRC some of the topics that became very clear were things like: * the importance of the order in which JOINs and WHEREs are applied (to limit the number of rows being accessed per step) * the relationship between columns that are selected and those that appear in WHERE and ORDER BY clauses. * the value of tables with a small number of columns (limiting data that must be read per row) * the cost/complexity of variable-width columns (VARCHAR vs a fixed-length string), again because of the time and complexity required to do something like "skip ahead three rows" in a data file * the behavior of CLOB/BLOB types (stored external to the "main" table content) * the various types of transaction isolation levels and the conditions in which it becomes hard or impossible to guarantee isolation without table or row locking * etc. The references at http://axion.tigris.org/readings.html http://axion.tigris.org/readings.html describe some of the theoretical concepts we seemed to think were important or useful at the time. Also, there's a post at http://heyrod.com/articles/radio-blog/pleasures-of-profiling.html http://heyrod.com/articles/radio-blog/pleasures-of-profiling... that describes a session of performance-tuning on the index implementation that gets into some of the nitty-gritty implementation topics. (But a lot of the issues addressed there were an artifact of Java's primitive vs. object representation, which has been reduced by things like generics.)

Denzel 8y ago

Do you have any more details on “the reasons” for the performance and syntax oddities?

rodw 8y ago
Hi Denzel. I replied to a very similar question at https://news.ycombinator.com/item?id=18562007 https://news.ycombinator.com/item?id=18562007 so rather than repeating myself I'll just point you to that.

emmanueloga_ 8y ago

On the subject of readable database source code: * LMDB is legendary for its performance, but it is source code is kind of hard to read. Found there's a pretty decent port to Java which looks a lot more readable [1]. * LMDB doesn't support SQL. But there's this SQL implementation: Apache Calcite [2]. Creating a DB using llmdbjava + Calcite sound like an interesting project. 1: https://github.com/lmdbjava/lmdbjava/tree/master/src/main/java/org/lmdbjava https://github.com/lmdbjava/lmdbjava/tree/master/src/main/ja... 2: https://calcite.apache.org/ https://calcite.apache.org/

snaky 8y ago
Maybe it would be better to start with https://github.com/leo-yuriev/libmdbx https://github.com/leo-yuriev/libmdbx
- emmanueloga_ 8y ago
  This looks super interesting! Although it still looks more complicated to understand than lmdbjava. I feel any C codebase will be more difficult to follow since there's gonna be a lot more code related to memory management that happens automatically in a GC language like java.
  - snaky 8y ago
    JVM and .NET ports - https://github.com/castortech/mdbxjni https://github.com/castortech/mdbxjni and https://github.com/wangjia184/mdbx.NET https://github.com/wangjia184/mdbx.NET are listed there as well.
    - sorokod 8y ago
      Wrt JVM, not a port, but a JNI interface.

emmanueloga_ 8y ago

Aside: found about your GraphViz cookbook on your site [1]. Looks super useful. Is it coming any time soon? 1: http://heyrod.com/projects/gv-cookbook.html http://heyrod.com/projects/gv-cookbook.html