8 ms·

>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

>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 powerful than we think, they're not going to go away, only get better, and having the data and the logic in the same space really removes tons of headaches. ML models, transformation of data, generating json for an API can all be done within the database rather than outside scripting language. Are others seeing this? Are the current tradeoffs just that more people know python vs sql or database specific languages to where moving logic to postgres or snowflake is looked down on?

actionfromafar 4y ago
I have yet to see a decent IDE or system which allows great version control, unit testing and collaboration with SQL source code. So I think a lot of the reluctance is from practical concerns.
- noloblo 4y ago
  We store individual sql files in github and keep them in separate folders This is very simple and scales well for our purposes
tomrod 4y ago
Absolutely. Consider too that PostgreSQL databases support different languages, like Python. Loads of for-profit companies have tried to cash in on this. SAP HANA is one of the ones I've had recent experience with. It is unfortunately a poor implementation. The right architecture tends to be: put your model behind an API interface, not internal on the system. Train your model separately from production systems, and so on. You might also be interested in checking out MLOps platforms like Kubeflow, Flyte, and others.
swyx 4y ago
had a very good chat with https://postgresml.org/ https://postgresml.org/ last week which is focusing on bringing ML to postgres: https://youtu.be/j8hE8-jZJGU https://youtu.be/j8hE8-jZJGU
- Lemaxoxo 4y ago
  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.
  - swyx 4y ago
    thanks for watching! i should really up the production quality haha but also this is what i can kinda manage with my existing workload. idk how the pro youtubers make these calls interesting
noloblo 4y ago
+1 sql is extremely elegant composable and is under rated Postgres is very powerful. While I sought a short detour in nosql Mongodb land now back to Mysql Postgresql sql territory and glad for it Being able to generate views is and stored procedures is useful as well.having sql Take over more like ml, gradient descent does open up good possibility. Also since sql is declarative it Makes it so it's rather easier than imperative scripting languages
- naasking 4y ago
  SQL has some positives but it is not composable. At all. This is because relations are not first-class values in SQL.
  - dventimi 4y ago
    Is a query not a relation?
    - naasking 4y ago
      Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate < '2000-01-01'
      - noloblo 4y ago
        Exactly since it declarative the style Lends itself using stored procedure calls to become composable
        naasking 4y ago
        You cannot abstract over stored procedures either, so that's still not composable.
        4y ago
daveguy 4y ago
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.
Scubabear68 4y ago
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.
- dventimi 4y ago
  Why would running code in a database process be intrinsically more expensive than running it in some other process?
  - yunwal 4y ago
    The issue is that many relational databases are not horizontally scalable, so you want to be frugal with their resources.
    - dventimi 4y ago
      The resources I'm familiar with are I/O, memory, and CPU. The only one I believe can be spared in the database by using that resource outside of the database, is CPU. When the database is far from saturated on CPU and latency and throughput are determined by I/O and memory, using CPU on some other machine that isn't the database can't possibly have any impact on latency and throughput.
      - yunwal 4y ago
        > 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.
ISL 4y ago
The challenge I, with limited knowledge, see with developing detailed algorithms in SQL is a lack of good testing, abstraction, and review tooling. Similarly for a lot of the user-defined-functions for the larger data warehouses (redshift, bigQuery, etc.) dbt solves a lot of it, but I'd love to learn more about good resources for building reliable and readily-readable SQL algorithms for complex logic.
- AndrewKemendo 4y ago
  I mean this is an opportunity right? Build those things
  - Lemaxoxo 4y ago
    I agree. Databases are going to be here for a long time, and we're barely scratching the surface of making people productive with them. dbt is just the beginning.
- adeelk93 4y ago
  I've supported 3 different models over the years with inference implemented in SQL. First one I inherited, loved it so much that I implemented it twice again. Amazingly fast for TBs of data and no waiting on other tech teams. That tooling you're describing is definitely not there. Bigquery has BQML but it's very much in its infancy. I tend to do all the modeling in Python/R on sampled data and then deploy to SQL.
  - fithisux 4y ago
    BQML should become standard.
FpUser 4y ago
It is tempting to combine web server, database and some imperative language with built in data oriented / SQL features in a single executable and call it an application server that would communicate with the outside world using for example JSON based RPC. I think there were / are some products in the area even with the built in IDE (like Wakanda).
justsomeuser 4y ago
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 first filter to get the data into my program where I may do further processing with the general language.
- fifilura 4y ago
  > Type systems SQL has types > compilers For what specifically do you need a compiler? > debuggers Some tasks - like the concurrency SQL enables - are just very difficult to debug with debuggers. It would be the same with any other language. What SQL does here though is to allow you to focus on the logic, not the actual concurrency, > text editors, package managers I feel like these two are just for filling up the space. > C FFI Many SQL engines have UDFs
  - justsomeuser 4y ago
    > 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.
    - 4y ago
nerdponx 4y ago
In the data warehouse / OLAP space, I think we are heading towards a world where the underlying data storage consists of partitioned Arrow arrays, and you can run arbitrary UDFs in arbitrary languages on the (read-only) raw data with zero copy and essentially no overhead other than the language runtime itself and marshalling of the data that is emitted from the UDF. Something like the Snowflake data storage model + DuckDB as an execution engine + a Pandas/Polars-like API. There is no reason why we have to be stuck with "database == SQL" all the time. SQL is extremely powerful, but sometimes you need a bit more, and in that case we shouldn't be so constrained. But in general yes, the world is gradually waking up to the idea that performance matters, and that data locality can be extremely important for performance, and that doing your computations and data processing on your warehouse data in-place is going to be a huge time and money saver in the longer term.
nonethewiser 4y ago
> Databases are much more powerful than we think And a function of what people think is attitudes towards working at the DB level. I see this often with ORM's in the web dev sphere (rather than Dat Science). Yes, ORM's are great but many people rely on them to completely abstract away the database and are terrified by raw sql or even query building. You also see it with services that abstract away the backend like PocketBase, Fireship, etc. Writing a window function or even a sub select looks like black magic to many. I say this after several experiences with codebases where joins and filtering were often done at the application layer and raw sql was seen as the devil.
- boringg 4y ago
  Opposite here - dont like ORMs. Too much overhead - though i get their value.
dchftcs 4y ago
There is a recent trend in database research, ML in databases. Not sure how much an impact it can make though, the sweet spot is doing relatively linear stuff, arguably just a step up from analytical functions in queries, while cutting edge ML needs dedicated GPUs for compute load and often uses unstructured and even binary data.
62951413 4y ago
Junior developers like me were uncomfortable with SQL twenty years ago. Java ORM frameworks became popular because of the Object-Relational impedance. I kind of see the same kind of sentiment nowadays among newer generations but in Python&Co. The success of the Apache Spark engine can at least partially be attributed to * being able to have the same expressive power as SQL but with a real Scala API (including having reusable libraries based on it) * being able to embed it into unit tests at a low price of additional ~20 seconds latency to spin up a local Spark master
roflyear 4y ago
> Are the current tradeoffs just that more people know python vs sql or database specific languages to where moving logic to postgres or snowflake is looked down on? Yes, mostly development, deployment, etc.. concerns. I haven't ever seen an org that versions their SQL queries, unless they are in a codebase. The environment is just unfriendly towards that type of management. Nevermind testing! Things that have solutions but we haven't matured enough here, because that type of development has been happening in application code. Also, SQL is generally more complex than application logic, because they are designed to do different things. What is a simple exercise in iteration or recursion can more easily become something a little more of a headache. Problems that can be resolved, but they are problems.
bob1029 4y ago
> Databases are much more powerful than we think The older I get the more I agree with this. There is nothing you cannot build by combining SQL primitives. Side effects can even be introduced - on purpose - by way of UDFs that talk to the outside world. I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. You might think this is horrible and indeed many manifestations are. But, there lurk an opportunity for incredible elegance down this path, assuming you have the patience to engage in it.
- giraffe_lady 4y ago
  > I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. I did this for a side project a few months ago and even used postgrest to serve the page with correct headers for html. It felt simultaneously really cursed and obvious. Shit you could even use plv8 to run mustache or whatever in the db if you really wanted to piss people off.
  - dventimi 4y ago
    I'm doing this right now with a DO droplet built with PostgreSQL, postgrest, nginx, and not much else. Do you have any tips, tricks, or blog posts you can share based on your experience? You should post it to HN. Strike while the iron's hot. With a little luck you'll hit the front page.
    - giraffe_lady 4y ago
      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.
      - 4y ago
_a_a_a_ 4y ago
Self-proclaimed database expert here. What a database is good at depends on what you're trying to get that database to do, at least in part. Take it into piecess, elegance and efficiency. These will correspond to a logical statement of what you're trying to do, and how quickly the database will actually do it in practice. SQL can do some nice things in areas, making it elegant in those areas. Elsewhere it can be pretty wordy and ugly. In efficiency, it comes down largely to how the database is implemented and that also includes the capability of the optimiser. Both of these are out of your control. In my experience trying to turn a database into a number cruncher is just not going to work. I guess that's long way round of me saying that I don't think I agree with you!
somat 4y ago
A dbms is really it's own operating system, usually this is hosted on another operating system, one that understands the hardware. I remember one place I worked where we had several old graybeard programmers who considered the dbms[1] the operating system, as a unix sysadmin we had some interesting discussions as I was always confused and confined by the dbms and they felt the same about unix. 1. unidata if curious, a weird multi value(not relational) database, very vertically integrated compared to most databases today.
7thaccount 4y ago
Database first designs make a lot of sense in a lot of ways. I've worked for a company with an Oracle database that has SQL scripts that do all the validation and create text files for downstream usage. I think it makes more sense than a ton of Java, but there are pros and cons. One is that SQL is relational and the advanced stuff can be extra hard to troubleshoot if you don't have enough experience. Even those that can't code can usually understand a for loop and can think imperatively. Unfortunately it's an expensive commercial product or I'd recommend you look at kdb+ if you work with time series data. The big banks use it and essentially put all thier latest RT data into kdb+ and then can write extremely succinct queries with a SQL-like syntax, but the ability to approach it far more programmatically than what is typically doable with something like PL-SQL. You can even write your neural network or whatever code in less than a page of code as the language of kdb+ is extremely powerful, although also basically incomprehensible until someone puts some time into learning it. It's extremely lightweight though, so very easy to deal with in an interactive fashion. All that to say I agree with you that it's nice to just have everything you want all in one spot rather than to deal with 4 different tools and pipelines and shared drives and so on.
maCDzP 4y ago
I agree. I took a course in databases and SQL and was blown away by its power. With CTE’s and PLSQL you can do a lot of stuff inside the database. I played with SQLite and it’s json columns. Once you get the hang of the syntax for walking a json structure you can do all sorts of neat things. Doing the same thing in Python would be tedious. And I also believe it ended up being way faster than what I did in python.
fzeindl 4y ago
Fully agree. And using PostgREST [0] you can serve your postgreSQL database as REST-API. And if you throw foreign data wrappers / multicorn in the mix, you can map any other datasource into your postgreSQL-db as table. [0] https://postgrest.org/en/stable/ https://postgrest.org/en/stable/
- JUNGLEISMASSIVE 4y ago
  [dead]
jhd3 4y ago
> Databases are much more powerful than we think and data has mass. One example of bringing the work to the data is https://madlib.apache.org/ https://madlib.apache.org/ (works on Postgres and Greenplum) [Disclaimer - former employee of Pivotal]
CreRecombinase 4y ago
I spend most of my time in the parallel universe that is scientific computing/HPC. In this alternate reality SQL (not to mention databases) never really took off. Instead of scalable, performant databases, we have only the parallel filesystem. I'm convinced the reason contemporary scientific computing don't involve much SQL is sociological/path-dependency, but there are also very good technical reasons. Optimizing software in scientific computing involves two steps: 1) Think hard about your problem until you can frame it as one or more matrix multiplications 2) Plug that into a numerical linear algebra library The SQL abstraction (in my experience) takes you very much in the opposite direction.
- wpietri 4y ago
  For sure. Anything done in SQL is running on top of a million lines or more of extremely complicated non-SQL code. If that works for a given use case, great, but if not, optimizing can get very challenging. I'd much rather deal with something closer to the metal.
spprashant 4y ago
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. Also traditional relational databases have a way to go before they can support parallelized machine learning workloads. You do not have control or the ability to spin up threads or processes to boost your performance. You rely on the query processor to make those decisions, and depending on your platform the results will be mixed.
- rukuu001 4y ago
  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.
  - itsthecourier 4y ago
    Updating stored procedures was a pain in the ass last time I checked. Checking changes on them in deployed servers was too very painful
  - spprashant 4y ago
    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?
    - dventimi 4y ago
      For testing if I'm in PostgreSQL I use pgTAP. For version control I use git just as I do for other program units.
margorczynski 4y ago
The problem with this approach is that: 1) No static typing 2) Updating the logic requires migrations 3) You put the logic into the place which is the hardest to scale and many times a single point of failure 4) Cannot compose the code effectively and general verbosity It's one of those ideas that sounds great on paper and maybe works in some smaller problems but as you go up in complexity things get worse and worse
haolez 4y ago
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 doesn't work sometimes)
- dventimi 4y ago
  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?

actionfromafar 4y ago

I have yet to see a decent IDE or system which allows great version control, unit testing and collaboration with SQL source code. So I think a lot of the reluctance is from practical concerns.

noloblo 4y ago
We store individual sql files in github and keep them in separate folders This is very simple and scales well for our purposes

tomrod 4y ago

Absolutely. Consider too that PostgreSQL databases support different languages, like Python. Loads of for-profit companies have tried to cash in on this. SAP HANA is one of the ones I've had recent experience with. It is unfortunately a poor implementation. The right architecture tends to be: put your model behind an API interface, not internal on the system. Train your model separately from production systems, and so on. You might also be interested in checking out MLOps platforms like Kubeflow, Flyte, and others.

swyx 4y ago

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

Lemaxoxo 4y ago
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.
- swyx 4y ago
  thanks for watching! i should really up the production quality haha but also this is what i can kinda manage with my existing workload. idk how the pro youtubers make these calls interesting

noloblo 4y ago

+1 sql is extremely elegant composable and is under rated Postgres is very powerful. While I sought a short detour in nosql Mongodb land now back to Mysql Postgresql sql territory and glad for it Being able to generate views is and stored procedures is useful as well.having sql Take over more like ml, gradient descent does open up good possibility. Also since sql is declarative it Makes it so it's rather easier than imperative scripting languages

naasking 4y ago
SQL has some positives but it is not composable. At all. This is because relations are not first-class values in SQL.
- dventimi 4y ago
  Is a query not a relation?
  - naasking 4y ago
    Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate < '2000-01-01'
    - noloblo 4y ago
      Exactly since it declarative the style Lends itself using stored procedure calls to become composable
      - naasking 4y ago
        You cannot abstract over stored procedures either, so that's still not composable.
        4y ago

daveguy 4y ago

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.

Scubabear68 4y ago

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.

dventimi 4y ago
Why would running code in a database process be intrinsically more expensive than running it in some other process?
- yunwal 4y ago
  The issue is that many relational databases are not horizontally scalable, so you want to be frugal with their resources.
  - dventimi 4y ago
    The resources I'm familiar with are I/O, memory, and CPU. The only one I believe can be spared in the database by using that resource outside of the database, is CPU. When the database is far from saturated on CPU and latency and throughput are determined by I/O and memory, using CPU on some other machine that isn't the database can't possibly have any impact on latency and throughput.
    - yunwal 4y ago
      > 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.

ISL 4y ago

The challenge I, with limited knowledge, see with developing detailed algorithms in SQL is a lack of good testing, abstraction, and review tooling. Similarly for a lot of the user-defined-functions for the larger data warehouses (redshift, bigQuery, etc.) dbt solves a lot of it, but I'd love to learn more about good resources for building reliable and readily-readable SQL algorithms for complex logic.

AndrewKemendo 4y ago
I mean this is an opportunity right? Build those things
- Lemaxoxo 4y ago
  I agree. Databases are going to be here for a long time, and we're barely scratching the surface of making people productive with them. dbt is just the beginning.
adeelk93 4y ago
I've supported 3 different models over the years with inference implemented in SQL. First one I inherited, loved it so much that I implemented it twice again. Amazingly fast for TBs of data and no waiting on other tech teams. That tooling you're describing is definitely not there. Bigquery has BQML but it's very much in its infancy. I tend to do all the modeling in Python/R on sampled data and then deploy to SQL.
- fithisux 4y ago
  BQML should become standard.

FpUser 4y ago

It is tempting to combine web server, database and some imperative language with built in data oriented / SQL features in a single executable and call it an application server that would communicate with the outside world using for example JSON based RPC. I think there were / are some products in the area even with the built in IDE (like Wakanda).

justsomeuser 4y ago

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 first filter to get the data into my program where I may do further processing with the general language.

fifilura 4y ago
> Type systems SQL has types > compilers For what specifically do you need a compiler? > debuggers Some tasks - like the concurrency SQL enables - are just very difficult to debug with debuggers. It would be the same with any other language. What SQL does here though is to allow you to focus on the logic, not the actual concurrency, > text editors, package managers I feel like these two are just for filling up the space. > C FFI Many SQL engines have UDFs
- justsomeuser 4y ago
  > 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.
  - 4y ago

nerdponx 4y ago

In the data warehouse / OLAP space, I think we are heading towards a world where the underlying data storage consists of partitioned Arrow arrays, and you can run arbitrary UDFs in arbitrary languages on the (read-only) raw data with zero copy and essentially no overhead other than the language runtime itself and marshalling of the data that is emitted from the UDF. Something like the Snowflake data storage model + DuckDB as an execution engine + a Pandas/Polars-like API. There is no reason why we have to be stuck with "database == SQL" all the time. SQL is extremely powerful, but sometimes you need a bit more, and in that case we shouldn't be so constrained. But in general yes, the world is gradually waking up to the idea that performance matters, and that data locality can be extremely important for performance, and that doing your computations and data processing on your warehouse data in-place is going to be a huge time and money saver in the longer term.

nonethewiser 4y ago

> Databases are much more powerful than we think And a function of what people think is attitudes towards working at the DB level. I see this often with ORM's in the web dev sphere (rather than Dat Science). Yes, ORM's are great but many people rely on them to completely abstract away the database and are terrified by raw sql or even query building. You also see it with services that abstract away the backend like PocketBase, Fireship, etc. Writing a window function or even a sub select looks like black magic to many. I say this after several experiences with codebases where joins and filtering were often done at the application layer and raw sql was seen as the devil.

boringg 4y ago
Opposite here - dont like ORMs. Too much overhead - though i get their value.

dchftcs 4y ago

There is a recent trend in database research, ML in databases. Not sure how much an impact it can make though, the sweet spot is doing relatively linear stuff, arguably just a step up from analytical functions in queries, while cutting edge ML needs dedicated GPUs for compute load and often uses unstructured and even binary data.

62951413 4y ago

Junior developers like me were uncomfortable with SQL twenty years ago. Java ORM frameworks became popular because of the Object-Relational impedance. I kind of see the same kind of sentiment nowadays among newer generations but in Python&Co. The success of the Apache Spark engine can at least partially be attributed to * being able to have the same expressive power as SQL but with a real Scala API (including having reusable libraries based on it) * being able to embed it into unit tests at a low price of additional ~20 seconds latency to spin up a local Spark master

roflyear 4y ago

> Are the current tradeoffs just that more people know python vs sql or database specific languages to where moving logic to postgres or snowflake is looked down on? Yes, mostly development, deployment, etc.. concerns. I haven't ever seen an org that versions their SQL queries, unless they are in a codebase. The environment is just unfriendly towards that type of management. Nevermind testing! Things that have solutions but we haven't matured enough here, because that type of development has been happening in application code. Also, SQL is generally more complex than application logic, because they are designed to do different things. What is a simple exercise in iteration or recursion can more easily become something a little more of a headache. Problems that can be resolved, but they are problems.

bob1029 4y ago

> Databases are much more powerful than we think The older I get the more I agree with this. There is nothing you cannot build by combining SQL primitives. Side effects can even be introduced - on purpose - by way of UDFs that talk to the outside world. I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. You might think this is horrible and indeed many manifestations are. But, there lurk an opportunity for incredible elegance down this path, assuming you have the patience to engage in it.

giraffe_lady 4y ago
> I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. I did this for a side project a few months ago and even used postgrest to serve the page with correct headers for html. It felt simultaneously really cursed and obvious. Shit you could even use plv8 to run mustache or whatever in the db if you really wanted to piss people off.
- dventimi 4y ago
  I'm doing this right now with a DO droplet built with PostgreSQL, postgrest, nginx, and not much else. Do you have any tips, tricks, or blog posts you can share based on your experience? You should post it to HN. Strike while the iron's hot. With a little luck you'll hit the front page.
  - giraffe_lady 4y ago
    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.
    - 4y ago

_a_a_a_ 4y ago

Self-proclaimed database expert here. What a database is good at depends on what you're trying to get that database to do, at least in part. Take it into piecess, elegance and efficiency. These will correspond to a logical statement of what you're trying to do, and how quickly the database will actually do it in practice. SQL can do some nice things in areas, making it elegant in those areas. Elsewhere it can be pretty wordy and ugly. In efficiency, it comes down largely to how the database is implemented and that also includes the capability of the optimiser. Both of these are out of your control. In my experience trying to turn a database into a number cruncher is just not going to work. I guess that's long way round of me saying that I don't think I agree with you!

somat 4y ago

A dbms is really it's own operating system, usually this is hosted on another operating system, one that understands the hardware. I remember one place I worked where we had several old graybeard programmers who considered the dbms[1] the operating system, as a unix sysadmin we had some interesting discussions as I was always confused and confined by the dbms and they felt the same about unix. 1. unidata if curious, a weird multi value(not relational) database, very vertically integrated compared to most databases today.

7thaccount 4y ago

Database first designs make a lot of sense in a lot of ways. I've worked for a company with an Oracle database that has SQL scripts that do all the validation and create text files for downstream usage. I think it makes more sense than a ton of Java, but there are pros and cons. One is that SQL is relational and the advanced stuff can be extra hard to troubleshoot if you don't have enough experience. Even those that can't code can usually understand a for loop and can think imperatively. Unfortunately it's an expensive commercial product or I'd recommend you look at kdb+ if you work with time series data. The big banks use it and essentially put all thier latest RT data into kdb+ and then can write extremely succinct queries with a SQL-like syntax, but the ability to approach it far more programmatically than what is typically doable with something like PL-SQL. You can even write your neural network or whatever code in less than a page of code as the language of kdb+ is extremely powerful, although also basically incomprehensible until someone puts some time into learning it. It's extremely lightweight though, so very easy to deal with in an interactive fashion. All that to say I agree with you that it's nice to just have everything you want all in one spot rather than to deal with 4 different tools and pipelines and shared drives and so on.

maCDzP 4y ago

I agree. I took a course in databases and SQL and was blown away by its power. With CTE’s and PLSQL you can do a lot of stuff inside the database. I played with SQLite and it’s json columns. Once you get the hang of the syntax for walking a json structure you can do all sorts of neat things. Doing the same thing in Python would be tedious. And I also believe it ended up being way faster than what I did in python.

fzeindl 4y ago

Fully agree. And using PostgREST [0] you can serve your postgreSQL database as REST-API. And if you throw foreign data wrappers / multicorn in the mix, you can map any other datasource into your postgreSQL-db as table. [0] https://postgrest.org/en/stable/ https://postgrest.org/en/stable/

JUNGLEISMASSIVE 4y ago
[dead]

jhd3 4y ago

> Databases are much more powerful than we think and data has mass. One example of bringing the work to the data is https://madlib.apache.org/ https://madlib.apache.org/ (works on Postgres and Greenplum) [Disclaimer - former employee of Pivotal]

CreRecombinase 4y ago

I spend most of my time in the parallel universe that is scientific computing/HPC. In this alternate reality SQL (not to mention databases) never really took off. Instead of scalable, performant databases, we have only the parallel filesystem. I'm convinced the reason contemporary scientific computing don't involve much SQL is sociological/path-dependency, but there are also very good technical reasons. Optimizing software in scientific computing involves two steps: 1) Think hard about your problem until you can frame it as one or more matrix multiplications 2) Plug that into a numerical linear algebra library The SQL abstraction (in my experience) takes you very much in the opposite direction.

wpietri 4y ago
For sure. Anything done in SQL is running on top of a million lines or more of extremely complicated non-SQL code. If that works for a given use case, great, but if not, optimizing can get very challenging. I'd much rather deal with something closer to the metal.

spprashant 4y ago

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. Also traditional relational databases have a way to go before they can support parallelized machine learning workloads. You do not have control or the ability to spin up threads or processes to boost your performance. You rely on the query processor to make those decisions, and depending on your platform the results will be mixed.

rukuu001 4y ago
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.
- itsthecourier 4y ago
  Updating stored procedures was a pain in the ass last time I checked. Checking changes on them in deployed servers was too very painful
- spprashant 4y ago
  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?
  - dventimi 4y ago
    For testing if I'm in PostgreSQL I use pgTAP. For version control I use git just as I do for other program units.

margorczynski 4y ago

The problem with this approach is that: 1) No static typing 2) Updating the logic requires migrations 3) You put the logic into the place which is the hardest to scale and many times a single point of failure 4) Cannot compose the code effectively and general verbosity It's one of those ideas that sounds great on paper and maybe works in some smaller problems but as you go up in complexity things get worse and worse

haolez 4y ago

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 doesn't work sometimes)

dventimi 4y ago
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?