Tablespace in Postgresql

#Command to find the size of all Databases : 

postgres=# SELECT datname,pg_size_pretty(pg_database_size(datname)) FROM pg_database;
     datname     | pg_size_pretty
-----------------+----------------
 template1            | 6393 kB
 template0            | 6385 kB
 postgres               | 6501 kB
 test1                     | 6517 kB
 testdb1                  | 6393 kB
 mydb                    | 6501 kB
 foo                        | 6501 kB
 example_backups | 6517 kB

# Command to find the size of all tablespaces : 

postgres=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;

  spcname   | pg_size_pretty

------------+----------------

 pg_default | 44 MB

 pg_global  | 437 kB

 data_tbs   | 6509 kB

(3 rows)

# Command to find the size of particular tablespace : 

postgres=# SELECT pg_size_pretty(pg_tablespace_size('data_tbs'));

 pg_size_pretty

----------------

 6509 kB

About tablespaces in Postgresql : 

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

=>Tablespaces in PostgreSQL allow database administrators to define locations in the file system
   where the files representing database objects can be stored.
=>An administrator can control the disk layout of a PostgreSQL installation.
=>Creation of the tablespace itself must be done as a database superuser.
=>Tables, indexes, and entire databases can be assigned to particular tablespaces.

To create tablespace:

os level directory must create  one folder and change postgres user Permission's.

1.mkdir /tbs1
2.chown -R postgres:postgres /tbs
3.CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
4.CREATE TABLESPACE tbs1 LOCATION '/tbs1;

alter database data11 set tablespace tbs1;


SET default_tablespace = fastspace;
CREATE TABLE foo(i int);
create table t1(id int) tablespace tbs1;

Default two tablespace Will create after database create
1.PG_GLOBAL
2.PG_DEFAULT

 =>The pg_global tablespace is used for shared system catalogs.
 =>The pg_default tablespace is the default tablespace of the template1 and template0 databases.


 TOAST (The Oversized-Attribute Storage Technique):-

Looking at page size it is easy to discover that some data cannot be stored in such a small space.
For these cases there is a mechanism called TOAST.

By default PostgreSQL has two variables, toast_tuple_threshold and toast_tuple_target with value 2K.
 When a tuple is being stored and is larger than 2K, the fields where it can be applied (not all of them apply to TOAST) are stored in a TOAST table.


PostgreSQL uses a fixed page size (commonly 8Kb), and does not allow tuples to span multiple pages,
it's not possible to store very large field values directly.

Physical Layout :

All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as PGDATA

PG_VERSION A file containing the major version number of PostgreSQL
base Subdirectory containing per-database subdirectories
global Subdirectory containing cluster-wide tables, such as pg_database
pg_clog Subdirectory containing transaction commit status data
pg_subtrans Subdirectory containing subtransaction status data
pg_tblspc Subdirectory containing symbolic links to tablespaces
pg_xlog Subdirectory containing WAL (Write Ahead Log) files
postmaster.opts A file recording the command-line options the postmaster was last started with
postmaster.pid A lock file recording the current postmaster PID and shared memory segment ID
 (not present after postmaster shutdown)


Free Space Map:

Each heap and index relation, except for hash indexes, has a Free Space Map (FSM)
  --to keep track of available space in the relation.
The Free Space Map is organized as a tree of FSM pages.
The bottom level FSM pages store the free space available on each heap (or index) page.
The contrib/pg_freespacemap module can be used to examine the information stored in free space maps (see Section F.26).
SELECT * FROM pg_freespace('foo');

Visibility Map:

Each heap relation has a Visibility Map (VM) to keep track of which pages contain only tuples that are known to be
 visible to all active transactions.



 To view tablespace information:-

SELECT spcname, spclocation FROM pg_tablespace;

 \db+


Rename:-
 ------
alter tablespace tbs1 rename to tbsnew

privilage
alter tablespace tbs1 owner to scott;

set for database
alter tablespace tbs1 set (default_tablespace='tbs');

reset
alter tablepace tbs1 reset default_tablespace;

Drop

drop tablespace tbs1;


Disk space occupied by a tablespace
select pg_size_pretty(pg_tablespace_size('tbs1'));

/u02/tbs1/du -c -h


SELECT spcname, spclocation FROM pg_tablespace;


select
  tablename
, pg_relation_size(tablename)
, pg_size_pretty(pg_relation_size(tablename) ) as relsize
, pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
, pg_total_relation_size(tablename)
from pg_tables where schemaname <> 'information_schema'
order by 2 desc



SELECT
    *
FROM pg_class
    JOIN pg_tablespace ON reltablespace = pg_tablespace.oid
WHERE
    spcname <> 'name of tablespace';