CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-compute-engine
rotten
rotten

Reputation: 1630

Can Google Cloud Local SSD be used for PostgreSQL Temp Tablespace?

We have a PostgreSQL instance running in a VM in the Google Cloud. The nature of the queries that we run involves lots of PostgreSQL temporary table space. (5 or 6 or more TB of disk I/O every day)

This I/O continues to be a major bottleneck in our database. Currently I have it all happening on an SSD persistent disk - not because we need to save any of the data in the event of a reboot, but because PostgreSQL lays out a file structure on the disk that it then uses for the temporary tables and if the file structure is missing when the database starts up, it isn't very good.

What I'd like to do is configure the temporary tablespace on the local SSD's because of their much higher I/O throughput. Unfortunately, they get wiped out on every reboot. I'd like a simple way to be able to re-layout the disk after reboot and before PostgreSQL starts back up.

I could tar up the empty file structure and then write a script that untars it after every boot. Does that make sense? Is there a better way/best practice for doing this?

What would be awesome is if there was a PostgreSQL extension out there that did this magically.

Ideas?

Upvotes: 3

Views: 912

Answers (1)

JosMac
JosMac

Reputation: 2302

I dug a bit into my previous tests and here is some summary:

PostgreSQL tablespace is just a directory - no big deal. Plus - if you will use it only as temporary table space there will be no persistent file left when you shutdown database.

You can create tablespace for temp tables on any location you want and then go to this location and check directory structure to see what PG created. But you must do under OS because PG will show you only tablespace main directory - both \db+ in psql or select oid, spcname, pg_tablespace_location(oid) from pg_tablespace; work the same way.

My example:

  • (I used /tempspace/pgtemp as presumed mounting point) CREATE TABLESPACE p_temp OWNER xxxxxx LOCATION '/tempspace/pgtemp'; created in my case structure /tempspace/pgtemp/PG_10_201707211
  • I set temp_tablespaces = 'pg_temp' in postgresql.conf and reloaded configuration.
  • When I used create temp table .... PG added another subdirectory - /tempspace/pgtemp/PG_10_201707211/16393 = oid of schema - but this does not matter for temp tablespace because if this subdirectory will be missing PG will create it.
  • PG created in this subdir files for temp table.
  • When I closed this session files for temp table were gone.

Now I stopped PG and tested what would happened if directories will be missing:

  • I deleted PG_10_201707211 with its subdir
  • started PG and log showed message LOG: could not open tablespace directory "pg_tblspc/166827/PG_10_201707211": No such file or directory but PG started
  • I tried to create temp table - I got error message ERROR: could not create directory "pg_tblspc/166827/PG_10_201707211/16393": No such file or directory SQL state: 58P01
  • Now (with running PG) I issued these commands in OS:
    • sudo mkdir -p /tempspace/pgtemp/PG_10_201707211
    • sudo chown postgres:postgres -R /tempspace/pgtemp
    • sudo chmod 700 -R /tempspace/pgtemp
  • I tried to create temp table again and insert and select values and everything worked OK

So conclusion is - since PG tablespace is no "big magic" just directories you can simply create bash script running on linux startup which will check (and mount if necessary) local SSD and create necessary directories for PG temp tablespace.

Upvotes: 5

Related Questions