How to Do UPDATE … LIMIT in PostgreSQL (With Standard SQL Alternatives)

If you have ever migrated a workload from MySQL to PostgreSQL, you have probably hit a wall the first time you tried to run a statement like UPDATE mytable SET status = 'done' LIMIT 100;. In MySQL this is perfectly valid, but PostgreSQL rejects it with a syntax error. Learning how to do UPDATE ... LIMIT in PostgreSQL is a rite of passage for anyone moving to the world's most advanced open source database, and the good news is that the standard SQL approach is arguably cleaner, safer, and more predictable than the MySQL shortcut it replaces. In this guide to UPDATE LIMIT in PostgreSQL we will explain why PostgreSQL does not support UPDATE ... LIMIT, why the limitation actually protects you from subtle bugs, and how to achieve exactly the same result using portable, standard-compliant SQL. Along the way we will cover batched updates, deterministic ordering, locking behavior, and performance considerations so that your SQL UPDATE statements remain correct even under heavy concurrency.

Why PostgreSQL Does Not Support UPDATE ... LIMIT

MySQL allows you to attach a LIMIT clause directly to an UPDATE or DELETE statement. It is convenient, but it hides a fundamental ambiguity: without an explicit ORDER BY, which rows are actually modified? In MySQL the answer depends on the storage engine, the physical order of rows, and the execution plan, none of which are guaranteed to be stable over time. When it comes to UPDATE LIMIT in PostgreSQL, PostgreSQL takes a stricter, more principled stance. The SQL standard does not define LIMIT as part of the UPDATE statement, and PostgreSQL follows the standard closely, as documented in the official PostgreSQL UPDATE documentation. An UPDATE in PostgreSQL is a set-based operation: it applies to every row that matches the WHERE clause, with no notion of "stop after N rows." This design decision keeps the semantics of your data modification statements unambiguous. If you want to restrict the number of rows affected, you must express that intent explicitly, and that explicitness is exactly what makes the standard SQL alternative so robust. The takeaway is simple: the absence of UPDATE ... LIMIT in PostgreSQL is not a missing feature, it is a deliberate design choice that pushes you toward writing safer queries. Understanding this is the first step to implementing UPDATE LIMIT in PostgreSQL the right way.

The Standard SQL Way: UPDATE With a Subquery

The canonical technique for UPDATE LIMIT in PostgreSQL is to combine UPDATE with a subquery that selects the primary keys of the rows you want to change. The subquery does the ORDER BY and LIMIT work, and the outer UPDATE touches only the rows returned by that subquery. To demonstrate UPDATE LIMIT in PostgreSQL, suppose we have a jobs table and we want to mark the 100 oldest pending jobs as processing. Here is how you would write it:

UPDATE jobs
SET status = 'processing'
WHERE id IN (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100
);

This statement is the direct equivalent of MySQL's UPDATE jobs SET status = 'processing' WHERE status = 'pending' ORDER BY created_at LIMIT 100, but it is fully standard SQL and it works on virtually every relational database. The inner query returns exactly 100 primary key values, and the outer UPDATE modifies only those rows. Notice that we included an explicit ORDER BY created_at. This is not optional if you care about which rows get updated. Without it, the LIMIT would pick an arbitrary set of 100 pending jobs, and the result could differ between executions. Deterministic ordering is the single most important detail when replicating UPDATE LIMIT in PostgreSQL.

Using a CTE for Cleaner UPDATE ... LIMIT Logic

A Common Table Expression, or CTE, often makes UPDATE LIMIT in PostgreSQL even clearer, especially when the selection logic is complex. PostgreSQL fully supports writable CTEs and using them to drive an UPDATE reads very naturally:

WITH candidates AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100
)
UPDATE jobs
SET status = 'processing'
FROM candidates
WHERE jobs.id = candidates.id;

Here the candidates CTE isolates the row-selection logic, and the UPDATE ... FROM clause joins the target table to those candidates. Functionally this is identical to the subquery version, but many developers find it easier to read and maintain. The FROM candidates join is also frequently more efficient than a large IN (...) list because the planner can treat it as a join rather than a membership test. Both patterns are excellent solutions for the SQL UPDATE limiting problem and are the recommended way to do UPDATE LIMIT in PostgreSQL. Choose the subquery form for short, simple limits and the CTE form when the candidate selection involves multiple conditions, joins, or window functions.

Locking Rows Safely With FOR UPDATE and SKIP LOCKED

The UPDATE LIMIT in PostgreSQL examples so far work perfectly in a single-session context. But in production, especially in job-queue and worker-pool designs, multiple processes often compete to update the same rows at the same time. If two workers both run the subquery and both pick the same 100 rows, they will fight over the same locks and effectively serialize, or worse, process duplicate work. PostgreSQL solves this elegantly with FOR UPDATE SKIP LOCKED, a locking clause explained in the official SELECT documentation. When you add these clauses to the inner SELECT, each worker locks the rows it selects and skips any rows already locked by another transaction. This turns a naive queue into a highly concurrent, contention-free work distributor:

WITH candidates AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'processing'
FROM candidates
WHERE jobs.id = candidates.id
RETURNING jobs.id, jobs.status;

The FOR UPDATE SKIP LOCKED combination is one of PostgreSQL's most powerful features for building reliable queues. Each worker grabs a distinct batch of rows, never blocking on rows another worker already claimed. The RETURNING clause is a bonus that lets you retrieve the affected rows in the same round trip, which is invaluable when the worker needs to know exactly what it just picked up. This pattern is the reason many teams no longer reach for dedicated message brokers for moderate workloads. A single PostgreSQL table with FOR UPDATE SKIP LOCKED can serve as a surprisingly capable job queue.

How to Do DELETE ... LIMIT in PostgreSQL

Just like UPDATE LIMIT in PostgreSQL, the same limitation applies to DELETE: PostgreSQL does not accept DELETE ... LIMIT. The workaround is identical in spirit. You select the primary keys to remove in a subquery or CTE and delete only those rows. This is extremely common when you need to purge old data in controlled batches without locking the entire table:

DELETE FROM jobs
WHERE id IN (
SELECT id
FROM jobs
WHERE status = 'done'
AND finished_at < now() - interval '30 days'
ORDER BY finished_at
LIMIT 1000
);

Batched deletes, a close cousin of UPDATE LIMIT in PostgreSQL, are the recommended way to clean up large tables. Deleting millions of rows in a single statement can bloat the write-ahead log, hold locks for a long time, and delay autovacuum. Deleting in bounded chunks keeps transactions short and your system responsive.

Processing an Entire Table in Batches

One of the most common reasons people search for how to do UPDATE LIMIT in PostgreSQL is that they need to update a very large table without holding a single massive lock or generating an enormous transaction. A giant single-statement update can run for hours, block other queries, and consume large amounts of memory and WAL. The solution is to loop over the table in batches. You can drive the batching loop from application code, but it is often convenient to do it entirely inside PostgreSQL using a DO block with PL/pgSQL. The following example updates every row in the jobs table in chunks of 5,000, committing implicitly between iterations if you run it as separate transactions, or in one transaction as shown here:

DO $$
DECLARE
rows_affected INTEGER;
BEGIN
LOOP
UPDATE jobs
SET normalized = lower(payload)
WHERE id IN (
SELECT id
FROM jobs
WHERE normalized IS NULL
LIMIT 5000
);

GET DIAGNOSTICS rows_affected = ROW_COUNT;
EXIT WHEN rows_affected = 0;

RAISE NOTICE 'Updated % rows', rows_affected;
END LOOP;
END $$;

This loop keeps updating batches of 5,000 rows until no rows remain that match the condition. The GET DIAGNOSTICS statement captures how many rows the last UPDATE touched, and the loop exits when that count reaches zero. Because each iteration only affects a bounded number of rows, locks are held briefly and the table stays available to other queries. For truly enormous tables where even a single long-running transaction is undesirable, run each batch as its own transaction from your application layer, or use a procedure with explicit COMMIT statements. Committing between batches lets autovacuum reclaim dead tuples as you go, preventing table bloat during long migrations. For deeper tuning guidance, see our other PostgreSQL performance articles.

Keying Batches on an Indexed Column

When you scale UPDATE LIMIT in PostgreSQL to huge tables, the batching examples above rely on a WHERE condition that shrinks as rows are processed, such as normalized IS NULL. When there is no such self-limiting predicate, you should page through the table using an indexed key, typically the primary key. This avoids scanning the same rows repeatedly and keeps every batch fast:

UPDATE jobs
SET status = 'archived'
WHERE id IN (
SELECT id
FROM jobs
WHERE id > 10000
ORDER BY id
LIMIT 5000
)
RETURNING id;

By remembering the highest id returned by each batch and feeding it back into the next iteration's WHERE id > :last_id condition, you achieve efficient keyset pagination. This is far superior to OFFSET-based pagination, which becomes progressively slower as the offset grows because PostgreSQL must still read and discard all the skipped rows.

Performance Considerations for UPDATE LIMIT in PostgreSQL

Correctly implementing UPDATE LIMIT in PostgreSQL means getting the syntax right is only half the battle. To make your limited updates fast and safe, keep the following points in mind. Index the columns in your WHERE and ORDER BY clauses. The inner subquery that selects your candidate rows should be able to use an index for both filtering and ordering. Without an appropriate index, PostgreSQL must scan and sort large portions of the table on every batch, turning an O(N) operation into something much worse. In our job-queue example, an index on (status, created_at) lets the planner find the oldest pending jobs almost instantly. Always use a deterministic ORDER BY for UPDATE LIMIT in PostgreSQL. If two rows have identical values in your ordering column, add a tiebreaker such as the primary key so that the order is fully deterministic. Ambiguous ordering combined with LIMIT reintroduces exactly the non-determinism that made MySQL's UPDATE ... LIMIT risky in the first place. Keep batches reasonably sized. Very small batches add round-trip and transaction overhead, while very large batches hold locks longer and generate more WAL. A batch size between 1,000 and 10,000 rows is a good starting point for most workloads, but you should benchmark against your own data and hardware. Watch out for table bloat. Every UPDATE in PostgreSQL creates a new row version and marks the old one dead, because of its MVCC design. Large update campaigns produce a lot of dead tuples. Committing between batches and ensuring autovacuum is tuned aggressively will keep bloat under control.

A Complete Job-Queue Example

Let us tie our approach to UPDATE LIMIT in PostgreSQL together with a realistic, production-ready pattern. Imagine a worker that claims a batch of pending jobs, processes them, and marks them complete. The claim step uses everything we have discussed: a CTE, deterministic ordering, a limit, row locking with SKIP LOCKED, and RETURNING to hand the claimed rows back to the application.

WITH claimed AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at
LIMIT 50
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'processing',
claimed_at = now(),
worker_id = 'worker-7'
FROM claimed c
WHERE j.id = c.id
RETURNING j.id, j.payload;

Each call to this statement atomically claims up to 50 of the highest-priority pending jobs that no other worker has locked, stamps them with the current time and worker identity, and returns their payloads so the worker can begin processing immediately. Multiple workers can run this concurrently with essentially zero contention, which is exactly the behavior you want from a scalable queue.

Frequently Asked Questions

Does PostgreSQL have any plan to add native UPDATE LIMIT in PostgreSQL? There is no such feature in current PostgreSQL releases, and it is unlikely to be added because it conflicts with the SQL standard's set-based semantics. The subquery and CTE patterns are the officially recommended approach. Is the UPDATE LIMIT in PostgreSQL subquery approach slower than MySQL's native LIMIT? Not meaningfully. With proper indexes on the filter and ordering columns, the subquery is resolved quickly and only the selected primary keys drive the update. In many cases the explicit, index-backed approach is faster and far more predictable than relying on physical row order. Can I use ctid instead of the primary key? You can, and it can be marginally faster because ctid is the physical row locator. However, ctid values change when a row is updated or the table is vacuumed and rewritten, so use it only within a single short transaction and never store it. The primary key is almost always the better, safer choice.

Further Reading and References on UPDATE LIMIT in PostgreSQL

The patterns described above are backed by the official PostgreSQL documentation and by decades of production experience. If you want to dig deeper into how UPDATE LIMIT in PostgreSQL is best expressed with standard SQL, the following authoritative resources are well worth reading:

• The PostgreSQL UPDATE statement reference describes the exact grammar the parser accepts, confirming that no LIMIT clause is permitted.
• The LIMIT and OFFSET documentation explains why deterministic ordering matters whenever you limit a result set.
• The explicit locking guide covers FOR UPDATE and SKIP LOCKED semantics used in the concurrent job-queue example.

Bookmarking these references will help you reason about UPDATE LIMIT in PostgreSQL correctly the next time you need to bound the number of rows a statement touches.

Conclusion

PostgreSQL does not offer UPDATE ... LIMIT the way MySQL does, but that is a feature, not a bug. Every technique for UPDATE LIMIT in PostgreSQL flows from that design. By forcing you to express row selection explicitly through a subquery or a CTE with a deterministic ORDER BY, PostgreSQL helps you write updates whose behavior is clear, repeatable, and portable across databases. Add FOR UPDATE SKIP LOCKED when concurrency matters, process large tables in indexed batches to avoid long locks and bloat, and always back your filtering and ordering with the right indexes. Master these UPDATE LIMIT in PostgreSQL patterns and you will never miss UPDATE ... LIMIT again. The standard SQL alternatives for UPDATE LIMIT in PostgreSQL are more powerful, safer under concurrency, and a genuine upgrade over the MySQL syntax you left behind.