zlacker

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_+(OP)
Thanks so much for this. I have been looking for this in Postgres. MySQL has memory back table which helped us a lot in the past.

>>truth_+(OP)
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+y9
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.

>>truth_+zb
Uh, yeah, that confirms what osigurdson said, not that they're in-memory. For that IIRC you need to mount a ram-disk in your OS and put the table on that. Definitely also make it UNLOGGED, though.

>>truth_+(OP)
I came here to post this. CREATE TABLE UNLOGGED is basically a redis in your postgres (with periodic dumps to disk), but with transactional joins into more persistent data.

>>truth_+(OP)
Unlogged tables ARE NOT in memory tables. They are written to disk like every other table, but unlogged tables don‘t have use the wal and are therefore much lighter.