Hi! I have read around the Internet a lot about the idea of using /dev/shm for a tablespace to put tables in and issues with that. But I still have not managed to get a good grasp why would that be a bad idea for using it for temporary objects. I understand that for regular tables this might prevent database startup and recovery because tables and all files associated with tables would be missing. While operations for those tables could reside in the oplog. (Not sure if this means that unlogged tables can be stored on such tablesspace.)
I have experimented a bit and performance really improves if /dev/shm is used. I have experimented with creating temporary tables inside a regular (SSD backed) tablespace /dev/shm and I have seen at least 2x improvement in time it takes for a set of modification+select queries to complete. I have also tested what happens if I kill all processes with KILL and restart it. There is noise in logs about missing files, but it does start up. Dropping and recreating the tablespace works. So I wonder, should we add a TEMPORARY flag to a TABLESPACE which would mark a tablespace such that if at startup its location is empty, it is automatically recreated, without warnings/errors? (Maybe some other term could be used for this.) Ideally, such tablespace could be set as temp_tablespaces and things should work out: PostgreSQL should recreate the tablespace before trying to use temp_tablespaces for the first time. We could even make it so that only temporary objects are allowed to be created in a TEMPORARY TABLESPACE, to make sure user does not make a mistake. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
