Most databases are static.
You create a table.
You create some indexes.
You deploy the application.
Then you hope the workload behaves the way you predicted.
But production workloads do not care about your predictions.
A query that ran once per hour during development can suddenly run 50,000 times per minute. A column that was rarely filtered yesterday can become the most important access path tomorrow. A new feature can completely change the shape of your workload.
And yet, the database may continue using the same indexes you designed months ago.
This creates an interesting question:
What if a database could watch its workload and change its indexes automatically?
Not simply rebuild an index when it becomes fragmented.
Not merely recommend indexes through an administrator dashboard.
But actually observe queries, measure their behavior, determine which indexes would improve performance, create those indexes when justified, remove indexes that no longer provide value, and continuously adapt as the workload changes.
That is the idea behind an adaptive database.
In this article, we are going to design one from scratch.
The goal is not to build a production replacement for PostgreSQL, MySQL, or another mature database.
The goal is more interesting:
We are going to understand the architecture behind a database that can learn from its workload.
The Database Is a Living System
Traditional database design looks something like this:
Developer
|
v
Schema
|
v
Indexes
|
v
Queries
|
v
Production
The indexes are mostly determined before the real workload arrives.
An adaptive database changes the relationship:
+------------------+
| Query Workload |
+--------+---------+
|
v
+------------------+
| Workload Monitor |
+--------+---------+
|
v
+------------------+
| Query Analyzer |
+--------+---------+
|
v
+------------------+
| Index Advisor |
+--------+---------+
|
v
+------------------+
| Cost Evaluator |
+--------+---------+
|
+--------+--------+
| |
CREATE DROP
| |
+--------+--------+
|
v
+------------------+
| Index Manager |
+------------------+
|
v
+------------------+
| Database Engine |
+------------------+
The important part is the feedback loop.
The database observes itself.
It makes a decision.
It changes itself.
Then it observes the consequences.
This is much closer to a control system than a traditional static configuration.
Start With the Workload
Before creating indexes automatically, we need to know what the database is actually doing.
Imagine a table:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT,
status VARCHAR(30),
country VARCHAR(50),
total DECIMAL(12,2),
created_at TIMESTAMP
);
Suppose our application generates queries like:
SELECT *
FROM orders
WHERE customer_id = 42;
and:
SELECT *
FROM orders
WHERE status = 'pending';
and:
SELECT *
FROM orders
WHERE country = 'Zambia'
AND status = 'pending';
Initially, we may have only:
PRIMARY KEY (id)
The database starts observing.
For every query, we can record something like:
query_id
table
columns_used
predicates
frequency
execution_time
rows_examined
rows_returned
For example:
Query: Q17
Table:
orders
Predicates:
customer_id = ?
Frequency:
82,400/min
Average execution:
71ms
Rows examined:
1,200,000
Rows returned:
4
This is an enormous signal.
The database does not need a human to tell it that customer_id might deserve an index.
The workload is telling us.
The Query Fingerprint
One of the first problems we encounter is that applications rarely execute the exact same SQL string.
Consider:
SELECT *
FROM orders
WHERE customer_id = 42;
and:
SELECT *
FROM orders
WHERE customer_id = 781;
These are logically the same workload pattern.
We need to normalize them.
The database can transform both into:
SELECT *
FROM orders
WHERE customer_id = ?;
This becomes a query fingerprint.
A fingerprint might contain:
fingerprint = hash(
normalized_sql
)
For example:
8f3c91ab
Then the database can maintain:
Fingerprint Count Avg Time
------------------------------------
8f3c91ab 2,841,201 68ms
9a21cc71 381,211 121ms
72be19fd 92,101 9ms
Now we can reason about workload patterns instead of individual queries.
Measuring Query Pain
Frequency alone is not enough.
Suppose we have two queries.
Query A
Runs:
1,000,000 times/day
Average:
2ms
Query B
Runs:
10,000 times/day
Average:
800ms
Which deserves attention?
Both might.
A useful metric is cumulative execution cost:
total_cost =
frequency × average_execution_time
Therefore:
Query A:
1,000,000 × 2ms
= 2,000 seconds
Query B:
10,000 × 800ms
= 8,000 seconds
Query B consumes much more execution time despite running less frequently.
We could therefore rank workload patterns by:
impact =
frequency
× execution_time
× rows_examined
The exact formula can evolve.
The important concept is that the database needs a way to determine:
Which queries are causing the most pain?
Candidate Index Generation
Now we reach the interesting part.
Suppose the database observes:
SELECT *
FROM orders
WHERE customer_id = ?;
It can generate:
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
For:
SELECT *
FROM orders
WHERE status = ?
AND country = ?;
it might generate:
CREATE INDEX idx_orders_status_country
ON orders(status, country);
But candidate generation becomes complicated very quickly.
Consider:
SELECT *
FROM orders
WHERE country = ?
AND status = ?
ORDER BY created_at DESC;
Possible indexes include:
(country)
(status)
(country, status)
(status, country)
(country, status, created_at)
(status, country, created_at)
We cannot blindly create all of them.
That would destroy the database.
Indexes consume:
- disk space
- memory
- CPU during writes
- background maintenance
- cache capacity
An adaptive database therefore needs an index candidate system.
Indexes Have a Cost
An index is not free.
For a read-heavy workload, an index can be extremely valuable.
For a write-heavy workload, every additional index introduces maintenance work.
Suppose:
INSERT INTO orders (...)
VALUES (...);
Without secondary indexes, the database might update one structure.
With five secondary indexes, the same insert may need to update six structures.
Therefore we can think of an index as:
Index Value =
Read Benefit
-
Write Cost
-
Storage Cost
-
Maintenance Cost
This is the core economic model of adaptive indexing.
We are not asking:
Can this index make a query faster?
We are asking:
Is making this index worth the cost?
Estimating Selectivity
One of the most important concepts in index optimization is selectivity.
Suppose we have one million orders.
If:
status = 'pending'
matches 500,000 rows, that predicate may be relatively unselective.
But:
customer_id = 93812
might match only 15 rows.
An index on customer_id can therefore be much more useful.
We can estimate:
selectivity =
matching_rows / total_rows
Lower selectivity means fewer rows match.
For example:
customer_id:
15 / 1,000,000
= 0.000015
status:
500,000 / 1,000,000
= 0.5
The first predicate provides a much narrower search.
But selectivity alone cannot determine index usefulness.
A low-selectivity column may still be useful when combined with another predicate.
That is why adaptive indexing needs workload context.
Composite Indexes
This is where our system becomes more intelligent.
Consider:
SELECT *
FROM orders
WHERE country = 'Zambia'
AND status = 'pending';
An adaptive database might initially observe that both columns are commonly queried.
It could consider:
(country)
(status)
(country, status)
(status, country)
How do we choose?
We examine workload patterns.
Suppose we observe:
country + status:
70% of relevant queries
status only:
20%
country only:
10%
A composite index might provide greater benefit.
But column order matters.
An index:
(country, status)
is not equivalent to:
(status, country)
because B-tree traversal follows the index's ordering structure.
Our index advisor therefore needs to model not just columns, but query access patterns.
The Index Advisor
We can now design our advisor.
class IndexAdvisor:
def analyze(workload):
candidates = generate_candidates(workload)
for index in candidates:
benefit = estimate_benefit(index, workload)
cost = estimate_cost(index)
score = benefit - cost
if score > threshold:
recommend(index)
A more useful conceptual model is:
score(index) =
expected_query_savings
-
write_penalty
-
storage_penalty
-
maintenance_penalty
The advisor should also consider how often the affected queries run.
A theoretical index saving 500ms on a query executed twice per day may not justify itself.
A 5ms improvement executed millions of times might.
The Database Needs a Memory
An adaptive database cannot make decisions from a single query.
It needs historical workload information.
For example:
Index:
idx_orders_customer_id
Created:
2026-09-01
Queries improved:
Q17, Q23
Reads served:
82,100,000
Average savings:
43ms
Write overhead:
7ms
Storage:
180MB
Now imagine the workload changes.
After several weeks:
Queries using index:
12/day
Meanwhile another index is handling millions of queries.
The database should eventually ask:
Why am I still maintaining this?
This creates the second half of adaptive indexing.
Index deletion.
Automatic Index Removal
Creating indexes is relatively easy.
Deleting them safely is much harder.
Imagine:
DROP INDEX idx_orders_customer_id;
What if the query workload changes tomorrow?
What if a hidden application query still depends on it?
What if the index is supporting a rare but critical operation?
A serious adaptive system needs confidence before removal.
We can define states:
ACTIVE
|
v
UNDERUSED
|
v
CANDIDATE_FOR_REMOVAL
|
v
SHADOWED
|
v
DROPPED
Instead of immediately deleting an index, we place it into a grace period.
For example:
No meaningful usage for:
30 days
Then we re-evaluate.
Shadow Mode
One particularly interesting technique is shadow mode.
Suppose we think an index is useless.
Instead of deleting it immediately, we can stop considering it during query planning while continuing to monitor what would happen.
Conceptually:
Actual planner:
uses other indexes
Shadow planner:
tests hypothetical old index
We compare:
actual_plan_cost
vs
plan_with_index_cost
If the index provides no meaningful benefit, confidence increases.
This allows the database to become conservative.
Adaptive systems should not make irreversible decisions too quickly.
Hypothetical Indexes
There is another powerful idea:
What if we could evaluate an index without actually creating it?
Suppose the database wants to know whether:
CREATE INDEX idx_orders_country_status
ON orders(country, status);
would improve a query.
We can construct a hypothetical index representation:
HypotheticalIndex {
table: orders
columns: [country, status]
type: BTree
}
Then the query optimizer can estimate the plan as though the index existed.
This gives us:
Current plan:
Sequential Scan
Estimated cost: 18420
Hypothetical index:
Index Scan
Estimated cost: 312
That is powerful.
We can evaluate thousands of possible indexes without actually creating thousands of physical structures.
The Feedback Loop
Now the architecture starts looking like a machine-learning system.
Observe
|
v
Measure
|
v
Generate Candidates
|
v
Simulate
|
v
Choose
|
v
Apply
|
v
Measure Results
|
+------------------+
|
v
Observe
The system does not simply optimize once.
It continuously learns from consequences.
Suppose it creates:
idx_orders_customer_id
Before:
Average latency:
71ms
After:
Average latency:
4ms
The system records:
observed_benefit = 67ms
But perhaps write latency increases:
INSERT:
3ms -> 5ms
The real net benefit is therefore:
read savings
-
write overhead
This feedback lets future decisions become better.
Avoiding Oscillation
Adaptive systems have a dangerous failure mode:
oscillation.
Imagine the database does this:
Create index
↓
Reads become faster
↓
Writes become slower
↓
Drop index
↓
Writes become faster
↓
Reads become slower
↓
Create index
↓
The database is constantly changing itself.
That is terrible.
We need hysteresis.
For example:
Create threshold:
+1000 benefit units
Drop threshold:
-500 benefit units
The thresholds are deliberately different.
An index must become significantly valuable before creation.
And it must become significantly useless before removal.
We can also introduce a minimum lifetime:
minimum_index_age = 7 days
An index created yesterday cannot be dropped today.
Rate Limiting Schema Changes
Index creation itself can be expensive.
Suppose our adaptive engine discovers:
1,000 candidate indexes
We absolutely should not create them all.
We need a schema-change scheduler.
IndexJob {
type: CREATE
table: orders
index: idx_orders_customer_id
priority: 87
}
The scheduler can enforce:
max concurrent index builds = 1
or:
max disk growth/hour = 2GB
or:
max write amplification = 10%
This converts index adaptation into controlled background work.
Protecting the Database
An adaptive database should never be allowed to optimize itself into destruction.
We need safety constraints.
For example:
max_indexes_per_table = 12
max_index_size = 50GB
max_daily_index_growth = 10GB
max_write_overhead = 15%
min_query_frequency = 100/hour
These are guardrails.
The system might discover that a particular index would improve a query by 90%.
But if that index consumes 300GB on a 500GB database, the system should reconsider.
Optimization always exists inside constraints.
Index Merging and Consolidation
Another advanced feature is recognizing redundant indexes.
Suppose we have:
idx_orders_customer
(customer_id)
idx_orders_customer_created
(customer_id, created_at)
The second index may already support many queries that use customer_id.
Our advisor should recognize index relationships.
We can represent indexes as sets:
A = [customer_id]
B = [customer_id, created_at]
If B covers the access patterns of A, maintaining A may not be worthwhile.
The system can therefore perform index consolidation.
Instead of:
5 indexes
we might reduce the table to:
3 strategically chosen indexes
while preserving query performance.
A Simple Cost Model
Let's build a simplified mathematical model.
For an index I:
Benefit(I) =
Σ(query_frequency × latency_saved)
Then:
Cost(I) =
write_frequency × write_penalty
+
storage_cost
+
maintenance_cost
Therefore:
NetValue(I) =
Benefit(I) - Cost(I)
Suppose:
Query frequency:
2,000,000/day
Latency savings:
10ms
Then:
Benefit =
2,000,000 × 0.010
=
20,000 seconds/day
Now suppose writes introduce:
5ms overhead
across:
100,000 writes/day
Then:
Write cost =
100,000 × 0.005
=
500 seconds/day
Ignoring other costs:
Net value =
19,500 seconds/day
This index looks attractive.
The numbers are simplified, but the architecture is real.
Learning the Workload
Now we can go beyond static heuristics.
Imagine the system tracks:
hour
query
frequency
latency
rows_scanned
rows_returned
We may discover patterns such as:
09:00–12:00
customer lookup dominates
12:00–14:00
inventory queries dominate
18:00–22:00
order history dominates
The database can therefore become workload-aware.
This raises a fascinating possibility:
indexes could become time-aware.
Perhaps a workload is seasonal.
During a major shopping period:
orders by status
becomes dominant.
After the event:
customer history
returns to dominance.
An adaptive database can respond to the workload rather than assuming it is permanent.
But Don't Rebuild Everything Every Hour
A naive implementation would continuously run expensive analysis.
That would defeat the purpose.
Instead, we can use sampling.
For example:
collect:
100% of slow queries
10% of fast queries
1% of trivial queries
Or maintain aggregated counters directly inside the database.
We could store:
query_fingerprint
execution_count
total_latency
max_latency
rows_examined
last_seen
Then:
avg_latency =
total_latency / execution_count
This gives us useful information without storing every query event.
Architecture of the Adaptive Engine
A practical prototype could look like this:
+-----------------------------+
| Application |
+--------------+--------------+
|
v
+-----------------------------+
| Query Engine |
+--------------+--------------+
|
+--------------------+
| |
v v
+---------------+ +-------------+
| Query Metrics | | Query Plan |
+-------+-------+ +------+------+
| |
+---------+----------+
|
v
+----------------+
| Workload Store |
+-------+--------+
|
v
+----------------+
| Index Advisor |
+-------+--------+
|
v
+----------------+
| Cost Simulator |
+-------+--------+
|
v
+----------------+
| Policy Engine |
+-------+--------+
|
v
+----------------+
| Schema Worker |
+-------+--------+
|
v
Database
The policy engine is important.
It decides whether the advisor is actually allowed to change the schema.
This separation prevents the optimization algorithm from having unlimited authority.
A Prototype Algorithm
We can write a simplified loop:
def adaptive_cycle():
workload = collect_workload()
hot_queries = rank_queries(workload)
candidates = []
for query in hot_queries:
candidates.extend(
generate_index_candidates(query)
)
candidates = deduplicate(candidates)
for index in candidates:
current_cost = estimate_current_cost(index.table)
hypothetical_cost = estimate_cost_with_index(index)
benefit = current_cost - hypothetical_cost
write_cost = estimate_write_penalty(index)
storage_cost = estimate_storage_cost(index)
score = (
benefit
- write_cost
- storage_cost
)
if score > CREATE_THRESHOLD:
schedule_create(index)
for index in existing_indexes():
usage = measure_index_usage(index)
if usage < DROP_THRESHOLD:
schedule_removal_review(index)
This is not a complete database optimizer.
But it captures the central architecture.
The Hardest Problem: Causality
There is one subtle problem.
Suppose we create an index and query latency drops.
Did the index cause the improvement?
Maybe.
But maybe:
- cache warmed up
- traffic decreased
- the application changed
- the dataset changed
- another index was created
- the query plan changed
An adaptive database needs to reason about causality.
One approach is controlled experimentation.
For selected workloads, we can compare:
baseline plan
against:
candidate plan
using sampled queries.
We can also record before-and-after measurements:
Index creation:
10:00
Before:
p95 = 180ms
After:
p95 = 24ms
The more controlled the experiment, the stronger the evidence.
The Database as a Control System
At this point, something interesting becomes visible.
We are no longer merely designing a database feature.
We are designing a feedback controller.
The database has:
Input:
Query workload
State:
Indexes + statistics
Observation:
Latency + execution plans + resource usage
Controller:
Index advisor
Action:
Create/drop indexes
Feedback:
Measured performance
That gives us:
+----------------------+
| |
v |
Workload → Database → Metrics → Controller
^ |
| |
+---- Actions
This is why adaptive databases are such an interesting systems project.
They combine:
- databases
- algorithms
- observability
- optimization
- statistics
- distributed systems
- control theory
- resource management
Going Distributed
Now imagine the database is distributed.
We have:
Node A
Node B
Node C
Node D
Should every node independently create indexes?
Probably not.
Otherwise we could get:
Node A:
create index X
Node B:
create index Y
Node C:
create index X
Node D:
create index Z
Now schema state diverges.
Instead, we need an index control plane.
+----------------+
| Index Control |
| Plane |
+-------+--------+
|
+-----------+-----------+
| | |
v v v
Node A Node B Node C
The control plane can determine:
desired_index_state
and distribute schema changes.
For example:
{
"table": "orders",
"indexes": [
["customer_id"],
["country", "status"],
["created_at"]
],
"version": 42
}
Each node converges toward version 42.
Now adaptive indexing becomes a distributed state-management problem.
What Happens During Failure?
Imagine the database decides to build:
idx_orders_country_status
Then the node crashes halfway through.
We need:
IndexJob {
id: 781
status: BUILDING
}
After restart:
BUILDING
can transition to:
FAILED
and then:
RETRY
or:
CANCELLED
Schema changes therefore need their own durable job system.
This is another reason not to implement adaptive indexing as a simple background thread.
It is infrastructure.
Security Matters
An adaptive database can modify its own schema.
That is powerful.
It is also dangerous.
A compromised query workload could theoretically manipulate the optimizer into creating expensive indexes.
Imagine an attacker deliberately generates queries such as:
WHERE random_column_1 = ?
WHERE random_column_2 = ?
WHERE random_column_3 = ?
If our database blindly trusts workload signals, it could create thousands of indexes.
This becomes an index exhaustion attack.
Therefore adaptive indexing needs:
rate limits
storage limits
schema permissions
candidate limits
audit logs
human override
Every automatic schema change should be explainable.
For example:
Created index:
idx_orders_customer_id
Reason:
Query Q17 represented 38.2% of database execution time.
Estimated latency reduction:
71ms → 5ms
Estimated storage:
180MB
Estimated write overhead:
1.7%
That's much better than:
Index magically appeared.
Observability
The adaptive engine itself needs monitoring.
Expose metrics such as:
adaptive_indexes_created_total
adaptive_indexes_dropped_total
adaptive_index_build_failures_total
index_candidate_count
index_storage_bytes
estimated_query_savings
observed_query_savings
index_write_overhead
optimization_cycle_duration
We can also expose decisions:
[21:03:02]
Detected hot query Q17
[21:03:03]
Generated candidate:
(customer_id)
[21:03:03]
Estimated benefit:
12,400 units
[21:03:04]
Estimated cost:
1,200 units
[21:03:04]
Decision:
CREATE
[21:04:19]
Index created successfully
Now the system becomes understandable.
Don't Make It Fully Autonomous on Day One
A great engineering strategy is to build levels of autonomy.
Level 1 — Observe
The database only reports:
Potential index:
(customer_id)
Level 2 — Recommend
It produces:
Recommendation:
CREATE INDEX...
Level 3 — Simulate
It evaluates hypothetical indexes.
Level 4 — Auto-create
It automatically creates approved indexes.
Level 5 — Auto-remove
It removes indexes after confidence checks.
Level 6 — Continuous adaptation
The system continuously manages the index portfolio.
This progression makes the project much easier to test.
You don't have to build the final autonomous system immediately.
Testing the Adaptive Database
We need workloads that actually change.
For example:
Phase 1
90% customer queries
10% order queries
The system should favor:
customer_id
Then switch the workload.
Phase 2
20% customer queries
80% order status queries
Now the database should eventually adapt.
Then:
Phase 3
50% country + status
30% created_at
20% customer_id
The index portfolio should change again.
This is how we test whether the database is truly adaptive.
Benchmarking
A useful benchmark compares three configurations:
Static indexes
versus:
No indexes
versus:
Adaptive indexes
Measure:
p50 latency
p95 latency
p99 latency
throughput
CPU
memory
disk usage
write latency
index creation overhead
The important metric is not simply:
"Did adaptive indexing make reads faster?"
It is:
"Did adaptive indexing improve total workload efficiency?"
That distinction matters.
The Bigger Idea
There is a deeper lesson here.
For decades, software developers have configured databases manually.
We decide:
which indexes exist
which queries matter
which optimizations are worth the cost
But software is becoming increasingly dynamic.
Applications change.
Workloads change.
Data changes.
Traffic changes.
Therefore static infrastructure becomes increasingly interesting as a limitation.
An adaptive database asks a different question:
Instead of configuring the database once, can we build a database that continuously configures itself?
That idea extends far beyond indexes.
Imagine a database that automatically changes:
indexes
query plans
cache sizes
partition strategies
compression
replication levels
storage tiers
materialized views
Now we are no longer talking about an adaptive index system.
We are talking about an adaptive database engine.
The Ultimate Architecture
The mature version could look like this:
APPLICATION
|
v
+-------------------+
| Query Engine |
+---------+---------+
|
+----------------+----------------+
| |
v v
+-------------+ +-------------+
| Query Plans | | Execution |
| & Statistics| | Metrics |
+------+------+ +------+------+
| |
+----------------+----------------+
|
v
+-------------------+
| Workload Analyzer |
+---------+---------+
|
v
+-------------------+
| Optimization |
| Controller |
+---------+---------+
|
+------------+------------+
| | |
v v v
Indexes Caches Partitions
| | |
+------------+------------+
|
v
+-------------------+
| Database Storage |
+-------------------+
|
+------ feedback ------+
This is a database that does not simply execute workloads.
It responds to workloads.
Final Thoughts
Building an adaptive database that changes its indexes automatically is one of those projects that looks simple until you start pulling the thread.
At first, it sounds like:
Detect slow query.
Create index.
Done.
But that quickly becomes:
How do we fingerprint queries?
How do we measure workload impact?
How do we estimate selectivity?
How do we generate candidate indexes?
How do we evaluate composite indexes?
How do we simulate hypothetical indexes?
How do we calculate write amplification?
How do we prevent redundant indexes?
How do we safely remove indexes?
How do we prevent oscillation?
How do we handle failures?
How do we coordinate distributed nodes?
How do we prevent malicious workloads from manipulating the optimizer?
How do we prove that an index actually improved performance?
And suddenly you are designing an entire intelligent control plane around a database.
That's what makes the project fascinating.
The most interesting part isn't the SQL statement:
CREATE INDEX ...
The interesting part is the system that decides when that statement should exist at all.
A static database says:
"Tell me how you want me configured."
An adaptive database says:
"Show me how you use me, and I will continuously adapt."
That shift—from configuration to feedback—is much bigger than automatic indexing.
It is a different philosophy for building infrastructure.
Software doesn't always have to remain static after deployment.
Sometimes, the most powerful system is the one that watches what is happening, measures the consequences, learns from them, and changes itself accordingly.
And an adaptive database is a beautiful place to start.
Code is the machine. Architecture is the intelligence. Feedback is what makes the machine learn.
