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

61–70 of 110 posts

Re: Advent of Code 2024 in pure SQL

#61

Post by fifilura »

crazygringo wrote: post #38
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.

Yes, I'm talking about end user queries. Not reports that take 2 hours to run. But even with BigQuery, you've still got to worry about partioning and clustering, and yes they've even added indexes now. The only time you really just get to think in sets, is when performance doesn't matter at all and you don't mind if your query takes hours. Which maybe is your case. But also -- the issue isn't generally CPU, but rathe…

Of course there are optimizations to be made, such as not joining on the raw data or saving the order by to last. And avoid outer joins between two large sized partitioned tables.

But to me those optimizations are not imperative in nature.

(And BQ will probable eat the 10 million to 10 million join for breakfast...)

Re: Advent of Code 2024 in pure SQL

#62

Post by fifilura »

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 are rightfully proud of your skills!

Re: Advent of Code 2024 in pure SQL

#63

Post by grumple »

thehours wrote: post #59

Does 'pure SQL' have a specific definition (or reference spec)? The author doesn't mention it, except to say he tested it across Umbra, Postgres, and DuckDB. Even then, some days weren't 'supported by DuckDB due to a missing xor operator'.

There is an ISO spec (you have to pay for access): https://www.iso.org/standard/76583.html

Re: Advent of Code 2024 in pure SQL

#64

Post by UltraSane »

qsort wrote: post #5
scop wrote: post #4

I reacted to this title the way I react a new menu item at Taco Bell: a strange mixture of desire, shame, and admiration for human ingenuity.

I work a lot with databases and I've seen... stuff. It's not as bad as you might think if you know what you are doing. Most RDBMSs support recursive CTEs, it feels like writing Prolog with a slightly sadistic syntax. For something like AoC the most difficult part is probably parsing the input.

A company I worked for uses Syteline ERP which heavily relies on SQL Server. But the DBA was constantly complaining about how slow the Syteline SQL was. One major issue was long running transactions taking 10 minutes locking rows/tables for too long and using a lot of memory. You would think very expensive ERP systems would have decent SQL.

Re: Advent of Code 2024 in pure SQL

#65

Post by tnvmadhav »

trjordan wrote: post #18

If you like this kind of degeneracy, I tried AoC in Google Sheets this year. I only made it to Day 6, and not even both stars every day. I'm pretty confident my Day 7 solution is correct, but I hit per-cell character limits on longer inputs. Enjoy :) oh but don't open it on mobile, some sheets crash the app https://docs.google.com/spreadsheets/d/10FY-89y19tnRM_EAAnCd...

holy sh*t, I kneel.

Re: Advent of Code 2024 in pure SQL

#66

Post by ulrikrasmussen »

qsort wrote: post #5
scop wrote: post #4

I reacted to this title the way I react a new menu item at Taco Bell: a strange mixture of desire, shame, and admiration for human ingenuity.

I work a lot with databases and I've seen... stuff. It's not as bad as you might think if you know what you are doing. Most RDBMSs support recursive CTEs, it feels like writing Prolog with a slightly sadistic syntax. For something like AoC the most difficult part is probably parsing the input.

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 recursive reference over all subqueries in the UNION even though it would be safe to only restrict the number of recursive calls for each subquery (each corresponding to a separate Datalog rule for the same relation). It is possible to work around that restriction using a local WITH expression (a hack really), but then you are also on your own since it disables all checks and allows you to write rules which uses actual nonlinear recursion and will give incorrect result sets when evaluated.

I really would like Postgres to have proper support for writing Datalog queries, and with better and more efficient incremental evaluation algorithms as opposed to the iterative semi-naive algorithm that is only supported now.

Re: Advent of Code 2024 in pure SQL

#67

Post by xarope »

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.

In the past (why do I feel so old when I say this?), DBMSs WERE used to implement complex logic. Not just complex business logic, but even authentication, authorization, etc were implement with stored procs, embedded sql, native DB features.

The cycle in tech will shift from one side to another; last decade was a lot of no-sql where all this logic and implementation had to reside on the application side. I'm seeing a shift back to DBs again (e.g. supabase), and the reality is that it's probably a continuum somewhere in-between, depending on your requirements (as always, the answer is "Yes, but it depends").

Remember, these are all "just" tools.

Re: Advent of Code 2024 in pure SQL

#68

Post by liontwist »

FridgeSeal wrote: post #55
liontwist wrote: post #48

Earlier quoted context omitted.

Why not? I think the main barrier is the programmers are not comfortable with recursion, rather than a technical limitation.

I’d hazard a guess that quite a few devs have at one point, been lumped with a db where someone pushed too much logic into it. Probably great for the one person who wrote it, and awful for everyone else. Mystery triggers, unclear or badly managed stored procedures, opaque invariants. It’s not to say that all of these things are bad, but a certain level of DB complexity will naturally set off alarm bells for some devs…

SQL tends to be very dense (more thinking than typing).

It is tedious to specify all that information (access, constraints, foreign key, cascading, views), but it’s all essential information you would end up specifying in another way.

Re: Advent of Code 2024 in pure SQL

#69

Post by liontwist »

scop wrote: post #57
FridgeSeal wrote: post #55

Earlier quoted context omitted.

I’d hazard a guess that quite a few devs have at one point, been lumped with a db where someone pushed too much logic into it. Probably great for the one person who wrote it, and awful for everyone else. Mystery triggers, unclear or badly managed stored procedures, opaque invariants. It’s not to say that all of these things are bad, but a certain level of DB complexity will naturally set off alarm bells for some devs…

One of the most eye opening moments in my junior dev career was when I found a really nifty way to achieve an operation using SQL alone and brought it to our VP (start up, small company size). It wasn’t esoteric, but used some SQL features not well-trodden by web devs. I thought I was the bees knees and this would be a clear demonstration of my sizable brain. He complimented the thought behind it, but immediately vet…

I don’t think this is a win. Cautioning against adding new technology to learn and maintain is one thing. But not using an effective solution in your toolset because it’s too hard is absurd.

Our Industry pretends solutions must be immediately understandable with common sense, without study or explanation. But nothing that actually matters does this (relational databases in the first place).

No other engineering discipline has this confusion. The world has complexity and it’s our job to understand it.

Re: Advent of Code 2024 in pure SQL

#70

Post by harrall »

ulrikrasmussen wrote: post #66
qsort wrote: post #5

Earlier quoted context omitted.

I work a lot with databases and I've seen... stuff. It's not as bad as you might think if you know what you are doing. Most RDBMSs support recursive CTEs, it feels like writing Prolog with a slightly sadistic syntax. For something like AoC the most difficult part is probably parsing the input.

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

Post reply on HN