Disk Space Error and Temp Files Management in Azure PostgreSQL Flexible Server

Question

Environment:

• Service: Azure Database for PostgreSQL Flexible Server
• Compute Size: 8 vCores, 64 GB Memory, 512 GiB Premium SSD Storage
• Issue: Running out of disk space during large query operations. Problem: I am frequently running into disk space issues when executing large queries that involve spatial operations (e.g., ST_DWithin, ST_Contains, ST_Intersects). Despite increasing the server size and storage, I get the following error during query execution or data export:
• ERROR: could not write to file "pg_tblspc/16386/PG_15_202209061/pgsql_tmp/pgsql_tmp28058.147": No space left on device Details:
  • The database has 512 GB storage, and the storage utilization is around 18-19% according to Azure Portal metrics.
  • The error persists when attempting to run large spatial queries or exporting data using ogr2ogr with GPKG format. Attempted Solutions:
    1. Increased server size and storage:
       • CPU, memory, and storage are well within limits (according to Azure metrics).
       • Current configuration: Standard_E8ads_v5, 64 GB memory, 512 GB Premium SSD.
       • Despite this, the error persists.
    2. Checked Temp File Usage:
       • I tried increasing PostgreSQL configuration parameters like work_mem, temp_buffers, and maintenance_work_mem, but the error still occurs.
    3. Superuser limitations:
       • The azuresu superuser account was created automatically by Azure, and I don’t have access to it.
       • Attempted to create a custom tablespace to handle temp files, but I received the following error: ERROR: permission denied to create tablespace "temp_space" HINT: Must be superuser to create a tablespace.
         1. How can I better manage temp files on Azure PostgreSQL Flexible Server without access to the superuser account (azuresu)? Is there a way to monitor and clean up temp files generated by my queries?
         2. Is there a way to work around superuser limitations on a managed Azure PostgreSQL Flexible Server, such as creating a temporary tablespace to store temp files elsewhere?
         3. Can you provide guidance on optimizing large spatial queries that involve ST_DWithin, ST_Contains, and other spatial joins to avoid the temp file overflow?
       1. Are there any PostgreSQL configuration settings or best practices specific to Azure PostgreSQL Flexible Server for handling large spatial datasets that generate significant temp file usage?

Answer 1

Hi @Osama Khan,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:
Environment:

• Service: Azure Database for PostgreSQL Flexible Server
• Compute Size: 8 vCores, 64 GB Memory, 512 GiB Premium SSD Storage
• Issue: Running out of disk space during large query operations. Problem: I am frequently running into disk space issues when executing large queries that involve spatial operations (e.g., ST_DWithin, ST_Contains, ST_Intersects). Despite increasing the server size and storage, I get the following error during query execution or data export:
• ERROR: could not write to file "pg_tblspc/16386/PG_15_202209061/pgsql_tmp/pgsql_tmp28058.147": No space left on device Details:
• The database has 512 GB storage, and the storage utilization is around 18-19% according to Azure Portal metrics.
• The error persists when attempting to run large spatial queries or exporting data using ogr2ogr with GPKG format. Attempted Solutions:
  1. Increased server size and storage:

            - CPU, memory, and storage are well within limits (according to Azure metrics).
          
            
                     - Current configuration: **Standard_E8ads_v5**, **64 GB memory**, **512 GB Premium SSD**.
          
                     
                              - Despite this, the error persists.
          
                              
                              1. **Checked Temp File Usage**:
          
                              
                                       - I tried increasing PostgreSQL configuration parameters like `work_mem`, `temp_buffers`, and `maintenance_work_mem`, but the error still occurs.
          ```1. **Superuser limitations**:
       
          ```yaml
            - The `azuresu` superuser account was created automatically by Azure, and I don’t have access to it.
          
            
                     - Attempted to create a custom **tablespace** to handle temp files, but I received the following error: ERROR: permission denied to create tablespace "temp_space" HINT: Must be superuser to create a tablespace.
          
                     
                                 1. **How can I better manage temp files** on Azure PostgreSQL Flexible Server without access to the superuser account (`azuresu`)? Is there a way to monitor and clean up temp files generated by my queries?
          
                                 
                                             1. **Is there a way to work around superuser limitations** on a managed Azure PostgreSQL Flexible Server, such as creating a temporary tablespace to store temp files elsewhere?
          
                                             
                                                         1. **Can you provide guidance on optimizing large spatial queries** that involve `ST_DWithin`, `ST_Contains`, and other spatial joins to avoid the temp file overflow?
     

  2. Are there any PostgreSQL configuration settings or best practices specific to Azure PostgreSQL Flexible Server for handling large spatial datasets that generate significant temp file usage?

Solution:
I just found another solution! I changed my file's coordinate system, and now it works fine. Before, I was working with EPSG:4326 coordinates, which was slowing things down. Then, I converted to EPSG:27700, and it works much faster.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and **Yes **for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi, @Mahesh Kurva sorry for the late response. I just found another solution! I changed my file's coordinate system, and now it works fine. Before, I was working with EPSG:4326 coordinates, which was slowing things down. Then, I converted to EPSG:27700, and it works much faster. Thanks for your help, mate!