PostgreSQL Temp File Logging Disabled on Cloud SQL

PostgreSQL writes temporary files to disk when a query needs more working memory than work_mem allows. Sorts, hash joins, and large aggregations are common culprits. By default, Cloud SQL does not log these events, so you have no record of which queries are spilling to disk, how often, or how large the spills are. This Lensix check, sql_pgtempfiles, looks at the log_temp_files database flag on your GCP Cloud SQL PostgreSQL instances and reports any instance where it is not enabled.

---

What this check detects

The check inspects the database flags on each Cloud SQL PostgreSQL instance and verifies that log_temp_files is present and set to a value that logs temporary file usage. The flag controls a threshold, measured in kilobytes:

• -1 (the default) disables temporary file logging entirely.
• 0 logs every temporary file regardless of size.
• Any positive integer logs only temporary files at or above that size in KB.

If the flag is missing or set to -1, the check fails. The recommended secure value is 0, which captures all temporary file activity.

---

Why it matters

Temporary file logging is one of those settings that looks like pure performance tuning until you need it during an incident. Here is why it earns a place in your security and reliability baseline.

Disk spills are a silent performance killer

When a query spills to disk, it stops using fast memory and starts hitting storage. On a busy instance this can multiply query latency, exhaust IOPS, and cascade into connection pile-ups. Without logging, you find out about it through user complaints and guesswork rather than data. With logging on, you have a precise record of which statements caused the spill and the size of each file.

It exposes inefficient and abusive queries

Repeated large temporary files often point to a missing index, an unbounded ORDER BY, or a query pulling far more rows than it should. They can also signal a query crafted to consume resources, whether by accident from a bad deploy or deliberately as part of a denial-of-service attempt against your database. Temporary file logs make these patterns visible.

It supports compliance and audit requirements

Benchmarks such as the CIS Google Cloud Platform Foundations Benchmark call out database logging flags explicitly. Auditors expect to see evidence that you can reconstruct database activity, and temporary file logs are part of a complete logging posture alongside log_connections, log_disconnections, and log_min_duration_statement.

---

How to fix it

You can enable the flag through the gcloud CLI, the Google Cloud console, or infrastructure as code. Pick whichever matches how you manage the instance.

Option 1: gcloud CLI

First, check the current flags so you do not accidentally drop existing ones. The --database-flags argument replaces the entire flag set on each call.

gcloud sql instances describe INSTANCE_NAME \
  --project PROJECT_ID \
  --format="value(settings.databaseFlags)"

Then apply log_temp_files=0 alongside any flags you already had. Include every existing flag in the same command:

gcloud sql instances patch INSTANCE_NAME \
  --project PROJECT_ID \
  --database-flags log_temp_files=0,log_min_duration_statement=1000

Option 2: Google Cloud console

1. Open SQL in the Google Cloud console and select your PostgreSQL instance.
2. Click Edit.
3. Expand Flags.
4. Click Add a database flag, choose log_temp_files, and set the value to 0.
5. Click Save and confirm.

Option 3: Terraform

If you manage Cloud SQL with Terraform, add the flag to the instance settings block. This makes the configuration durable and reviewable.

resource "google_sql_database_instance" "postgres" {
  name             = "my-postgres-instance"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    tier = "db-custom-2-7680"

    database_flags {
      name  = "log_temp_files"
      value = "0"
    }

    database_flags {
      name  = "log_min_duration_statement"
      value = "1000"
    }
  }
}

Apply it with the usual workflow:

terraform plan -out tfplan
terraform apply tfplan

Verify the change

Confirm the flag took effect, then generate some load and check Cloud Logging for the new entries.

gcloud sql instances describe INSTANCE_NAME \
  --project PROJECT_ID \
  --format="value(settings.databaseFlags)" | grep log_temp_files

Temporary file events appear in the PostgreSQL logs with a message like temporary file: path "...", size NNNN. You can query them in Cloud Logging:

gcloud logging read \
  'resource.type="cloudsql_database" AND textPayload:"temporary file"' \
  --project PROJECT_ID \
  --limit 20

---

How to prevent it from happening again

Fixing one instance by hand is fine. Stopping the misconfiguration from reappearing across dozens of instances is the real goal. Bake the flag into the way you provision and review databases.

Make it a module default

If you use a shared Terraform module for Cloud SQL, set the logging flags inside the module so every instance inherits them. Teams provisioning a new database get the secure baseline without thinking about it.

variable "extra_database_flags" {
  type    = map(string)
  default = {}
}

locals {
  base_flags = {
    log_temp_files             = "0"
    log_min_duration_statement = "1000"
    log_connections            = "on"
    log_disconnections         = "on"
  }
  all_flags = merge(local.base_flags, var.extra_database_flags)
}

Gate it in CI/CD with policy as code

Catch missing flags before they ever reach production by validating Terraform plans in your pipeline. An Open Policy Agent (Conftest) rule can reject any Cloud SQL PostgreSQL instance without temporary file logging:

package main

deny[msg] {
  rc := input.resource_changes[_]
  rc.type == "google_sql_database_instance"
  startswith(rc.change.after.database_version, "POSTGRES")

  flags := { f.name: f.value |
    f := rc.change.after.settings[_].database_flags[_]
  }

  not flags["log_temp_files"]
  msg := sprintf("%s is missing log_temp_files flag", [rc.address])
}

Run it against a plan in JSON form during the pull request:

terraform show -json tfplan > plan.json
conftest test plan.json --policy policy/

---

Best practices

Temporary file logging works best as part of a broader logging and observability baseline for PostgreSQL on Cloud SQL.

• Enable a full set of logging flags. Combine log_temp_files=0 with log_min_duration_statement, log_connections=on, log_disconnections=on, log_checkpoints=on, and log_lock_waits=on for a complete picture of activity.
• Tune work_mem deliberately. Once you can see which queries spill, decide whether to raise work_mem for the workload, optimize the query, or add an index. Logging without action just fills storage.
• Alert on spill volume. Create a log-based metric in Cloud Monitoring counting temporary file events and alert when the rate spikes. A sudden jump often correlates with a bad deploy or a runaway report.
• Review log retention and cost. Decide how long you keep these logs and route high-volume entries to a sink such as BigQuery or Cloud Storage if you need long retention without paying for hot logging storage.
• Apply the same baseline to read replicas. Flags do not always propagate the way you expect. Confirm replicas carry the same logging configuration as the primary.

The flag itself is a one-line change, but the value comes from treating temporary file logs as a signal you actually monitor. Turn it on, build an alert around it, and you convert an invisible class of performance and abuse problems into something you can see and act on.