What is a stored procedure, and what are its pros/cons vs. application-layer logic?

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;

    IF (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
END;
$$;

CALL transfer_funds(1, 2, 100);

Advantages

• Fewer network round-trips. Multiple statements execute as one call instead of several separate queries from the application, each paying network latency — meaningful for logic with many small, dependent steps.
• Logic co-located with data. Business rules enforced in a procedure apply no matter which application, script, or ad-hoc tool touches the database — you can't accidentally bypass validation by writing to the table through a different code path.
• Reduced data transfer. Complex calculations happen where the data already lives, rather than pulling large intermediate result sets to the application tier just to compute something and write it back.
• Precompiled execution plan (in some engines) — repeated calls can reuse a cached plan rather than re-parsing/re-planning every time, though modern query engines also cache plans for parameterized ad-hoc queries, narrowing this advantage.

Disadvantages

• Weaker tooling for versioning/testing. Application code benefits from mature version control diffing, unit testing frameworks, code review tooling, and CI pipelines; stored procedure logic often lives partially outside that ecosystem unless deliberately integrated (migration-based deployment, dedicated SQL test frameworks).
• Vendor lock-in. Procedural SQL dialects (PL/pgSQL, T-SQL, PL/SQL) are not portable across engines — logic written for PostgreSQL doesn't run on SQL Server without a rewrite, unlike application code written in a general-purpose language.
• Split logic, harder to reason about. A developer reading application code may not realize significant business logic actually lives in the database, making the system harder to understand holistically and harder to debug with standard application debugging tools.
• Scaling application logic independently of the database becomes harder — application-tier logic can scale horizontally across many stateless app servers; database-tier logic is bottlenecked by the database's own compute capacity.

Most modern application architectures favor keeping business logic in the application layer, reserving stored procedures for narrow cases where their advantages are decisive: enforcing an invariant that absolutely must never be bypassed regardless of caller, or a genuinely data-intensive operation where minimizing round-trips/data transfer matters more than tooling/portability concerns.