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
);
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
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
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)