Skip to content

README.md

Latest commit

 

History

History
116 lines (82 loc) · 3.2 KB

README.md

File metadata and controls

116 lines (82 loc) · 3.2 KB

Tobias

Test Gem Version

Tobias is your friendly PostgreSQL DBA who is obsessed with optimizing your database.

Named after a nerdy but friendly DBA who is obsessed with query performance, Tobias helps you find the optimal work_mem setting for your PostgreSQL queries. It runs your queries with various memory settings to determine the minimum work_mem needed to keep your queries entirely in memory without creating temporary files.

Prerequisites

You'll need the huggingface CLI. On macOS you can install it via Homebrew:

$ brew install huggingface-cli

Please note that Hugging Face is only being used for fetching example datasets. No part of this project uses AI models.

Installation

$ gem install tobias

How It Works

Tobias tests your queries against a range of work_mem settings (from 64kB to 8GB) and determines the minimum memory required to avoid temporary file spill-over. This helps you:

  • Optimize query performance by keeping operations in memory
  • Right-size your work_mem setting per query or workload
  • Avoid over-allocating memory while preventing disk spill

Usage

Basic Usage

$ tobias profile path/to/your/script.rb --database-url <database-url>

Options

  • --database-url: PostgreSQL connection string (required)
  • --debug: Enable debug logging (default: false)

Creating Query Scripts

Create a Ruby script that defines your queries using the query method:

# my_queries.rb

query(:complex_aggregation) do
  from(:orders).
    join(:order_items, order_id: :id).
    join(:products, id: :product_id).
    where(created_at: Date.today.beginning_of_month..Date.today).
    group(:category).
    select(
      :category,
      Sequel.function(:sum, :quantity).as(:total_quantity),
      Sequel.function(:avg, :price).as(:avg_price)
    ).
    order(:total_quantity)
end

query(:heavy_join) do
  from(:customers).
    join(:orders, customer_id: :id).
    join(:order_items, order_id: Sequel[:orders][:id]).
    where(Sequel[:customers][:created_at] > Date.today - 365).
    select(
      Sequel[:customers][:email],
      Sequel.function(:count, Sequel[:orders][:id]).as(:order_count),
      Sequel.function(:sum, Sequel[:order_items][:quantity]).as(:total_items)
    ).
    group(Sequel[:customers][:id], Sequel[:customers][:email])
end

Each query block should contain Sequel ORM code that builds and returns a dataset.

Example Run

$ tobias profile scripts/tpcc.rb --database-url postgres://localhost/tpcc_test

Example Script

See scripts/tpcc.rb for a complete example that tests TPC-C benchmark queries.

Running Tests Locally

Install act.

$ brew install act

Run the build.

$ act --container-architecture linux/amd64

Requirements

  • Ruby 3.3+
  • PostgreSQL database with pg_stat_database access
  • Your database user needs permissions to:
    • Execute SET work_mem
    • Read from pg_stat_database
    • Execute pg_stat_reset()