Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Stochastic gradient descent written in SQL

maxhalford.github.io

Post reply on HN
Print view View original

21–30 of 187 posts

Re: Stochastic gradient descent written in SQL

#21

Post by noloblo »

Lemaxoxo wrote: post #3
andrenotgiant wrote: post #2

This is really interesting, but a basic part I don't understand: What would it actually look like to run this on a live dataset? If I understand correctly: you'd run the recursive query, it produces results for every step, effectively showing you the progression of output over time, and then once it hits "present day", it completes and stops? How would you generate results going forward? I.E. A minute elapses after t…

Good question. I touched upon this in the conclusion. Basically, if you run this in a streaming SQL database, such as Materialize, then you would get a true online system which doesn't restart from scratch.

Does this work with Postgres?

Re: Stochastic gradient descent written in SQL

#22

Post by Lemaxoxo »

pradeepchhetri wrote: post #18

You might want to consider checking out ClickHouse which supports many ML functions natively: - stochasticLinearRegression ( https://clickhouse.com/docs/en/sql-reference/aggregate-funct... ) - stochasticLogisticRegression ( https://clickhouse.com/docs/en/sql-reference/aggregate-funct... )

Thanks for the links, I wasn't aware of them. The Russians often seem to have a step ahead in the ML world.

Re: Stochastic gradient descent written in SQL

#23

Post by [deleted] »

andrenotgiant wrote: post #2

This is really interesting, but a basic part I don't understand: What would it actually look like to run this on a live dataset? If I understand correctly: you'd run the recursive query, it produces results for every step, effectively showing you the progression of output over time, and then once it hits "present day", it completes and stops? How would you generate results going forward? I.E. A minute elapses after t…

[deleted]

Re: Stochastic gradient descent written in SQL

#24

Post by Lemaxoxo »

sergius wrote: post #14
Lemaxoxo wrote: post #3

Earlier quoted context omitted.

Good question. I touched upon this in the conclusion. Basically, if you run this in a streaming SQL database, such as Materialize, then you would get a true online system which doesn't restart from scratch.

Did you try running it using DuckDB?

DuckDB is what I used in the blog post. Re-running this query simply recomputes everything from the start. I didn't store intermediary that would allow starting off from where the query stopped. But it's possible!

Re: Stochastic gradient descent written in SQL

#25

Post by Lemaxoxo »

noloblo wrote: post #21
Lemaxoxo wrote: post #3

Earlier quoted context omitted.

Good question. I touched upon this in the conclusion. Basically, if you run this in a streaming SQL database, such as Materialize, then you would get a true online system which doesn't restart from scratch.

Does this work with Postgres?

Postgres has excellent support for WITH RECURSIVE, so I see no reason why it wouldn't. However, as I answered elsewhere, you would need to set some stateful stuff up if you don't want the query to start from scratch when you re-run it.

Re: Stochastic gradient descent written in SQL

#26

Post by swasheck »

hobs wrote: post #4
Lemaxoxo wrote: post #3

Earlier quoted context omitted.

Good question. I touched upon this in the conclusion. Basically, if you run this in a streaming SQL database, such as Materialize, then you would get a true online system which doesn't restart from scratch.

In a non-streaming db What would prevent you from storing the result set and just using the last iteration to calculate the next?

i’m trying to learn here so please pardon my ignorance. wouldn’t a pre-aggregated set affect the new aggregate result? i suppose you could store avg, sum, count and then add the new value(s) to sum, new count to count, and recalculate average from that. or even just avg and count and then re-aggregate as ((avg*count)+new values)/(count + new values count) but i didn’t know if there’s a better way to process new values into a set of data that’s already been aggregated

Re: Stochastic gradient descent written in SQL

#27

Post by daveguy »

jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

I would like for this to be the case. I was DBA of a Postgres database for LIMS almost 2 decades ago. Back then you could code functions for the database to execute on data and it was very powerful, but also very clunky. The tools to do software development in the database was not mature at all. A lot has changed in the past 20 years and SQL has evolved. Do you think SQL will expand that much or there will be APIs built into the database? Near-data functions are powerful and useful, but I would want my development environment to be more like version controlled code than "built-in".

I wonder if near-data functions on small databases is the solution to the limit of statelessness that you have with functions as a service.

Re: Stochastic gradient descent written in SQL

#28

Post by Lemaxoxo »

swyx wrote: post #17
jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

had a very good chat with https://postgresml.org/ last week which is focusing on bringing ML to postgres: https://youtu.be/j8hE8-jZJGU

I'm watching it, it's really good. Montana makes a great point: you can move data to the models, or move the models to the data. Data is typically larger than models, so it makes sense to go with the latter.

Re: Stochastic gradient descent written in SQL

#30

Post by Scubabear68 »

jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

With “traditional” RDBMS systems, putting a lot of code in the DB lead to a lot of scaling issues where you’d need gigantic machines with a lot of RAM and CPU: because the DB was doing so much work. It was expensive and clunky to get HA right.

In more modern DBs being distributed horizontally, this approach may see a rebound. The big “but” is still costs, in my experience in AWS as an example, managed Postgres Aurora was surprisingly expensive in terms of monthly cost.

Post reply on HN