When you write procedures, they often become the "heart" of your database, running a bunch of operations. But these same procedures can turn into a "bottleneck," especially if:
- They do unnecessary stuff (like hitting the same data over and over).
- They don't use indexes efficiently.
- They do way too many operations inside a single transaction.
Like one wise developer said: "Trying to speed up badly written code is like asking your lazy friend to run faster." So optimizing procedures isn't just about making things faster—it's about making the whole foundation better!
Minimizing the Number of Operations Inside a Transaction
Every transaction in PostgreSQL adds some overhead to manage its operations. The bigger the transaction, the longer it holds locks, and the more likely it is to block other users. To keep these effects in check:
- Don't cram too many operations into one transaction.
- Use
EXCEPTION ENDto locally limit changes. This is handy if only part of the work needs to be rolled back. - Break up big transactions into smaller ones (if your app's logic allows it).
Example: splitting a bulk data insert into "batches":
-- Example: Procedure for batch loading with step-by-step commits
CREATE PROCEDURE batch_load()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
batch_cnt INT := 0;
BEGIN
FOR r IN SELECT * FROM staging_table LOOP
BEGIN
INSERT INTO target_table (col1, col2) VALUES (r.col1, r.col2);
batch_cnt := batch_cnt + 1;
EXCEPTION
WHEN OTHERS THEN
-- Logging the error; changes for this item will be rolled back
INSERT INTO load_errors(msg) VALUES (SQLERRM);
END;
IF batch_cnt >= 1000 THEN
COMMIT; -- commit every 1000 operations
batch_cnt := 0;
END IF;
END LOOP;
COMMIT; -- final commit
END;
$$;
Tip: don't forget that every COMMIT saves changes, so make sure splitting the transaction won't mess up your data integrity.
Using Indexes to Speed Up Queries
Let's say you have a table called orders with a million rows, and you often query by customer_id. Without an index, the query will scan every row:
CREATE INDEX idx_customer_id ON orders(customer_id);
Now queries like this:
SELECT * FROM orders WHERE customer_id = 42;
will run way faster, skipping the full table scan.
Important: When writing procedures, make sure the fields you use are indexed, especially in filter conditions, sorts, and joins.
Performance Analysis with EXPLAIN ANALYZE
EXPLAIN shows the query execution plan (how PostgreSQL plans to run it), and ANALYZE adds real execution stats (like how long it took). Here's a typical example:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
How do you use it inside a procedure?
You can "break out" complex queries from your procedure and run them separately with EXPLAIN ANALYZE:
DO $$
BEGIN
RAISE NOTICE 'Query Plan: %',
(
SELECT query_plan
FROM pg_stat_statements
WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
);
END $$;
Example of Analysis and Improvement
Original procedure (slow):
CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
UPDATE sales
SET total = (
SELECT SUM(amount)
FROM orders
WHERE orders.sales_id = sales.id
);
END $$ LANGUAGE plpgsql;
What's happening? For every row in the sales table, it runs a subquery SUM(amount), which means a ton of operations. That's slow.
Better version:
CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
UPDATE sales as s
SET total = o.total_amount
FROM (
SELECT sales_id, SUM(amount) as total_amount
FROM orders
GROUP BY sales_id
) o
WHERE o.sales_id = s.id;
END $$ LANGUAGE plpgsql;
Now the subquery with SUM runs just once, and all the data gets updated at once.
Rolling Back Data on Errors
If something goes wrong inside a procedure, you can roll back just part of the transaction. For example:
BEGIN
-- Insert data
INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
WHEN OTHERS THEN
-- This block is like rolling back to an internal savepoint!
RAISE WARNING 'Error updating data: %', SQLERRM;
END;
Practice: Building a Robust Order Processing Procedure
Let's say your task is to process an order. If something goes wrong (like not enough product in stock), the order is canceled and the error is logged.
CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
v_in_stock INT;
BEGIN
-- Check stock
SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;
BEGIN
IF v_in_stock < 1 THEN
RAISE EXCEPTION 'No product in stock';
END IF;
UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
-- ... other operations
EXCEPTION
WHEN OTHERS THEN
-- All changes in this block are rolled back!
INSERT INTO order_logs(order_id, log_message)
VALUES (p_order_id, 'Order processing error: ' || SQLERRM);
RAISE NOTICE 'Order processing error: %', SQLERRM;
END;
-- The rest of the code continues if there were no errors
-- You can log: order processed successfully
END;
$$;- Even if there's an error, the order won't be processed, and a log will show up in the
order_logstable. - If there's an error, the internal savepoint kicks in, so you don't lose all your context.
Key Rules for Optimizing and Making Procedures Robust
- Use indexes for queries inside procedures.
- Break big operations into small batches, process step by step.
- Be able to log errors—make a separate table for logging errors from bulk operations.
- For "partial" rollbacks, only use nested blocks with
EXCEPTION. - Don't use
ROLLBACK TO SAVEPOINTinside PL/pgSQL—it'll throw a syntax error. - In procedures, use COMMIT/SAVEPOINT only when called in autocommit mode!
- Analyze the execution plan of heavy queries (
EXPLAIN ANALYZE) outside of procedures, before integrating them.
GO TO FULL VERSION