docker/docker-compose tmpfs - pgsql_tmp directory

Question

Has anyone tried running a postgresql container with a tmpfs volume mount to pgsql_tmp? (any database with a temp files directory i would imagine)

Has it caused any problem?

Is this a bad idea?

Additional helpful info?

docker tmpfs - "As opposed to volumes and bind mounts, a tmpfs mount is temporary, and only persisted in the host memory. When the container stops, the tmpfs mount is removed, and files written there won’t be persisted."

pgsql_tmp - "Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend."

Putting this out for reference and research from the community. Thank you for any assistance.

Have experienced artifactory database filling inodes a couple of times. After restarting the stack, had to manually delete the tmp files, this took about an hour.

Azaleah

This is an interesting idea. Did you end up attempting to implement this? if so, I'm curious what you observed. – Outlook

I have only run in development (short running), but not in a production (long running with potential for disaster) situation. I'm still not 100% sure this would be a problem. After we've experienced the tmp file issue, we always have to clean up the tmp dir... One time to get around the tmp files issue, we actually volume mounted tmpfs to the pgsql_tmp location.... one container cleaning up the files and one container (temporarily) getting us back up and running. – Azaleah

Answer 1

While in my (little) experience it worked, my understanding of this thread (mirror) is that in some cases (advanced usages?), this folder might receive not-so-temporary files that would be needed on startup (see warning on tablespace docs), so it seems better to adjust work_mem, temp_buffers and temp_file_limit as mentioned.

For reference however, this is how you might implement it using docker-compose:

services:
  postgres:
    image: postgres:alpine
    command:
      - postgres
      # - -cshared_buffers=512MB  # Default = 128M
      # - -cwork_mem=16MB  # Default = 4M ; total = this*worker(8)*parallel(2)?
      # - -ctemp_buffers=32MB  # Default = 8M
      - -clog_temp_files=0  # Log all temporary files creation
      # - -clog_min_duration_statement=60s  # Log queries longer than this delay
    # Ignored in swarm (https://github.com/moby/moby/issues/26714):
    # shm_size: 512M  # Defaults to 64M ; related to total work_mem?
    # Unsupported in swarm:
    # tmpfs:
    #   - /dev/shm:size=512M
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - type: tmpfs
        target: /var/lib/postgresql/data/base/pgsql_tmp
        tmpfs:
          # 1G max - https://www.kernel.org/doc/html/latest/filesystems/tmpfs.html
          size: 1073741824
      # Workaround for ignored shm_size in swarm:
      # - type: tmpfs
      #   target: /dev/shm
      #   tmpfs:
      #     size: 536870912  # 512M max ; defaults to 64M ; relative to total work_mem?

Gunter