One of the most amusing features of PostgreSQL is Tablespaces, let me explain what it does and how it works ⚡
Tablespaces are the storage locations for data files, essentially directories on the filesystem where the actual data is stored. When we create a table, it is made part of a default tablespace, and, all the meta and data files are created are placed in it.
We can also create additional tablespaces to organize the data across different storage locations. For example, we can create one tablespace for hot data on SSD and one tablespace for cold data on HDD.
Now when we keep the frequently accessed tables in hot tablespace, we are optimizing for the performance of queries accessing that data; and by moving infrequently accessed data to cold tablespace, we are saving cost.
By the way, we can provide a tablespace while creating the table and even move the table from one tablespace to another by just firing an alter table command.
Apart from performance and cost, tablespaces are also used for moving tables across disks for load distribution, multi-tenancy, and even geo-proximity.
Attached are the SQL commands to create a table space and specify tablespace while creating a table. Also, we can see the two tablespaces that are created when we create and run a PostgreSQL server.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
arpitbhayani.me/course