Published at Apr 30, 2024
Start supercharging your PostgreSQL today.
Written by Sarah Conway
It’s a day like any other. You’re running PostgreSQL on a server when, all of a sudden, your memory usage spikes, operations stop, and you see the dreaded error message: “Could not resize shared memory segment.”
Luckily, it’s a fairly common memory fault encountered when using PostgreSQL. Let’s take a look at why it happens and how to address it.
PostgreSQL uses shared memory to cache pages and execute queries. Because of this, database transactions are typically subject to system-wide allocation limits, meaning when a server detects that memory usage is exceeding the current setting, you’ll quickly be notified of the thrown error, and the system will refuse the requested operation.
When you receive the error message…
ERROR: could not resize shared memory segment... : No space left on device
…this is an indication that Postgres ran into a problem with memory allocation on the device. Luckily, there’s a simple solution: you just need to adjust the memory limits set within your system! In this blog post, we’ll review a few of the ways you can do that in order to mitigate this error.
We know that this error represents a performance bottleneck due to memory use. But what’s the actual cause of the problem?
Most commonly, there are system limits set for shared memory segment sizes. As an example, the Docker platform has a default shared memory size of 64 MB—a setting that’s easily able to be exceeded when running any kind of substantial workload or database operation. This default value does vary between each operating system; however, it’s typical for this setting to be too low to handle data at a large scale.
First, you’ll need to start out by checking the current state of resource usage to determine if the operation you’re attempting is overloading the system. Take a look at your monitoring stack to see if there’s a bottleneck or high spike in usage, and note what value it’s attempting to exceed and for what query. This will allow you to add more memory or otherwise optimize queries as needed as you proceed with mitigating the error. Viewing logs will also give you more information about specific transactional statements that are getting locked or that are hanging for long periods of time due to this error.
There are specific PostgreSQL logging parameters that can be defined that will enable you to log related events for maximum observability in this situation, such as log_lock_waits = on (default off) to create an audit trail of events logged for queries that have been waiting for longer than the deadlock_timeout.
You can maximize error output using log_error_verbosity and even control the types of SQL statements that get logged with log_statement (choosing between none, ddl, mod, and all), which is by default set to none. Modify these kinds of settings according to the level of detail you need to identify problematic queries or situations that need to be addressed in the context of resolving this error.
Once you’ve got a good idea of what’s actually happening within your system to trigger the error and have determined that the queries currently being executed are optimized and running as expected with an appropriate end result, you’ll want to take a look at Postgres resource consumption configuration parameters.
You can start out by checking the current setting for shared_buffers (to set the memory limits for shared memory buffer use, default 128 MB). This value controls the data cache for Postgres and dictates the main allocation of shared memory that's made available at server start. It’s recommended by the Postgres docs to set this to around 25 percent of available memory usage when exceeding the current setting. It does, however, need to be at least slightly less than the current setting of shm_size (if applicable, when running on Docker systems).
Check out this blog post for more details on how to tune these key PostgreSQL parameters to improve your database performance.
work_mem should also be noted as it allows you to set the base maximum amount of memory to be used by a query operation before writing to the temporary disk (default 4 MB). Queries are allowed to use up to whatever the value is defined for work_mem on execution for each node and each process in the EXPLAIN plan. When many parallel worker operations are running or many tables and partitions are being handled, the amount of memory in use exponentially increases.
If you continue running into problems, you can also attempt disabling parallel queries to minimize concurrent operations and limit allowed operations to one query at a time. When parallel workers are enabled, queries are executed faster but consume more resources per transaction (thereby worsening overall throughput). This functionality can be controlled with the max_parallel_workers setting.
From the Lock Management section of the docs, you may also want to take a look at the current setting of max_locks_per_transaction. When iterating over a huge number of tables, this limit can restrict operations (causing this shared memory error). This happens most often when running logical backups, as pg_dump locks tables throughout the backup, which can easily trigger this error.
Read this article to better understand PostgreSQL locks.
Of course, reading the Postgres docs (linked for each above) is highly recommended when deciding how best to calculate all of these values, as there’s usually specific math performed or other special considerations that need to be factored in to optimize memory allocation settings.
Additionally, per each operating system or development platform, you’ll need to identify what shared memory parameter will need to be adjusted and size it up accordingly. For this kind of change, you’ll need to restart your system or rebuild your cloud instance once the setting is adjusted for it to take effect.
As a result, take your time to look into platform-specific documentation. These localized configuration files are designed to override Postgres defaults and dynamically allocate resources, so even if you alter the primary configuration files, you may still run into this shared memory error.
You can usually change these settings by passing in a flag to the interactive server start command (in Docker, --shm-size=256MB passed as a command line flag to docker run, for example) or by altering applicable values within the main configuration file for that platform (such as in docker-compose, where the shm_size parameter will set the global default for memory).
Be sure to keep in mind that some cloud database platforms do leverage dynamic resource management (such as Google Cloud E2 VM instances) so you may find that your changes are consistently overwritten. If you continue to run into errors or related problems, you should seek support directly from your cloud vendor that’s hosting your PostgreSQL databases to identify additional settings that might need to be adjusted to resolve this error.
Our cloud service Timescale operates as an extension on top of PostgreSQL. Therefore, any advice recommended for errors seen in Postgres (including the contents of this blog post!) will still be directly helpful for your situation. You’ll still want to look at current memory and resource consumption, identify queries triggering the error, and investigate the suggested memory settings described above that are used natively within Postgres to resolve the observed memory error.
For additional advice on Timescale-specific parameters that would be helpful to take note of and adjust accordingly for maximizing performance and minimizing errors, check out our related blog "Timescale Parameters You Should Know About (and Tune) to Maximize Your Performance."
While it can be conceptually simple to understand the cause of this memory error, it is more difficult to calculate how resources should be allocated—especially when seeking to make these settings as cost-efficient and performant as possible for your exact use case. Staying on top of monitoring and observability within your infrastructure is the best starting place for understanding usage patterns and requirements for day-to-day operations, especially as your data use grows over time.
Using a cloud service like Timescale can help with this effort. With our offering, you get out-of-the-box monitoring dashboards for databases using Insights, features oriented specifically for database monitoring and query optimization. With Timescale’s Support team, you can save time and money for innovation and expedite time-to-market with complete peace of mind using this built-in functionality, knowing that Postgres experts are here to help. Not already using PostgreSQL or Timescale, but want to be? Migration assistance is included with support plans to help you ensure a seamless transition from one platform to another.
Sound interesting? Or, do you have any questions about how to resolve this error or otherwise optimize memory and resource settings within PostgreSQL and Timescale? Our team is here to help! If you're currently an active user of Timescale, you are able to get access to our support with your base plan. Email us anytime at support@timescale.com. Otherwise, you can give Timescale a try free for 30 days with no credit card entry required (and get full access to our expert support during the trial, too!).
We’re looking forward to connecting with you!