Prepared Statements in PostgreSQL: A Complete Guide

Posted on by Ian

Prepared statements are a feature in PostgreSQL that can improve performance and security when executing repetitive SQL queries.

By creating a prepared statement, the server can parse and plan the query once and reuse it multiple times, leading to faster execution. Prepared statements also help in preventing SQL injection attacks by safely handling user input.

In this article, we’ll explore prepared statements in PostgreSQL, discuss how to create and use them, and cover other important considerations such as query planning and cleaning up statements.

What is a Prepared Statement?

In PostgreSQL, a prepared statement is a precompiled SQL statement that you can execute multiple times with different arguments. It is prepared once and then it can be executed repeatedly, reducing the overhead of query parsing and planning.

Prepared statements can be used for the following statements:

Prepared statements can be especially useful when you have queries that you run multiple times with varying inputs.

Prepared statements are only available to the current session, and they only last for duration of the current database session. Therefore, multiple sessions cannot share the same prepared statement – each session needs to create its own.

Benefits of Prepared Statements

Prepared statements can be beneficial in certain scenarios. In particular, here are some of the main benefits:

  • Improved Performance: The query is parsed and planned only once, and subsequent executions use the same execution plan, making it faster.
  • Security: Prepared statements help protect against SQL injection by handling user inputs safely.
  • Consistency: The structure of the query remains constant, reducing the risk of query errors due to dynamic query building.

Creating a Prepared Statement

To create a prepared statement, use the PREPARE command. This defines the SQL query and placeholders for parameters that will be provided at execution time. Here’s the syntax:

PREPARE statement_name (parameter_type [, ...]) AS query;
  • statement_name: The name you assign to the prepared statement.
  • parameter_type: The data type(s) of the parameters the query will accept.
  • query: The actual SQL query, with placeholders ($1, $2, etc.) representing parameters.

Example: Create a Prepared Statement

Let’s say we want to repeatedly retrieve user details based on their user_id. We can create a prepared statement like this:

PREPARE get_user_by_id (int) AS
SELECT user_id, username, email FROM users WHERE user_id = $1;

In this example:

  • get_user_by_id is the name of the prepared statement.
  • It takes one parameter of type int, representing the user_id.
  • The query retrieves the user_id, username, and email fields from the users table.

Executing a Prepared Statement

Once a prepared statement is defined, you can execute it using the EXECUTE command. When executing, you pass in the actual values for the parameters.

Example: Execute the Prepared Statement

To execute the get_user_by_id statement with a specific user_id, say 101, you would run:

EXECUTE get_user_by_id(101);

This retrieves the user details where user_id = 101. You can execute this statement multiple times with different parameter values:

EXECUTE get_user_by_id(102);

That retrieves the user details for user 102.

This works because the underlying SELECT statement remains the same, only the user_id changes.

Analysing a Prepared Statement

To inspect the plan for a prepared statement, you can use the EXPLAIN command, which shows the query execution plan of the query. You can combine it with the EXECUTE statement to analyze how the query will run:

EXPLAIN EXECUTE get_user_by_id(101);

This will output the planned execution plan for retrieving the user with user_id = 101, allowing you to analyze query performance and optimize if needed.

You can also use the ANALYZE option to cause the query to actually be executed:

EXPLAIN ANALYZE EXECUTE get_user_by_id(101);

Bear in mind that, while the query is actually executed when using this option, it returns the query plan rather than the results of the query.

Viewing Current Prepared Statements

You can use the pg_prepared_statements view to return a list of all prepared statements available to the current session:

SELECT * FROM pg_prepared_statements;

New rows are added when a new prepared statement is created and removed when a prepared statement is released (for example with DEALLOCATE or when the session ends).

DEALLOCATE: Cleaning Up Prepared Statements

Prepared statements are retained in memory until they are explicitly deallocated, or the session ends. If you’re done with a prepared statement and want to free up resources, you can use the DEALLOCATE command.

Syntax

The syntax for cleaning up a prepared statement goes like this:

DEALLOCATE [ PREPARE ] { name | ALL }

If you specify ALL, all prepared statements for the current session will be deallocated. Otherwise you can specify the name of a specific prepared statement to deallocate. The PREPARE keyword is optional.

Example: Deallocate a Prepared Statement

To deallocate the get_user_by_id statement:

DEALLOCATE get_user_by_id;

This removes the prepared statement from memory, and it cannot be executed anymore unless redefined.

Understanding Query Plans: Generic vs Custom

When a prepared statement is created, PostgreSQL has two possible strategies for executing it: use a generic plan or use a custom plan.

Generic Plan

  • Generic plans are created without considering the specific parameter values and are reused for subsequent executions of the prepared statement.
  • Generic plans avoid the overhead of generating a new query plan each time, but they may be less efficient than custom plans in certain cases.

Custom Plan

  • Custom plans are generated based on the actual parameter values provided when the query is executed.
  • Custom plans are optimal for queries where the performance heavily depends on specific parameter values. However, the query plan needs to be generated on every execution, which adds some overhead.

Default Behavior

By default, PostgreSQL uses a hybrid approach:

  • For the first five executions of a prepared statement, PostgreSQL uses a custom plan.
  • After the fifth execution, if PostgreSQL determines that a generic plan would be faster overall (by comparing it to the average custom-plan cost), it switches to the generic plan for future executions.

This balances the trade-off between the efficiency of using a generic plan and the optimization that a custom plan can provide for specific parameter values.

The default behavior is used when plan_cache_mode is set to auto (which is the default setting). You can change this by setting it to either force_generic_plan or force_custom_plan, which will force Postgres to use the specified plan instead.

If PostgreSQL settles on the generic plan even though you find that it’s not the most efficient, then you might want to force it to use a custom plan. This could happen if the optimal plan depends largely on the parameter values.

When to Use Prepared Statements?

Prepared statements are beneficial in several scenarios, but they are not always the best choice. Here’s when you should consider using them:

Use prepared statements when:

  • You need to execute the same query multiple times with different parameters.
  • Performance is a concern, and you want to avoid the overhead of parsing and planning the same query multiple times.
  • Security is a priority, especially when working with user inputs that could otherwise lead to SQL injection.
  • You are working with repetitive tasks in a session, such as batch processing or repeated querying.

Avoid prepared statements when:

  • The query is only executed once or a few times. In such cases, the overhead of preparing the statement may outweigh the benefits.
  • The same queries are shared across different sessions. Prepared statements can only be accessed from the current session, so trying to create one for multiple sessions won’t work.

Key Takeaways

Here are the main points in this article:

  • PREPARE to define a reusable query with placeholders.
  • EXECUTE to run the prepared statement with actual parameters.
  • EXPLAIN EXECUTE to analyze the execution plan of a prepared statement.
  • DEALLOCATE to remove a prepared statement and free up memory.
  • Understand the difference between generic and custom query plans and how PostgreSQL automatically switches between them to optimize performance.

Summary

Prepared statements in PostgreSQL can be useful for optimizing query execution and ensuring security, especially in applications where queries are run repeatedly with different parameters. By understanding the trade-offs between generic and custom query plans, you can fine-tune performance for your specific use cases.

Remember to clean up prepared statements using DEALLOCATE when they are no longer needed to free up server resources.