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

Advent of Code 2024 in pure SQL

databasearchitects.blogspot.com

Post reply on HN
Print view View original

71–80 of 110 posts

Re: Advent of Code 2024 in pure SQL

#71

Post by harrall »

knicholes wrote: post #46
larodi wrote: post #39

Earlier quoted context omitted.

but why? what would make you react at human ingenuity with shame and desire? is this something about you or something about them in particular? isnt the whole of HN about human ingenuity...? are we to feel Taco Bell menu about it all, what am I missing?

This is just a guess, but if the OP's reason is similar to mine, DBMSs should be reserved for managing databases and not implementing complex logic.

SQL is like regex.

A small fraction of people bothered to learn how they work and know how and when to use them properly.

Everyone else just perpetuates a giant in-joke of “it’s black magic!”

Re: Advent of Code 2024 in pure SQL

#72

Post by solumunus »

larodi wrote: post #42
fifilura wrote: post #26

Earlier quoted context omitted.

> Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query in the first place. And in "modern" SQL this is solved with CTEs. Use them to unwrap the schema the way you want it first, before implementing the business logic.

this is poor man's SQL to unwrap for business logic. SQL is such more more about everything else and so litte about the trouble with mapping business logic into storage.

Please elaborate.

Re: Advent of Code 2024 in pure SQL

#73

Post by pjmlp »

SoftTalker wrote: post #11

Over my career I've certainly written more SQL than any other type of code. Not so much in the last five years so I'm sure I've lost some of it, but I used to really enjoy it. Once you stop thinking iteratively and start thinking in set operations it becomes quite natural and powerful.

Yeah, I also kind of like coding in SQL, with PL/SQL being my favourite extension language, which is kind of heresy in HN, but whatever.

Re: Advent of Code 2024 in pure SQL

#75

Post by phartenfeller »

Nicely done. I know this seems crazy at first but in my opinion big SQLs are one of the best ways to store complexity.

The problem being complex is the issue. SQL is a standard, condensed, extremely performant, actually testable, and logical language. Sure, not anybody can instantly maintain it but that would be the same as if it was a lot of lines and functions in Java. The more lines the more risk for bugs.

I also love how deep SQL goes. And that makes totally sense. It powers the world of data since 40+ years so of course people asked for niche features. One of my favorite is the model clause in Oracle with which you can implement multidimensional arrays. A friend implemented Conway's game of life with it in way less lines you expect.

Re: Advent of Code 2024 in pure SQL

#76

Post by dagss »

I have been writing a ton of SQL -- implementing a lot of the business logic of a (stream processing) application in it. I really really like it, especially that I bring the computation to the data instead of the data to the computation.

I often talk to developers who hate that idea though. They want me to instead move all the data to the backend, for a massive IO hit, just so that the computations can be expressed in a "real" programming language.

I think SQL the concept is good but SQL the language is the problem. There are so many awkward things in it -- not strange as it has had no competition in 40(?) years!! The mental model of the program I write is fine but I really need to overlook the syntax and to the program I am really writing to see any elegance...

What we need I think is a properly designed programming language, designed for existing databases (Postgres, MSSQL) compiling to SQL dialects. I see some contenders but they all have some attachment to a special domain, such as not allowing data modifications (PreQL) or being associated with other databases.

Itching to do it myself, but it's a lot of work and a long long road to any adoption with no guarantee of success, and no revenue stream that I can think of.

The most popular backend languages were made by large companies, but I think coding in SQL is in a catch-22 where it will be frowned upon until there is a better language and no better language until it is more popular..

Re: Advent of Code 2024 in pure SQL

#77

Post by RobinL »

liontwist wrote: post #54
fifilura wrote: post #28

Earlier quoted context omitted.

It may be true, until you do your ETL in an index-less database such as BigQuery or Trino. Postgres will always be faster for optimized, end user serving, queries. But BigQuery allows you to scale it to 100s of CPUs without having to worry about indexes.

This sounds awful. I would do almost any amount of iteration and index tuning to keep the query on a single machine rather than deal with a networked distributed system. When you get slow queries the real problem is algorithmic complexity and linear workers only can do so much,

You can use duckdb on a single machine. It's also indexless (or more accurately, you don't have to explicitly create indexes)

Re: Advent of Code 2024 in pure SQL

#78

Post by willvarfar »

dagss wrote: post #76

I have been writing a ton of SQL -- implementing a lot of the business logic of a (stream processing) application in it. I really really like it, especially that I bring the computation to the data instead of the data to the computation. I often talk to developers who hate that idea though. They want me to instead move all the data to the backend, for a massive IO hit, just so that the computations can be expressed i…

Me too me too :D

There's a lot that is very right about SQL, but a few clunky bits around the edges.

CTEs made a lot of difference, and window functions - which can be a bit head bending - made difficult things a tiny bit easier.

I'm using BigQuery, which supports structs and arrays, but only recently allowed arrays to be grouped by, although there is still no equality check etc.

BigQuery is slowly adding more sugar, like aggregate UDFs and polymorphic UDFs using ANY TYPE parameters etc, and I find myself putting more reused logic into tidy functions, but my pet want is for temporary functions to be declared and scoped like CTEs so they integrate a lot better into tooling like DBT that wants everything to be in one statement.

And the one most productive thing they could add? Allowing you to specify null behaviour on JOIN USING. (Having to spell out foo.bar IS NOT DISTINCT FROM bar.bar on a join is unobvious and ugly. Something like USING (bar RESPECT NULLS) would be so much nicer.)

Re: Advent of Code 2024 in pure SQL

#79

Post by Moosturm »

pearjuice wrote: post #30

Doing it in pure SQL is really impressive but I think the real tell-tale sign of peak "cracked engineer energy" is the maintained, decade-old blogspot site. Can't exactly put my finger on it, but really gives off "niche mastery". I don't even know the authors but I'm sure in the right circles a few dudes maintaining a blogspot site called "database architects" for a decade probably don't need an introduction.

Those authors are the elite of DB mastery.

Re: Advent of Code 2024 in pure SQL

#80

Post by bob1029 »

harrall wrote: post #70
ulrikrasmussen wrote: post #66

Earlier quoted context omitted.

It is closer to Datalog I think, or can you express cut? CTEs are fairly restricted compared to logic programmming languages though, at least for Postgres. In particular, relations cannot be mutually recursive and your rules may only be linearly recursive in themselves (i.e. can contain only one instance of themselves in the right hand side). Postgres is overly restrictive in the latter and requires at most once recu…

Haven’t written SQL in a while (and I used to write a lot) but I think SQL Server recursive CTEs are fairly unbounded so it’s just a Postgres limitation unfortunately. (I’m a fan of MS SQL but it’s Microsoft and also hard to financially justify for many companies. But if you ever get to use it, it is a very solid RDBMS, even if the rest of your stack is open source.)

The cost of MSSQL is largely controlled by how the system is designed and the complexity of the business.

The model I am most familiar with is a 10-20 employee B2B SaaS startup running one "big" instance on a single vm in the cloud somewhere. If this is approximately all you require, then the cost should not be a dominating factor in your decision.

I think "because Microsoft" is also really poor justification if we are being serious about the technological capabilities and pursuing high quality business outcomes. If your business is fundamentally about open source advocacy and you are operating as a non profit, I totally get it. But, this is probably not your business model.

Post reply on HN