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

Databases are the endgame for data-oriented design

spacetimedb.com

Post reply on HN
Print view View original

71–80 of 157 posts

Re: Databases are the endgame for data-oriented design

#71

Post by FridgeSeal »

whstl wrote: post #63
FridgeSeal wrote: post #57

Earlier quoted context omitted.

Counterpoint: don’t do this. The path to hell is paved with good intentions. Some co-workers and I inherited a code-base where the authors went down the views and stored procedures route. It was basicallly impossible to untangle; there was no knowing what relied on a view or a proc, so you couldn’t touch them at all, there were no docs, there were duplicates of everything (and again, no way to know what’s used). A go…

If your colleagues are not applying basic engineering rules to views and stored procedures, it's not fair to say that this is a problem with views and sprocs. They can and should most definitely be testable, documentable, trackable and version controlled. Should one also judge all backend programming by PHP standards circa 1998?

I tend to judge things by the average-worst-case that they enable and how easily things go wrong, and IME stored procs specifically, go badly real quick.

Re: Databases are the endgame for data-oriented design

#72

Post by ako »

FridgeSeal wrote: post #57
ako wrote: post #47

Earlier quoted context omitted.

Use views and stored procedures if you need composability.

Counterpoint: don’t do this. The path to hell is paved with good intentions. Some co-workers and I inherited a code-base where the authors went down the views and stored procedures route. It was basicallly impossible to untangle; there was no knowing what relied on a view or a proc, so you couldn’t touch them at all, there were no docs, there were duplicates of everything (and again, no way to know what’s used). A go…

You're blaming the language for the bad coding practices of the developers. You can build maintainable database code, and modern SQL IDEs like Datagrip provide similar tools for SQL like you have for other languages, e.g., refactoring, dependency/usage info, versioning, etc. The downsides you mention are not inherent to SQL.

Re: Databases are the endgame for data-oriented design

#73

Post by gocartStatue »

deadbabe wrote: post #17
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…

Can you keep an open mind? We’ve used stored procedures for years. It has worked wonderfully for creating a single source and producer of truth for business data. Instead of potentially having business logic across multiple repos and deployments, everything exists in one place, with absolute unquestionable authority. It’s not difficult to debug at all, you might just be unskilled.

The tradeoff seems to be „ability to deploy working software without reliance on single central authority”. You may get rid of several smaller bottlenecks this way, introducing an enormous, all-encompassing one. Or am I wrong?

Re: Databases are the endgame for data-oriented design

#74

Post by whstl »

hot_gril wrote: post #32
fifilura wrote: post #4

My colleagues hate me, but I also found that SQL is The way to write business logic. Lots of caveats about difficulty to test and weird syntax. But it is just that SQL is the most terse and standard way so express logic. And that in itself is the most important factor to avoid bugs. Not what testing strategy you choose.

Same, every backend service I write now has the majority of the business logic in SQL and a little pre/post-processing in regular code. A well-designed schema will mean that your queries don't get messy. If some more complex thing starts feeling forced, I add a bit more non-SQL code to make it reasonable. When teammates look at my code, and logic is all right there instead of scattered around, and there's a schema fi…

The fear of committing SQL to the codebase is one of the most baffling things in modern backends.

To me the most infuriating thing is the "SQL query scattered around multiple files" pattern, where a backend engineer will decompose a perfectly fine SQL query into 3 or 4 files, with multiple functions, often with very artificial separations (for example: a function just for the "select ..." part, another for the joins).

All that in the name of having small files, small functions, small lines. You take complexity away from the "micro" parts and embed it into the invisible parts of your program.

Re: Databases are the endgame for data-oriented design

#75

Post by ako »

manicennui wrote: post #67
ako wrote: post #48

Earlier quoted context omitted.

And in any other language this wouldn’t result in multi-thousand lines of code with historical business logic and edge cases?

The difference is that most people don't write their business logic in a single flat function in most programming languages, and there is a good chance that individual pieces would have tests.

Neither do good SQL developers: you can easily structure database logic in multiple stored functions, stored procedures, packages, and multiple files. Unit test frameworks are also available.

Re: Databases are the endgame for data-oriented design

#76

Post by ndriscoll »

Etheryte wrote: post #5
fifilura wrote: post #4

My colleagues hate me, but I also found that SQL is The way to write business logic. Lots of caveats about difficulty to test and weird syntax. But it is just that SQL is the most terse and standard way so express logic. And that in itself is the most important factor to avoid bugs. Not what testing strategy you choose.

I feel like you've come to the right conclusion, but partly for the wrong reasons. Terseness in and of itself is not useful per se. Code golfing languages are the tersest there is, but we don't write code that way.

Or they just used the wrong word. I'd agree with them, but clarify that SQL is not just terse, but concise.

A join or a group by is going to be much clearer than writing the code the query plan is going to generate, creating temporary hash maps, doing nested loops, etc.

Re: Databases are the endgame for data-oriented design

#77

Post by da_chicken »

wvenable wrote: post #58
da_chicken wrote: post #24

Earlier quoted context omitted.

> Databases should never, ever, ever, be used to perform logic, they are datastores, that is it. I wouldn't go that far. Relational algebra is performing logic. Constraints and foreign keys are logic, as well. I'm not going to argue that you should go back in time 15-20 years and start shredding XML strings in stored procedures again. But thinking of the database as one step above a flat file is similarly backward th…

> At a concept level, that's exactly the same thing. But at a practical level they very different. If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc). I'm not hard-core against stored procedures used lightly but they have a lot of downsides and they simply aren't needed. There's no performance advantage. There…

> If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc).

I'm not sure what you're using, but you can absolutely use source control for stored procedures. There's any number of database change management tools available. You should already be using one for your schema. Stored procedure debugging tools also exist for most platforms.

I'm not really interested in the "it's not a real programming language" topic. That's almost universally someone going on an ego trip about what they like. If your point is, "We get to use a single language that the whole team is experienced and familiar with," then sure that's valuable. But that's about the team's capabilities more than anything.

> There's no performance advantage.

No, that's an outrageous claim. I've seen and implemented some processes as stored procedures and in some of those cases it's worked much better simply because we don't have to pull the data pool out of the cloud and across the country to wherever the CPU is, manipulate it, and then push it all back up to the data store.

Stored procedures are not some universal panacea that the 4GL crowd wanted them to be, but it's also not something that's universally worse.

Re: Databases are the endgame for data-oriented design

#78

Post by crazygringo »

eddd-ddde wrote: post #64
magicalhippo wrote: post #61

Earlier quoted context omitted.

We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL. Imagine the fun we're having...

I'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?

In that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is.

Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.

Re: Databases are the endgame for data-oriented design

#79

Post by Rusky »

p1necone wrote: post #62
munificent wrote: post #23

As an ex-game developer and software architecture nerd, I'm very excited about data-oriented design and ECS. It really is a cool pattern, and it's a very common one in shipping games today. It's not just architecture astronaut stuff. At the same time, the level of hype about ECS today reminds me an awful lot of the amount of hype surrounding OOP in the 90s. Can ECS be a better way to structure your game entities and…

> There are ECS frameworks in JavaScript, which gives you absolutely no control over memory layout and thus completely defeats one of the primary purposes of the pattern. It doesn't give you direct control over the memory layout, but it's still fairly safe to assume that arrays are going to end up in relatively contiguous memory, which the relevant part for the performance difference between structs of arrays and arr…

JS arrays don't store their elements directly, they store references to their elements. (Unless the elements are primitives and the engine is using NaN-boxing or pointer tagging to store those inline, or something like that, which doesn't apply to ECS components generally.)

Depending on how the GC works, the elements themselves might still wind up next to each other in memory some of the time. But that is definitely not a pattern that you would expect to hold in general- components will be added and removed over the course of the game, with lots of other stuff happening in between.

In C++, the layout of the array elements is actually part of the language semantics. In JS, the language semantics don't even have a way to talk about that layout, and engines in practice don't use the layout you want.

Re: Databases are the endgame for data-oriented design

#80

Post by ndriscoll »

buryat wrote: post #10
fifilura wrote: post #4

My colleagues hate me, but I also found that SQL is The way to write business logic. Lots of caveats about difficulty to test and weird syntax. But it is just that SQL is the most terse and standard way so express logic. And that in itself is the most important factor to avoid bugs. Not what testing strategy you choose.

that's how you end up with multi-thousand LOC sql files that contain all the historical business logic and edge cases of a company and it just keeps being piled up on and its complexity keeps growing because not everything is expressible in SQL. So you end up having a big monster that someone needs to support and one that's not very testable easily

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 to explore parts of your query to really get a sense that things are working.

Post reply on HN