One query wrote 376 GB of temp files: shared Postgres lessons

One SQL query wrote 376 GB of temporary files to my server’s root disk. It ran for three and a half hours, pushed the disk to 84 % full and set off the disk-space alert before anyone noticed it was a single statement. Killing it freed the space instantly.

The query was not malicious, just badly shaped: six tables joined with LEFT JOIN and a GROUP BY, on a shared PostgreSQL instance that had no limits at all. This post covers that incident and the shared-database lessons that followed it on my single-node k3s cluster: temp file limits, a RAM-disk tablespace that broke on the first restart, a Service IP change that silently cut off a consumer, and a checkpoint storm that looked like a failing disk.

Contents · 11 sections

TL;DR #

• 3.5 hours of runtime for one query; a six-way LEFT JOIN multiplied rows into a near-cartesian product and spilled 376 GB to pgsql_tmp.
• 10 GB and 1 hour; my values for temp_file_limit and statement_timeout, which every shared PostgreSQL needs.
• 10 GiB of RAM disk; that is where temp files go now, via temp_tablespaces. It broke on the first pod restart, because the tablespace’s version directory lives on the RAM disk and Postgres does not recreate it.
• A recreated Service got a new ClusterIP, and a consumer with a hard-coded IP went silent. Use DNS names.
• Checkpoints every 19 seconds; a bulk write caused them, and they saturated the encrypted disk’s queue. That is a max_wal_size question, not a hardware failure.

The setup #

My cluster runs one “generic databases” namespace: a PostgreSQL 18 instance, MySQL, MongoDB and Redis, shared by many apps. The Postgres holds more than a dozen databases, from threat-intelligence data to my research library’s catalogue. Sharing one instance keeps memory use reasonable on a single machine. It also means one bad query affects everyone.

The incident #

On 28 June 2026 the disk-space alert fired on the root filesystem. The space was in the Postgres data directory, under pgsql_tmp.

Postgres writes temporary files when a sort, hash or join does not fit in work_mem. They are normally small and short-lived. These were enormous.

SELECT pid, now() - query_start AS runtime, state, left(query, 120)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

One query had been running for 3.5 hours. It joined investigations with evidence, actors, findings, connections and infrastructure, all with LEFT JOIN, then grouped the result. An investigation can have many rows in each of those tables. Joining them all at once does not add the rows; it multiplies them. As an illustration, five related tables with ten rows each per investigation give 100,000 rows per investigation before the GROUP BY collapses them again.

The caller was a pod that no longer existed. I terminated the query:

SELECT pg_terminate_backend(<pid>);

The temp files were deleted immediately and the disk went from 84 % to 62 %.

Limits that should have been there #

Postgres ships with no temp file limit and no statement timeout. On a database used by one carefully written app, that is a reasonable default. On a shared instance it means any client can fill the disk.

ALTER SYSTEM SET temp_file_limit = '10GB';    -- per session
ALTER SYSTEM SET statement_timeout = '1h';
SELECT pg_reload_conf();

temp_file_limit is per process: a query that tries to write more is cancelled with an error instead of eating the disk. statement_timeout cancels any statement that runs longer. Both can be overridden per role or per session for the jobs that really need more:

ALTER ROLE <batch_role> SET statement_timeout = '6h';

The query itself also needed fixing: aggregate each related table in its own subquery or LATERAL join, then join the small aggregates, so rows never multiply.

A RAM disk for temp files, and its first restart #

To make sure temp files could never touch the root disk again, I moved them to a RAM disk: an emptyDir with medium: Memory, size-limited to 10 GiB, mounted into the Postgres pod and registered as a tablespace.

volumes:
  - name: pg-temp
    emptyDir:
      medium: Memory
      sizeLimit: 10Gi

CREATE TABLESPACE ramdisk LOCATION '/var/lib/postgresql/ramdisk';
ALTER SYSTEM SET temp_tablespaces = 'ramdisk';

Two things to know about memory-backed volumes: they count against the pod’s memory limit, so I raised the limit to 12 GiB; and they are empty after every restart.

The second point bit me the same day. My first init container created the tablespace with postgres --single when the pod started. After the next restart, Postgres could not create temporary tables at all. The error pointed at a missing directory named like PG_18_<catalog-version>.

The mechanism: a tablespace’s location contains a version-specific subdirectory, PG_<major>_<catalog version>, created when the tablespace is created. The tablespace definition lives in the catalog on the persistent disk and survives restarts. The subdirectory lives on the RAM disk and does not. Postgres does not recreate it on first use.

The fix was an init container that rebuilds that directory before Postgres starts:

major=$(cat "$PGDATA/PG_VERSION")
catver=$(pg_controldata "$PGDATA" | awk -F: '/Catalog version number/ {gsub(/ /,"",$2); print $2}')
mkdir -p "/var/lib/postgresql/ramdisk/PG_${major}_${catver}"
chown -R 70:70 /var/lib/postgresql/ramdisk
chmod 700 /var/lib/postgresql/ramdisk

It creates the tablespace itself only on the very first start, when the tablespace symlink does not exist yet. After that, every restart just recreates the version directory. With the limit at 10 GB and the RAM disk at 10 GiB, a runaway query now fails long before it can affect the disk.

A footnote I learned later: a backup tool that copies pod volumes will happily back up that RAM disk and nothing else if the real data is on a hostPath. That is a separate post about Velero.

The Service IP that moved #

During the same work, the Postgres Service was recreated. It had no fixed clusterIP, so it got a new random one. Most consumers used the Service DNS name and did not notice. My honeypot’s log pipeline had the old IP hard-coded and went silent. No errors in the cluster, just missing data.

The quick fix was to pin clusterIP in the Service manifest so a recreate keeps the same address:

spec:
  clusterIP: <fixed-ip-in-service-range>

The real fix was to stop using IPs at all. Hard-coded IPs had crept in because I assumed host-side tools could not resolve cluster DNS. They can, once the host resolver forwards the cluster domain to CoreDNS. In October I replaced hard-coded cluster IPs across scripts, configs and manifests with DNS names. The pins stay as a second safety net.

The checkpoint storm that looked like a dying disk #

On 8 July the NodeDiskIOSaturation alert fired for 30 minutes. The queue on the encrypted root device peaked around 112. Disk failure was the first thought.

It was my own batch job. An entity-resolution run was bulk-writing to one database, and Postgres was checkpointing every 19 seconds, each about 540 MB of WAL apart, around 28 MB/s of WAL. The log hinted at the cause: “checkpoints are occurring too frequently, consider increasing max_wal_size”. When the batch ended, the alert cleared by itself.

Two details worth keeping:

• the queue on the encrypted device-mapper layer read about five times higher than on the NVMe drive beneath it, because of queueing in the crypt layer. Look at the physical device before panicking;
• if bulk loads become routine, raise max_wal_size so checkpoints are driven by time, not by WAL volume, or rate-limit the writer.

A shared instance is shared in every way #

Later the same summer, another memory complaint about Postgres turned out to be the kernel: with the stock vm.swappiness=60 on a large-memory host, the kernel pushed Postgres’ anonymous memory to swap to grow the page cache. Lowering it to 10 fixed that. In October I moved all database passwords out of workload specs into Kubernetes Secrets and made health probes credential-free.

The pattern is the same each time: on a shared database, every client’s behaviour and every host setting becomes everyone’s problem, so the defaults have to be defensive.

Checklist #

• Set temp_file_limit and statement_timeout on every shared Postgres; override per role for real batch jobs.
• Watch pgsql_tmp size and long-running queries in pg_stat_activity.
• Avoid joining several one-to-many tables at once; aggregate first, then join.
• If you put temp files on a RAM disk, count it in the memory limit and recreate the version directory on every start.
• Never hard-code ClusterIPs; use Service DNS names, and pin IPs only as a backup.
• Treat frequent-checkpoint warnings as a max_wal_size question.
• Read the physical disk’s queue, not just the encrypted layer’s.
• Keep database passwords in Secrets and probes free of credentials.

Frequently asked questions #

What causes huge files in PostgreSQL’s pgsql_tmp directory? #

Sorts, hashes and joins that do not fit in work_mem spill to temporary files. A query that multiplies rows, for example by joining several one-to-many tables at once, can produce hundreds of gigabytes. Find it in pg_stat_activity and terminate it.

How do I stop one query from filling the disk? #

Set temp_file_limit, which cancels a query that exceeds the limit, and statement_timeout, which cancels long-running statements. Both can be set globally with ALTER SYSTEM and relaxed per role.

Can PostgreSQL temp files live on a RAM disk in Kubernetes? #

Yes: mount an emptyDir with medium: Memory, create a tablespace there and set temp_tablespaces. Size the pod’s memory limit to include it, and recreate the tablespace’s PG_<major>_<catalog version> directory in an init container, because the RAM disk is empty after every restart.

Why did my app lose its database connection after a Service was recreated? #

A Service without a fixed clusterIP gets a new address when recreated. Clients using the DNS name follow automatically; clients with a hard-coded IP do not. Use DNS names everywhere.

The lesson #

A shared database is only as safe as its least careful client. The 376 GB query was not a Postgres bug; it was a missing guard rail on a server that trusted every caller. The fixes were all small: two settings, a RAM disk with a correct init container, DNS names instead of IPs. What they have in common is that each one assumes something will eventually go wrong, and makes sure that when it does, it fails in one place instead of everywhere.