Introdução
Organizar os objetos no banco de dados é algo relativamente simples de gerenciar e é fundamental para a arquitetura das aplicações, sendo que em certos casos, pode ser utilizado para melhorar a performance de acesso e escrita. Serão abordadas as definições de owner, schema e tablespace dos objetos tabela e índice e como alterá-las.
Configurações iniciais – setup
As configurações iniciais devem ser aplicadas para que os comandos utilizados nas próximas seções possam ser executados com sucesso.
As configurações iniciais foram divididas em:
- Database: banco de dados teste em um cluster postgresql a fim de concentrar e configurar objetos.
- Tablespaces: criação de tablespaces no banco de dados a partir de diretórios configurados em filesystem na máquina do banco de dados. As tablespaces serão utilizadas para alocar fisicamente os objetos.
- Schemas: configurados para realizar a organização lógica dos objetos no banco de dados.
- Roles/Users: criados para demonstrar questões de acesso e propriedade de determinados objetos.
- Privilégios: permitir que as ações dos usuários/roles possam ocorrer em determinados recursos do banco de de dados.
- Objetos: criados para demontrar as suas definições e processos de configuração.
Observação: Se for de interesse realizar as configurações, configurá-las em um ambiente propício a testes, a fim de mitigar erros em ambientes críticos.
mkdir /data/tbs_app_test_tbl
mkdir /data/tbs_app_test_idx
chown postgres:postgres /data/tbs_app_test_tbl
chown postgres:postgres /data/tbs_app_test_idx
psql
create database db_test;
revoke connect on database db_test from public;
\c db_test
create tablespace tbs_app_test_tbl location '/data/tbs_app_test_tbl';
create tablespace tbs_app_test_idx location '/data/tbs_app_test_idx';
create user testuser with encrypted password 'strongpassword01';
create role role_app_query_test;
create user user_aux_test with encrypted password 'strongpassword02' in role role_app_query_test;
grant create on tablespace tbs_app_test_tbl to user_app_test;
grant create on tablespace tbs_app_test_idx to user_app_test;
grant connect on database db_test to user_app_test;
grant connect on database db_test to user_aux_test;
create schema schema_user_app_test_default;
create schema schema_user_app_test_01;
create schema schema_user_app_test_02;
create schema schema_user_aux_test_default;
grant all privileges on schema schema_user_app_test_01 to user_app_test;
grant all privileges on schema schema_user_app_test_02 to user_app_test;
grant all privileges on schema schema_user_aux_test_default to user_aux_test;
alter user user_app_test set search_path to schema_user_app_test_default;
alter user user_aux_test set search_path to schema_user_aux_test_default;
Configuração dos diretórios das tablespaces na máquina do banco de dados.
Configuração do banco de dados, tablespaces, schemas, usuários/roles e privilégios.
Exemplos de criação e movimentação de objetos
Serão abordados 3 casos gerais:
- 01 – Criação de objetos em owner, schema e tablespace diferentes dos necessários, utilizando usuário diferente do owner dos objetos.
- 02 – Criação de objetos especificando owner, schema e tablespace necessários, utilizando usuário diferente do owner dos objetos.
- 03 – Criação de objetos especificando schema e tablespace necessários, utilizando o owner dos objetos.
Criar objetos em owner, schema e tablespace diferentes dos necessários, utilizando usuário diferente do owner dos objetos.
No banco de dados (db_test) foram criados os objetos tabela e índice para demonstrar as propriedades deles referentes à owner, schema e tablespace, ou seja, respectivamente, quem é o proprietário do objeto e quais são as suas organizações lógica e física.
create table table_proc_01 (id serial,
name varchar(30),
data text);
create index idx_table_proc_01_col_id on table_proc_01(id);
grant select on table_proc_01 to role_app_query_test;
select *
from information_schema.table_privileges
where table_name = 'table_proc_01';
select current_database()
,pg_get_userbyid(pc.relowner) as table_owner
,psat.relname as table_name
,psat.schemaname as table_schema
,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace
from pg_class pc
join pg_stat_all_tables psat on psat.relid = pc.oid
where psat.schemaname not in ('pg_toast','pg_catalog','information_schema')
order by table_owner,
table_name;
select current_database(), session_user, current_user, current_schema;
No exemplo acima, os objetos foram criados a partir do acesso com o superuser postgres. Verifica-se que o owner da tabela (table_owner), por padrão, foi assumido como o usuário postgres (current_user), e o schema foi o public (current_schema), enquanto que a tablespace foi a pg_default (table_tablespace). Visando deixar os objetos devidamente organizados no banco de dados, a tabela pode ter suas definições alteradas.
Alterar o owner e o schema da tabela
A tabela pode receber outro owner e ser organizada em outro schema, o que será exibido no exemplo abaixo.
alter table table_proc_01 owner to user_app_test;
alter table public.table_proc_01 set schema schema_user_app_test_01;
select *
from information_schema.table_privileges
where table_name = 'table_proc_01';
select current_database()
,pg_get_userbyid(pc.relowner) as table_owner
,psat.relname as table_name
,psat.schemaname as table_schema
,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace
from pg_class pc
join pg_stat_all_tables psat on psat.relid = pc.oid
where psat.schemaname not in ('pg_toast','pg_catalog','information_schema')
order by table_owner,
table_name;
No exemplo acima, a tabela que estava com o owner postgres foi transferida ao owner user_app_test, e o schema dela foi alterado de public para schema_user_app_test_01. Observa-se também que os privilégios foram atualizados e o grantor, que antes era o postgres, passou a ser o user_app_test.
Alterar a tablespace da tabela
A tabela pode ser alocada em outra tablespace. De modo bem generalista, não há uma necessidade e ganhos expressivos em criar tablespaces para alocar os objetos fora da tablespace default e assim organizá-los fisicamente, sendo que essa necessidade pode surgir através de análises de performance. Aqui será demonstrada a possibilidade de alteração da tablespace, atentando-se para o lock que é atribuído à tabela, causando a sua indisponibilidade por determinado tempo, o que varia conforme o tamanho da tabela.
Observação: A movimentação de uma tabela entre tablespaces gera locks o que pode impactar negativamente a sua utilização. Quando isso precisar ser realizado, atentar-se ao período da execução e qual será a metodologia para realizar a movimentação, se será feita de modo "básico"/nativo ao postgresql ou se será utilizada alguma exetensão para auxiliar no processo.
do $$
begin
for i in 1..1000000 loop
insert into schema_user_app_test_01.table_proc_01 (name, data)
values ('name', 'data');
end loop;
end $$;
alter table schema_user_app_test_01.table_proc_01 set tablespace tbs_app_test_tbl;
----- Em uma segunda sessão, paralela a alteração da tablespace na primeira sessão, executar os comandos abaixo.
\x on
select pd.datname
,pg_get_userbyid(pc.relowner) owner
,pc.relname
,psa.usename
,psa.pid
,psa.query
,psa.query_start
,round(extract(epoch from (now() - psa.state_change)),2) as elapsed_time_sec
,psa.state
,case when pl.granted = 'True' then 'helds lock'
else 'lock awaited' end as granted
,pl.locktype
,mode
from pg_locks pl
join pg_class pc on pl.relation = pc.oid
join pg_stat_activity psa on pl.pid = psa.pid
join pg_database pd on pl.database = pd.oid
where relname = 'table_proc_01';
----- Em uma segunda sessão, paralela a alteração da tablespace na primeira sessão, observar o output dos comandos acima.
select current_database()
,pg_get_userbyid(pc.relowner) as table_owner
,psat.relname as table_name
,psat.schemaname as table_schema
,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace
from pg_class pc
join pg_stat_all_tables psat on psat.relid = pc.oid
where psat.schemaname not in ('pg_toast','pg_catalog','information_schema')
order by table_owner,
table_name;
No exemplo acima, antes da movimentação, foram adicionados registros na tabela de teste. Durante a movimentação da tabela entre as tablespaces, em uma segunda sessão conectada ao banco de dados (db_test), foi consultado o lock (AccessExclusiveLock) que foi gerado na tabela.
Alterar a tablespace do índice
Aqui também será demonstrada a possibilidade de alteração da tablespace, atentando-se para o lock que é atribuído ao índice, causando a sua indisponibilidade por determinado tempo, o que varia conforme o seu tamanho. Observa-se que, mesmo tendo alterado a tablespace da tabela relativa ao índice, o índice continua alocado em sua tablespace inicial.
Observação: A movimentação de um índice entre tablespaces gera locks o que pode impactar negativamente a sua utilização. Quando isso precisar ser realizado, atentar-se ao período da execução e qual será a metodologia para realizar a movimentação, se será feita de modo "básico"/nativo ao postgresql ou se será utilizada alguma exetensão para auxiliar no processo.
select schemaname
,tablename
,indexname
,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else pi.tablespace end as table_tablespace
from pg_indexes pi
where tablename = 'table_proc_01'
order by schemaname
,tablename
,indexname;
alter index schema_user_app_test_01.idx_table_proc_01_col_id set tablespace tbs_app_test_idx;
select pd.datname
,pg_get_userbyid(pc.relowner) owner
,pc.relname
,psa.usename
,psa.pid
,psa.query
,psa.query_start
,round(extract(epoch from (now() - psa.state_change)),2) as elapsed_time_sec
,psa.state
,case when pl.granted = 'True' then 'helds lock'
else 'lock awaited' end as granted
,pl.locktype
,mode
from pg_locks pl
join pg_class pc on pl.relation = pc.oid
join pg_stat_activity psa on pl.pid = psa.pid
join pg_database pd on pl.database = pd.oid
where relname = 'idx_table_proc_01_col_id';
select schemaname
,tablename
,indexname
,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else pi.tablespace end as table_tablespace
from pg_indexes pi
--where tablename = 'table_proc_01'
order by schemaname
,tablename
,indexname;
Durante a movimentação do índice entre as tablespaces, em uma segunda sessão conectada ao banco de dados (db_test), foi consultado o lock (AccessExclusiveLock) que foi gerado na tabela.
Criar objetos especificando owner, schema e tablespace, utilizando usuário diferente do owner dos objetos
Conectado no banco de dados com um usuário diferente daquele que deve ser o owner dos objetos, definir a role (set role) para que o objeto seja criado no owner adequado, sendo que nesse caso a conexão está sendo realizada com o superuser postgres. Na especificação do comando de create abaixo, o schema e a tablespace estão sendo especificados. Após a criação do objeto, a role foi resetada e assumiu o valor default.
set role 'user_app_test';
select current_database(), session_user, current_user, current_schema;
create table schema_user_app_test_02.table_proc_02 (id serial,
name varchar(30),
data text) tablespace tbs_app_test_tbl;
reset role;
select current_database(), session_user, current_user, current_schema;
Criar objetos especificando schema e tablespace, utilizando o owner dos objetos
No exemplo abaixo foi realizada a conexão ao banco de dados (db_test) com o usuário (user_app_test) que será o owner dos objetos. Os objetos terão o owner desejado e schema e tablespace estão sendo informados nos comandos de create.
psql -h localhost -p 5432 -U user_app_test -d db_test
select current_database(), session_user, current_user, current_schema;
create table schema_user_app_test_01.table_aux_01 (id serial,
name varchar(30),
data text) tablespace tbs_app_test_tbl;
create index idx_table_aux_01_col_id on schema_user_app_test_01.table_aux_01(id) tablespace tbs_app_test_idx;
create table schema_user_app_test_02.table_aux_02 (id serial,
name varchar(30),
data text) tablespace tbs_app_test_tbl;
select current_database(),
pg_get_userbyid(pc.relowner) as table_owner,
psat.relname as table_name,
psat.schemaname as table_schema,
case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace
from pg_class pc
join pg_stat_all_tables psat on psat.relid = pc.oid
where psat.schemaname not in ('pg_toast','pg_catalog','information_schema')
--psat.relname in ('table_name')
order by table_owner,
table_name;
select schemaname
,tablename
,indexname
,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name
else pi.tablespace end as table_tablespace
from pg_indexes pi
where schemaname in ('schema_user_app_test_01')
order by schemaname
,tablename
,indexname;
Considerações
Garantir a organização do banco de dados é muito importante, portanto, assegurar que novos recursos tenham as definições adequadas e que os já existentes estejam devidamente configurados. Ao realizar alterações em objetos, ter em vista que pode ocorrer a indisponibilidade deles e consequentemente haver o impacto negativo em terceiros (aplicações, usuários, rotinas, etc.).