DEV Community

Kaziu
Kaziu

Posted on

Indexing Calculated Results from Multiple SQL Columns, and the Limits of Write Overhead

When you run a search in a database that combines multiple columns (e.g. (unit price × quantity - discount) × tax rate), it usually triggers a full scan (Table Scan) and causes major slowdowns.

In this post, I'll explain how to roll the calculated result of multiple columns into a single or composite index, along with how big the write overhead (latency risk) you're probably worried about actually gets, and what to do when you need to be careful.


1. Two ways to put the calculated result of multiple columns into an index

For example, consider an orders table with these 5 columns:

  • price (unit price)
  • quantity (quantity)
  • discount (discount)
  • tax_rate (tax rate)
  • status (order status)

If you want to quickly find "completed orders with a total billed amount of 10,000 yen or more," there are 2 approaches.

Approach A: Create an expression index directly

Define the formula itself in the index, and create a composite index that combines it with the status column.

SQL (PostgreSQL)

CREATE INDEX idx_orders_total_amount_and_status ON orders (
  ((price * quantity - discount) * (1 + tax_rate)), 
  status
);
Enter fullscreen mode Exit fullscreen mode

Rails migration

class AddCalculatedIndexToOrders < ActiveRecord::Migration[7.0]
  def change
    add_index :orders, 
              ["((price * quantity - discount) * (1 + tax_rate))", :status],
              name: "idx_orders_total_amount_and_status"
  end
end
Enter fullscreen mode Exit fullscreen mode

Approach B: Generated column + composite index [Recommended]

When the formula is complex, defining a "calculated result column" on the table and indexing that makes your SQL far more readable and maintainable.

Rails (Migration)

class AddTotalAmountToOrders < ActiveRecord::Migration[7.0]
  def change
    change_table :orders do |t|
      # 1. Define a calculated column using the 4 columns (automatically computed & stored by the DB)
      t.virtual :total_amount, 
                type: :decimal, 
                precision: 10, 
                scale: 2, 
                as: "(price * quantity - discount) * (1 + tax_rate)", 
                stored: true

      # 2. Create a composite index on the generated column and status
      t.index [:total_amount, :status], name: "idx_orders_total_amount_and_status"
    end
  end
end
Enter fullscreen mode Exit fullscreen mode

2. How big the write (INSERT / UPDATE) overhead gets

When you use a calculated index or a generated column, every time even one of the target columns is INSERTed or UPDATEd, a "recalculation + B-Tree index reshuffle" happens.

The performance impact depends less on "the number of rows" and more on "the number of writes/updates per second (TPS)."

Alert level Writes/updates per second (TPS) Impact on the app, assessment, recommended action
Level 1: Safe Up to ~50/sec Typical web systems and e-commerce sites. Finishes in under a millisecond.
No action needed (go ahead and use it)
Level 2: Caution 100–500/sec Traffic spikes and social media apps. Write latency rises slightly.
Keep monitoring (watch the slow query log)
Level 3: Warning 1,000+/sec IoT data ingestion, financial transactions, heavy batch processing. CPU usage spikes and lock contention occurs.
Action required (the architecture needs rethinking)

At what "data volume" does the impact get big?

As data grows, performance drops sharply at "the point where the index itself no longer fits in memory (RAM)."

  • Size that fits in memory (up to a few million rows):
    All index updates complete in memory (RAM), so even with the calculation overhead, they finish in under a millisecond (a few milliseconds).

  • Size that doesn't fit in memory (tens of millions to hundreds of millions of rows):
    Every index update causes reads/writes to disk (SSD/HDD). At that point, the latency of a single UPDATE jumps from a few milliseconds to anywhere from a few milliseconds to several seconds.

"Worst case" patterns that tend to be especially slow

Even for the same writes, the following patterns have especially large overhead.

  • It includes columns that are updated super frequently (e.g. updated_at or status)
    Amounts like price rarely change once they're set, but if status or updated_at is in the formula, every status change triggers a recalculation & index reshuffle.

  • You do bulk updates (UPDATE ... WHERE or BULK INSERT)
    Updating one row at a time only costs a few milliseconds of latency, but when you bulk-update 100,000 rows like UPDATE orders SET price = price * 1.1, 100,000 recalculations and index updates run at once, and the DB freezes for anywhere from a few seconds to a few minutes.

Top comments (0)