Part 10 - PostgreSQL : What is a Tablespace in PostgreSQL.

hello and welcome to e multi skills video tutorial in this video tutorial we will learn what is post gray tablespace so this is an introduction to the post scree tablespace so if we say in the layman language will say tablespace is space or a storage location where all the tables are objects occupying the storage are stored so what is tablespace so it is a location on the disk whether we are talking about the file system on the Linux or drive on on windows system so this is the location where both gray stores data files and how both gray is letting the the instance know where it has to store the file that is referred in the tablespace so what what tablespace actually does it map a logical name to a physical location on the disk so in the demo we will see the the logical name and where does it corresponds to so you you would already be knowing that there is something called as dollar PG data which which denotes to the data directory location in post gray we are by default all the data files or objects occupying this storage are stored so when we talk about the table spaces table spaces are nothing they are just a logical name which corresponds to a physical location on the disk let's say you have installed your your post gray instance on the on database in in Window System than in your C Drive at a particular location you will have the the data files located so so by default there are two kinds of table spaces mentioned in post gray one is known as the PG default which stores all the users data will be stored over there and PG underscore global where all the global database global data is so when when an instance is created you have to tablespaces one SPG default and another SPG global close default means the one weird user realizes as a stored and global means where all the global data is stored so it's good to ask what is the advantage of using tablespaces let's say I wanted to create a new table space so does it does it mean something so so by default in in Linux the data files are located in slash where they post gray let's say the version is 12 and slash data at this particular location but the storage in slash where which is the default location data directory is not unlimited and if you wanted to create a new file system if you are unable to to create extend the storage and if you wanted to use a new file system and in that case you have to create a new table space and you have to point that particular location to the new file system or drive or there may be a possibility that you have high-performing storage and you create a new table space and you put your high-performing objects which requires the height performance in that table space and it is good for the performance of poster a sequel instance so what is the default storage and Postgres equal so when you install your post screen let's say on on on RHEL or Windows so in Windows everything will be done automatically by the uninstaller but in in linux system when you install the binary then it asks you to initialize the database so you initialize the database using PG underscore CTL - D the data directory location if it is non default and in a DB so you start the instance following the initialization and what happens during this course of action there is a location which is created which is called a dollar PG data where in all the data or data files related to Postgres equal is stored so by default there are two or table spaces which are created when you install a new instance one is PG underscore global which refers to the global system catalog catalog files or what you say the the shared system catalogs or there is something called as PG underscore default where where your user data is is created so we are more interested in PG underscore default because your user data will be stored over here and how do we create a new table space for creating a new table space you will have to login into poster a command prompt and just say create tablespace tablespace name location and this is the physical location on your on your Linux system or your Windows system and it on the poster a prompt if you say / DV plus so this will show you all the the table spaces available so how do we create the object in a new table space so you simply say let's create a new database create a database DB one and if you don't want to mention the default tablespace and if you mention a new tablespace which you have created you simply say create a database database name which is more than sufficient and if you wanted to have these database created in a new tablespace you just say tablespace and tablespace name so you switch to the new database and then any table which you create inside that database will be automatically created in the new tablespace that means it will go to the new file system or the location which you have mentioned otherwise what you can do when you create the table tables using create table table command you can mention the tablespace name so either you say tablespace new tablespace or tablespace the existing tablespace which SPG and a school default so it works this way so now we will see the demo how it works so we will initialize instance and once the initialization is done we will check what is BG data which we have already discussed in the previous tutorial and then we will see what is post create tablespace what is the default tablespace and then we will see the location of the default tablespace we will create a new table space the objects in the new tablespace then we'll move objects from one table space to another and then we will see on the instance label how to change the default tablespace so this is what we are going to do on on post cray demo so this is the list of steps which we are going to perform and let me close the PowerPoint presentation and this is my Linux box I'm already logged in as mobile Eckstrom tool so I'll just say sudo su - for screen sorry let's clear the screen and what we are going to do is we either you create a new instance or you initialize the existing one so we are going to initialize the existing one we will say PD CTL - D data in it DV so let's do this so okay let me do one thing let me stop the instance or let's create a new instance I'll just say mkdir - P and create this location and then let's instantiate it we do CTL - D the directory location so that a new instance is created in a particular data directory and in a DB oops it is also not empty let's create another one let's say to create this directory done the start do the initialization this is also existing so let me see how what what all I have done instance one and instance 2 is also available let's create instance free i'm k di r and then initialize the database you can see that fixing permission or existing directory this is data directory so this is how you create a new instance so let's initiate or start the this particular instance this was starting this is how how do you start the instance so what I will do is I'm not going to use this this is just how how do you create a new instance I'm going to the existing one sudo systemctl status Postgres equal - 12 and you can see there is something called as PG data for the default instance so I'll go to CD dollar PG data if I say Alice - LRT you can see n number of files are over here and then what I am going to do is inside if if you see the content of the files or the folders or the subdirectories under this you will be able to see these many number of files in PG data right so by default when you create the objects where does it go it goes into PG data so this will go in this particular location so over a period of time this particular location will keep occupying more and more storage as the user data is is inserted created or it is it is done so how it happens so it happens due to the the what do you say the table spaces so let me log into to the browser using the PG admin for tool this is the IP address of my server 192 168 1.61 so just click it and you will be able to see something called as stable spaces so by default these are the two table spaces which are created PG underscore default so you can see that I have a database DVD rental so DVD rental will find the storage in PG underscore default whether it is sequences indexes or there will be the tables also these are the tables so so you can see that these are existing in PG underscore default and when you see the PG underscore or global tablespace you will see the catalog related tables or the indexes and tablespace in actual corresponds to what do you say as the PG data location so PG data PG underscore global table space is used for share the system catalogs and PG underscore default is for default tablespace so this is how it works let's let's create a new table space I'll say TBS to mkd I am the first stuff which we have to do is we have to create a directory location or folder location in window so mkdir - P have created that then what we have to do will have to login as P sequel and then we have to fire the command so by default when you say / DB + this shows you all the table spaces which are existing as of now so I already have two more table spaces which I created in another demo so these are the two table spaces which are created by default and if the location is not mentioned that means it is corresponding to PG data which is the default data directory location if you have changed the data directory location it must show it over here so the next step is we have to create a new table space so the command is create able space table space name and the physical location on the file system so you can see there is a relationship between the location and table space named table space created just say / DB plus and this will show you the new table space which we have created so this is the new table space TBS true and this is corresponding to this physical location so what we will do next is there is something called as PG underscore table space location PWD inside C D data this is your default data directory and there is something called as PG underscore table space if you go inside that and say LS - LT r this will show you a symbolic link in in our case this is the object type the OID unique object ID for the new table space the whenever you create a new object in in a new table space it goes to post gray slash data slash to be s - so all the information is still there in PG data and in PG underscore table space so it has created the link automatically so now let's create the object so we'll say create we have already created the table space and now let's create database create database db2 tablespace TBS 2 which is already we have it oops I entered the wrong password alright just say create database database name and tablespace name so that means this particular database will be created a new tablespace so any object which is created inside this one will be automatically created a new tablespace so that's that's obvious so let's switch to the new database which is db2 and now let's create the table when we say create table tab1 so it is going to create that particular table in your new tablespace let's say create table Tab 2 and if you mention the tablespace name then also it is going to create in the new tablespace or if you wanted to mention the table space-time it will so there is nothing great and as you just mentioned where you wanted to create it let's say I wanted to create a new table space in in default new table in default tablespace so I can do that so the problematic part is when you have to mention the tablespace name so how can we do that can we move the objects from one tablespace to another we can do that or can we alter the change the tablespace from one to another we can do all of that so if I just say alter table all in tablespace and we we set it to default tablespace it is it is possible we can do that but wherever you do all the separate operation there will be a lock on the table and nobody will be able to use that what if I wanted to utilize the the new tablespace so any new user who comes in wanted to use the new tablespace so let's do a demo for that so let's create a new location I'm Kay dir iPhone pepo scree slash data new tablespace - and we'll say that tablespace name is TBS - and this is the location and we are going to create a table in new tablespace so this is the I mean this is the time taking process right we create a folder this is this is an administrator part so that has to be done without any any I mean you cannot skip this step the second is when you create you have to create the location you have to create the tablespace with the new location and if the application team wanted to create the table inside the new tablespace they have to mention the tablespace name so create table table named tablespace but this is this is problematic let's let's create new table because this table already exists there is also existing so let's create table table name table space new table space name yes so this is done but it is always a very problematic one you have to mention this so can we do something so that the default tablespace has changed so we can say set default tablespace to the new tablespace name so we can set this let's just say show default tablespace so there is one default tablespace let's change the default tablespace to the one which we have just created you can see the default tablespace has changed from PG underscore default to so any user now who is going to create that the data either table indexes or anything so it is going to find the storage in a new location which is which is this location and which is corresponding to this particular tablespace since we have already mentioned it that default tablespace is this so any object which is created will be created in this particular table space so if I simply say create table full three then it is going to create the table into new tablespace and if you wanted to cross verify what you can do is just refresh it you will be able to see the new tablespace and the objects will be here so it's still not a showing fetching dependent information from the server so this is the foo two and the three which we have created so this time we have created foo 3 so by default it has gone to the the new tablespace so is there a way we can so the the problem will be when you have to take the the backup so the command is already mentioned PG and a score based back up which we have already seen in previous tutorial you just mentioned the format and you have to mention the location of your your new tablespace which you have the additional tablespace which you have to create so you have to just make an order of that and finally why is why is tablespace used for so so tablespace just used so when your database is crowing so you wanted to go to a new location so you mention it using the new tablespace or it may be possible that you have a heii ops or SSD disk which you wanted to use for your objects so you create a new table space and move your all objects over there so just to recap what we have done we saw we initialized an instance and then we saw what a specie data PG data is nothing but all the data in your instance goes to this particular location which corresponds to the default tablespace default tablespace has two components so by default when an instance is created you have to to tablespaces speech and ask or default PG underscore global PG underscore default is a tablespace wherein user data is stored and PG underscore global where global data is a store which is related to the instance and then we have seen what is the default tablespace location default tablespace location is PG data and then we created a new tablespace so you create a new physical location using mkdir then you create the table space then you mention the location so it will be linked then you say /d v plus so this will show you the all the table spaces and when you go to CD to data and PG underscore tablespace this will show you the the symbolic link that means all the information is still in PG data PG underscore tablespace a new location will be mentioned over there and that is a kind of symbolic link so you can see we created this particular TBS to this is the unique object ID and this is the new location and it is available in PG data inside PG underscore tablespace directory then we created a new table space and you can create the table display table ending with the table space name either it is PG underscore default or the new table spaces or if you wanted to set the table space parameter at the instance level you just say set default tablespace and table tablespace name so that the new objects which are created they will be created inside the new table space and how do we back up the additional table spaces you can use PG underscore base back up you provide the table space mapping and data set and finally why is stable space used for table space is actually used when you your database is growing and you want it to go to a new location new physical location on the server and so you bind the logical name with the physical name the logical name is your table space and physical name is the the location on the server and there may be a scenario also where and you wanted to keep the frequently X accessed indexes or the tables into that particular high performing desk and you create a new table space and you move all the objects over there so this is all about the table spaces in in general so I think this is more than enough I hope you have learnt a lot about table spaces and poor scree and see you in another video thanks for watching

Channel: E-MultiSkills Database Tutorials

Views: 1,862

Keywords: postgresql tablespace, tablespace in postgresql, create tablespace in postgresql, what is a tablespace in postgresql

Id: Sp8vDl2UO_s

Length: 27min 20sec (1640 seconds)

Published: Sat Jul 25 2020