Postgres | TEMP tablespace to local NVMe (instance store)
I am evaluating moving their TEMP tablespace to local NVMe (instance store) for temporary/sort operations, since high-IOPS workloads on EBS can become expensive — and TEMP I/O is a significant contributor. Using ephemeral local storage could substantially reduce their block storage IOPS costs.
I am comfortable with the trade-off: TEMP tablespace is transient by nature and can be rebuilt automatically after an instance restart or failure, so the loss of local storage on crash is acceptable.
Any recommendation on this pattern in production? Any gotchas around rebuild automation or performance consistency?
- Topics
- Database
- Tags
- PostgreSQL
- Language
- English
asked 2 months ago84 views
- Newest
- Most votes
- Most comments
Moving your PostgreSQL TEMP tablespace to local NVMe storage is a well-supported pattern on AWS, and there are purpose-built features designed for exactly this use case.
For RDS for PostgreSQL (versions 13.10+, 14.7+, and 15.2+ on NVMe-based instance classes), Amazon RDS Optimized Reads automatically handles this for you. It places temporary tables on local NVMe storage instead of EBS, delivering up to 50% faster query processing for analytical queries, read replicas, and workloads that generate internal temporary tables. The temp_tablespaces parameter is preconfigured to point to rds_temp_tablespace, which uses the local NVMe storage. If you ever need to switch back to EBS—for example, if local storage becomes insufficient for a specific operation—you can modify the parameter at the DB parameter group level or use a session-level SET command to redirect to pg_default.
For Aurora PostgreSQL, Aurora Optimized Read clusters provide similar functionality, using local NVMe-based SSD storage for temporary files generated by complex queries.
Key recommendations for production:
Add retry logic for read-only queries. Since local storage is ephemeral, queries can fail if the instance store fills up. Your application should be prepared to handle these transient failures gracefully.
Monitor local storage closely. Use CloudWatch metrics like FreeLocalStorage, ReadIOPSLocalStorage, WriteIOPSLocalStorage, and related latency/throughput metrics to track usage and performance. You can also query the database directly to check current local storage consumption.
Understand the failure modes. Transactions will fail when the instance store is full. Unlike EBS, you can't simply expand local storage on the fly, so capacity planning is critical.
Rebuild is automatic. After an instance restart or failure, the temporary tablespace is automatically reinitialized—there's no manual intervention required. This aligns perfectly with your comfort level around ephemeral storage.
This pattern is particularly effective for workloads with multiple concurrent queries that generate significant temporary file usage. The cost savings from offloading high-IOPS temporary operations from EBS can be substantial, and the performance gains are measurable. Just ensure your monitoring and retry logic are robust to handle the ephemeral nature of local storage.
Sources
Improving query performance for RDS for PostgreSQL with Amazon RDS Optimized Reads - Amazon Relational Database Service
Managing temporary files with PostgreSQL - Amazon Aurora
Managing temporary files with PostgreSQL - Amazon Relational Database Service
When running PostgreSQL on self-managed EC2 instances, this is likely the core topic, here are some points to consider for actual operations:
- Assuming data is lost only during a crash can be risky
Instance store data is lost in scenarios other than just a crash:
- Standard stop/start operations (data is wiped even with intentional stops)
- Relocation due to host retirement or maintenance
- Instance type changes
You must design your automation based on the premise that data loss can occur during routine operations, not just during rare failure events. Misjudging this can lead to incidents where the database fails to start after scheduled maintenance.
- Incorporate reconstruction into the startup sequence
PostgreSQL will throw errors during startup or query execution if the symbolic link targets for pg_tblspc/<OID> do not exist. Furthermore, the version-specific subdirectories directly under the tablespace (e.g., names like PG_16_202307071) must also be present.
If using systemd, configure a unit that runs before postgresql.service to execute the following:
- NVMe device detection (device names can change upon reboot, so identify them reliably using
nvme id-ctrlor udev rules) mkfs→ mount (include logic to skip reformatting if already formatted)- Creation of the tablespace directory and version-specific subdirectories
chown postgres:postgresandchmod 0700
PostgreSQL will refuse to start if permissions are incorrect here.
- Replicas require the same paths
With streaming replication, tablespace paths are replicated exactly as they are. Replication will fail if the NVMe is not mounted at the identical path on the standby server. You must run the same automation on each replica; this is the point most easily overlooked in operations.
Similarly, you must specify --tablespace-mapping when performing a pg_basebackup.
- Safeguard against storing permanent data there
Configure this only for temp_tablespaces; never set it for default_tablespace. Additionally, it is prudent to implement safeguards—such as permission controls or a review process—to prevent anyone from accidentally executing CREATE TABLE ... TABLESPACE temp_ts. If even a single permanent table is created there, the next restart will result in data loss and replication corruption.
- Regarding performance consistency
Initial write penalty: Since instance store performance suffers during the first access to unwritten blocks, "pre-warming" the entire volume (e.g., using dd) immediately after mounting ensures stable performance; it is advisable to incorporate this step into your rebuild scripts.
If multiple NVMe drives are available, using RAID 0 (striping) increases throughput (redundancy is unnecessary since the data is ephemeral).
You can use aggressive mount options—such as noatime—since data loss is not a concern.
Watch out for capacity exhaustion: Unlike EBS, instance store volumes cannot be expanded online. Concurrent large sort operations can cause queries to fail with a "No space left on device" error. Enable log_temp_files to monitor actual spill volumes and select an appropriate instance type based on that data.
Relevant content
asked 3 years ago
Is this a right statement "NVME's are good for random writes but not that effective for sequential writes. Temporary tablespaces are usually sequential" ?