PostgreSQL vs MySQL: Real Memory Use Compared
PostgreSQL is not inherently a much lighter server than MySQL based on documentation alone; actual memory usage depends heavily on configuration, workload, and how each database handles caching. The documentation for both databases describes default settings and tunable parameters, but real-world memory footprints can overlap significantly, with PostgreSQL often using more memory for complex queries and MySQL potentially using more for simple read-heavy workloads.
Background: How Each Database Manages Memory
PostgreSQL and MySQL take fundamentally different approaches to memory management, which explains why their documentation shows different numbers. PostgreSQL uses a shared buffer cache (shared_buffers) for data pages, plus per-backend memory for query execution. MySQL (particularly InnoDB) uses a buffer pool that caches data and indexes, and it also allocates memory for per-connection threads and query caches (though the query cache is removed in MySQL 8.0).
The default values in the documentation reflect these architectural differences. For example, PostgreSQL’s default shared_buffers is often 128MB, while MySQL’s default innodb_buffer_pool_size is usually 128MB or 256MB depending on the version. However, these defaults are conservative starting points, not fixed limits. Production systems routinely adjust them based on available RAM and workload.
How It Works: Key Memory Parameters
To compare memory usage accurately, you need to look at the key configuration parameters for each database:
- PostgreSQL: shared_buffers (shared cache), work_mem (per-operation sort/hash memory), maintenance_work_mem (for maintenance tasks), and effective_cache_size (a hint for the planner).
- MySQL/InnoDB: innodb_buffer_pool_size (main cache), innodb_log_buffer_size (for transaction logs), and max_connections (each connection has a thread stack).
PostgreSQL’s work_mem is allocated per operation, not per connection, so a single complex query can multiply memory usage. MySQL’s buffer pool is a single large allocation shared across connections. This means that for a workload with many concurrent simple queries, MySQL may use less memory, but for a workload with a few heavy analytical queries, PostgreSQL can consume more.
How to Choose: Matching Database to Workload
Choosing between PostgreSQL and MySQL based on memory should be driven by your specific use case, not just documentation defaults. Consider the following:
- Read-heavy web applications: MySQL with a large InnoDB buffer pool often performs well with modest memory, especially for simple key-value lookups.
- Complex queries and analytics: PostgreSQL’s advanced query planner and support for parallel query execution can use more memory, but it may handle complex joins and subqueries more efficiently.
- Concurrency: PostgreSQL uses a process-per-connection model, which can consume more memory with hundreds of connections. MySQL uses threads, which are lighter per connection.
- Data integrity and features: PostgreSQL offers more advanced features like full-text search, JSONB, and better constraint handling, which may justify higher memory usage.
Common Problems: Misreading Documentation Defaults
A common mistake is assuming that the default memory settings in the documentation are optimal for your server. Defaults are chosen for compatibility, not performance. For example, PostgreSQL’s shared_buffers default of 128MB is far below what a dedicated database server with 16GB RAM should use. Similarly, MySQL’s default buffer pool size may be too small for a large dataset.
Another issue is confusing allocated memory with used memory. Both databases may allocate memory lazily, so the operating system reports lower usage than the configured maximum. Also, memory used for caching is often reclaimed by the OS when other processes need it, so raw RSS (resident set size) numbers can be misleading.
Comparisons: Real-World Memory Footprints
In a typical test with a 1GB dataset and 100 concurrent connections, MySQL might use around 500MB of RAM with a 256MB buffer pool, while PostgreSQL might use around 700MB with shared_buffers set to 256MB and work_mem at 4MB. However, if you increase PostgreSQL’s work_mem to 64MB for analytical queries, memory usage can spike to several gigabytes. Conversely, if you reduce MySQL’s buffer pool to 128MB, memory usage drops but performance suffers.
It’s also worth noting that PostgreSQL’s memory usage scales with the number of connections because each connection has its own backend process. MySQL’s thread-based model is more memory-efficient for high connection counts, but each thread still has a stack and session variables.
Costs: Memory as a Resource
Memory is a finite and often expensive resource, especially in cloud environments where you pay per GB. When comparing costs, consider not just the database’s memory usage but also the performance you get per GB. A database that uses more memory but processes queries faster may be more cost-effective than one that uses less memory but requires more CPU or disk I/O.
Both databases allow you to tune memory settings to fit within a budget. For PostgreSQL, you can set work_mem lower for OLTP workloads and higher for reporting. For MySQL, you can adjust the buffer pool size dynamically (in MySQL 5.7+). The key is to monitor actual usage and adjust accordingly.
Safety: Avoiding Memory Overcommitment
Setting memory parameters too high can cause the operating system to swap or even kill database processes. Always leave headroom for the OS and other applications. A common guideline is to set shared_buffers to about 25% of total RAM for PostgreSQL, and innodb_buffer_pool_size to about 70% for MySQL, but these are rough starting points.
Use tools like pg_stat_activity and SHOW ENGINE INNODB STATUS to monitor memory usage. Be careful with settings like work_mem in PostgreSQL, as it’s multiplied by the number of sort operations, not just connections. A single query with many sorts can exhaust memory.
Alternatives: Other Lightweight Databases
If memory is extremely constrained, consider alternatives like SQLite, which runs in-process and uses very little memory, or MariaDB, which is a MySQL fork with similar memory behavior. For embedded or IoT applications, SQLite is often the best choice. For a full-featured client-server database with low memory, you might also look at Firebird or even a NoSQL option like Redis (for caching) or MongoDB (though it can be memory-hungry).
Frequently Asked Questions
Does PostgreSQL always use more memory than MySQL?
No. Under heavy analytical workloads with large sorts and joins, PostgreSQL can use more memory, but for simple point lookups with a well-tuned buffer pool, MySQL may use more. The actual usage depends on configuration and workload.
Can I reduce PostgreSQL’s memory usage?
Yes, lower shared_buffers, work_mem, and max_connections. For example, setting work_mem to 1MB and shared_buffers to 128MB on a small server can keep memory usage low, though it may hurt performance for complex queries.
Why does MySQL’s documentation show lower memory usage?
MySQL’s documentation often lists the default buffer pool size as a single value, while PostgreSQL’s documentation lists multiple parameters that add up. The actual memory footprint depends on how you configure each.
Is it safe to set innodb_buffer_pool_size to 80% of RAM?
It can be safe on a dedicated database server, but you must leave memory for the OS, connection threads, and other processes. Monitor for swapping and reduce the value if you see issues.
How do I measure real memory usage of my database?
Use operating system tools like top or htop to see RSS, and query database status variables. For PostgreSQL, check pg_stat_activity and pg_settings; for MySQL, use SHOW GLOBAL STATUS to see memory-related counters.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.