Stochastic gradient descent written in SQL

This PL/SQL Overlord is running banks https://www.avaloq.com/solutions/products/avaloq-core

We've been doing this type of gradient descent in production for a couple of years now, and we've been very happy with it. A few insights from my experience:

One of the main benefits of doing differentiable programming over a relational database is that you can use joins to traverse the normalized data schema, and this acts as a very effective prior for your model. For example, you can learn parameters associated to shirt colors by having a parameter in the color table, and joining it with the shirt sales table (through the shirts table). And knowledge of the table structure also lets you improve convergence, for example by adding a factor to compensate for the fact that not all colors have the same number of sales (and therefore have been updated fewer times during each epoch). Here's a paper from last year: https://arxiv.org/abs/2209.03771v1

Automatic differentiation is an absolute must-have. Once you start having a few complex joins and aggregations, it's too hard to derive the gradient by hand. Doing automatic differentiation on relational queries requires some adjustments (in particular, because you cannot use a tape), so you end up having to define a subset of relational algebra that is closed by automatic differentiation. The general ideas were presented in https://ceur-ws.org/Vol-2971/paper07.pdf

On the other hand, while it's possible to do SQL-to-SQL automatic differentiation, the resulting queries have poor performance, so it's better to go one step lower (at the level of the executor for an already-planned query) and perform the automatic differentiation there. And we had an excellent intern work with us on dedicated parallelization for gradient descent queries: https://blog.lokad.com/pdf/reproducible-parallel-sgd-ziyad-b...

Finally, in practice, it usually doesn't matter if the gradient descent is stochastic, so long as you run several passes over the data.

I'm very interested in differentiable programming on relational languages, as the mainstream research concentrates on unstructured, low-information-density data (images/sound/text) when there are many domains where the data is more dense and structured, and (because of that structure) is stored in a database.

Also, there is MindsDB: https://mindsdb.com/

> Type systems

Sure SQL has types, but they are checked at runtime, not compile time. Also you cannot define function input and return arguments with types that are checked before you run the program.

> compilers

If you want efficient and/or portable code. They will check your code for type errors before you run them. They give you coding assistance in your editor.

> debuggers

Being able to break a program and see its state and function stack is useful. The quality of the tools for real languages are much better than SQL.

I agree that databases do concurrency better than most languages with their transactions (I mentioned I would use the db for ACID).

> text editors, package managers.

Editor support of real languages is much better than SQL.

Package managers enable code re-use.

> C FFI

Take for example Python. A huge amount of the value comes from re-using C libraries that are wrapped with a nice Python API.

You might be able to do this in SQL, but you'll have to wrap the C library yourself as there is no package manager, and no one else is doing the same thing.

Thank you, I knew that derivatives were used in gradient descent, but automatic differentiation is new to me. The Wikipedia article is fairly opaque compared to what I learned in school, but this tidbit stood out:

https://en.wikipedia.org/wiki/Automatic_differentiation#Impl...

Source code transformation (SCT): the compiler processes source code so that the derivatives are calculated alongside each instruction.

Operator overloading (OO): operators are overridden so that derivatives are calculated for numbers and vectors.

Based on the state of software these days, I'm guessing that OO (the "bare hands" method) is what's mainstream. It would be far better IMHO to use SCT, since it's a universal solution that doesn't require manually refactoring programs.

But stuff like SCT might be considered metaprogramming, which seems to have fallen out of fashion. I grew up with C++ macros and templates, so I feel that this is somewhat tragic, although readability and collaboration are much better today. A modern example might be something like aspect-oriented programming (AOP):

https://en.wikipedia.org/wiki/Aspect-oriented_programming

I once used the AOP library AspectJ to trace a Java app's execution, since Java made the (unfortunate) choice to focus on objects rather than functions, which makes it generally a poor fit for data processing, due to its high use of mutable state within objects (mutable state is what limits most object-oriented projects to around 1 million lines). Meaning that I couldn't remember the program's context as I was stepping through it, and had to analyze traces instead. AspectJ allows one to hook into the code without modifying it, sort of like a debugger, so that stuff like function calls and variable mutations can be watched:

https://en.wikipedia.org/wiki/AspectJ

https://www.eclipse.org/aspectj/doc/released/progguide/index...

Looks like this might still be an open problem in Python:

https://stackoverflow.com/questions/12356713/aspect-oriented...

https://docs.spring.io/spring-python/1.2.x/sphinx/html/aop.h...

But it seems like SQL would be a good candidate for AOP:

https://stackoverflow.com/questions/12271588/aspect-oriented...

https://technology.amis.nl/it/aspect-oriented-programming-ao...

If we had that, maybe we could automatically generate derivatives for the set operations. Then either access them as variables in stored procedures, or possibly as something like views or via metadata stored somewhere like MySQL's INFORMATION_SCHEMA.

I don't really know, but maybe these breadcrumbs could be helpful.