public.process_queue
Description
process queue for async job
Columns
| Name | Type | Default | Nullable | Comment |
|---|---|---|---|---|
| channel | text | false | ||
| id | bigint | false | bigint primary key (upgraded from integer to avoid sequence exhaustion at sustained load). | |
| payload | text | false | ||
| processed | timestamp with time zone | true | ||
| ref_id | text | false | either user_id or vessel_id | |
| stored | timestamp with time zone | false |
Constraints
| Name | Type | Definition |
|---|---|---|
| process_queue_channel_not_null | n | NOT NULL channel |
| process_queue_id_not_null | n | NOT NULL id |
| process_queue_payload_not_null | n | NOT NULL payload |
| process_queue_pkey | PRIMARY KEY | PRIMARY KEY (id) |
| process_queue_ref_id_not_null | n | NOT NULL ref_id |
| process_queue_stored_not_null | n | NOT NULL stored |
Indexes
| Name | Definition | Comment |
|---|---|---|
| process_queue_channel_idx | CREATE INDEX process_queue_channel_idx ON public.process_queue USING btree (channel) | |
| process_queue_pending_idx | CREATE INDEX process_queue_pending_idx ON public.process_queue USING btree (channel, stored DESC) WHERE (processed IS NULL) | Optimizes: Background job queue processing (FIFO order) Query pattern: SELECT * FROM process_queue WHERE channel=X AND processed IS NULL ORDER BY stored ASC LIMIT N Key strategy: channel (partition key) + processed (status filter) + stored ASC (FIFO ordering) Partial index: Only unprocessed items (processed IS NULL) - dramatically reduces index size Size: ~24 bytes per pending item Used by: Background workers polling for jobs, task queue processors Performance: O(1) lookup for next job in channel, hot index (fully cached) Queue behavior: FIFO (First In First Out) via stored ASC ordering Why partial: Processed items are never queried again, excluding them saves ~95% index space Maintenance: Consider VACUUM to reclaim space from processed/deleted items |
| process_queue_pkey | CREATE UNIQUE INDEX process_queue_pkey ON public.process_queue USING btree (id) | |
| process_queue_processed_idx | CREATE INDEX process_queue_processed_idx ON public.process_queue USING btree (processed) | |
| process_queue_ref_id_idx | CREATE INDEX process_queue_ref_id_idx ON public.process_queue USING btree (ref_id) | |
| process_queue_stored_idx | CREATE INDEX process_queue_stored_idx ON public.process_queue USING btree (stored) |
Relations
erDiagram
"public.process_queue" {
text channel ""
bigint id "bigint primary key (upgraded from integer to avoid sequence exhaustion at sustained load)."
text payload ""
timestamp_with_time_zone processed ""
text ref_id "either user_id or vessel_id"
timestamp_with_time_zone stored ""
}
Generated by tbls