Mastering Tablespaces in PostgreSQL: Boost Performance & Efficiency

Key Takeaways

what's up internet I am Manish from rebellionrider.com today let's learn about tablespace in postgresql So without wasting your time let's start what is the table space in postgresql tablespace is a logical name given to a location on a disk drive this location holds the data files containing database objects like tables views indexes and others advantages of table space in postgresql the biggest advantage of having a table space is that all the database objects remain organized under a named location that in turn helps in easily backing up the database and recovering it the second Advantage is database performance you can wisely decide the location of your data files containing frequently used database objects on a faster device and tables containing less frequent data on slower device default tablespaces by default postgres equal have two table spaces which are PG underscore default this table space holds user data like template 0 and template 1 database PG underscore Global this tablespace holds Global database data like system catalog right on your screen is the Syntax for creating a tablespace in postgresql the statement starts with the result phrase create tablespace you can write it either in capital letters or small letter case doesn't matter but I prefer writing SQL preserve keywords in capital letters followed by the result phrase create tablespace we have to put the tablespace name you can name your tablespace whatever you want just try to make it logical also the name cannot start with the PG underscore PG underscore is reserved for postgresql next we have another keyword owner using this keyword we specify the username who do we want to own this table space this is optional parameter if you omit it then by default the user who executes the create table space is the owner of the tablespace then comes the keyword location using this keyword we Define the location on our disk drive that we want to create as a tablespace using this keyword we specify a directory path this is usually an absolute part to an empty directory make sure the user must own this directory to perform read and write operations now that we know the syntax let's create a tablespace here I am on my command prompt right now I am connected to my postgresql database using postgres user as you can see let's clear the screen now let's create the tablespace make sure to enclose the location path into single quotes as I have installed my postgresql database within a Docker container I am using a directory location that exists within the postgresql container on the docker file system however if you have directly installed your database on your Windows or Mac system you can specify the directory location that you have created there on execution this statement will create a tablespace with the name PS underscore users let's execute we got a warning but our tablespace is created successfully let's first quickly clear the screen let's see now you want to see the details of all the table spaces that you have created so far for that we have a backslash command in postgres which is backslash D B this command will show you the brief information of the tablespace let's check as you can see here if you want to see the details then use backslash D B plus this will show you the detailed information of your tablespace let's execute as you can see here that concludes our tutorial for today thanks for watching in next tutorial we will dive deeper into how to use stable space and create a database in postgresql if you found this video helpful please consider liking it and subscribe to the channel for more content thanks for watching this is Manish from rebellionrider.com