What are stored procedures and when should you use them?

Stored procedures and functions live in the database and are invoked by name. They are useful for multi-step data operations that benefit from running close to the data, especially when they avoid many network round trips.

CREATE PROCEDURE transfer_funds(
  p_from BIGINT,
  p_to BIGINT,
  p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts
  SET balance = balance - p_amount
  WHERE id = p_from;

  UPDATE accounts
  SET balance = balance + p_amount
  WHERE id = p_to;
END;
$$;

Trade-offs

• Fewer network round trips for multi-step logic
• Centralized enforcement for operations used by several services
• Database-specific language and tooling, which reduces portability
• Harder to unit test, review, and deploy than application code
• Versioning and rollback need a migration process
• Security definer functions can bypass caller permissions if misused

Keep business rules in application code when they change often or need rich testing. Use procedures for stable, data-intensive operations where locality and atomicity matter more than portability.