Hanami 2 and PostgreSQL: A Performance Guide for ROM Relations

Good evening. You have chosen the road less documented.

Hanami 2 is the most thoughtfully designed Ruby web framework nobody is writing performance guides for. It uses ROM-rb as its persistence layer, which uses Sequel under the hood for SQL databases. This gives you three layers of abstraction — Hanami's repos, ROM's relations, Sequel's datasets — each with its own performance characteristics, and exactly zero blog posts explaining how they interact under load.

This is a gap worth filling. ROM-rb's architecture is fundamentally different from ActiveRecord. There is no lazy loading. There are no callbacks firing behind your back. Relations are explicit. Data loading is opt-in. These design choices eliminate entire categories of performance problems — and introduce a few new ones that are specific to ROM's mental model.

I should be candid about why this guide exists at all. Hanami 2's adoption is modest. The community is small but remarkably thoughtful. The documentation is correct but sparse, and performance guidance is essentially nonexistent outside of reading ROM-rb's source code — which, to be fair, is well-organized and worth reading, but is a rather bracing way to learn the performance characteristics of your persistence layer.

I have spent considerable time with Hanami 2 and PostgreSQL in production configurations, and I have formed opinions about where the performance lives in this stack. They are strong opinions, but I hold them with appropriate humility. Shall we proceed.

ROM's architecture: three layers, one database

Before we optimize anything, we need to understand what is actually happening when a Hanami 2 application talks to PostgreSQL. The stack has three distinct layers, and confusion about which layer is responsible for what is the source of most performance mistakes I encounter in Hanami applications.

Relations define your database tables and their associations. They are the closest thing ROM has to models, but they are not models — they are query interfaces. A relation represents a dataset, not an object. This distinction matters. An ActiveRecord model is a row. A ROM relation is a query that may return zero, one, or many rows. The mental model is fundamentally different, and the performance implications follow from this difference.

# app/relations/orders.rb
module Main
  module Relations
    class Orders < Main::DB::Relation
      schema(:orders, infer: true) do
        associations do
          belongs_to :customer
          has_many :order_items
        end
      end

      # Dataset method — drops to Sequel's query builder
      def pending
        where(status: "pending")
      end

      def recent(days: 30)
        where { created_at > Date.today - days }
      end

      # Combine with other relations for eager loading
      def with_customer
        combine(:customer)
      end

      def with_items
        combine(:order_items)
      end
    end
  end
end

Notice what is absent. No callbacks. No validations. No before_save hooks firing in unpredictable orders. No default_scope silently appending conditions to every query. A relation is a pure query interface — it describes what data you want, and ROM translates that description into SQL. Nothing happens behind your back. This is a feature, not a limitation.

Repos are the public API for data access. They use relations internally but expose domain-oriented methods to the rest of the application. This is where eager loading, projection, and pagination decisions are made.

# app/repos/order_repo.rb
module Main
  module Repos
    class OrderRepo < Main::Repo
      # Structs — lightweight read objects, no ActiveRecord overhead
      struct_namespace Main::Structs

      # Basic queries through the relation
      def pending_orders
        orders.pending.to_a
      end

      # Eager loading with combine — 2 queries, not N+1
      def pending_with_customers
        orders.pending.combine(:customer).to_a
      end

      # Deep eager loading — 3 queries total
      def pending_full
        orders
          .pending
          .combine(customer: :tier, order_items: :product)
          .to_a
      end

      # Aggregate — embeds children inside parent structs
      def orders_with_nested_items
        aggregate(:order_items).to_a
        # Each order struct has an .order_items array
        # 2 queries: orders, then order_items WHERE order_id IN (...)
      end

      private

      def orders
        container["relations.orders"]
      end
    end
  end
end

The repo layer is where performance decisions crystallize. Every repo method is a commitment — you are declaring exactly which data you need, how it should be loaded, and what shape it should take. This feels verbose compared to ActiveRecord's implicit loading. It is. The verbosity is the documentation. Six months from now, you can read a repo method and know exactly what SQL it produces without running it.

Datasets are Sequel's query builder — the actual SQL-generating layer beneath ROM. When you need PostgreSQL-specific features, raw SQL, or EXPLAIN ANALYZE output, you drop to the dataset level.

This layering matters for performance because each layer has different costs. Relations and repos add minimal overhead — they are largely pass-through. The expensive decisions are which associations to load, which columns to project, and when to drop to Sequel's dataset for PostgreSQL-specific operations.

One observation I find instructive: in a well-structured Hanami application, the repo layer acts as a natural chokepoint for all database access. Every query goes through a repo method. This means every query is named, documented by its existence, and testable in isolation. ActiveRecord scatters query logic across controllers, models, concerns, and scopes. ROM concentrates it in one place. The performance implications of this concentration are significant — it becomes possible to audit every database interaction in the application by reading a handful of files.

N+1 in ROM: visible by design, fixable by convention

ActiveRecord hides the N+1 problem behind lazy loading — associations load silently when accessed, and the 200 extra queries are invisible unless you are watching the logs. ROM takes the opposite approach. Associations are not loaded unless you explicitly request them. Access an unloaded association and you get nil or an error, not a silent query.

This sounds like a constraint. It is actually a gift. The N+1 pattern cannot hide from you in ROM.

# The N+1 — accessing associations without combine
repo = Main::Repos::OrderRepo.new

orders = repo.pending_orders
orders.each do |order|
  # Each .customer call triggers a separate SELECT
  customer = customers_relation.where(id: order.customer_id).one!
  puts "#{order.id}: #{customer.name}"
end
# 200 pending orders = 201 queries. Every. Single. Time.

# ROM does NOT lazy-load like ActiveRecord. It will not
# silently fetch associations — you get nil or a missing
# attribute error. This is actually a feature: the N+1
# is visible immediately, not hidden behind magic.

In ROM, you would never accidentally write this because accessing order.customer without loading the association raises an error or returns nil. You are forced to be explicit about what you load. The framework makes the right thing easy and the wrong thing loud.

Allow me to be precise about the mechanism. When you call .to_a on a ROM relation, ROM materializes the query results into structs. Those structs contain only the attributes from the columns returned by the query. If you did not combine(:customer), the struct has no customer attribute. There is no proxy object waiting to fire a lazy query. There is no method_missing hook intercepting attribute access. The attribute simply does not exist. This is ROM's philosophy: what you ask for is what you get. Nothing more, nothing less.

# Fixed — combine fetches everything in 2 queries
orders = repo.pending_with_customers
orders.each do |order|
  # customer is already loaded as a nested struct
  puts "#{order.id}: #{order.customer.name}"
end

# What ROM sends to PostgreSQL:
# Query 1: SELECT * FROM orders WHERE status = 'pending'
# Query 2: SELECT * FROM customers WHERE id IN (42, 17, 89, ...)
#
# 2 queries. Always 2. Whether you have 50 orders or 5,000.

ROM's combine method generates an IN query for each association level. Two queries for one level of nesting. Three queries for two levels. The query count is predictable, bounded, and independent of the row count. 50 orders or 5,000 — still 2 queries.

I should be honest about the tradeoff. ROM's explicitness means you write more code. Every view that needs customer data requires a repo method that combines customers. In ActiveRecord, you can access order.customer anywhere and it "just works" — silently and expensively, but it works. ROM requires forethought. You must know what data you need before you fetch it. This is a design discipline, not a framework limitation, but it does impose a cognitive cost that ActiveRecord does not.

For teams accustomed to ActiveRecord's implicit loading, the transition to ROM feels like typing with gloves on. The gloves protect you — no accidental N+1, no surprise queries — but the reduced dexterity is real. The adjustment period is typically two to three weeks, after which the explicitness feels natural rather than burdensome. I have yet to meet a team that, having adjusted, wished to return to implicit loading.

combine vs aggregate: choosing the right loading strategy

ROM offers two primary eager loading mechanisms, and the distinction between them matters more than the documentation suggests.

# combine — returns flat tuples with associated data
orders.combine(:customer).to_a
# => [#<Main::Structs::Order id=1 total=49.99 customer=#<Struct name="Acme">>]
# Each order has a .customer attribute

# aggregate — embeds children as arrays inside the parent
orders.aggregate(:order_items).to_a
# => [#<Main::Structs::Order id=1 total=49.99 order_items=[#<Struct ...>, ...]>]
# Each order has an .order_items array

# combine with multiple levels
orders.combine(customer: :tier, order_items: :product).to_a
# Query plan:
#   1. SELECT * FROM orders WHERE ...
#   2. SELECT * FROM customers WHERE id IN (...)
#   3. SELECT * FROM tiers WHERE id IN (...)
#   4. SELECT * FROM order_items WHERE order_id IN (...)
#   5. SELECT * FROM products WHERE id IN (...)
# 5 queries total. Predictable. Bounded. No cartesian explosion.

The key insight: ROM's combine and aggregate both use the multi-query approach with IN clauses. Neither generates SQL JOINs for eager loading. This is deliberate — JOINs on one-to-many relations produce cartesian expansion, duplicating parent rows for every child match. ROM avoids this entirely by keeping each relation query independent.

If you have read our analysis of N+1 patterns across ORMs, you will recognize this as the sanest default. The multi-query approach has predictable performance characteristics. Its worst case — a few extra milliseconds of round-trip latency — is vastly preferable to the nested-loop explosions that JOIN-based eager loading can produce on deep hierarchies.

When you genuinely need a SQL JOIN — a 1:1 relation on a single-row lookup, for instance — drop to the dataset level. ROM gives you the escape hatch. Use it deliberately, not as a default.

The IN clause at scale

ROM's multi-query approach has an upper bound that the documentation does not discuss: the IN clause itself. When your parent query returns 10,000 rows, the association query becomes WHERE customer_id IN (1, 2, 3, ..., 10000). PostgreSQL handles this competently up to roughly 10,000 values. Beyond that, you begin to pay a parsing and planning cost that is not negligible.

# The IN clause problem with very large result sets
#
# ROM's combine generates: WHERE id IN (1, 2, 3, ..., 10000)
# PostgreSQL handles this fine up to ~10,000 IDs.
# Beyond that, three things happen:
#
# 1. Parse time increases — the SQL string itself becomes large
# 2. Planner time increases — evaluating 50,000 literal values
# 3. You may hit max_query_length or pg driver limits
#
# If your parent query returns 50,000+ rows and you're combining
# associations, you have a pagination problem, not a loading problem.

# Solution 1: Paginate before combining
def pending_page(page, per_page)
  orders
    .pending
    .combine(:customer)
    .limit(per_page)
    .offset((page - 1) * per_page)
    .to_a
  # The IN clause now contains at most `per_page` IDs.
end

# Solution 2: Use a subquery instead of IN for large batches
def bulk_with_customers
  # Drop to Sequel for a correlated subquery approach
  order_ids = orders.pending.dataset.select(:id)
  customers_relation.dataset.where(
    id: orders.pending.dataset.select(:customer_id)
  ).to_a
  # Generates: WHERE id IN (SELECT customer_id FROM orders WHERE ...)
  # PostgreSQL optimizes this as a semi-join — no literal list.
end

This is not a flaw in ROM's approach — it is a boundary condition. Any system that batches association loading via IN clauses hits this limit eventually. The correct response is not to switch loading strategies; it is to paginate. If you are loading 50,000 parent rows with associations, you have a design problem that no eager loading strategy can solve elegantly.

Profiling with dataset.explain: see what PostgreSQL sees

ROM's greatest performance advantage over ActiveRecord is not a feature — it is access. ROM exposes Sequel's dataset, and Sequel's dataset exposes PostgreSQL's query planner. You do not need a separate profiling tool. You do not need to copy-paste SQL into psql. The profiling interface is two method calls away from any relation.

# Profiling with dataset — ROM exposes Sequel's dataset
relation = Main::App["relations.orders"]

# Get the underlying Sequel dataset
dataset = relation.dataset

# .explain — shows PostgreSQL's query plan
puts dataset.where(status: "pending").explain
# EXPLAIN output:
# Seq Scan on orders  (cost=0.00..1842.00 rows=523 width=64)
#   Filter: (status = 'pending')

# .explain(analyze: true) — actually runs the query
puts dataset.where(status: "pending").explain(analyze: true)
# Seq Scan on orders  (cost=0.00..1842.00 rows=523 width=64)
#   (actual time=0.024..4.218 rows=487 loops=1)
# Planning Time: 0.082 ms
# Execution Time: 4.301 ms

# Missing index detected! 487 rows from a sequential scan on
# a table that will only grow. Time for a partial index:
#
# CREATE INDEX idx_orders_pending ON orders (created_at)
#   WHERE status = 'pending';

This is how you catch missing indexes before they become production incidents. A sequential scan on a 50,000-row table is fine in development. It will not be fine at 5 million rows with 200 concurrent requests. The explain(analyze: true) output tells you exactly what PostgreSQL is doing — sequential scan, index scan, bitmap scan, sort method, row estimates versus actuals.

Build this into your development workflow. Before any repo method ships, call .explain(analyze: true) on the underlying dataset with production-representative data. If you see Seq Scan on a table that will grow, add an index. If the estimated rows diverge wildly from actual rows, ANALYZE the table. If the query plan shows a nested loop where a hash join would be faster, check your work_mem setting.

Three minutes of profiling saves three hours of incident response. The math is not subtle.

Reading EXPLAIN output with purpose

The EXPLAIN output tells you more than whether an index was used. It tells you the full execution story — and for ROM's multi-query combine pattern, you need to profile each leg independently.

# Beyond basic EXPLAIN — output formats for different needs
dataset = relation.dataset.where(status: "pending")

# EXPLAIN with BUFFERS — shows I/O statistics
puts dataset.explain(analyze: true, buffers: true)
# Seq Scan on orders  (cost=0.00..1842.00 rows=523 width=64)
#   (actual time=0.024..4.218 rows=487 loops=1)
#   Buffers: shared hit=312 read=42
#   Planning Time: 0.082 ms
#   Execution Time: 4.301 ms
#
# "shared hit=312" means 312 pages found in PostgreSQL's buffer cache.
# "read=42" means 42 pages read from disk. If read >> hit, your
# shared_buffers is undersized for this workload.

# For combine queries, profile each leg separately:
puts orders.pending.dataset.explain(analyze: true)
puts customers_relation.dataset.where(
  id: orders.pending.pluck(:customer_id)
).explain(analyze: true)
# Now you know which leg of the combine is expensive.
# Usually it's the second — the IN clause on a table
# without an index on the foreign key.

The BUFFERS option is particularly valuable for ROM applications. ROM's combine pattern generates multiple independent queries, and each query has its own buffer cache behavior. If the parent query hits the cache but the association query reads from disk, you have found the bottleneck. The parent query's IN clause determines the association query's access pattern — if those IDs are scattered across the table, the association query performs random I/O regardless of indexing.

I should note an honest limitation here. Profiling in development with 1,000 rows tells you the plan shape — index scan versus sequential scan — but does not tell you the production cost. PostgreSQL's planner makes different choices at different table sizes. A table with 1,000 rows may get an index scan in development and a bitmap heap scan in production because the cost model changes with table statistics. If you can, profile against a staging database with production-scale data. If you cannot, at least verify the plan shape is correct and trust that the plan cost will scale predictably.

Index strategies for ROM query patterns

ROM's explicitness extends to indexing. Because every query is declared in a repo method, you can read the repo and derive the exact indexes needed. This is not possible with ActiveRecord, where queries are scattered across the codebase and assembled dynamically through scope chains.

# Index strategies for common ROM query patterns
#
# Pattern 1: Status-based filtering (the partial index)
# Your relation: orders.where(status: "pending")
# The index:
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';
# Only indexes pending orders. Tiny index, fast scans.
# As orders move to 'shipped', they leave the index automatically.

# Pattern 2: Eager loading foreign keys (the combine index)
# ROM's combine generates: WHERE customer_id IN (...)
# Without an index on customer_id, this is a sequential scan.
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
# This is the single most impactful index for ROM applications.
# Every belongs_to association in every combine depends on it.

# Pattern 3: Composite indexes for filtered + sorted queries
# Your repo: orders.pending.order(:created_at).limit(25)
CREATE INDEX idx_orders_pending_recent ON orders (created_at DESC)
  WHERE status = 'pending';
# PostgreSQL reads the index in order — no sort step needed.
# LIMIT 25 stops after 25 index entries. Sub-millisecond.

# Pattern 4: Covering indexes to avoid table lookups
# If your repo only projects certain columns:
# orders.pending.select(:id, :total, :created_at)
CREATE INDEX idx_orders_pending_covering ON orders (created_at)
  INCLUDE (id, total)
  WHERE status = 'pending';
# Index-only scan — PostgreSQL never touches the heap.
# Verify with EXPLAIN: "Index Only Scan" in the output.

The second pattern — indexing foreign keys for combine — deserves emphasis. ROM's eager loading generates WHERE foreign_key IN (...) for every association. Without an index on that foreign key, the association query performs a sequential scan. PostgreSQL does not automatically index foreign keys. This is the single most common performance oversight in ROM applications, and it is trivially fixable.

I would go so far as to suggest a rule: for every belongs_to association in your relations, verify that the foreign key column has an index. If it does not, create one. The cost is negligible — a B-tree index on an integer column adds minimal write overhead and transforms association queries from sequential scans to index lookups.

Partial indexes deserve particular attention in Hanami applications. ROM relations frequently filter by status — pending, active, shipped — and partial indexes cover exactly these patterns. A partial index on orders WHERE status = 'pending' is dramatically smaller than a full index on orders, because pending orders are typically a small fraction of total orders. Smaller index means faster scans, less memory pressure, and more of the index fits in PostgreSQL's buffer cache.

Sequel extensions: unlocking PostgreSQL-specific types

ROM sits on top of Sequel, and Sequel has the most comprehensive PostgreSQL extension library in the Ruby ecosystem. These extensions give you native access to PostgreSQL features that ActiveRecord either does not support or supports through third-party gems of varying quality.

# config/db.rb — loading Sequel PostgreSQL extensions
module Main
  class DB < ROM::SQL::Gateway
    # Sequel ships with PostgreSQL-specific extensions
    # ROM exposes them through the gateway configuration
  end
end

# In your Hanami provider or setup:
Main::DB.use_extension(:pg_array)      # native array operations
Main::DB.use_extension(:pg_json)       # jsonb support
Main::DB.use_extension(:pg_range)      # range types
Main::DB.use_extension(:pg_hstore)     # key-value pairs
Main::DB.use_extension(:pg_triggers)   # trigger management

# With extensions loaded, you get PostgreSQL-native types in ROM:
class Products < Main::DB::Relation
  schema(:products, infer: true) do
    attribute :tags, Types::PG::Array("text")
    attribute :metadata, Types::PG::JSONB
    attribute :price_range, Types::PG::NumRange
  end
end

# Query with PostgreSQL array operators via Sequel's dataset
products.dataset.where(
  Sequel.pg_array_op(:tags).contains(
    Sequel.pg_array(["organic", "local"])
  )
)
# => SELECT * FROM products WHERE tags @> ARRAY['organic','local']

# JSONB containment queries
products.dataset.where(
  Sequel.pg_jsonb_op(:metadata).contains(
    Sequel.pg_jsonb({ "origin" => "Japan" })
  )
)
# => SELECT * FROM products WHERE metadata @> '{"origin":"Japan"}'

A few patterns worth highlighting:

pg_array enables native PostgreSQL array columns with GIN-indexed containment queries. For tags, permissions, feature flags — short lists that are queried together — a PostgreSQL array with a GIN index is simpler and faster than a many-to-many join table. One column instead of three tables.

pg_json gives you proper JSONB support with containment operators (@>), path queries, and indexing. Sequel's pg_jsonb_op generates the correct PostgreSQL operators rather than falling back to text comparison.

pg_range supports PostgreSQL range types — int4range, tsrange, numrange. If you are modeling time windows, price brackets, or version ranges, native range types with GiST indexes outperform the two-column start_date/end_date pattern. Overlap queries (&&) and containment queries (@>) work natively.

These extensions are loaded at the Sequel connection level and are available to any ROM relation through its dataset. The ROM relation provides the structure. Sequel provides the PostgreSQL-specific query capabilities. You get both without leaving the Hanami framework.

JSONB patterns that earn their keep

JSONB deserves a deeper treatment because it is the PostgreSQL feature most frequently misused — either avoided entirely by teams who distrust schema-less storage, or adopted wholesale by teams who should be using normalized columns instead. The correct position is neither extreme.

# Advanced JSONB patterns through Sequel's pg_jsonb_op
dataset = products.dataset

# Path extraction — get a nested value
dataset.select(
  :id,
  Sequel.pg_jsonb_op(:metadata).get_text("origin").as(:origin)
)
# => SELECT id, metadata->>'origin' AS origin FROM products

# Existence check — does the key exist?
dataset.where(
  Sequel.pg_jsonb_op(:metadata).has_key?("discontinued")
)
# => SELECT * FROM products WHERE metadata ? 'discontinued'

# Path query — nested key access
dataset.where(
  Sequel.pg_jsonb_op(:metadata).get_text(
    Sequel.pg_array(["dimensions", "weight"])
  ).cast(Float) > 5.0
)
# => SELECT * FROM products
#    WHERE (metadata #>> ARRAY['dimensions','weight'])::float > 5.0

# Index it properly:
# CREATE INDEX idx_products_metadata ON products
#   USING GIN (metadata jsonb_path_ops);
#
# jsonb_path_ops is smaller and faster than the default GIN
# operator class, but only supports containment (@>) queries.
# If you need existence (?) or key queries, use the default.

Use JSONB for data that is genuinely semi-structured — metadata, configuration, audit payloads, third-party webhook bodies. Data where the schema varies per row and you cannot predict all possible keys at design time. Do not use JSONB for data that has a stable schema. If every product has a weight and a color, those are columns, not JSON keys. Columns get type checking, NOT NULL constraints, and cheaper index access. JSONB gets flexibility at the cost of all three.

A candid counterpoint: Sequel's JSONB support, while the best in the Ruby ecosystem, is still more verbose than what you would write in raw SQL. The pg_jsonb_op wrapper generates correct SQL, but the Ruby expression Sequel.pg_jsonb_op(:metadata).get_text("origin") is notably less readable than metadata->>'origin'. For complex JSONB queries with multiple path operations, I find it more honest to drop to raw SQL through db.fetch than to construct a tower of Sequel method calls. The SQL is clearer and easier to verify against the PostgreSQL documentation.

Materialized views as ROM relations

Materialized views are PostgreSQL's answer to expensive aggregations that do not need to be real-time. ROM handles them gracefully because ROM relations are not tied to tables — they can be backed by any queryable object, including views.

# Materialized views as ROM relations — read-only, fast
#
# Step 1: Create the materialized view in a migration
# CREATE MATERIALIZED VIEW monthly_revenue AS
#   SELECT
#     date_trunc('month', o.created_at) AS month,
#     c.tier,
#     COUNT(*) AS order_count,
#     SUM(o.total) AS revenue,
#     AVG(o.total) AS avg_order_value
#   FROM orders o
#   JOIN customers c ON c.id = o.customer_id
#   WHERE o.status = 'shipped'
#   GROUP BY date_trunc('month', o.created_at), c.tier;
#
# CREATE UNIQUE INDEX idx_monthly_revenue
#   ON monthly_revenue (month, tier);

# Step 2: Define a ROM relation backed by the view
# app/relations/monthly_revenues.rb
module Main
  module Relations
    class MonthlyRevenues < Main::DB::Relation
      schema(:monthly_revenue, infer: true)

      def for_tier(tier)
        where(tier: tier)
      end

      def last_12_months
        where { month > Date.today << 12 }
      end
    end
  end
end

# Step 3: Query it like any other relation
repo.monthly_revenues.for_tier("enterprise").last_12_months.to_a
# Reads from the pre-computed materialized view.
# Sub-millisecond. No joins. No aggregation at query time.

# Step 4: Refresh on schedule (cron, Sidekiq, or rake task)
# REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

The pattern is straightforward. Create the materialized view in a migration. Define a ROM relation backed by it. Query it through a repo like any other relation. The view stores pre-computed results — no joins, no aggregation at query time, sub-millisecond reads.

When to use materialized views:

• Dashboard analytics. Revenue by month, user counts by tier, order trends. Data that changes hourly, not per-request.
• Leaderboards and rankings. Pre-compute RANK() and PERCENTILE_CONT() rather than running window functions on every page load.
• Search facets. Category counts, price range distributions, tag frequencies. Expensive to compute, stable enough to cache.

Refresh strategies that actually work

The view itself is the easy part. Refresh is where materialized views become operational burdens if not handled thoughtfully.

# Materialized view refresh patterns in Hanami 2
#
# Option 1: Rake task (simplest — cron or CI/CD trigger)
# lib/tasks/matviews.rake
namespace :matviews do
  desc "Refresh all materialized views"
  task refresh: :environment do
    db = Main::App["persistence.rom"].gateways[:default]
    db.connection.run(
      "REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue"
    )
    puts "Refreshed monthly_revenue at #{Time.now}"
  end
end
# crontab: */15 * * * * cd /app && bundle exec rake matviews:refresh

# Option 2: Sidekiq job (when you need error handling and retries)
class RefreshMatviewJob
  include Sidekiq::Job
  sidekiq_options retry: 3, queue: :maintenance

  def perform(view_name)
    db = Main::App["persistence.rom"].gateways[:default]
    db.connection.run(
      "REFRESH MATERIALIZED VIEW CONCURRENTLY #{view_name}"
    )
  end
end
# Schedule: RefreshMatviewJob.perform_in(15.minutes, "monthly_revenue")

# Option 3: Check staleness before refreshing
# Add a tracking table:
# CREATE TABLE matview_refresh_log (
#   view_name TEXT PRIMARY KEY,
#   last_refreshed_at TIMESTAMPTZ NOT NULL
# );
#
# Only refresh if stale:
def refresh_if_stale(view_name, max_age: 15.minutes)
  last = db.connection.fetch(
    "SELECT last_refreshed_at FROM matview_refresh_log WHERE view_name = ?",
    view_name
  ).first

  return if last && last[:last_refreshed_at] > Time.now - max_age

  db.connection.run(
    "REFRESH MATERIALIZED VIEW CONCURRENTLY #{view_name}"
  )
  db.connection.run(
    "INSERT INTO matview_refresh_log (view_name, last_refreshed_at) " \
    "VALUES (?, NOW()) ON CONFLICT (view_name) " \
    "DO UPDATE SET last_refreshed_at = NOW()",
    view_name
  )
end

Refresh strategy depends on your staleness tolerance. REFRESH MATERIALIZED VIEW CONCURRENTLY allows reads during refresh but requires a unique index. Without CONCURRENTLY, the refresh takes an exclusive lock — no reads while refreshing. For a dashboard view that refreshes every 15 minutes, a 2-second exclusive lock is acceptable. For a view backing a customer-facing page, it is not.

One caution: do not over-materialize. Every materialized view is a cache, and every cache is a correctness liability. If the data must be current within the same transaction, use a regular view or a direct query. Materialized views are for data where "five minutes ago" is good enough.

I should also note that materialized views do not participate in PostgreSQL's MVCC system the way tables do. You cannot roll them back. A refresh is a full replacement. If the underlying data changes in a way that makes the view incorrect — a bug in the source query, for instance — the view will be incorrect until the next refresh. Test your materialized view queries thoroughly before deploying them. A bug in a regular query returns wrong results for one request. A bug in a materialized view returns wrong results for every request until the view is refreshed with a corrected query.

Column projection: the optimization everyone forgets

I find it telling that most performance guides — including early drafts of this one — spend pages on eager loading and indexing but neglect column projection entirely. Yet for wide tables, projection is often the single most impactful optimization available. SELECT * is the default in almost every ORM, and it is almost never what you actually need.

# Column projection — only fetch what you need
#
# The default: SELECT * — every column, every row
orders = repo.pending_orders
# SELECT * FROM orders WHERE status = 'pending'
# If orders has 20 columns and you only need 4, you're
# transferring 5x more data than necessary.

# Projected: only the columns you use
def pending_summary
  orders
    .pending
    .select(:id, :customer_id, :total, :created_at)
    .combine(:customer)
    .to_a
end
# SELECT id, customer_id, total, created_at
#   FROM orders WHERE status = 'pending'
# SELECT * FROM customers WHERE id IN (...)

# For wide tables with TEXT or JSONB columns, this matters enormously.
# A single JSONB column averaging 2KB per row × 1,000 rows = 2MB
# of data you never needed. Projection eliminates it at the source.

# The tradeoff: projected structs have fewer attributes.
# Accessing a non-projected attribute raises NoMethodError.
# This is ROM being explicit — you asked for 4 columns,
# you get 4 columns. No surprises.

# For reports and exports — project + raw hashes for maximum speed
def export_pending
  orders
    .pending
    .select(:id, :total, :created_at)
    .dataset        # drop to Sequel dataset
    .to_a           # returns hashes, not structs
end
# Minimal columns, minimal allocation, maximal throughput.

ROM makes projection straightforward through the .select method on relations. The SQL is immediately narrower — fewer bytes transferred from PostgreSQL, less memory allocated in Ruby, faster serialization to JSON. For a table with a 2KB JSONB metadata column that you never display in a list view, projection eliminates 2KB per row. At 1,000 rows, that is 2MB of data you never needed to transfer, parse, or garbage-collect.

The tradeoff is the same one that pervades ROM's entire design: explicitness costs keystrokes. A projected relation produces structs with fewer attributes. Access a non-projected attribute and you get a NoMethodError, not a lazy load. This is ROM being consistent — you asked for four columns, you get four columns. But it means you need different repo methods for different views of the same data: one for the list page that projects five columns, one for the detail page that projects all columns, one for the export that projects a different subset.

The proliferation of repo methods is the most common complaint about ROM, and I understand it. But consider what those methods represent: each one is a documented, testable contract between the application and the database. The alternative — SELECT * everywhere and letting views pick what they need — is simpler to write and harder to optimize. You cannot index a query you have not defined. You cannot profile a query that changes shape depending on which view renders it.

Structs, hashes, and the object allocation tax

ROM repos return structs by default — lightweight Ruby objects with named attributes. They are not ActiveRecord models. There are no callbacks, no dirty tracking, no association proxies. But they are still Ruby objects, and object allocation is still a cost.

# ROM structs vs raw hashes — know the cost
#
# Struct (default from repo) — typed, named attributes
orders = repo.pending_orders
orders.first.class  # => Main::Structs::Order
orders.first.total  # => 49.99 (attribute access)

# Raw hashes — skip struct instantiation entirely
orders = relation.where(status: "pending").to_a
# Or through the repo with a custom method:
def pending_as_hashes
  orders.pending.map_with(:auto_struct).to_a
end

# For large result sets (10,000+ rows), the difference matters:
#
# 10,000 rows as structs:  ~45ms Ruby-side overhead
# 10,000 rows as hashes:   ~12ms Ruby-side overhead
#
# The SQL is identical. The difference is object allocation.
# For API responses that serialize to JSON immediately,
# hashes skip one full object-creation cycle.

# Even leaner — project specific columns only
def pending_ids
  orders.pending.pluck(:id)
  # Returns: [1, 2, 3, ...] — flat array, minimal allocation
end

For API responses that will be serialized to JSON immediately, the struct is an intermediate object that adds no value. You create it, serialize it, garbage-collect it. Returning hashes directly skips one full allocation cycle.

For internal application logic — passing data between components, applying business rules, rendering views — structs provide meaningful value through named attributes and type safety. The allocation cost is justified.

The pluck method deserves special mention. When you need a list of IDs for a batch operation, pluck(:id) returns a flat array with minimal allocation. It is the leanest read path ROM offers, and it is appropriate for subqueries, existence checks, and batch job inputs.

I should be honest about the magnitude of this optimization. For most web requests returning 25-50 rows, the difference between structs and hashes is negligible — a few milliseconds at most. The optimization matters for batch operations, exports, and data pipelines where you are processing thousands of rows. If your endpoint returns a paginated list of 25 items, spend your optimization budget on indexes and projection, not on struct-versus-hash debates. If your export job processes 100,000 records, hashes and pluck can meaningfully reduce memory pressure and GC pauses.

The raw SQL escape hatch: CTEs, window functions, and beyond

ROM's relation API covers standard CRUD and moderate-complexity queries. When you need PostgreSQL's full analytical power — common table expressions, window functions, lateral joins, recursive queries, PERCENTILE_CONT — drop to Sequel's dataset and write SQL directly.

# When ROM's relation API is not enough, drop to Sequel
relation = Main::App["relations.orders"]

# Complex analytics — window functions, CTEs, lateral joins
result = relation.dataset.db.fetch(<<~SQL)
  WITH order_stats AS (
    SELECT
      customer_id,
      COUNT(*) AS order_count,
      SUM(total) AS lifetime_value,
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median_order
    FROM orders
    WHERE status = 'shipped'
    GROUP BY customer_id
  )
  SELECT
    c.name,
    c.tier,
    os.order_count,
    os.lifetime_value,
    os.median_order,
    RANK() OVER (PARTITION BY c.tier ORDER BY os.lifetime_value DESC)
      AS tier_rank
  FROM customers c
  JOIN order_stats os ON os.customer_id = c.id
  ORDER BY os.lifetime_value DESC
  LIMIT 50
SQL

result.each { |row| puts row[:name] }
# Returns plain hashes — no ORM overhead, full PostgreSQL power.

This is not a failure of the ORM. This is the ORM working as designed. ROM handles the 85% of queries that follow standard patterns. The remaining 15% — analytics, reporting, complex aggregations — deserve hand-written SQL because the optimal query shape is not expressible through any method-chaining API. This applies to every ORM in every language.

Sequel's db.fetch returns plain hashes with no ORM overhead. For large analytic queries returning thousands of rows, this is measurably faster than constructing structs. Use it without guilt for read-heavy analytics paths.

Lateral joins: the query PostgreSQL was made for

If there is one PostgreSQL feature that justifies dropping to raw SQL, it is the lateral join. The "top N per group" query — the three most recent orders per customer, the five highest-rated products per category — is awkward in every ORM and elegant in PostgreSQL.

# Lateral joins — when you need "top N per group"
# This cannot be expressed through ROM's relation API.
# It barely fits in Sequel's query builder.
# Write it directly.

result = relation.dataset.db.fetch(<<~SQL)
  SELECT
    c.name,
    c.tier,
    recent.id AS order_id,
    recent.total,
    recent.created_at
  FROM customers c
  CROSS JOIN LATERAL (
    SELECT o.id, o.total, o.created_at
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'shipped'
    ORDER BY o.created_at DESC
    LIMIT 3
  ) recent
  WHERE c.tier = 'enterprise'
  ORDER BY c.name, recent.created_at DESC
SQL

# This returns the 3 most recent orders per enterprise customer
# in a single query. The LATERAL join lets each customer row
# execute its own subquery — PostgreSQL optimizes this with an
# index on orders (customer_id, created_at DESC).
#
# The alternative — fetching all enterprise customers, then
# fetching all their orders, then selecting the top 3 in Ruby —
# transfers far more data and does work in Ruby that PostgreSQL
# handles natively.

A lateral join lets each row in the outer query execute its own subquery. This sounds expensive. It is not — with an appropriate index on the inner table (in this case, orders(customer_id, created_at DESC)), PostgreSQL performs an index scan per outer row. For 100 enterprise customers, that is 100 index lookups, each returning at most 3 rows. The total work is bounded and predictable.

The alternative approaches — fetching all orders and grouping in Ruby, or using a window function with ROW_NUMBER() and filtering — either transfer too much data or require PostgreSQL to compute a rank for every row before discarding most of them. The lateral join computes only what is needed. It is the most direct expression of "for each X, give me the top N of Y" — and directness, in SQL, usually means performance.

I would not attempt to express this through Sequel's query builder. The resulting Ruby code would be longer, harder to read, and harder to verify than the SQL itself. When the query shape is this specific, raw SQL is not an escape hatch — it is the proper tool.

Connection pool tuning for Hanami 2

Hanami 2 applications use Sequel's connection pool, which defaults to 4 connections. This is adequate for development and woefully insufficient for production behind Puma.

# config/providers/persistence.rb — Hanami 2 database setup
Hanami.app.register_provider :persistence do
  prepare do
    require "rom"

    # ROM uses Sequel under the hood for SQL databases.
    # Connection pool settings pass through to Sequel's
    # connection pool, which wraps the pg gem.
    config = ROM::Configuration.new(
      :sql,
      target["settings"].database_url,
      extensions: [:pg_array, :pg_json],
      # Sequel pool options:
      max_connections: 20,    # default is 4 — too small
      pool_timeout: 10,       # seconds to wait for a connection
      preconnect: :concurrently
    )

    config.auto_registration(
      target.root.join("app/relations"),
      namespace: "Main::Relations"
    )

    register "persistence.config", config
  end

  start do
    config = target["persistence.config"]
    register "persistence.rom", ROM.container(config)
  end
end

# Rule of thumb for max_connections:
#   Puma workers * Puma threads = concurrent requests
#   max_connections = concurrent requests + headroom
#   Example: 2 workers * 5 threads = 10 → max_connections = 15-20

The formula is straightforward. Puma workers multiplied by Puma threads gives you the maximum concurrent requests. Your connection pool must be at least that large, plus some headroom for background jobs and admin queries.

A pool that is too small does not cause errors — it causes latency. Request threads wait for a connection to become available. At 95th percentile, this waiting time dominates the response time. Monitor with pg_stat_activity to see how many connections are actually in use during peak load.

# Monitoring connection usage with pg_stat_activity
# Run this during peak load to see actual connection usage

result = relation.dataset.db.fetch(<<~SQL)
  SELECT
    state,
    COUNT(*) AS connections,
    ROUND(AVG(EXTRACT(EPOCH FROM (NOW() - state_change)))::numeric, 2)
      AS avg_seconds_in_state
  FROM pg_stat_activity
  WHERE datname = current_database()
    AND pid != pg_backend_pid()
  GROUP BY state
  ORDER BY connections DESC
SQL

# Typical healthy output:
# state    | connections | avg_seconds_in_state
# ---------+-------------+---------------------
# idle     |          12 | 0.84
# active   |           3 | 0.02
# idle in transaction | 0 | 0.00
#
# Warning signs:
# - "idle in transaction" > 0: someone opened a transaction
#   and forgot to close it. This holds a connection AND a
#   PostgreSQL snapshot, preventing vacuum from reclaiming space.
# - "idle" >> "active": your pool is oversized. Those idle
#   connections each consume ~10MB on the PostgreSQL server.
# - "active" near max_connections: you need more connections
#   or your queries are too slow.

A pool that is too large wastes PostgreSQL resources. Each connection consumes ~10MB of memory on the PostgreSQL server. 200 idle connections across multiple application instances is 2GB of memory doing nothing. Size the pool to match actual concurrency, not theoretical maximum.

One pattern I encounter frequently in Hanami deployments: the application is configured with 20 connections in the Sequel pool, but PostgreSQL's max_connections is still at the default of 100. Three application instances with 20 connections each, plus a Sidekiq worker pool with 10 connections, plus your admin tools — and you are at 80 connections before considering replication or monitoring tools. The wall arrives faster than you expect.

For applications that need more than 100 total connections, the correct answer is almost never to increase max_connections. It is to add a connection pooler — PgBouncer is the standard choice — between your application instances and PostgreSQL. PgBouncer multiplexes many application connections onto fewer PostgreSQL connections, and it does this with negligible overhead for transaction-mode pooling. Hanami's Sequel connection pool connects to PgBouncer instead of directly to PostgreSQL. No application code changes required.

I should note a compatibility concern: PgBouncer's transaction-mode pooling does not support prepared statements, and Sequel uses prepared statements by default. Disable them in your ROM configuration with prepared_statements: false when using PgBouncer. The performance impact of losing prepared statements is typically less than 1ms per query for simple queries — far less than the latency reduction from proper connection pooling.

Testing query performance in CI

Everything discussed so far — indexes, projections, eager loading — can regress silently. A developer adds a new column, forgets the index, and the sequential scan returns to production. A refactored repo method drops a combine call, reintroducing N+1. These regressions are invisible to unit tests that only check return values.

# spec/repos/order_repo_spec.rb — testing ROM query performance
RSpec.describe Main::Repos::OrderRepo do
  subject(:repo) { described_class.new }

  describe "#pending_with_customers" do
    it "loads customers in exactly 2 queries" do
      # Create test data
      customer = create(:customer)
      create_list(:order, 5, customer: customer, status: "pending")

      query_count = 0
      counter = ->(_name, _start, _finish, _id, payload) {
        query_count += 1 if payload[:sql] =~ /^SELECT/
      }

      ActiveSupport::Notifications.subscribe("sql.sequel", &counter)
      repo.pending_with_customers
      ActiveSupport::Notifications.unsubscribe(counter)

      expect(query_count).to eq(2)
    end
  end

  describe "#pending_page" do
    it "paginates without loading all rows" do
      create_list(:order, 50, status: "pending")

      page1 = repo.pending_page(1, 10)
      page2 = repo.pending_page(2, 10)

      expect(page1.length).to eq(10)
      expect(page2.length).to eq(10)
      expect(page1.map(&:id)).not_to eq(page2.map(&:id))
    end

    it "uses an index scan, not a sequential scan" do
      relation = Main::App["relations.orders"]
      plan = relation.pending.dataset
        .order(Sequel.desc(:created_at))
        .limit(10)
        .explain

      expect(plan).not_to include("Seq Scan")
      # If this fails, you need an index.
      # This test catches missing indexes in CI,
      # before they reach production.
    end
  end
end

Two testing patterns earn their maintenance cost. First, query-count assertions: verify that a repo method executes the expected number of queries. If pending_with_customers should run 2 queries and suddenly runs 201, the test catches it. Second, plan-shape assertions: verify that queries use index scans, not sequential scans. If a migration drops an index accidentally, the test catches it before the next deployment.

These tests are not free. They are coupled to implementation details — the number of queries, the plan shape — rather than behavior. A refactor that changes the query count without changing the result requires updating the test. This coupling is the cost. The benefit is that performance regressions are caught in CI rather than in production monitoring at 3am.

I find the tradeoff worthwhile for repo methods that are on hot paths — the endpoints that serve the most traffic. For rarely-used admin endpoints, the maintenance cost of performance tests exceeds their value. As with all testing, apply judgment about where the risk lives.

Putting it together: a Hanami action with optimized ROM queries

Theory is valuable. Working code is more valuable. Here is a Hanami 2 action that applies the patterns from this guide — eager loading, pagination in SQL, counted totals, and minimal object allocation.

# app/actions/orders/index.rb — Hanami 2 action with ROM
module Main
  module Actions
    module Orders
      class Index < Main::Action
        include Deps["repos.order_repo"]

        def handle(request, response)
          page = request.params[:page]&.to_i || 1
          per_page = 25

          # Eager-loaded, paginated, with only needed fields
          orders = order_repo.pending_page(page, per_page)
          total = order_repo.pending_count

          response.body = {
            orders: orders,
            pagination: {
              page: page,
              per_page: per_page,
              total: total
            }
          }.to_json
        end
      end
    end
  end
end

# app/repos/order_repo.rb — the repo method
def pending_page(page, per_page)
  orders
    .pending
    .combine(:customer)
    .order(Sequel.desc(:created_at))
    .limit(per_page)
    .offset((page - 1) * per_page)
    .to_a
end

def pending_count
  orders.pending.count
end
# 3 queries total: count, orders with LIMIT/OFFSET, customers IN (...)
# Pagination happens in SQL. Eager loading adds exactly 1 query.

Three queries total. Pagination happens in PostgreSQL, not in Ruby. Eager loading adds exactly one additional query regardless of page size. The repo method is explicit about what it loads and how.

Compare this to the ActiveRecord equivalent, where Order.where(status: "pending").includes(:customer).page(1).per(25) looks simpler but hides the query count, the loading strategy, and the pagination implementation behind conventions that you cannot see without reading source code or watching logs.

ROM's verbosity is the cost. ROM's predictability is the payoff.

There is a subtlety in the pagination worth noting. The pending_count method runs a separate COUNT(*) query. For tables with millions of rows, this count can be expensive — PostgreSQL must scan the table (or an index) to count matching rows. If your staleness tolerance allows it, consider caching the count or using PostgreSQL's pg_stat_user_tables.n_live_tup estimate. For exact counts on large tables, a partial index on the status column makes the count query fast because PostgreSQL can count index entries instead of scanning the heap.

The honest counterpoint: when Hanami's ROM is the wrong choice

A guide that only praises its subject is not a guide — it is marketing. I owe you a frank assessment of where Hanami 2 with ROM falls short, because pretending these limitations do not exist would be a disservice to you and an embarrassment to me.

Ecosystem size matters. ActiveRecord has thousands of gems, plugins, and battle-tested patterns. ROM has a small, dedicated community and significantly fewer integrations. When you need a third-party gem for authentication, payment processing, or admin dashboards, it will assume ActiveRecord. Adapting it to ROM ranges from straightforward to painful, depending on how deeply the gem couples to ActiveRecord's internals.

Hiring is harder. Most Ruby developers know Rails and ActiveRecord. Few have production experience with Hanami and ROM. This is not a technical limitation — ROM's concepts are learnable in weeks — but it is an operational reality that affects team scaling and onboarding costs.

Write operations are more complex. This guide focuses on read performance, where ROM excels. Write operations — creating, updating, and deleting records through ROM's changesets and commands — are more verbose and less intuitive than ActiveRecord's create, update, and destroy. ROM's write path has improved significantly in recent versions, but it remains an area where ActiveRecord's developer experience is genuinely superior.

The Sequel dependency cuts both ways. Sequel's PostgreSQL support is excellent, and having access to the dataset layer is genuinely valuable. But it also means you are learning and maintaining three abstractions instead of one. When something goes wrong at the query level, you need to understand whether the issue is in your ROM relation, your Sequel dataset, or your PostgreSQL configuration. The debugging surface area is larger.

If you are building a content management system, an e-commerce platform, or any application where the primary concern is developer productivity and the team is experienced with Rails — ActiveRecord is the correct choice. Its implicit loading, convention-based queries, and vast ecosystem serve that context well.

If you are building a system where query performance is a primary architectural concern, where you need fine-grained control over what data is loaded and how, and where you value explicitness over convention — ROM in Hanami 2 is worth the additional verbosity. It does not make your application faster by default. It makes your application's performance characteristics visible, predictable, and auditable. That is a different value proposition, and it is the honest one.

What happens below the relation

Every optimization in this guide operates at the application layer — choosing the right ROM methods, structuring queries to minimize round trips, using Sequel extensions for PostgreSQL-native types. These are essential, and they are entirely within your control.

There is, however, a layer below. The SQL that ROM and Sequel generate arrives at PostgreSQL, where the query planner decides how to execute it. Missing indexes cause sequential scans. Stale statistics lead to bad join strategies. Repeated expensive queries compute the same aggregation thousands of times per hour.

Gold Lapel sits between your Hanami application and PostgreSQL — a proxy that observes the actual query traffic. It detects missing indexes from access patterns and creates them. It identifies repeated expensive queries and materializes the results automatically. It catches N+1 patterns that escaped code review and batches them before they reach the database.

ROM sends explicit, predictable SQL. This is exactly the kind of traffic that a query-aware proxy can optimize most effectively — no magic method calls to untangle, no lazy loading surprises, just clean SQL with clear access patterns. Write good ROM code — use combine, use pluck, profile with .explain(analyze: true) — and let the infrastructure handle the rest.

ROM's transparency is, in this regard, an architectural advantage that extends beyond the application itself. A system that sends predictable SQL is a system that can be observed, profiled, and optimized at every layer of the stack. The waiter does not claim to manage every room in the house. But a well-run household begins with clear instructions — and ROM's instructions to PostgreSQL are as clear as they come.