Three Pitfalls in Scheduled PostgreSQL Jobs

SQL that runs on a schedule rarely gets watched. It wakes up once an hour, aggregates a batch of rows and writes the totals somewhere else. Most of the time the numbers look fine, and when something goes wrong there is often no error at all, just figures that drift. During a design review of one system I was adding tests to an hourly rollup job, and three problems came out of it. None of them is hard on its own, but buried in a file of perfectly ordinary-looking SQL they are easy to miss. The examples below use simplified tables and were run on PostgreSQL 16.

Two tables to start with, one for raw bets and one for hourly totals:

CREATE TABLE bets (
  id         bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  amount     numeric NOT NULL
);

CREATE TABLE rollup_hourly (
  bucket timestamptz PRIMARY KEY,
  n      bigint,
  total  numeric
);

INSERT INTO bets (created_at, amount) VALUES ('2026-09-01 10:30:00+00', 100);

1. Comparing timestamp with timestamptz pulls in the session time zone

Each run has to work out which hour it is aggregating, and a common way to do that is to convert the current time to UTC and date_trunc it. The catch is that timestamptz AT TIME ZONE 'UTC' returns a timestamp without time zone. When you compare that value with a timestamptz column, PostgreSQL casts it back using the session’s TimeZone setting, and the window moves with it.

SET TimeZone = 'Asia/Taipei';

WITH w AS (
  SELECT date_trunc('hour', timestamptz '2026-09-01 10:30:00+00' AT TIME ZONE 'UTC') AS ws
)
SELECT pg_typeof(w.ws) AS ws_type,
       (SELECT count(*) FROM bets
         WHERE created_at >= w.ws
           AND created_at <  w.ws + interval '1 hour') AS hits
FROM w;

           ws_type           | hits
-----------------------------+------
 timestamp without time zone |    0

Run the same query after SET TimeZone = 'UTC' and hits is 1. The job meant to cover 10:00 to 11:00 UTC. In a Taipei session it actually compared against 10:00 to 11:00 Taipei time, eight hours earlier, and the bet fell outside the window. Developer laptops and CI usually run in UTC, so the tests pass. The difference only shows up once a connection defaults to Asia/Taipei, or someone reruns the job by hand from psql.

I have spent a fair amount of time telling engineers that the timestamp in their hands may not be the timestamp they think it is, and that UTC conversion often behaves differently from what they expect. On a UTC host you rarely notice. Move to a host in another zone, or follow the data all the way out to the client, and the problems start to pile up. AT TIME ZONE is a good example. The name suggests a conversion to UTC, yet the value it returns has no zone attached, and how that value is read later depends on whatever the current session is set to.

The fix is to keep the window boundaries as timestamptz the whole way through. The quickest version adds a second AT TIME ZONE 'UTC' to convert back:

WHERE created_at >= w.ws AT TIME ZONE 'UTC'
  AND created_at <  (w.ws + interval '1 hour') AT TIME ZONE 'UTC'

Since PostgreSQL 12, date_trunc also accepts a time zone argument. date_trunc('hour', now(), 'UTC') returns a timestamptz directly, so a zone-less value never appears in the middle, and the query is harder to misread.

2. A DELETE inside a CTE is invisible to the INSERT in the same statement

A rollup should be safe to rerun, and the usual approach is to delete the bucket’s old totals and insert fresh ones. Written as a single statement it looks reasonable:

WITH del AS (
  DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00'
)
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;

If the bucket already holds a row, it fails:

ERROR:  duplicate key value violates unique constraint "rollup_hourly_pkey"
DETAIL:  Key (bucket)=(2026-09-01 10:00:00+00) already exists.

The PostgreSQL documentation is explicit about this. Data-modifying statements in WITH run concurrently with the main query against the same snapshot, so none of them can see what the others did to the tables. The row that del removes is still there as far as the INSERT’s uniqueness check is concerned. The statement only succeeds when the bucket starts out empty, which is exactly the case a rerun does not cover.

Splitting it into two statements inside one transaction fixes it:

BEGIN;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

The second statement sees the result of the first, the whole thing is still atomic, and a failure halfway rolls both back. In that system a worker reads db/jobs/*.sql and runs each file inside a single transaction, so the split needed no extra handling.

3. A lone pg_try_advisory_xact_lock does not stop overlapping runs

Scheduled runs can overlap. The previous run may still be going when the next one starts, or two workers wake up at once. The obvious guard is to grab an advisory lock at the top of the file:

BEGIN;
SELECT pg_try_advisory_xact_lock(42) AS got;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

With another session already holding lock 42, running this gives:

 got
-----
 f

DELETE 1
INSERT 0 1
COMMIT

The lock was not acquired and both statements ran anyway. pg_try_advisory_xact_lock simply returns a boolean. A plain SQL file has no if, nothing reads that false, and it ends up as one more row of query output.

What works is moving the lock attempt into each statement’s own condition, so a statement that fails to get the lock does nothing:

BEGIN;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
DELETE FROM rollup_hourly r
USING scope
WHERE r.bucket = scope.ws;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
INSERT INTO rollup_hourly
SELECT scope.ws, count(*), sum(amount)
FROM bets, scope
WHERE created_at >= scope.ws
  AND created_at <  scope.ws + interval '1 hour'
GROUP BY scope.ws;

COMMIT;

With the other session still holding the lock, the result is DELETE 0 and INSERT 0 0, and no data is touched. Asking for the lock twice is fine, because re-acquiring an advisory lock you already hold in the same transaction returns true. Both statements get the same answer, so they run together or skip together, and the old totals are never deleted without the new ones going in.

The downside is that a skipped run raises no error. To find out whether a run actually did anything, you have to check the affected row counts or record it separately. If you would rather have overlapping runs queue up behind each other, use the blocking pg_advisory_xact_lock instead. In that case a lock on the first line does work, since it waits until it gets the lock.

One more: a partitioned parent takes no storage parameters

This came up in the same review. Setting fillfactor on the parent of a partitioned table is rejected outright:

CREATE TABLE p (id int, d date) PARTITION BY RANGE (d) WITH (fillfactor = 90);

ERROR:  cannot specify storage parameters for a partitioned table
HINT:  Specify storage parameters for its leaf partitions instead.

fillfactor has to be set on each partition, and you need to remember it whenever a new partition is created, or that partition falls back to the default.

Notes

The timestamp in your hands may not be the one you think it is, and UTC conversion often behaves differently from what you expect. You rarely see it on a UTC host, and it bites on hosts in other zones or once the data reaches the client.
timestamptz AT TIME ZONE 'UTC' returns a timestamp, and comparing it with a timestamptz applies the session time zone. Keep window boundaries as timestamptz throughout, or use the zone-aware date_trunc available since PostgreSQL 12.
A DELETE in a WITH clause shares a snapshot with the main query, so an INSERT in the same statement cannot see the deletion. For a rerunnable job, use two statements in one transaction.
A standalone pg_try_advisory_xact_lock just returns a boolean, and plain SQL has no control flow. Put it in each statement’s WHERE. Re-acquiring it in the same transaction returns true, so the statements stay consistent.
A run skipped by a try-lock raises no error, so check row counts or log it. If you want runs to queue, use the blocking pg_advisory_xact_lock.
Storage parameters such as fillfactor can only be set on leaf partitions.

Three Pitfalls in Scheduled PostgreSQL Jobs