Most of us assume that stored procedures are cached globally, but this is not the case with both MySQL and PostgreSQL 🤯 Here’s how it works…
Both MySQL and PostgreSQL cache stored procedures on a per-session basis. Thus, different connections also do not share cached procedure structures.
When we execute a stored procedure for the first time in a session, the server parses and converts the procedure body into an internal structure and caches it. In MySQL, this structure is called sp_head; while PostgreSQL creates prepared statements for each SQL command in the function.
Subsequent calls within the same session reuse this cached structure, avoiding re-parsing. This is where the benefits of using a stored procedure kick in.
So, in MySQL and PostgreSQL, if 100 connections each call the same stored procedure, each one builds and maintains its own cached copy. By the way, SQL Server and Oracle maintain global procedure caches shared across sessions.
When the session ends, the database discards all cached procedure structures associated with it. This means the first call to a stored procedure in every new session pays the parsing cost.
So, if we use a large number of short-lived connections each invoking stored procedures, we are going to be actually slower and more expensive, because of all the reparsing and caching over and over again.
Something to keep in mind the next time you use a stored procedure. Pro tip: always read the official documentation, never assume things :)