The argument that really hits home for me, after 30+ years in this industry, is stored procedures. The “Stored Procedures are Evil” argument to me is an artifact of an industry that promotes treating engineers and infrastructure as entirely interchangeable and anything that gets in the way of that is Evil(tm). But what working at Salesforce in the 2000’s taught me is that you can do really amazing things if you’re willing to invest heavily in understanding your infrastructure and specializing the hell out of it. Of course that created Oracle lock-in for Salesforce, but that lock-in was the result of Oracle having capabilities that simply didn’t exist elsewhere that Salesforce needed to scale. I would argue Google took that same idea and 100X’d it by building the capabilities they needed when they needed them. In the case of stored procedures, I think if you find yourself fetching huge amounts of data and then doing complex manipulation to it that you can’t do with SQL, consider doing it with stored procedures in the engine and greatly simplifying your application. It may just work out!
I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.