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

171–180 of 187 posts

Re: Stochastic gradient descent written in SQL

#171

Post by dventimi »

kiwicopple wrote: post #164
giraffe_lady wrote: post #137

Earlier quoted context omitted.

Nah nothing that refined. Pretty predictably supabase is doing some weird stuff along these lines and I found some abandoned and semi-active repos associated with them and people working for them that were useful examples of some things. As for posting to HN absolutely no thanks. These people are so fucking hostile there is no accomplishment too small to tear apart for entertainment here. I have no interest in it.

disclosure: supabase ceo plv8 doesn't have network access, but this can be done with PostgREST. That said, we actually restricted the HTML content-type on our platform so that users cannot do this. This is because we don't want to get too deep into the "frontend" world, with ever-changing frameworks. We've found that if we offer a small feature, our users demand feature-completeness. In this case we're better-off wai…

disclosure: Supabase user

Long time user, first time caller. Besides the DO droplet, separately I also use Supabase and I love it.

I understand completely about restricting the content type, maybe just for now. Fun fact: I work around it by serving up text/xml plus XSLT. I'm sure you don't mind.

Re: Stochastic gradient descent written in SQL

#172

Post by yunwal »

dventimi wrote: post #131
yunwal wrote: post #129

Earlier quoted context omitted.

> When the database is far from saturated on CPU The issue here is if you scale enough saturate the database, you'll have to rewrite essentially all your code if you're a typical CRUD webapp. Basically all of your business logic is about data retrieval. There's probably some companies that can get away with this, but it would be way too expensive for most.

If I scale up to saturate the database CPU...by doing data retrieval? Setting aside my skepticism about saturating the CPU with mere data retrieval, how is that solved by moving the data to another host's CPU, when moving the data involves the very data retrieval that's saturating the database's CPU?

If you mix in compute-heavy calculations (like stochastic gradient descent) in with your pure data-retrieval, yes you'll saturate the database's CPU, and you won't have a reasonable way to scale it.

If you do it on a host that's not a database, then you can horizontally scale it. There's a reason stateless apps are the default.

Re: Stochastic gradient descent written in SQL

#173

Post by dventimi »

yunwal wrote: post #172
dventimi wrote: post #131

Earlier quoted context omitted.

If I scale up to saturate the database CPU...by doing data retrieval? Setting aside my skepticism about saturating the CPU with mere data retrieval, how is that solved by moving the data to another host's CPU, when moving the data involves the very data retrieval that's saturating the database's CPU?

If you mix in compute-heavy calculations (like stochastic gradient descent) in with your pure data-retrieval, yes you'll saturate the database's CPU, and you won't have a reasonable way to scale it. If you do it on a host that's not a database, then you can horizontally scale it. There's a reason stateless apps are the default.

Ok so if I'm not mixing in compute heavy workloads like the stochastic gradient descent described in the article, then it's less likely I'll saturate the CPU. Perhaps that won't happen at all and then I won't have to scale horizontally.

On the other hand if I'm doing stochastic gradient descent that's saturating the CPU then there's a good chance I'm doing offline training of an ML model. In that case my latency tolerances are probably much much higher. In other words, I can also avoid scaling horizontally provided I can live with longer training times. That might be a worthwhile trade-off to me given the added complexity of horizontal scaling.

Good to know!

Re: Stochastic gradient descent written in SQL

#174

Post by dventimi »

spprashant wrote: post #168
rukuu001 wrote: post #141

Earlier quoted context omitted.

No comment re rDBs supporting parallelized ML but re stored procedures - if your workflow evolves to treat them as ‘1st class’ code assets they’ll be just the same as the rest of your code. We always had them in version control, unit tested etc. The tools are there if you want to use them.

I ll take this as a learning opportunity. I have looked around to find a reliable framework to implement within our team and failed to find anything usable. How do you guys manage to implement versioning and testing? If you had a new stored procedure to deploy, where do you deploy? How to you integrate with existing applications which rely on it?

For testing if I'm in PostgreSQL I use pgTAP.

For version control I use git just as I do for other program units.

Re: Stochastic gradient descent written in SQL

#175

Post by dventimi »

justsomeuser wrote: post #35
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 think general programming languages are better for general programs than SQL. Specifically they have: Type systems, compilers, debuggers, text editors, package managers, C FFI etc. But I agree that having the data and the program in the same process has benefits. Writing programs in SQL is one way. Another way is to move your data to your general program with SQLite. I like using SQL for ACID, and queries as a firs…

I don't consider querying a relational database, transforming its data, or validating its state transitions to be general programs. I consider those to be special purpose programs, which benefit from special purpose tools tailored for writing them. SQL is one such tool.

Re: Stochastic gradient descent written in SQL

#176

Post by dventimi »

haolez wrote: post #160
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've created a big system with only SQL in the past like you just said. I wouldn't do it again because of these two pain points that I had to deal with: 1. it's really hard to debug SQL queries and stored procedures (at least it was in Postgres 11) 2. when you hit a performance bottleneck, you don't have much control over it - parallelizing is hard and you have to trick the query planner to do what you want (and it d…

Not challenging your experience but genuinely trying to learn from it, in broad strokes what kinds of things were you doing in these stored procedures?

Re: Stochastic gradient descent written in SQL

#177

Post by glogla »

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... )

I always love DuckDB articles because I can learn what is new in ClickHouse.

Re: Stochastic gradient descent written in SQL

#178

Post by UnCommonLisp »

Lemaxoxo wrote: post #22
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.

That is an outdated view, it is a global and culturally diverse company.

Re: Stochastic gradient descent written in SQL

#179

Post by kiwicopple »

dventimi wrote: post #171
kiwicopple wrote: post #164

Earlier quoted context omitted.

disclosure: supabase ceo plv8 doesn't have network access, but this can be done with PostgREST. That said, we actually restricted the HTML content-type on our platform so that users cannot do this. This is because we don't want to get too deep into the "frontend" world, with ever-changing frameworks. We've found that if we offer a small feature, our users demand feature-completeness. In this case we're better-off wai…

disclosure: Supabase user Long time user, first time caller. Besides the DO droplet, separately I also use Supabase and I love it. I understand completely about restricting the content type, maybe just for now. Fun fact: I work around it by serving up text/xml plus XSLT. I'm sure you don't mind.

> I work around it by serving up text/xml plus XSLT. I'm sure you don't mind.

Not at all, I hear "application/xhtml+xml" is a nice hack from some of the team

Re: Stochastic gradient descent written in SQL

#180

Post by CRConrad »

spprashant wrote: post #135
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…

The problem with throwing everything in a database is you end up with brittle stored procedures all over the place, which are painful to debug. There is no good support for version control or testing, which means you end up creating a dozen copies of each function named (sp_v1, sp_v2,.., etc.). It much more harder to practice iterative development which the rest of software development seems implements effectively. A…

> The problem with throwing everything in a database is you end up with brittle stored procedures all over the place, which are painful to debug. There is no good support for version control or testing...

Shell scripts with the SQL as Here-Docs, in stead of stored procedures. Ordinary text files, eminently gittable. Also lets you insert varying table/column names as environment variables, in stead of the regex rigmarole in TFA. (Or was that in another post on his blog? Just returned from reading several of them.) Best of both worlds.

Post reply on HN