Impact of Database Indexing on Web Application Performance

Experimental study of PostgreSQL B-tree indexing and its impact on database and web application performance.

Overview

This repository contains the code, experimental results, figures, and reproducibility materials for:

Impact of Database Indexing on Web Application Performance: An Experimental Study Using PostgreSQL and FastAPI

The experiment evaluates how workload-specific B-tree indexes affect:

SQL query performance
API response time
Concurrent throughput
Performance as the database grows
Storage and write overhead

For the complete methodology and analysis, see the full research paper linked below.

Results at a Glance

At 420,000 total rows:

Metric	Without Indexes	With Indexes	Result
Database execution — Q1–Q5	67.55 ms	0.76 ms	98.9% lower
API latency — Q1–Q4	17.42 ms	2.28 ms	86.9% lower
Q1 throughput	255.4 req/s	1,241.5 req/s	4.86×
Q2 throughput	67.3 req/s	406.1 req/s	6.04×
Q3 throughput	34.3 req/s	1,042.5 req/s	30.39×
Q4 throughput	87.3 req/s	1,167.7 req/s	13.37×
Secondary-index storage	—	23.2 MB	+72.5%
Bulk insert time	26.28 ms	96.49 ms	+267.1%

Main finding: appropriate indexes can dramatically improve read performance, while introducing measurable storage and write costs.

Experimental Setup

The benchmark uses a simple web-application database:

users
products
orders

Five representative queries were tested:

Query	Workload
Q1	Email lookup
Q2	Status + date filtering
Q3	Latest orders
Q4	Three-table join
Q5	Primary-key lookup

The indexed configuration adds four B-tree indexes:

CREATE INDEX idx_users_email
    ON users (email);

CREATE INDEX idx_orders_status_date
    ON orders (status, order_date);

CREATE INDEX idx_orders_order_date
    ON orders (order_date);

CREATE INDEX idx_orders_user_date
    ON orders (user_id, order_date DESC);

Dataset Scaling

The experiment was repeated from 160K to 4.1M rows:

Scale	Users	Products	Orders	Total
S1	50K	10K	100K	160K
S2	100K	20K	300K	420K
S3	300K	50K	1M	1.35M
S4	1M	100K	3M	4.10M

Database Results

At the reference scale:

Query	Without Index	With Index	Reduction
Q1	4.793 ms	0.0167 ms	99.65%
Q2	15.204 ms	0.666 ms	95.62%
Q3	36.514 ms	0.0159 ms	99.96%
Q4	11.028 ms	0.054 ms	99.51%
Q5	0.0091 ms	0.0104 ms	—

Q5 is the control query because its primary key is already indexed.

Scaling Behavior

Q1 — Email Lookup

Dataset	Without Index	With Index
160K	2.333 ms	0.0176 ms
420K	4.793 ms	0.0167 ms
1.35M	14.466 ms	0.0182 ms
4.10M	38.302 ms	0.0181 ms

Q3 — Latest Orders

Dataset	Without Index	With Index
160K	20.549 ms	0.0179 ms
420K	36.514 ms	0.0159 ms
1.35M	114.305 ms	0.0175 ms
4.10M	328.351 ms	0.0184 ms

The indexed workloads remained comparatively stable while scan-heavy workloads increased with dataset size.

API Performance

At 8 concurrent clients:

Endpoint	Latency A	Latency B	Throughput A	Throughput B
Q1	7.34 ms	1.61 ms	255.4	1,241.5
Q2	17.24 ms	3.53 ms	67.3	406.1
Q3	31.35 ms	1.88 ms	34.3	1,042.5
Q4	13.76 ms	2.09 ms	87.3	1,167.7

Average Q1–Q4 latency:

17.42 ms → 2.28 ms

86.9% lower.

Concurrency

The benchmark also evaluates:

1 → 2 → 4 → 8 → 16 → 32 clients

For Q3 at 32 clients:

Metric	Without Index	With Index
Mean latency	973 ms	33 ms
P95 latency	1,354 ms	43.8 ms
Throughput	27.2 req/s	835 req/s

Visual Results

Database Performance

API Performance

Cost of Indexing

Indexes also introduce overhead.

Storage

Secondary indexes: ~23.2 MB

Writes

Without indexes : 26.28 ms
With indexes    : 96.49 ms

Write overhead: +267.1%

This highlights the read/write trade-off of indexing.

Environment

Component	Version
Ubuntu	24.04.4 LTS
PostgreSQL	16.13
Python	3.11.15
FastAPI	0.141.1
Uvicorn	0.46.0
CPU	2 × Xeon vCPU @ 2.80 GHz
RAM	7.8 GiB

Reproducibility

Install dependencies:

pip install "psycopg[binary]" psycopg_pool fastapi uvicorn httpx matplotlib python-docx

Run the main experiments:

python3 bench_db.py
python3 scale_bench.py

Run the API benchmark:

python3 -m uvicorn app:app --host 127.0.0.1 --port 8100 --workers 1

python3 bench_api.py A
python3 bench_conc.py A

Then run Configuration B after creating the indexes:

python3 bench_api.py B
python3 bench_conc.py B

Generate figures and the paper:

python3 figures.py
python3 build_paper.py

Repository Structure

File	Purpose
`gen.sql`	Schema and data generation
`queries.py`	Queries and index definitions
`bench_db.py`	Database benchmark
`scale_bench.py`	Scaling benchmark
`app.py`	FastAPI service
`bench_api.py`	API benchmark
`bench_conc.py`	Concurrency benchmark
`figures.py`	Figure generation
`build_paper.py`	Paper generation
`results_*.json`	Raw measurements
`fig*.png`	Benchmark figures

Key Takeaway

The experiment demonstrates that carefully selected B-tree indexes can significantly improve database-backed web applications:

Database execution
67.55 ms → 0.76 ms

API latency
17.42 ms → 2.28 ms

Measured throughput improvement
up to 30.4×

At the same time:

Storage overhead  ≈ +72.5%
Write overhead    ≈ +267.1%

Index for the workload, measure the result, and consider both read performance and maintenance cost.

Research Paper

For the complete methodology, analysis, scaling study, discussion, limitations, and conclusions, read the full research paper: