1. Introduction
In modern microservices and web application architectures, performance degradation rarely stems from CPU or memory exhaustion on the application application servers themselves. More often, performance bottlenecks occur downstream in the persistence layer. When an endpoint that normally responds in milliseconds begins taking several seconds or timing out entirely—while system resources appear completely normal—the database query execution path is the primary suspect.
This article details a real-world troubleshooting scenario (“Luxor”) where a customer analytics REST API experienced severe latency. We walk through a systematic SRE troubleshooting methodology: starting with baseline HTTP latency measurements, inspecting application source code, profiling database execution plans using EXPLAIN ANALYZE, identifying permission boundaries within PostgreSQL, applying an indexing fix, and validating sub-second endpoint SLAs.
By reading this guide, you will learn:
- How to isolate database-bound bottlenecks when host system metrics (CPU, RAM, disk I/O) are healthy.
- How to read and interpret PostgreSQL
EXPLAIN ANALYZEquery plans to identify costly full table scans (Seq Scan). - How PostgreSQL Role-Based Access Control (RBAC) and Linux Unix domain socket peer authentication dictate database administrative privileges.
- How to implement proper indexing strategies and safely apply schema changes in production environments without causing service disruption.
2. Problem Description and Environment
The operational team received reports that the customer sales lookup API endpoint was taking several seconds per request and frequently timing out under high load. Surprisingly, the host running the database and application showed no obvious signs of resource distress—CPU utilization, memory usage, and disk I/O remained well within healthy operational parameters.
Problem Specifications
- Target SLA: The endpoint
/api/customers/<customer_id>/sales/countmust complete requests with a total response time of less than 500 ms (0.5 seconds). - Observed Behavior: Requests consistently took ~1.66 seconds to complete.
- Service Name:
sad-analytics-api.servicerunning on port9090. - Application Path:
/opt/sad/analytics_api.py. - Database Credentials: User
saduser, Passwordsadpassword, Databaseanalytics_db, Target Tablesales_data.
Environment Architecture
[ Client / Curl ]
│ (HTTP GET on :9090)
▼
[ sad-analytics-api ] (Python 3 / Flask on Systemd)
│ (psycopg2 SQL Query over TCP/Socket)
▼
[ PostgreSQL Engine ] (Database: analytics_db | Table: sales_data)
3. Initial Investigation and Hypotheses
Before making any system configuration changes or database updates, we established a structured troubleshooting framework to evaluate potential root causes.
What Was Known vs. Unknown
- Known: The server host resources (CPU, RAM, disk space) were healthy. The API service was active and listening on port 9090.
- Unknown: The specific SQL statement executed by the API, the internal execution strategy used by PostgreSQL for that query, table index coverage, and table cardinality.
Formulated Hypotheses
| Hypothesis | Supporting Evidence | Evidence Against | Investigation Step |
|---|---|---|---|
| H1: Unindexed Database Column | CPU/Memory on host are idle, but HTTP request takes >1.5s; indicative of I/O wait during a Sequential Scan ($O(N)$). | None yet. | Run EXPLAIN ANALYZE on the underlying database query and inspect table schema (\d). |
| H2: Inefficient Application Logic | Python process processing large datasets inefficiently in user space. | Systemd status shows negligible CPU time (287ms) and memory (24.9M). | Inspect Python source code (/opt/sad/analytics_api.py). |
| H3: Database Connection Contention | Flask development server single-threaded execution causing request queuing. | Issue reproduces reliably on a single isolated test request. | Test raw query execution speed directly in psql. |
4. Troubleshooting Process
Step 1: Baseline API Latency and Service Status Check
Objective: Confirm the reported latency using client-side timing metrics and check the health of the systemd application service.
Commands Executed:
curl -s -w "\nTotal time: %{time_total}s\n" http://127.0.0.1:9090/api/customers/1234/sales/count
systemctl status sad-analytics-api
Command Explanation:
-
curl: Client URL transfer tool. -
-s(--silent): Suppresses the standard transfer progress meter. -
-w "\nTotal time: %{time_total}s\n": Appends custom output displaying the exact duration of the request in seconds. -
systemctl status: Displays runtime status, PID, resource usage, and recent systemd journal logs forsad-analytics-api.service.
Output and Analysis:
{"count":986,"customer_id":1234}
Total time: 1.665438s
● sad-analytics-api.service - SadServers analytics API
Loaded: loaded (/etc/systemd/system/sad-analytics-api.service; enabled; preset: enabled)
Active: active (running) since Mon 2026-11-09 05:21:30 UTC; 1min 12s ago
Main PID: 936 (python3)
Tasks: 1 (limit: 501)
Memory: 24.9M (peak: 25.1M)
CPU: 287ms
CGroup: /system.slice/sad-analytics-api.service
└─936 /usr/bin/python3 /opt/sad/analytics_api.py
Decision & Interpretation:
The API endpoint returned a valid JSON payload ({"count":986,"customer_id":1234}), but the response time was 1.665 seconds—over three times the 500 ms target threshold.
The service status confirmed the Flask application ran as PID 936 using only 24.9 MB of RAM and 287 ms of total CPU time. Because the application process itself spent minimal time using CPU resources, the delay occurred while blocking on an external dependency (the PostgreSQL database query).
Step 2: Inspect Application Source Code
Objective: Inspect how /opt/sad/analytics_api.py constructs and executes the database query.
Command Executed:
cat -n /opt/sad/analytics_api.py
Command Explanation:
cat: Concatenates and prints file contents to standard output.-n: Prepends line numbers to every line of output to assist with code references.
Output and Analysis:
1 #!/usr/bin/env python3
2 """Analytics API — customer sales lookups backed by PostgreSQL."""
3
4 from flask import Flask, jsonify
5 import psycopg2
6
7 app = Flask(__name__)
8
9 DB_CONFIG = {
10 "dbname": "analytics_db",
11 "user": "saduser",
12 "password": "sadpassword",
13 }
14
15
16 def get_customer_sales_count(customer_id: int) -> int:
17 conn = psycopg2.connect(**DB_CONFIG)
18 try:
19 with conn.cursor() as cur:
20 cur.execute(
21 "SELECT COUNT(*) FROM sales_data WHERE customer_id = %s",
22 (customer_id,),
23 )
24 return cur.fetchone()[0]
25 finally:
26 conn.close()
27
28
29 @app.route("/health")
30 def health():
31 return jsonify({"status": "ok"})
32
33
34 @app.route("/api/customers/<int:customer_id>/sales/count")
35 def customer_sales_count(customer_id):
36 count = get_customer_sales_count(customer_id)
37 return jsonify({"customer_id": customer_id, "count": count})
38
39
40 if __name__ == "__main__":
41 app.run(host="0.0.0.0", port=9090)
Decision & Interpretation: The code investigation confirmed that the Python layer was clean and efficient:
- It delegates aggregation directly to SQL via
SELECT COUNT(*) FROM sales_data WHERE customer_id = %s. - It does not perform inefficient row fetching or application-level filtering loops.
- This isolated the root bottleneck directly to PostgreSQL’s execution of
SELECT COUNT(*) FROM sales_data WHERE customer_id = 1234.
Step 3: Analyze Database Execution Plan and Table Schema
Objective: Evaluate the query execution plan using PostgreSQL’s EXPLAIN ANALYZE utility and inspect existing table indexes.
Commands Executed:
PGPASSWORD='sadpassword' psql -U saduser -d analytics_db -c "EXPLAIN ANALYZE SELECT COUNT(*) FROM sales_data WHERE customer_id = 1234;"
PGPASSWORD='sadpassword' psql -U saduser -d analytics_db -c "\d sales_data"
Command Explanation:
-
PGPASSWORD='sadpassword': Inline environment variable to authenticate non-interactively. -
psql: Interactive command-line client for PostgreSQL. -
-U saduser: Database user connection handle. -
-d analytics_db: Target database. -
-c "...": Executes a single SQL command or metacommand and immediately exits. -
EXPLAIN ANALYZE: Analyzes the query execution strategy (cost, operations) and runs the query to measure exact elapsed execution timing. -
\d sales_data: Metacommand to describe table schema, column types, constraints, and indexes.
Output and Analysis:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=68710.13..68710.14 rows=1 width=8) (actual time=1487.786..1490.492 rows=1 loops=1)
-> Gather (cost=68709.91..68710.12 rows=2 width=8) (actual time=1487.778..1490.485 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=67709.91..67709.92 rows=1 width=8) (actual time=1483.502..1483.503 rows=1 loops=3)
-> Parallel Seq Scan on sales_data (cost=0.00..67708.88 rows=415 width=0) (actual time=3.550..1483.188 rows=329 loops=3)
Filter: (customer_id = 1234)
Rows Removed by Filter: 1666338
Planning Time: 0.242 ms
Execution Time: 1490.600 ms
Table "public.sales_data"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('sales_data_id_seq'::regclass)
product_id | integer | | not null |
customer_id | integer | | not null |
sale_date | timestamp without time zone | | not null |
amount | numeric(10,2) | | not null |
region | character varying(50) | | |
Indexes:
"sales_data_pkey" PRIMARY KEY, btree (id)
Decision & Interpretation: The output provided definitive evidence of the bottleneck:
- Parallel Sequential Scan: PostgreSQL executed a
Parallel Seq Scan on sales_data. Across 3 loop threads (1 leader + 2 workers), PostgreSQL examined and discarded 1,666,338 rows per loop (approx. 5 million total rows). - Execution Time: Query execution took 1490.600 ms (~1.49 seconds) out of the overall 1.665-second HTTP response time.
- Missing Index: Table description confirmed only a single primary key index on column
id(sales_data_pkey). Columncustomer_idhad no index coverage.
Step 4: Attempt Schema Modification and Privilege Resolution
Objective: Add a B-tree index on sales_data(customer_id) to optimize query filtering.
Command Executed:
PGPASSWORD='sadpassword' psql -U saduser -d analytics_db -c "CREATE INDEX idx_sales_data_customer_id ON sales_data(customer_id);"
Output and Analysis:
ERROR: must be owner of table sales_data
Decision & Interpretation:
PostgreSQL enforces strict Role-Based Access Control (RBAC). The application user saduser possesses SELECT permissions (allowing it to execute queries), but does not own table sales_data and lacks administrative Data Definition Language (DDL) rights (CREATE INDEX).
To perform schema modifications, we must execute the index creation command as the database superuser (postgres). Because local system administrator access (sudo) is available, we can leverage PostgreSQL’s default Linux peer authentication over Unix domain sockets to connect directly as the postgres system user.
5. Root Cause Analysis
The core root cause of the severe API latency was an unindexed foreign/lookup column (customer_id) in a high-cardinality table (sales_data).
Failure Mechanism
When PostgreSQL receives a query with a WHERE customer_id = 1234 clause:
- With No Index (Sequential Scan): PostgreSQL must perform an $O(N)$ algorithm. It reads every data block of table
sales_datafrom disk or shared buffer memory, evaluates thecustomer_id = 1234condition against all ~5 million rows, filters out non-matching rows (~4.999 million), and calculates the aggregate count. This required 1.49 seconds of execution time. - With B-Tree Index (Index Scan): PostgreSQL performs an $O(\log N)$ algorithm. It traverses a pre-sorted B-Tree index structure directly to the
customer_id = 1234entry block, counts the matching tuple pointers, and returns the result in <5 ms.
Sequential Scan (Before Fix - 1490ms):
[ Table sales_data (~5,000,000 Rows) ]
├── Read Row 1 ──> Filter Check ──> Discard
├── Read Row 2 ──> Filter Check ──> Match!
├── ...
└── Read Row 5,000,000 ──> Filter Check ──> Discard
Index Scan (After Fix - <5ms):
[ B-Tree Index: idx_sales_data_customer_id ]
└── Pointer Lookup (customer_id = 1234) ──> Fetch Matching Index Tuples Immediately
6. Solution and Remediation
Executed Remediation Command
Execute CREATE INDEX as the postgres superuser via sudo -u postgres:
sudo -u postgres psql -d analytics_db -c "CREATE INDEX idx_sales_data_customer_id ON sales_data(customer_id);"
Output:
CREATE INDEX
Why This Solution Was Selected
- Privilege Alignment: Using
sudo -u postgresleverages Linux Unix socketpeerauthentication, bypassing password authentication to grant administrative DDL capabilities. - Direct Remediation: Creating a B-tree index on
customer_idresolves the underlying algorithmic complexity issue without requiring application code changes, API redeployments, or service restarts.
7. Verification and Validation
Step 1: Re-verify Query Execution Plan
Command Executed:
PGPASSWORD='sadpassword' psql -U saduser -d analytics_db -c "EXPLAIN ANALYZE SELECT COUNT(*) FROM sales_data WHERE customer_id = 1234;"
Expected Output:
The execution plan switches from Parallel Seq Scan to an Index Only Scan or Bitmap Index Scan on idx_sales_data_customer_id, dropping execution time from ~1490 ms to under 5 ms.
Step 2: Measure End-to-End API Latency
Command Executed:
curl -s -w "\nTotal time: %{time_total}s\n" http://127.0.0.1:9090/api/customers/1234/sales/count
Output:
{"count":986,"customer_id":1234}
Total time: 0.122461s
Analysis: Total HTTP request time fell from 1.665 seconds to 0.122 seconds (122 ms), satisfying the SLA requirement of <500 ms.
Step 3: Run Validation Suite
Command Executed:
/home/admin/agent/check.sh
Output:
OK
8. Alternative Troubleshooting Approaches
While directly inspecting application code and running EXPLAIN ANALYZE was the most direct route in this scenario, several alternative tools and diagnostic paths are available for production environments.
| Diagnostic Approach | Tools Used | Information Provided | When to Use |
|---|---|---|---|
| Database Query Statistics | pg_stat_statements extension |
Aggregated statistics on query execution counts, total/mean duration, and buffer hits. | Best for production databases running hundreds of query types where individual slow queries must be identified automatically. |
| System Call Tracing | strace -p <PID> |
Low-level kernel interface calls (read, write, epoll_wait, recvfrom). |
Useful when source code is unavailable or binary processes freeze unexpectedly. |
| PostgreSQL Statement Logging | log_min_duration_statement in postgresql.conf |
Automatically writes queries taking longer than $N$ milliseconds to system logs (/var/log/postgresql/). |
Best for establishing automated server-side logging of slow queries across microservice environments. |
| Network Socket Inspection | ss -tulpn, lsof -i:9090 |
Verifies active listening ports, socket buffer queues, and connected socket endpoints. | Useful when determining if latency stems from network packet drops or connection queue exhaustion. |
9. Linux Command Deep Dive
Command 1: curl
Purpose: Transfer data to or from a server supporting protocols such as HTTP, HTTPS, FTP, and SFTP. Essential for API testing and latency measurement.
General Syntax:
curl [options] [URL]
Option Reference:
| Option | Long Flag | Description |
|---|---|---|
-s |
--silent |
Suppresses output of progress meters and status messages. |
-w |
--write-out |
Defines custom output formatting after request completion. |
-i |
--include |
Includes HTTP response headers in the output stream. |
-v |
--verbose |
Shows detailed debug traces including TCP handshakes and request headers. |
-X |
--request |
Specifies custom HTTP method (e.g., POST, PUT, DELETE). |
Practical Troubleshooting Examples:
# Measure precise breakdown of HTTP connection lifecycle phases
curl -s -o /dev/null -w "DNS: %{time_namelookup}s | Connect: %{time_connect}s | TTFB: %{time_starttransfer}s | Total: %{time_total}s\n" http://127.0.0.1:9090/health
# Test POST endpoint sending JSON data with custom headers
curl -X POST -H "Content-Type: application/json" -d '{"customer_id": 1234}' http://127.0.0.1:9090/api/lookup
Command 2: systemctl
Purpose: Primary command-line interface for controlling systemd system and service manager units.
General Syntax:
systemctl [subcommand] [unit_name]
Option & Subcommand Reference:
| Command / Option | Description |
|---|---|
status |
Shows service runtime state, main PID, memory/CPU usage, and recent log snippets. |
cat |
Displays the raw unit configuration file contents without needing to navigate paths. |
restart |
Restarts the target service unit. |
is-active |
Returns simple text string (active or inactive) for scripting verification. |
Practical Troubleshooting Examples:
# Display systemd configuration file for sad-analytics-api
systemctl cat sad-analytics-api
# List all units currently in a failed state across the host
systemctl list-units --state=failed
Command 3: psql (PostgreSQL CLI Client)
Purpose: Interactive command-line tool for PostgreSQL management, SQL execution, and schema inspection.
General Syntax:
psql -U <username> -d <dbname> [options]
Option Reference:
| Flag | Long Flag | Description |
|---|---|---|
-U |
--username |
Connects as specified database role. |
-d |
--dbname |
Specifies target database name. |
-c |
--command |
Executes a single SQL query or metacommand string and exits. |
-h |
--host |
Specifies database server host (defaults to local Unix domain socket). |
Practical Troubleshooting Examples:
# Inspect table columns, types, and indexes
psql -U saduser -d analytics_db -c "\d sales_data"
# List all databases running on local cluster
psql -U postgres -c "\l"
10. Underlying Linux and Database Concepts
1. Database Indexing: $O(N)$ vs $O(\log N)$ Execution Complexity
Without an index, PostgreSQL must perform a Sequential Scan, reading every table row sequentially off storage blocks ($O(N)$ complexity).
Creating a B-Tree Index constructs a balanced tree data structure sorting the target column values. Searching a B-Tree structure scales logarithmically ($O(\log N)$ complexity). On a table with 5,000,000 rows:
- Sequential Scan: Evaluates 5,000,000 row entries.
- B-Tree Index Scan: Requires approximately $\log_2(5,000,000) \approx 23$ block comparisons to pinpoint matching data.
2. PostgreSQL Role-Based Access Control (RBAC) & Linux Peer Authentication
PostgreSQL maintains its own internal authentication and privileges system separate from Linux OS users.
Linux OS Layer PostgreSQL Engine Layer
[ System User: postgres ] ──────── (Peer Auth over Unix Socket) ────────> [ Role: postgres (Superuser) ]
│
Can Create Indexes / Alter Tables
│
[ System User: admin ] ──────── (Password Auth over TCP) ────────> [ Role: saduser (App Role) ]
│
Restricted to SELECT Operations
- Role-Based Access Control (RBAC): Privileges are granted explicitly per object. A role created to read data (
SELECT) cannot execute DDL commands (CREATE INDEX,DROP TABLE) on tables owned by another role. - Peer Authentication: On local Unix domain socket connections, PostgreSQL maps the requesting Linux OS user directly to the matching PostgreSQL database role name. Running
sudo -u postgres psqlallows system administrators to authenticate instantly as the database superuser without hardcoded passwords.
11. Real-World Sysadmin and SRE Applications
1. Production Observability and SLI/SLO Metrics
In enterprise SRE practice, API latency is tracked as a primary Service Level Indicator (SLI).
- SLO Definition: 99% of customer count API requests must return in under 500 ms over a 30-day window.
- Observability Alerting: Prometheus or Datadog alerts should trigger when PostgreSQL sequential scan rates (
pg_stat_user_tables.seq_scan) spike rapidly alongside rising API latency histograms.
2. Zero-Downtime Database Migrations (CREATE INDEX CONCURRENTLY)
Standard CREATE INDEX statements take an exclusive SHARE lock on the target table, blocking all concurrent INSERT, UPDATE, and DELETE operations while the index is built.
In production environments with active write traffic, SREs must use the CONCURRENTLY modifier:
CREATE INDEX CONCURRENTLY idx_sales_data_customer_id ON sales_data(customer_id);
- Advantage: Performs index creation without locking write operations on the table.
- Operational Considerations: Requires two full scans of the table and cannot run inside a standard transaction block.
12. Further Learning and Experiments
Hands-on Experiments
Experiment 1: Benchmark Query Timing Differences in PostgreSQL
Compare raw performance differences between unindexed sequential scans and indexed scans directly within psql:
# 1. Drop the index temporarily (in a test environment!)
sudo -u postgres psql -d analytics_db -c "DROP INDEX idx_sales_data_customer_id;"
# 2. Time query without index
sudo -u postgres psql -d analytics_db -c "\timing" -c "SELECT COUNT(*) FROM sales_data WHERE customer_id = 1234;"
# 3. Re-create index and time again
sudo -u postgres psql -d analytics_db -c "CREATE INDEX idx_sales_data_customer_id ON sales_data(customer_id);"
sudo -u postgres psql -d analytics_db -c "\timing" -c "SELECT COUNT(*) FROM sales_data WHERE customer_id = 1234;"
Experiment 2: Inspect Active Process System Calls with strace
Attach strace to the Flask service process while issuing web requests to observe blocking system calls:
# Find Main PID of service
PID=$(systemctl show --property=MainPID --value sad-analytics-api)
# Trace network socket read/write calls
sudo strace -p $PID -e trace=network,read,write
Documentation and References
- PostgreSQL Official Documentation: EXPLAIN Performance Profiling
- PostgreSQL Official Documentation: Index Types and B-Trees
- PostgreSQL Official Documentation: Client Authentication - Peer Auth
- Linux Manual Pages:
man curl,man systemctl,man psql
13. Key Takeaways
- System Metrics Can Be Deceptive: Idle host CPU and RAM usage do not rule out application failure; applications spend most of their time waiting on unindexed database I/O.
- Always Use
EXPLAIN ANALYZE: Never guess why a SQL query is slow. Read the execution plan to check for high row counts andSeq Scanoperations. - Respect Database Privilege Segregation: Application roles (
saduser) should only possess permissions required for daily operations (SELECT/INSERT). Schema changes (CREATE INDEX) require owner or superuser roles (postgres). - Leverage Peer Authentication: Local administrative access (
sudo -u postgres) grants direct Unix socket database access for emergency maintenance. - Use Zero-Downtime Production Patterns: Apply
CREATE INDEX CONCURRENTLYin live environments to prevent write locking on high-traffic production tables.
14. Quick Troubleshooting Reference / Runbook
+----------------------------------------------+
| API Endpoint Returning High Latency / Timeouts|
+----------------------------------------------+
│
▼
[ 1. Measure Latency & Check Service Status ]
curl -s -w "%{time_total}\n" <URL>
systemctl status <service_name>
│
▼
[ 2. Inspect Application Source & Query ]
Locate SQL statements in app source code.
│
▼
[ 3. Profile Query Plan & Check Indexes ]
psql -U <user> -d <db> -c "EXPLAIN ANALYZE <SQL>"
psql -U <user> -d <db> -c "\d <table>"
│
┌────────────────────┴────────────────────┐
▼ ▼
(High Seq Scan Time) (Missing Column Index)
│ │
└────────────────────┬────────────────────┘
│
▼
[ 4. Execute Schema Fix as Superuser ]
sudo -u postgres psql -d <db> -c \
"CREATE INDEX CONCURRENTLY <idx_name> ON <table>(<col>);"
│
▼
[ 5. Validate Fix & Measure SLA Target ]
curl -s -w "%{time_total}\n" <URL>