In modern internet systems, database performance directly impacts user experience and system stability. This is especially true in continuous authentication systems, where databases need to insert user data frequently while quickly verifying user states.
Imagine an online education platform: thousands of users send actions every second, and the system must validate each user's identity in real-time. Any delay in the database can seriously affect the platform’s responsiveness and reliability.
So the question arises: in high-concurrency scenarios, which database performs better—PostgreSQL or MySQL?
This article dives into experimental data from academic research, explains the methodology, and shares practical insights for developers. You can even reproduce these experiments locally using tools like ServBay.
1. Background
Continuous authentication systems rely on two main types of database operations:
- Read operations (SELECT) – fetching all records or retrieving data for a specific user.
- Write operations (INSERT) – continuously adding new user activity or session data.
In production, these operations happen simultaneously. While the system is validating a user, it must write the latest activity records and return authentication results quickly. Testing a database’s read or write performance in isolation doesn’t fully capture real-world behavior; high concurrency and mixed workloads matter.
The experiments referenced here simulate a production-like environment with 1 million records, including fields like SessionID, timestamp, type, x, y, Event, and userId. This setup closely mimics how a continuous authentication system generates and queries data in real time.
2. Experimental Setup
Researchers designed a Python-based benchmarking framework to evaluate both databases systematically. The framework can:
- Read configuration files (
runs,test_cycles,methods) - Execute queries and inserts on MySQL and PostgreSQL
- Measure execution time for each operation
- Export results as CSV, JSON, and PDF files for statistical and visual analysis
2.1 Framework Components
- Configuration module – reads experiment settings and database connection info.
- Benchmark module – executes queries and inserts, recording system metrics (CPU, memory, DB version).
- Analysis module – generates statistical summaries (min, max, median, percentiles) and visual charts for performance comparison.
For developers wanting to replicate these tests locally, ServBay provides a convenient way to deploy testing environments and benchmark different database systems.
2.2 Experiment Types
- Primary experiments – measure the performance of a single operation (read-only or write-only).
- Complex experiments – evaluate mixed workloads under high concurrency, simulating real production scenarios.
Each test ran 100 iterations on the 1-million-record table to ensure statistically meaningful results.
3. Primary Experiments: Basic Performance
3.1 Full Table Scan
Full table scans test how fast a database can retrieve large datasets.
| Database | Min (ms) | Median (ms) | Max (ms) |
|---|---|---|---|
| MySQL | 6.75 | 9.61 | 14.65 |
| PostgreSQL | 0.49 | 0.69 | 0.95 |
PostgreSQL outperforms MySQL by roughly 13x in full table scans.
3.2 Conditional Queries (Single User)
Most authentication queries target a single user or a subset of users.
| Database | Min (ms) | Median (ms) | Max (ms) |
|---|---|---|---|
| MySQL | 0.69 | 0.84 | 1.35 |
| PostgreSQL | 0.059 | 0.073 | 0.156 |
PostgreSQL maintains a significant lead (~10x faster) for single-user queries, showing its advantage for real-time verification tasks.
3.3 Insert Performance
Frequent inserts are critical in continuous authentication systems.
| Database | Min (ms) | Median (ms) | Max (ms) |
|---|---|---|---|
| MySQL | 0.001 | 0.002 | 0.003 |
| PostgreSQL | 0.0007 | 0.001 | 0.0014 |
Insert performance is comparable, but PostgreSQL demonstrates slightly better stability under repeated operations.
4. Complex Experiments: High-Concurrency Workloads
Real-world systems rarely perform reads or writes in isolation. Mixed workloads are the true test.
4.1 Full Table Scan + Concurrent Inserts
| Database | Min (ms) | Median (ms) | Max (ms) |
|---|---|---|---|
| MySQL | 6.45 | 12.23 | 13.36 |
| PostgreSQL | 0.73 | 0.82 | 1.01 |
MySQL experiences a noticeable increase in query latency under concurrent inserts, while PostgreSQL remains stable.
4.2 Conditional Query + Concurrent Inserts
| Database | Min (ms) | Median (ms) | Max (ms) |
|---|---|---|---|
| MySQL | 0.75 | 1.25 | 1.78 |
| PostgreSQL | 0.059 | 0.093 | 0.189 |
PostgreSQL is roughly 9x faster under high-concurrency mixed workloads. Its optimization for hierarchical data and recursive queries ensures low-latency reads even during heavy writes.
5. Analysis and Insights
5.1 Theoretical Insights
- Database performance is multi-dimensional – latency depends on query complexity, write volume, concurrency, and index strategies.
- PostgreSQL’s advantages in query planning and transaction management shine in mixed, high-concurrency workloads.
- Reproducible benchmarking frameworks allow teams to compare different systems reliably, reducing guesswork in database selection.
5.2 Practical Takeaways
- Choosing the right database
- High-frequency reads, conditional queries, or complex data: PostgreSQL
- Write-heavy workloads with moderate read requirements: MySQL
- Performance tuning
- PostgreSQL: optimize indexes,
VACUUMstrategy, and connection pools. - MySQL: focus on query caching, isolation levels, and replication strategy.
- Monitoring & continuous testing
- Implement automated benchmarking and monitoring to catch bottlenecks early.
- Tools like ServBay can help set up local test environments for continuous performance evaluation.
6. Conclusion
Based on academic benchmarks and production-like simulations:
- PostgreSQL consistently outperforms MySQL in read-heavy and mixed workloads, especially under high concurrency.
- Insert performance is similar, but PostgreSQL shows better stability during simultaneous writes.
- Complex workloads highlight PostgreSQL’s strength in low-latency, high-stability queries.
- Reusable benchmarking frameworks provide actionable insights for enterprise database planning.
For developers building continuous authentication systems, real-time logging platforms, or other high-concurrency applications, PostgreSQL is a clear choice.
Data source: Benchmarking PostgreSQL and MySQL under Production-Like Scenarios for Continuous User Authentication Systems
Top comments (0)