Tablespace in postgresql

TABLESPACES

=>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';