Database Performance Tuning: Indexes, Profiling & Query Optimization

Bottleneck detection with profiling - database performance tuning

Step 1: establish a measurable baseline. In PostgreSQL use psql with EXPLAIN (ANALYZE, BUFFERS) and read query-level total time from pg_stat_statements. Capture the pre-change state with the example commands and run the same commands after the test to compare. Concrete commands:

-- PostgreSQL: baseline ölçümü
psql -d mydb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;"
-- Toplam süreleri pg_stat_statements'ten almak için
psql -d mydb -c "SELECT query, calls, total_time, mean_time FROM pg_stat_statements WHERE query LIKE '%FROM orders WHERE customer_id%';"

Why this measurement? EXPLAIN (ANALYZE, BUFFERS) gives detailed CPU/timing and buffer I/O counts; pg_stat_statements shows the total impact of hot queries. After changes measure reductions in metrics like 'total_time' and 'shared_blks_read' as a percentage: before = A, after = B, improvement = (A-B)/A*100.

For SQL Server use a similar approach: capture a baseline with Query Store or sys.dm_exec_query_stats, then make changes and run again. Example T-SQL to enable Query Store and get basic query statistics:

-- SQL Server: Query Store açma
ALTER DATABASE [MyDb] SET QUERY_STORE = ON;
-- Özet istatistikler
SELECT qsqt.query_sql_text, rs.avg_duration, rs.count_executions
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query q ON qsqt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Index design, fillfactor and rebuild strategy - database indexing

The goal of index strategy is not just adding indexes; control page density and page-splits to reduce write cost. In SQL Server you can set FILLFACTOR during rebuild to determine page fullness. Example:

-- SQL Server: indeks rebuild ile fillfactor
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (FILLFACTOR = 80);
-- Alternatif reorganize (çevrim içi, daha az maliyetli)
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;

PostgreSQL online reindex alternatives: CREATE INDEX CONCURRENTLY and pg_repack. pg_repack provides online repack/reindex for large tables. Installation and usage example:

# sistemde pg_repack kurulu olmalı
pg_repack --table 'public.orders' --dbname=mydb --host=dbhost --username=repuser
# ya da PostgreSQL'de yeni indeks oluşturup eskiyi drop'lamak
psql -d mydb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC);"

Measure bloat and physical statistics: use pgstattuple in PostgreSQL to assess index and table bloat, and sys.dm_db_index_physical_stats in SQL Server to inspect fragmentation and page counts. Example PostgreSQL query:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('public.orders');

These metrics — dead_tuple_count, free_percent — indicate how much physical data needs reorganization.

Query plan and statistics tuning - database optimization

Most plan regressions are caused by poor row estimates. In PostgreSQL increasing the statistics target for a column and running ANALYZE often produces a better plan. Example:

-- PostgreSQL: tek kolon için daha fazla histogram detayı
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 200;
ANALYZE VERBOSE orders;

Why this works? A higher statistics_target produces more histogram bins and better selectivity estimates; this affects join ordering and index vs seq scan decisions. It has a cost: ANALYZE takes longer and consumes more disk/CPU, so apply it only to high-impact columns.

On the SQL Server side use UPDATE STATISTICS and DBCC SHOW_STATISTICS; capture problematic plans with Extended Events or Query Store. Example:

-- SQL Server: tek tablo için istatistik güncelleme
UPDATE STATISTICS dbo.Orders (IX_Orders_CustomerId) WITH FULLSCAN;
-- Histogram kontrolü
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerId');

Indexes and data locality for NoSQL workloads - NoSQL training

MongoDB example: use explain('executionStats') to verify the query actually uses the index and how many documents were scanned. Command:

// Mongo shell
db.orders.createIndex({customerId: 1, createdAt: -1});
db.orders.find({customerId: 123}).sort({createdAt: -1}).explain('executionStats');

In executionStats, totalDocsExamined and indexKeysExamined show the real cost.

Considerations for NoSQL index selection: many small indexes increase write cost; a single compound index that covers both filter and sort can yield a covered query. Use mongostat and mongotop for measurement:

# terminal
mongostat --host dbhost:27017 --rowcount 5
mongotop 1 5

These tools show I/O, insert/update rates and query cadence.

Edge case: indexing low-cardinality fields (e.g. boolean) in MongoDB is often harmful; in sharded clusters the shard key choice directly affects write patterns and data locality. Test by running workload simulations and compare indexKeysExamined and p99 latency before/after.

Frequently Asked Questions

Which metrics should I check first for database optimization?

Measure latency and throughput first, then correlate I/O and CPU. For PostgreSQL use EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements total_time and shared_blks_read; at system level use iostat -x 1 and vmstat 1. For SQL Server use Query Store and sys.dm_exec_query_stats; for disk I/O check perfmon counters like LogicalDisk\Avg. Disk sec/Read. Baseline -> change -> re-measure and report percentage improvement.

Which profiling tools should I prioritize during SQL Server training?

Prioritize: Query Store (query performance history and plan comparison), Extended Events (lightweight, granular event capture) and sys.dm_exec_* DMVs (real-time). Example XE session creation:

CREATE EVENT SESSION [LongRunning] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(WHERE duration > 1000000)
ADD TARGET package0.event_file(SET filename=N'longruns.xel');
ALTER EVENT SESSION [LongRunning] ON SERVER STATE = START;

How to test index selection in MongoDB for NoSQL training?

Run your real queries with explain("executionStats") and compare indexKeysExamined vs totalDocsExamined. Also monitor write/read rates with mongostat. Example flow: 1) capture the current query with explain, 2) create the new index (db.collection.createIndex), 3) run the same query explain again, 4) if totalKeysExamined decreased and executionTime dropped, the index is appropriate.

When should I choose rebuild vs reorganize for database indexing?

For SQL Server use fragmentation thresholds: if avg_fragmentation_in_percent > 30 then REBUILD, if 5-30 then REORGANIZE. Decide using a query like:

SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('MyDb'), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');

For PostgreSQL use CREATE INDEX CONCURRENTLY or pg_repack for large tables.