zlacker

>>hyzyla+(OP)
While you are at it, don't forget to use UNLOGGED tables. UNLOGGED == In Memory.

But if you must use disk based table for Job queueing, set fillfactor = 50. This takes care of heavy updates.

Indexes are helpful but costs memory and CPU, so always make sure you partition the table based on job_type for performant pending job query.

I wouldn't recommend using LISTEN/NOTIFY unless you are okay with "at most once" semantics. I have used disk table based approach for PUB/SUB to replace Kafka. More fine tuned approach will also allow (job_type, consumer_group, publisher) as a partition key.

Ref - https://www.postgresql.org/docs/current/sql-createtable.html

>>truth_+Rx
My understanding is, UNLOGGED means that changes are not written to the WAL and data can be lost in the event of an unscheduled shutdown. It doesn't mean that the table only exists in memory however - the data is still eventually persisted to disk.

>>osigur+pH
From the official doc link i shared already:

UNLOGGED

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 29), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.