Alterando Tablespace de Tabelas e Indices no PostgreSQL

Bem, bem, bem ….
O uso de tablespace pode e muito ajudar na administração do banco. Com esse recurso conseguimos um valor mais preciso de volumetria, podemos também ( e é o mais recomendável) separar indices e dados.

Mundo Ideal ??? um disco só para indices e um só para dados assim não temos concorrência e ajudamos nosso amigo banco de dados.

O Postgres ao criar um objeto ele manda tudo para a tablespace padrão que é a pg_default, o que fiz foi criar 2 tablespaces a banco_data e a banco_idx e separar os objetos.

Com isso teremos dados de analise mais precisos e mais performance no banco.

Segue abaixo os scripts usados para essa façanha.

ALTERANDO AS TABELAS

– Cria TableSpace

CREATE TABLESPACE “banco_data” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_data’;

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Gera Script para alterar tabelas

SELECT ‘ALTER TABLE’ ,n.nspname AS schemaname,’.', c.relname AS tablename, ‘SET TABLESPACE banco_data;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Confere alteracao das tabelas

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname, c.relname

– Verifica tabelas sem tablespace

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
AND t.spcname IS NULL
ORDER BY t.spcname DESC
– Verifica tamanho da tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

ALTERANDO OS INDICES

– Cria TableSpace

CREATE TABLESPACE “banco_idx” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_idx’;

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Verifica quais sao os indices ( Nao primarios) e o tamanho

SELECT n.nspname AS schemaname,c.relname AS tablename,
c.relpages::numeric * 4.096 / 1024::numeric AS espaco_mb
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Gera Script para alterar indices

SELECT ‘ALTER INDEX’, n.nspname AS schemaname , ‘.’ ,c.relname AS tablename, ‘SET TABLESPACE banco_idx;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Confere alteracao dos indices

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Verifica indice sem tablespace

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
AND t.spcname IS NULL
ORDER BY t.spcname DESC
– Verifica tamanho da tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

Espero que tenha ajudado
Kenia Milene