The lost art of database optimisation with Stored Procedures

This database optimization technique has been lost in the sands of time.

When cache deployments were hard and RAM expensive, developers had to devise creative ways to optimize their apps.

One recurring problem is atomic updates: When a user likes a post, we must update the like count of the post and simultaneously add a user entry to the likes table.

These operations have difficult failure and edge cases, requiring a mechanism like 2PC or Paxos to succeed.

They also require multiple round trips from server to database, increasing network load and response latency.

Then comes the idea, why not perform these operations together inside the database?

---

STORED PROCEDURES

Atomicity, consistency, and other transaction guarantees are easier to manage in the database.

By performing the operations together, we also avoid the network calls and aggregation costs (removing duplicates and filtering) we would otherwise have to do on the server.

1. Comparing flat network calls with Stored Procedures

Here are some of the benefits:

1. Data fetching complexity is encapsulated
2. Operations can be run atomically, without external 2PC
3. Consistency guarantees are easy to enforce
4. Result Aggregation costs are eliminated
5. Fewer Round trips are made

However, stored procedures have drawbacks. Developers HATED coding complex business logic in SQL. They also had to debug requests partly on the server and then on the database.

Some of the major problems with stored procedures are:

1. Applications must know about existing stored procedures
2. Change in business logic leads to change in DB
3. Difficult to debug DB errors
4. DB Migration causes breaking changes

This is probably why most NoSQL databases do not support stored procedures, and interest in the topic has declined over the last two decades by 90%.

2. Interest in stored procedures as per Google Trends

But you never know: Technology has a way of going full circle.

---

If you want to know more about database optimization techniques and network protocols, try our system design course at InterviewReady!

Cheers!