Impact of index on a "status" field with one (guaranteed) change

Introduction

I have a PostgreSQL table setup as a queue/event-source.

I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for e2e testing.

I starting to run into query performance slow-downs (probably because of table bloat) and I don't know how to effectively query a table on a changing key.
Initial Setup

Postgres: v15
Table DDL

CREATE TABLE eventsource.events (
    id serial4 NOT NULL,
    message jsonb NOT NULL,
    status varchar(50) NOT NULL,
    createdOn timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT events_pkey PRIMARY KEY (id)
);
CREATE INDEX ON eventsource.events (createdOn)

Scrape Query (Pseudo Code)

BEGIN;  -- Start transaction

SELECT message, status
FROM eventsource.events ee
WHERE status = 'PENDING'
ORDER BY ee.createdOn ASC
FOR UPDATE SKIP LOCKED
LIMIT 10;  -- Get the OLDEST 10 events that are pending
-- I found that having a batch of work items was more performant than taking 1 at a time.

...
-- The application then uses the entries as tickets for doing work as in "I am working on these 10 items, no one else touch"
...
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_1
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_2
UPDATE ONLY eventsource.events SET status = 'FAIL' WHERE id = $id_3
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_n
...
END; -- finish transaction

Rough Worker outline

Multiple workers taking batches of work items form the queue then actioning them and reporting their statuses.
I want to have as little overlap as possible.

Assessment

When looking at the execution plan it looks like the query has to traverse the entire table to get the records that are in 'PENDING' status.

I thought this might be because of the ORDER BY ee.createdOn ASC at first. But after reviewing the execution plan I saw that the query was traversing the entire table searching for the status, and only TH

Don't use timestamp (without time zone)

Your whole setup is prone to failure:

CREATE TABLE eventsource.events (
    ...
    createdOn timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -- !
    ..

CURRENT_TIMESTAMP (a.k.a. now()) returns timestamptz, not timestamp.

If by chance, accident or malicious intent any session ever sets a different timezone and then inserts a row relying on the column default, you get a different (wrong) local time, breaking sort order. And you'd have a hard time finding out why. Don't do this. Especially not with such a column default. (LOCALTIMESTAMP runs into the same problem: also depends on the current timezone setting.)

Related:

• Difference between now() and current_timestamp

• Ignoring time zones altogether in Rails and PostgreSQL

Better table definition

CREATE TABLE eventsource.event (
  event_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, message     jsonb NOT NULL
, status      text NOT NULL CHECK (status = ANY ('{PENDING,DONE,FAIL}'::text[]))  -- more?
, created_on  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP  -- !!!
);

Use legal, lower-case identifiers if at all possible. See:

• Are PostgreSQL column names case-sensitive?

Use text and add a CHECK constraint to enforce legal states.

• Should I add an arbitrary length limit to VARCHAR columns?

IDENTITY is preferable over serial in modern Postgres. See:

• Auto increment table column

• Explicitly granting permissions to update the sequence for a serial column necessary?

Most importantly, use timestamptz as explained at the top. All other points are mere recommendations.
Better index

Use a partial index, as already suggested by Charlieface:

CREATE INDEX ON eventsource.event (created)
WHERE status = 'PENDING';

It's radically smaller for your use case and provides sorted rows. The small index is also cheaper to maintain. However, there will be a lot of churn, so the index will bloat quickly. See:

• When do partial indexes get updated in PostgreSQL?

Consider aggressive autovacuum settings for the table. Like:

ALTER TABLE eventsource.event SET (autovacuum_vacuum_scale_factor = 0.03);

The global default for autovacuum_vacuum_scale_factor is 0.2. Meaning, autovacuum is triggered after 20 % of the table rows + autovacuum_vacuum_threshold (50 by default) have been changed. If the table is big, that may be too lazy for your purpose. Find your balance between increased maintenance cost and improved query performance.

You may or may not need a full index on (created_on) additionally for other purposes.
Better approach

Assuming:

• Current Postgres 15.

• There can be concurrent writes (and/or concurrent locks).

• You want to process the oldest row that has not been processed, yet. (And is not being processed concurrently by another session.)

• The application process succeeds in the majority of cases.

BEGIN;  -- !!!

UPDATE eventsource.event
SET    status = 'DONE' 
WHERE  event_id = (
         SELECT event_id
         FROM   eventsource.event
         WHERE  status = 'PENDING'
         ORDER  BY created_on
         LIMIT  1
         FOR    UPDATE SKIP LOCKED  -- !!!
         )
RETURNING *;  -- or just what you need!

-- The application then processes the entries returned by the query and will then update them

-- ONLY in case of a failure !!!
-- Else just skip this:
UPDATE eventsource.event
SET    status = 'FAIL' 
WHERE  event_id = $id_3;  -- your failed ID

COMMIT;

This approach works reliably under concurrent write load, and never blocks.
It only locks a single row per session, minimizing chances for complications. It locks and updates the row immediately, which is faster than locking and updating later. In the rare case of a failure, you need a second update. But that's cheap in comparison.

If you need to (or just want to) lock and process multiple rows, that works in similar fashion. See:

• Postgres UPDATE ... LIMIT 1

StackExchange Database Administrators Q#326920, answer score: 5