Company
Date Published
Author
Bryn Llewellyn
Word count
2905
Language
English
Hacker News points
None

Summary

The use of stored procedures in distributed SQL databases is still an emerging concept, with only two databases, YugabyteDB and Amazon Aurora, currently supporting them. However, the benefits of using stored procedures, such as correct storage and retrieval of business-critical data, robust security, and highly productive problem diagnosis and system maintainability, are unassailable when used in a monolithic SQL database. In contrast, a distributed SQL database like YugabyteDB can provide performance benefits by reducing client-server round trips for multi-statement business transactions, with the hard shell approach being faster than the bag of tables approach. The use of stored procedures also mitigates the risk of SQL injection attacks and provides security through the principle of least privilege. Additionally, the performance benefit increases with the number of SQL statements that implement a transaction. However, distributed SQL databases may have additional round trips between nodes in the cluster compared to monolithic SQL databases, which can affect performance. Overall, while the case for stored procedures is unassailable in monolithic SQL databases, its benefits are more nuanced in distributed SQL databases like YugabyteDB.