Postgresql – Amount of memory used by postgres

postgresql

I am curious as to whether the performance of postgres depends on the amount of RAM available. i.e. will postgres performance(during reads) on several runs get affected by the amount of RAM that is available in the system. For example, would postgres performance after several runs be different for 4GB, 8GB, 16GB, 64GB systems.

I noticed change in postgres's perfomance. I want to acertain whether it is because of the RAM available in the system or not. Can someone please help me with this?

Best Answer

Yes, PostgreSQL performance is influenced by the amount of RAM available to PostgreSQL, among many other factors.

PostgreSQL benefits from RAM for:

  • The buffer cache in shared_buffers. Think of this as the database's first level cache.
  • work_mem for performing sorts, hash joins, etc
  • maintenance_work_mem for index building etc
  • Operating system disk cache - RAM that appears to be "unused" will be used by the OS to cache disk blocks, so they're much faster for PostgreSQL to read. This is estimated with effective_cache_size.

It's not as simple as "more RAM good" though.

Lots of slow RAM will be worse than less fast RAM for a database that fits entirely in RAM on both systems. Once you have enough RAM to cache the indexes and tables in heavy use, more RAM generally makes little difference.

NUMA (non-uniform memory architecture) machines can sometimes have performance issues related to NUMA page migration because the Linux kernel doesn't understand PostgreSQL's use of shared memory properly. This can have a drastic influence on performance, so a "bigger" machine can be slower. Appropriate kernel tuning will solve this issue.

There's a cost to maintaining shared_buffers, so too big a shared_buffers can slow things down. It's a balance between having enough workspace and the maintenance cost of that workspace.

effective_cache_size tells the PostgreSQL query planner how much system RAM is thought to be used for disk cache, and will help it choose better plans. If unset or incorrectly set the planner is less likely to pick the correct plan for a system.

A machine with tons of RAM and a pathetic disk subsystem might still be slower than one with little RAM and really fast disks - depending a lot on the workload.

RAM makes less difference for write-heavy workloads, it's mostly beneficial for read-heavy workloads.

You might be noticing a theme here - it depends on what you're doing, and no single system spec like amount of RAM can be taken in isolation. It'd be nice if it was simpler, but it isn't.

Related Question