Percona Live 2017 Open Source Database Conference

April 24 - 27, 2017

Santa Clara, California

Stored Procedures are Good Enough

Stored Procedures are Good Enough

 25 April - 3:50 PM - 4:40 PM @ Room 203
Experience level: 
50 minutes conference
Tools and Techniques


Traditionally, many mobile and web developers consider using stored procedures as a very old and obvious anti-pattern. But time goes on, Open Source (R)DBMSs become stronger, and it is a good moment to look at stored procedures and triggers at different angle. There are certain cases when server programming right inside the DBMS might be much better rather than developing code and deploing it to the "application" servers. Sometimes it might be even necessary if you want to achieve better characteristics for your project. I'm going to show you that cases where using stored procedures and triggers is good enough, are numerous. From data validation to integration tasks, direct access to all knowledge that database has, combined with amazing capabilities that it provides, this approach can save you time, efforts and significantly improve various aspects of your app and its development process. We'll compare pros and cons of using stored procedures taking PostgreSQL as an example of DBMS, with its well-known support of numerous procedure languages, such as PL/PgSQL, PL/Python, PL/R, and PL/Proxy. The overview of "good cases" showing when stored procedures can be really helpful will be provided, including the following cases: - data validation and data flow control; - auth: user permissions control; - processing of massive data sets (work with data in sharded databases, MapReduce, etc); - integration tasks; - machine learning with data stored in RDBMS; - rapid REST API development. To maintain the balance, the list of "bad cases" will also be given, showing cases when it is better to avoid using stored procedures at all. We'll also talk about the most important topics that relate to any app development: - performance, - security, - debugging, - version control and deployment.


Nikolay Samokhvalov's picture

Nikolay Samokhvalov

-, -



Share this talk