Stored Procedures are not great for general programming

Last Updated on Mar 04, 2021

Stored Procedures have been part of the SQL standard since 1999, and it is generally accepted that they improve system throughput. But they have gained a bad reputation over the last few years:

  • The languages used to write stored procedures have lagged behind general-purpose languages in features and expressivity. They also lack the ecosystem of libraries to realistically build complete applications.
  • Most databases also provide a custom variant of SQL standard to fit into their ecosystem and data models. So there is very little transferability across databases supplied by different vendors.
  • TDD has formed the bedrock of stable applications over the last two decades, but code running within a database is harder to debug and tricky to test. It is also challenging to version control and deploy, and database ecosystems lack tools to measure performance.
  • Poorly written stored procedures can easily take down the entire system, as a single database instance is often shared by many application servers.

New generation databases overcome these problems by abandoning PL/SQL and using existing general-purpose languages to write Stored Procedures: VoltDB uses Java/Groovy, Datomic uses Java/Closure, Redis uses Lua. Business logic that is traditionally written as part of the application can improve throughput by avoiding I/O when moved into the database.

Some databases like VoltDB use stored procedures for replication as well: the same stored procedure is executed on each replica instead of copying data across nodes. In this case, the database needs stored procedures to be deterministic. That is, they should produce the same result when run on different nodes.

© 2022 Ambitious Systems. All Rights Reserved.