When we execute an SQL query, PostgreSQL follows a process that consists of multiple steps. First, it parses the SQL statement, checking for syntax errors. Next, it analyzes whether the tables and columns used in the query exist. Then, it plans the instructions necessary to achieve the desired result. Finally, it executes the instructions based on the plan. By default, PostgreSQL parses, analyzes, and plans the query repeatedly, even if we run the same SQL query multiple times. The time needed can add up, causing unnecessary overhead.
To tackle this issue, PostgreSQL offers prepared statements. They can optimize the process by letting us parse, analyze, and plan a query beforehand. This way, the query is parsed, analyzed, and planned once and can be executed multiple times without redoing those steps.
In this article, we explain how prepared statements work in PostgreSQL and implement them both using raw SQL queries and with the Drizzle ORM.
Introducing prepared statements
Each article our database stores consists of a title, content, and creation date.
1export const articles = pgTable('articles', {
2 id: serial('id').primaryKey(),
3 title: text('title').notNull(),
4 content: text('content').notNull(),
5 createdAt: timestamp('created_at', {
6 withTimezone: true,
7 })
8 .notNull()
9 .defaultNow(),
10});Let’s write the SQL query that returns all articles created yesterday.
1SELECT * FROM articles
2WHERE created_at >= current_date - interval '1 day'
3 AND created_at < current_date;When we run our query, PostgreSQL first parses and analyzes it. Then, it creates an execution plan. To see it, we can use the EXPLAIN keyword.
1EXPLAIN
2SELECT * FROM articles
3WHERE created_at >= current_date - interval '1 day'
4 AND created_at < current_date;Creating a prepared statement with raw SQL
To create a prepared statement with raw SQL, we need to use the PREPARE keyword together with a descriptive name.
1PREPARE get_articles_from_yesterday AS
2SELECT * FROM articles
3WHERE created_at >= current_date - interval '1 day'
4AND created_at < current_date;To run our prepared statement, we need the EXECUTE keyword.
1EXECUTE get_articles_from_yesterday;When we create a prepared statement, it’s only for the current database session. If we close our session, we need to create our prepared statement again. This also means that other sessions can’t use the prepared statement we created.