16

I have a table with hundreds of millions of rows that I need to delete data from.

The existing indexes are the most efficient.

I can however use the existing indexes to find the rows to delete by using the ctid values:

DELETE FROM calendar_event WHERE ctid IN
(SELECT ctid FROM calendar_event WHERE user_id = 5 LIMIT 100 FOR UPDATE)

What are the risks of relying on the ctid in this case? My worst case scenario is deleting the wrong the row.

Community
  • 1
ruelloehr
  • 261
  • 2
  • 3

2 Answers2

15

The ROW SHARE lock taken by FOR UPDATE prevents concurrent write access that would change the physical location of the row. The manual:

This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends;

So the ctid should be stable for the duration of the command (or the transaction, even) unless you alter the row within the same transaction yourself. ctid is still a system column for internal use and the project will not offer any guarantees. If you have any unique (combination of) column(s) (PK?) use that instead of the ctid. In fact, the ctid is not guaranteed to be unique if multiple relations can be used for the same table, like with inheritance or partitioning. See:

And see Branko's added answer.

However, use a CTE to materialize the selection and avoid unexpected results.
And without ORDER BY you select arbitrary rows for deletion. You might as well add SKIP LOCKED to minimize lock contention with concurrent transactions. See:

WITH cte AS (
   SELECT ctid
   FROM   calendar_event
   WHERE  user_id = 5
   LIMIT  100
   FOR    UPDATE SKIP LOCKED
   )
DELETE FROM calendar_event
USING  del d
WHERE  d.ctid = c.ctid

If the query might target multiple tables under the hood (inheritance, partitioning, ...):

WITH del AS (
   SELECT ctid, tableoid
   FROM   calendar_event
   WHERE  user_id = 5
   LIMIT  100
   FOR    UPDATE SKIP LOCKED
   )
DELETE FROM calendar_event c
USING  del d
WHERE  (d.ctid, d.tableoid) = (c.ctid, c.tableoid);

About ctid:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
8

There actually is a serious pitfall with using ctid for identifying rows to delete: in partitioned tables, ctid is not unique. It is only unique in a given partition so, if one uses it as a criterion for deletion, this will likely result in deleting the desired row as well as the otherwise totally unrelated rows from other partitions (ask me how I know :-/ ).

What is actually unique in partitioned tables is {tableoid, ctid}, so the proper way to do it would be something like:

delete from my_table
where (tableoid, ctid) in 
(
    select tableoid, ctid 
    from my_table 
    where <deletion_predicate>
)

(For convenience, you can substitute tableoid with tableoid::pg_catalog.regclass - this is the actual partition name.)

This will work for non-partitioned tables too, as tableoid will be the same for all rows.

Branko Radovanovic
  • 136
  • 1
  • 2