Post History

There are a few reasons for wanting to move computation closer to data. One is performance, which you've mentioned. Another is security. Databases enforce their own security boundary, and data that don't leave the database are data that can't be exfiltrated if your application layer is exploited somehow but the database itself is still only accessible via the application layer credentials. A third reason is that it may increase the modularity of your overall system. If your data are being replicated in a complex way, and your application servers are also running multiple versions of your application at once, it may be desirable to standardize on an interface between databases and application layer that is more sophisticated than ‘access all the tables directly’. Said interface can be implemented with views and stored procedures, and then those entities would be versioned and replicated along with the data, independently of how your application layer is versioned and deployed, so data-layer restores from replica can happen in the event of a bad schema change without disrupting the application layer (because, of course, you would roll out the schema change before rolling out the new application layer components that require any new interface exposed). I don't expect this list is exhaustive.

Much like performance, how important these other reasons are to you will vary a lot based on context. You will always be trading complexity off against capability, and there won't be one right answer for all projects.

But in the abstract, I consider moving computation closer to the data it uses to be generally a good idea—the negatives of using stored procedures are IMO all due to the relatively poor overall developer experience of deploying code to database servers versus deploying code to application servers, and that's just a point-in-time state-of-the-art thing (albeit one that has lasted for a rather long time so far).