Databases are the endgame for data-oriented design • Hacker News Board

Re: Databases are the endgame for data-oriented design

#131

Post by hitpointdrew » Thu, Dec 07, 2023, 4:43 PM UTC

paulddraper wrote: post #52

hitpointdrew wrote: post #12

Nope, nope and nope. Went to the github page for spacetimedb, it does everything that is terrible. >Instead of deploying a web or game server that sits in between your clients and your database, your clients connect directly to the database and execute your application logic inside the database itself. You can write all of your permission and authorization logic right inside your module just as you would in a normal…

> It also means you have a single point of failure, no read-replicas or redundancy. Hate everything about this. How does writing an application as a single language/binary prevent read-replicas or redundancy? The application could do that. Or you could do it at disk level (RAID).

>The application could do that.

How do you manage multiple instances of the application and not introduce split brain?

> Or you could do it at disk level (RAID).

That is not comparable. RAID's redundancy is not the same as the redundancy in a multi node database cluster. You have one service, not multiple services, network card gets fried, your database goes down, you can't promote a standby to master and be on your way. Also RAID is a single disk as far as the OS is concerned, so you could hit I/O limits (especially if you have a single binary) that cause your app to chug, you cannot split your writes and reads across different physical or virtual machines that have different disks.

Re: Databases are the endgame for data-oriented design

#132

Post by munificent » Thu, Dec 07, 2023, 5:47 PM UTC

tlarkworthy wrote: post #124

munificent wrote: post #105

Earlier quoted context omitted.

You don't need ECS for that and it doesn't necessarily buy you much. Composition over inheritance can often accomplish the same goal.

Of course it buys a lot, you don't blow away program state when you change the program. That's huge for highly stateful programs. Also ecs is a prime example of composition over inheritance

ECS is anti-composition.

With composition, you say, "This object A has pieces B, C, and D. The way to get to B, C, and D is by going through A."

ECS is the exact opposite: "There are components B, C, and D. They may or may not be associated with entity A. In most cases, you shouldn't care because you should be working with B, C, and D directly and not going through A at all."

Re: Databases are the endgame for data-oriented design

#133

Post by hot_gril » Thu, Dec 07, 2023, 5:48 PM UTC

randomdata wrote: post #130

hot_gril wrote: post #34

Earlier quoted context omitted.

If you were to write the equivalent of a SQL query in some other language, you'll probably make a lot more mistakes. Especially if you're trying to achieve the same performance. And I mean a read-only query, not even something with multi-writes and locking.

I don't think that is true as I could use a better relational calculus that exhibits all the of the same positive attributes that SQL brings to the table. SQL's traps are not all fundamental, just straight up poor language design. SQL's biggest trap is where it grossly deviates from Codd's relational algebra model. A relational system that is more "pure" would solve many bug vectors right out of the gate.

What's an example of a SQL query that's misleading but wouldn't be if it stayed more like Codd's model?

The issues I have with SQL are simpler things like `timestamp with time zone` not really storing a time zone and actually being the thing you want for tz-independent timestamps.

Re: Databases are the endgame for data-oriented design

#134

Post by randomdata » Thu, Dec 07, 2023, 6:04 PM UTC

hot_gril wrote: post #133

randomdata wrote: post #130

Earlier quoted context omitted.

I don't think that is true as I could use a better relational calculus that exhibits all the of the same positive attributes that SQL brings to the table. SQL's traps are not all fundamental, just straight up poor language design. SQL's biggest trap is where it grossly deviates from Codd's relational algebra model. A relational system that is more "pure" would solve many bug vectors right out of the gate.

What's an example of a SQL query that's misleading but wouldn't be if it stayed more like Codd's model? The issues I have with SQL are simpler things like `timestamp with time zone` not really storing a time zone and actually being the thing you want for tz-independent timestamps.

I'm not sure I have a great example in front of me – the bugs get fixed. I can point out that even `SELECT * FROM table` violates the relational model. I don't expect anyone is encountering bugs from that simple query as it is easy enough to reason about, but when you compound the complexity it becomes harder to remember where SQL is non-relational and to remember to account for that.

I suppose it can be avoided with extreme care, like properly managing memory in C, but a good language will help you out. Much like C, SQL is old so those traps may be excusable in a legacy language, but it is shame that we haven't put much effort into building something more modern. Where is the 'Rust' of SQL?

Re: Databases are the endgame for data-oriented design

#135

Post by fooododododo » Thu, Dec 07, 2023, 6:24 PM UTC

whizzter wrote: post #92

ndriscoll wrote: post #80

Earlier quoted context omitted.

Testing SQL is easy. E.g. the JVM has H2, which works for simple stuff, or you can use testcontainers, or just spin up a container and run your tests against that. You just run your migrations, insert mock data, and run your test. In fact testability is one of the best parts. You can safely test read-only queries against a prod secondary database to see that it gives reasonable results on real data, and use the repl…

SQL queries yes, what Bury above probably is talking about is obscure triggers/stored-procs,etc that encodes the business logic inside the database far away from any version control or sane ways to track it.

Tracking triggers etc is pretty straightforward, just put them in a repo and apply them as part of a schema change. Testing them can be tricky, as unit tests aren't as likely to capture the locks/time that can be an issue when the database is under load. Tools like pgreplay can be helpful here.

Re: Databases are the endgame for data-oriented design

#136

Post by hot_gril » Thu, Dec 07, 2023, 8:02 PM UTC

randomdata wrote: post #134

hot_gril wrote: post #133

Earlier quoted context omitted.

What's an example of a SQL query that's misleading but wouldn't be if it stayed more like Codd's model? The issues I have with SQL are simpler things like `timestamp with time zone` not really storing a time zone and actually being the thing you want for tz-independent timestamps.

I'm not sure I have a great example in front of me – the bugs get fixed. I can point out that even `SELECT * FROM table` violates the relational model. I don't expect anyone is encountering bugs from that simple query as it is easy enough to reason about, but when you compound the complexity it becomes harder to remember where SQL is non-relational and to remember to account for that. I suppose it can be avoided with…

Closest thing to the "Rust" of SQL is GoogleSQL, in that it has a little more safety. But only a little.

Re: Databases are the endgame for data-oriented design

#137

Post by randomdata » Thu, Dec 07, 2023, 9:53 PM UTC

hot_gril wrote: post #136

randomdata wrote: post #134

Earlier quoted context omitted.

I'm not sure I have a great example in front of me – the bugs get fixed. I can point out that even `SELECT * FROM table` violates the relational model. I don't expect anyone is encountering bugs from that simple query as it is easy enough to reason about, but when you compound the complexity it becomes harder to remember where SQL is non-relational and to remember to account for that. I suppose it can be avoided with…

Closest thing to the "Rust" of SQL is GoogleSQL, in that it has a little more safety. But only a little.

I'm not sure GoogleSQL is, for all intents and purposes, anything more than just a particular SQL implementation. Notably, it still breaks from the relational model in the same way and, as such, is prone to the very same bugs of which we speak.

The closest thing to the 'Rust' of SQL is probably Datalog. Even something like QUEL, which Postgres used for the first decade of its life, would have likely left us in a much better place.

But Oracle won the database wars, so we got left with the junk that came with it – for compatibility initially, and now we can't seem to move past SQL because the typical developer somehow has come to think SQL and relational algebra/calculus are the exact same thing...

Re: Databases are the endgame for data-oriented design

#138

Post by garfij » Thu, Dec 07, 2023, 10:36 PM UTC

crazygringo wrote: post #50

chrsig wrote: post #46

Earlier quoted context omitted.

> They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology. This is basically a strawman + appeal to authority argument. I've no desire to bicker. "Uniquely terrible" is quite subjective, we'll have to agree to disagree on if it appli…

Excuse me, but I'm certainly not "bickering" and I resent the insinuation. And you don't seem to understand what a strawman argument is. I'm not going to write an article on this for you, but you can certainly see the sentiment frequently expressed on HN that the rise of NoSQL and of ORM's is a direct result of otherwise capable programmers not wanting to deal with SQL and its conceptual building blocks such as joins…

I feel like your perspective might be more narrow than you think it is.

I've been coding professionally for 20 years and at every job I've ever had we've made the decision to move away from ORMs or as much as possible given the constraints of our system in preference of directly writing SQL. My previous employer dumped Rail in favor of a Go backend (a language whose userbase almost entirely eschews the idea of ORMs) and my current employer never even had one to start with, choose to write SQL directly in a typescript backend.

I'm not going to pull the stats for you but this very website is FULL of articles talking about the benefits of writing your own SQL and I can't even remember the last time someone tried to pitch me a shiny new ORM.

> teaching people arcane terminology distinctions like between WHERE and HAVING

This is akin to complaining about needing to teach people arcane terminology distinctions between FOR and WHILE. I think it's a crime that many CS degrees I see don't include a course in database and SQL. There are of course some arcade corners of SQL (I sometimes have to look up the different JSONB operators in postgres), but WHERE and HAVING ain't it.

> flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run

Heavens, if you think using an ORM is going to help you here, have you got another think coming.

Your take on this is so foreign to me I was honestly shocked to see it. That being said, you probably aren't the only one, so perhaps my perspective is more narrow than I had assumed as well.

Re: Databases are the endgame for data-oriented design

#139

Post by hot_gril » Thu, Dec 07, 2023, 11:11 PM UTC

randomdata wrote: post #137

hot_gril wrote: post #136

Earlier quoted context omitted.

Closest thing to the "Rust" of SQL is GoogleSQL, in that it has a little more safety. But only a little.

I'm not sure GoogleSQL is, for all intents and purposes, anything more than just a particular SQL implementation. Notably, it still breaks from the relational model in the same way and, as such, is prone to the very same bugs of which we speak. The closest thing to the 'Rust' of SQL is probably Datalog. Even something like QUEL, which Postgres used for the first decade of its life, would have likely left us in a much…

SQL isn't the purest, but I've yet to see a real-life example of some other query language that gracefully handles queries that would be difficult in SQL. Looking at these other languages with an open mind, I still don't see them making things easier. If there's something better, then there should be examples out there showing a stark contrast in usability.

The only real alternative I can think of is the map-reduce pattern (see PySpark), but it's for a different use case with some overlap. I have replaced SQL queries with map-reduce once before, and only because the application really didn't make sense to use SQL for.

Re: Databases are the endgame for data-oriented design

#140

Post by randomdata » Fri, Dec 08, 2023, 12:18 AM UTC

hot_gril wrote: post #139

randomdata wrote: post #137

Earlier quoted context omitted.

I'm not sure GoogleSQL is, for all intents and purposes, anything more than just a particular SQL implementation. Notably, it still breaks from the relational model in the same way and, as such, is prone to the very same bugs of which we speak. The closest thing to the 'Rust' of SQL is probably Datalog. Even something like QUEL, which Postgres used for the first decade of its life, would have likely left us in a much…

SQL isn't the purest, but I've yet to see a real-life example of some other query language that gracefully handles queries that would be difficult in SQL. Looking at these other languages with an open mind, I still don't see them making things easier. If there's something better, then there should be examples out there showing a stark contrast in usability. The only real alternative I can think of is the map-reduce p…

> I've yet to see a real-life example of some other query language that gracefully handles queries that would be difficult in SQL.

That's exactly it. Nothing has come anywhere close to seeing the same kind of effort as SQL put into it. SQL is the best application of relational calculus (or close approximation, at least) we have – but that doesn't make it good. A concerted effort to build a better application of relational calculus would undoubtedly yield a major improvement for developers, on the order of how something like Rust has improved over C, and as I said before it is unfortunate that we as an industry are not showing interest in a better language like we do with regards to general purpose computing.

SQL gets the job done, but we can do better.

> The only real alternative I can think of is the map-reduce pattern

The alternative is to build a better language, not jump to a completely different and unrelated model. Indeed, there is a place for map-reduce for certain problems, but they are outside of where you would use the relational model.