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

9 thoughts on “Alterando Tablespace de Tabelas e Indices no PostgreSQL

  1. Kenia, gostaria de saber como eu faco atraves de um comando select, pegar todos os indices de uma tabela e depois de pega-los como eu faco para saber quais colunas fazem parte de cada indice. Se puder me ajudar, por favor, envie para meu email alguma resposta. De qualquer forma obrigado.

  2. Olá Kennia.

    Muito bom seu material inclusive irei precisar dessas dicas pra implementar em um banco de dados.

    Onde posso encontrar mais soluções referente a particionamento, tablespaces,indices.

    Saudações

  3. Olá Kenia!

    Apesar de seu tópico ser antigo ele me ajudou muito agora!!
    Valeu por compartilhar!
    Estou entrando mais na área de BD e correndo atrás de conhecimento e material. Se tiver alguma coisa que possa mandar, será bem vinda!!!

    Um abraço!

  4. Olá Kenia,

    Estou modelando de um pequeno projeto com Postgresql, estou com algumas dúvida para separar índice por tablespace.
    Criei 2 tablespace tbs_dada e tbs_index
    1) Qual tablespace eu coloco os índices das Pk e Fk?

    E qual regla vc utiliza para índice em suas tabelas?

    Obrigado,

    Edson

  5. Minha cara bom dia,
    Qual seria o comando para colocar todas as tabelas num determinado tablespace? estou usando o powerdesign e estou perdendo a paciência.
    Obrigado
    Valnei

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s