Module: PGMQ::Client::Autovacuum

Included in:
PGMQ::Client
Defined in:
lib/pgmq/client/autovacuum.rb

Overview

Autovacuum and storage tuning for queue and archive tables.

PGMQ tables churn in a way PostgreSQL’s defaults are not tuned for. A hot queue inserts, updates, and deletes rows constantly: every read UPDATEs vt, read_ct, and last_read_at, and every read+archive cycle deletes from the queue table and inserts into the archive. Two defaults hurt under that load:

  • autovacuum_vacuum_scale_factor defaults to 0.2, so autovacuum only runs after dead tuples reach 20% of the table - by which point a busy queue has bloated its heap and B-tree indexes, slowing every read and lock.

  • fillfactor defaults to 100, so heap pages fill completely. Because vt is indexed and changes on every read, those UPDATEs are not HOT-eligible; leaving page headroom reduces page density between vacuum passes.

This module applies per-table storage parameters via ALTER TABLE so autovacuum runs far more often (and fillfactor reserves churn headroom) on these specific tables, without touching cluster-wide settings. It is intentionally opt-in: the gem is a thin wrapper and does not mutate table storage parameters unless you ask it to (either by calling #tune_autovacuum or by passing tune_autovacuum: true to a queue-creation method).

See Also:

Constant Summary collapse

DEFAULT_QUEUE_SETTINGS =

Default storage parameters for the active queue table. Aggressive: autovacuum and autoanalyze trigger at 1%/5% dead-or-changed tuples, a small cost_delay keeps each vacuum pass quick, and fillfactor reserves 30% of each page for the per-read UPDATE churn (the queue table is the only one that benefits from fillfactor).

{
  autovacuum_vacuum_scale_factor: 0.01,
  autovacuum_vacuum_threshold: 50,
  autovacuum_vacuum_cost_delay: 2,
  autovacuum_analyze_scale_factor: 0.05,
  fillfactor: 70
}.freeze
DEFAULT_ARCHIVE_SETTINGS =

Default storage parameters for the archive table. Archives are append-heavy with periodic purge, so a looser scale factor and a slightly higher cost delay are enough while still beating the 0.2 default. No fillfactor: archive rows are inserted once and never updated, so there is no page-churn headroom to reserve.

{
  autovacuum_vacuum_scale_factor: 0.05,
  autovacuum_vacuum_threshold: 50,
  autovacuum_vacuum_cost_delay: 5,
  autovacuum_analyze_scale_factor: 0.05
}.freeze

Instance Method Summary collapse

Instance Method Details

#tune_autovacuum(queue_name, queue_settings: {}, archive: true, archive_settings: {}) ⇒ void

Note:

On a partitioned queue or archive, the parameters are set on the parent table. PostgreSQL does not cascade storage parameters to existing partitions, so pre-existing partitions keep their own settings; set them per-partition if needed.

This method returns an undefined value.

Tunes autovacuum and storage parameters on a queue’s underlying tables.

Applies DEFAULT_QUEUE_SETTINGS to the queue table (+pgmq.q_<name>+) and, unless archive: false, DEFAULT_ARCHIVE_SETTINGS to the archive table (+pgmq.a_<name>+). Pass queue_settings: / archive_settings: to override or extend the parameters per table; the Hash you pass is merged onto the defaults, so you only name the keys you want to change. Any other PostgreSQL storage default is left untouched. Safe to call repeatedly - ALTER TABLE … SET is idempotent for a given value.

Examples:

Apply PGMQ-tuned defaults to an existing queue

client.tune_autovacuum("orders")

Override a couple of queue params, skip the archive

client.tune_autovacuum("orders", queue_settings: { autovacuum_vacuum_scale_factor: 0.005, fillfactor: 80 },
                       archive: false)

Parameters:

  • queue_name (String) —

    name of the queue

  • queue_settings (Hash{Symbol=>Numeric}) (defaults to: {}) —

    storage params for the queue table, merged onto DEFAULT_QUEUE_SETTINGS

  • archive (Boolean) (defaults to: true) —

    also tune the archive table (default: true)

  • archive_settings (Hash{Symbol=>Numeric}) (defaults to: {}) —

    storage params for the archive table, merged onto DEFAULT_ARCHIVE_SETTINGS

Raises:



80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/pgmq/client/autovacuum.rb', line 80

def tune_autovacuum(queue_name, queue_settings: {}, archive: true, archive_settings: {})
  validate_queue_name!(queue_name)

  with_connection do |conn|
    tune_autovacuum_on(
      conn,
      queue_name,
      queue_settings: queue_settings,
      archive: archive,
      archive_settings: archive_settings
    )
  end

  nil
end