Hacker News Reader

Not sure I follow in the context of the post. The post is about a database that can be wiped and effectively recreated from scratch. There wouldn't really be a need for rollback scripts when you can just rerun the entire workflow from scratch. Even easier if you have a hot/warm/cold setup.

In this sort of setup, changing a db function isn't unlike making a change to db-interfacing code in that it's not changing the underlying data, just the interface to it.

I've built similar systems that instead of making changes to db functions, changes are made to materialized views. The underlying data doesn't change, but its presentation does. No huge need for rollbacks. It works really well!

No that's a fair critique, if your application is dealing with a large amount of pretty darn static data and "refreshing" the database is as simple as switching to a backup, you could make the argument it's a lot more attractive.

I just think that most applications need stuff to be ACID and deal with a lot of writes (or may want to deal with some of those things later) and make this a bad idea.

If they are made to views, you have the problem of how do you roll out/back those changes. You could obviously make that configuration on the server side, but then you're still needing to roll out both the service's configuration changes and the database, when if you put all the logic in the service, you only need to handle that for the service.

I don't mind abstracting the views into another mental layer, but it's a harder sell to convince me that reduces a ton of risk because doing operations on the database (even if you have a perfect permission setup) is risky, because you can, by definition, impact that machine that's serving live customers.

It works well until you've got an issue, and then you're completely fucked.

I think the sense of the comment was "how can I deploy or test anything once it goes in production". You know, were starting again from scratch is not an option ever.

Using the database as a service layer is dumb for non-trivial scenarios were you need garantees that your service keeps running, or when you want to test new code with the production data.

It's interesting, and probably useful to have a set of functions with the database, but there is real problems once your database stops being a dumb store.

How would you be completely fucked when the rollback is to just rerun the workflow? Worst case scenario here is downtime, which is a far stretch from "completely fucked". It very much sounds like the databases that you work with and the databases that the article writer works with are used for two very, very different purposes. The author made no mentions about SLO-type requirements, so I think it's fair to say that SLO isn't a huge priority. But that can be mitigated, like I said, with a hot/warm/cold standby.

Do you think it's a bad idea to use something like PostGIS because it uses functions that live on the database?

"when you want to test new code with the production data". Take a snapshot and start a new instance from that snapshot.

For years I've wanted to try the idea in the article, particularly for a data intensive but not high scale project. It's never been worth the hassle though.

Still, thinking about it has led me to see how we are not really leveraging the capabilities of feature rich RDMSes like Portgres. A judicious use of user defined functions can help, particularly for polyglot companies/microservices where the database can ensure certain routines are standardized across all apps.

Presumably you can revert to a previous tag/label in source control and reload the previous procs/funcs to roll back versions.

The apps I have been working on are monoliths which run at about 2% CPU on the server, so there is no A/B comparison runs in production (e.g. on January 1, some law goes into effect and you WILL implement it). I would happily see ORM usage curb stomped.

> If I make the change in code, I can roll it out, test it in prod, and then slowly migrate traffic over in very standard and well defined ways.

Advanced methods (like blue/green deploys) are invented by people with high-traffic requirements and slowly filter down to everyone else until they are normal practice. 15 years ago server configurations were ordinarily painstakingly handcrafted and somewhat unique.

We can reimagine those techniques around a database without too much difficulty.

> You can have a bunch of servers, you really only can have one database. This means the database should be very sensitive to changes because it's a single point of failure. I don't like modifying or changing single points of failure without very good and well tested reasons.

This is a strong argument; I agree that very good reasons are required. I have seen scenarios where the reasons are IMO quite good, but agree this isn't a widely-applicable technique.

> Could you version your procs and have the new version of the code call the new procs? Sure, but now you have to manage deployment of both a service, and the database, and have to handle rollover and/or A/B for both. If my logic is in there service, I only have to worry about rolling back the service.

It has become common to have (and version/rollback/etc) each of web-frontend, web-backend code, web-backend engine, database schema, database engine.

Cutting that back to web-frontend, database http plugin, database schema & database engine is IMO a plausible win some of the time.

I've been noodling around for awhile with the concept of a precompiler for stored procedures which alters their names to include a hash of their code.

This would give you a safe way to seat multiple versions alongside one another.

* The frontend build process would pull in the hashes so it knew which version to call * A cron-job could delete the old ones once they're (manually marked?) no longer in use - this would be the most hairy bit since it's got to be 100% sure it's not in use anymore.

Rolling back then constitutes putting the old version of the frontend live so it'll refer to the previous stored procedures.

I’m struggling with a lot of things in this. Why couldn’t you test your stored procedures? Assuming you use an ORM, do you test it’s logic? Because if you don’t, then what’s the difference?

Why wouldn’t you have more than one database? What if you’re global?

Why would a database ever be a single point of failure?

Why wouldn’t you be capable of rolling it back, or test in anyway that’s different from what you do with your other code? Is it perhaps because you don’t know the tools to do so?

Why would database logic save you pain in development? Typically utilising stores procedures would make development less straightforward because you split your logic up, but you do it because database servers are a lot better and a lot more efficient at giving you the data you need.

Don’t get me wrong. I only use stored procedures, or even any SQL tooling, when they are the only way to get efficient or persistent data, but it’s not because or any of the reasons you list. It’s because 95% or the developers we hire don’t really know how to use databases.

A good example is an issue one of juniors had recently. We have a lot of data organised in what is essentially a tree structure where the nodes only know their parents. He needed to pick any node and gets its children + related rows from a range of tables in three databases. The task itself was simple enough, except the tree has millions and millions of nodes and even more millions of related rows, and he needed the data fast. He had hacked around, trying different stuff like loading it gradually, caching it between uses and so on, but he couldn’t get it fast enough. We made a recursive stored procedure that was capable of giving him the data almost instantly, because thats what database servers do. In a couple of years, when this needs tweaking, however, it’s very likely that someone like me will be needed to look at it, because recursive SQL isn’t something a lot of our developers do, so in any other case, doing it in C# would’ve been better.

> Assuming you use an ORM, do you test it’s logic? Because if you don’t, then what’s the difference?

You don't test the ORM, you test your models. If you're writing db procesures you'd test the procedures, not the db's procedure engine.

But yeah, you should be doing your testing in a test database already. The only problem I see is that you might have a tighter coupling between db schema version and software version with procedures (although then again, you could avoid that if you're careful and make it less coupled)

Recursion in SQL is one of the questions I always ask Data Engineers during interviews. The answer in MS T-SQL is to use a recursive CTE. It is not a common problem, but it comes up once in a while. Using a CTE is a better solution than calling an SP recursively because you can only nest 32 calls deep. In the MS Azure SQL Data Warehouse product you are simply fucked if you need to do recursion.

> SP recursively because you can only nest 32 calls deep

T-SQL only gives you 100 calls deep. Also, having CTEs calling other CTEs can create a very slow query plan, which takes unrolling each CTE into either a table variable or temp table to resolve.

> Recursion in SQL is one of the questions I always ask Data Engineers during interviews

Everyone always asks that in every language at every interview and it is rarely used in practice - ahhh interviews.

I think it depends on the developers style of how they reason. I use recursion a lot when dealing with loose data, to clean it up. Recursion is one of my go to's for deduplicating list.

They way I've seen this done is you version the namespace of the stored procedures (and include the git commit). So at any given time you're definitely only calling the exact function you want.

Reminds me of JDSL in a way: http://thedailywtf.com/articles/the-inner-json-effect

There's a trend of versioned databases that could address these issues. I've seen at least Dolt [1] for relational:

"Dolt is a data format and a database. The Dolt database provides a command line interface and a MySQL compatible Server for reading and writing data."

... and TerminusDB [2] for graphs:

"TerminusDB is an open-source knowledge graph database that provides reliable, private & efficient revision control & collaboration."

I don't know if Dolt supports stored procedures, but in any case versioning a database would be an amazingly powerful and convenient thing, if it can be done without sacrificing too much DB performance.

Worth investigating and has the potential of changing the way we write apps.

--

1: https://www.dolthub.com/docs/tutorials/installation/

2: https://terminusdb.com/

There can be many databases. They can be running in parallel with the same SQL inputs. You can have provisions for this in your middle ware, but there is also stuff for this in the db replication. Replaying SQL logs on test instances. It is possible to run quite sophisticated testing in this sort of setup.

Relying on ORM doesn't make a difference, since you are still sending DDL commands to a live database.

Using stored procedures, views, and triggers makes it easier to add abstraction between your middle ware and the database structure.

But on the whole, I agree it's probably not recommendable to anyone feeling hesitant about it.

I fully agree and treat my databases the same way. However, there’s no reason a stored procedure couldn’t be treated the same way with a random weight determining which version of a stored procedure is run.

But I still wouldn’t.

Put your functions in a schema e.g. v1, v2 etc. You get same thing you have with code. To test on prod data you take a snapshot and start an instance from a snapshot.

as a bonus your whole deployment is now just running a single sql script no fancy tools needed. You scale out by adding sync read replicas and segregating mutations to master and reads to replicas. You can autoscale by taking snapshot and spinning up a replica and spinning it down when no longer needed.

I have taken various runs at stored procedure versioning and agree with you. The database is the ultimate sacred resource. Change things at your peril and test like crazy.