PostgreSQL vs. MySQL: Syntax, Stored Routines, and Performance

When you compare PostgreSQL and MySQL or move a database from one to the other, SQL statements that look alike can follow different rules for routines, transactions, and query results. A stored routine is SQL code saved in the database and called by a client or another database statement. PostgreSQL and MySQL support routines, but their rules for routines and transactions differ.

For a 2026 comparison, use PostgreSQL 18 and MySQL 9.7 as the version context, and verify which releases your hosting provider supports. The practical choice depends on how your code uses SQL features and what your workload needs. There is no general performance winner supported by a controlled, version-matched comparison. The sections below show where syntax and behavior diverge, what to check when moving code, and how to benchmark the engines fairly.

Functions and procedures serve different roles

Both databases let developers define functions and procedures. The difference affects how a client calls each routine and how the routine returns data.

In PostgreSQL, a function declares a return type and can appear inside a query. It can return a single value or a set of rows. A procedure is called with CALL; it does not have a function return value, though it can pass values back through output parameters. PostgreSQL documents these distinctions in its references for CREATE FUNCTION and user-defined procedures.

MySQL functions return one value and can be used within SQL statements. Procedures are called with CALL and can return result sets, which are rows produced by a query. When you move a PostgreSQL function that returns rows to MySQL, you may need to redesign it as a procedure or use a different way to run the query. MySQL describes these routine forms in its stored routine documentation.

Developers also write and deploy routine bodies differently in the two databases. PostgreSQL commonly uses a PL/pgSQL block inside a dollar-quoted body:

CREATE FUNCTION add_tax(subtotal numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN subtotal * 1.08;
END;
$function$;

Dollar quoting marks both ends of the routine body and lets it contain single quotes without escaping them, as a conventional quoted string would require. PL/pgSQL uses BEGIN and END to mark a code block; those words do not start or finish a database transaction. See PostgreSQL’s explanation of PL/pgSQL block structure.

MySQL also uses BEGIN ... END for a multi-statement routine. In the mysql command-line client, developers commonly change the client’s statement delimiter while defining the routine, so the semicolons inside the body do not prematurely end the CREATE statement:

DELIMITER //

CREATE FUNCTION add_tax(subtotal DECIMAL(12,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN subtotal * 1.08;
END//

DELIMITER ;

The MySQL command-line client understands DELIMITER. It is not an SQL statement, so do not send it unchanged through every database driver. MySQL documents compound blocks in its BEGIN... END reference. Treat these examples as syntax illustrations; production routines also need deliberate decisions about precision, null values, and error handling.

Transaction control depends on the routine and caller

When you migrate routines, check how each one handles transactions.

A PostgreSQL function cannot commit or roll back the transaction that called it. An eligible procedure can execute COMMIT or ROLLBACK when the caller invokes it through an allowed top-level CALL or permitted chain of calls. If the caller places that CALL inside an explicit transaction block, the procedure cannot use transaction control. PostgreSQL describes the restrictions in its PL/pgSQL transaction rules.

MySQL allows transaction statements in stored procedures, but stored functions cannot perform explicit transaction control. So if a routine commits work, changing its syntax alone will not make the translation safe. Check whether the routine is a function or procedure, how the client calls it, and whether the surrounding code already starts a transaction. MySQL’s procedure and function reference documents the routine statements and restrictions.

Upserts and returned identifiers need deliberate translation

An upsert inserts a row or responds when its key already exists. Both databases support upserts, but they use different syntax and may return different values.

PostgreSQL uses ON CONFLICT to specify what happens when an insert collides with a unique or primary-key constraint. Its INSERT statement can also use RETURNING to return values from inserted or updated rows, including a generated identifier. See the PostgreSQL INSERT reference.

MySQL uses ON DUPLICATE KEY UPDATE to update a row when an insert conflicts with a primary or unique key. Code that needs the value of an AUTO_INCREMENT identifier can use LAST_INSERT_ID(). These features serve related needs. When you migrate, test which conflicts trigger an update and the exact value the client receives. Do not assume that replacing one clause with the other preserves every edge case.

PostgreSQL also folds unquoted identifiers to lowercase, while double-quoted identifiers preserve case and become case-sensitive. A schema that relies on mixed-case names can therefore surprise developers who move it or change how they quote identifiers. PostgreSQL explains these rules in its lexical structure reference. For MySQL, check the target server’s SQL mode and identifier conventions instead of assuming PostgreSQL’s rules apply.

Isolation defaults change what transactions see

A transaction groups database statements that the database commits or rolls back together. An isolation level sets which changes one transaction can see while other transactions update data. The default levels differ: PostgreSQL defaults to READ COMMITTED, while InnoDB, MySQL’s main transactional storage engine, defaults to REPEATABLE READ.

Under PostgreSQL’s default, each statement sees data committed before that statement began. Two reads in one transaction can therefore see different committed values if another transaction updates the row between them. PostgreSQL treats a request for READ UNCOMMITTED as READ COMMITTED; its client connection defaults document the setting.

Under REPEATABLE READ, InnoDB's ordinary reads, which do not lock rows, use a snapshot set by the transaction's first consistent read. This snapshot is the view of the data that the transaction sees. Depending on the query and indexes, InnoDB can also lock individual records or ranges of index values during locking reads and writes. MySQL describes these behaviors in its InnoDB isolation-level documentation.

These defaults affect both correctness and benchmark results. For a fair performance test, use the same isolation levels and durability settings on both databases. Then measure how each handles the transactions your code requires. PostgreSQL’s SERIALIZABLE level can abort a transaction when concurrent work would produce a result inconsistent with serial execution, so applications using it need a way to retry aborted transactions.

Performance depends on the workload

Neither product wins every workload. Query speed depends on the data, indexes, types and frequency of transactions, concurrent activity, configuration, hardware, and hosting environment. The reviewed version documentation describes product changes, but it does not establish a universal PostgreSQL-versus-MySQL speed ranking.

PostgreSQL 18 documents changes to asynchronous input/output, hash joins, and GROUP BY queries. It also adds B-tree skip scans, which can help some queries that use an index on several columns but do not filter on the first column in that index. The release notes document version-specific capabilities, which do not guarantee faster performance for a particular application. Review the PostgreSQL 18 release notes and test the queries that matter to your service.

To compare the databases, run the same set of typical queries and transactions against each one. Match the versions, data, schema, indexes, and types. Also match transaction frequency and isolation level, along with the hardware and connection path. Warm up each database, repeat the runs, and report both throughput and latency. A benchmark that leaves each database at its default isolation level measures two different transaction behaviors along with their performance.

Indexes and JSON affect schema design

PostgreSQL offers several index methods. B-tree uses a tree structure; the other methods are hash, GiST, SP-GiST, GIN, and BRIN. It also supports partial indexes, which cover only rows matching a condition, and expression indexes, which index a computed value. These indexes can help when queries repeatedly check only rows that meet a condition or search by a calculated value. They also use storage and add work when the database writes data. PostgreSQL describes its index methods and features.

MySQL’s index behavior depends on the storage engine and index type. InnoDB commonly uses B-tree indexes. In an index on several columns, the order matters because queries generally benefit most from the first columns listed. MySQL explains how it uses indexes. When moving a schema, rebuild indexes around the destination’s query patterns rather than copying definitions mechanically.

Both databases support JSON data, but their indexing approaches differ. PostgreSQL offers json and jsonb types, operators for querying documents, and GIN indexes for jsonb. The useful index configuration depends on the operators the queries use; PostgreSQL covers those details in its JSON functions and operators reference.

MySQL validates documents stored in its native JSON type and stores them in an optimized internal format. To index one value inside a JSON document, developers commonly use a generated column derived from the document, then create an index on that column. InnoDB also supports multivalued indexes for JSON arrays. See MySQL’s JSON data type documentation. If JSON queries affect performance, compare the exact query expressions and index definitions. The fact that both databases support JSON does not mean they handle those queries the same way.

Choose by behavior, then verify speed

Start by listing the SQL features your code uses. Record its routines and where transactions start and end. Also note its upserts and generated identifiers, along with its JSON paths and indexes. For each feature, match its behavior in the destination database. Pay particular attention to routines that return rows or control transactions. Then test the destination database with representative data and concurrency.

Choose PostgreSQL when its specific capabilities, such as its index options or PL/pgSQL behavior, fit the workload. Choose MySQL when its routine, InnoDB, JSON, or existing schema behavior fits better. In either case, measure the queries and transaction patterns the service actually runs before making a speed claim.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com