postgres postgresql tablespace
Um tablespace é um objeto no PostgreSQL, ou seja, ele tem um nome, atributos próprios e pode ser criado, alterado e destruído. Ele é usado para entregarmos ao PostgreSQL um local para que ele guarde outros objetos (como tabelas e índices).
Inicialmente existem dois tablespaces. O tablespace pg_default
aponta para o
diretório base
e é o padrão para novos bancos de dados e objetos. O
tablespace pg_global
aponta para o diretório global
e contém as definições
dos objetos globais.
Outros tablespaces são diretórios externos ao diretório de dados principal.
Um link simbólico para eles é mantido dentro de pg_tblspc
.
┌───────────────────┐ ╔════════════════════╡ instância/cluster ╞═════════════════════╗ ║ └───────────────────┘ ║ ╠═╡global/╞═══════╗ ╔═════════════════════════════════╡base/╞═╣ ║ ║ ║ ║ ║ O O ║ ║ ┏┥banco de dados┝┓ ┏━┥banco de dados┝━┓ ║ ║ ─┼─ ─┼─ ║ ║ ┃└┤postgres├────┘┃ ┃ ┌─┤schema├─────┐ ┃ ║ ║ │ │ ║ ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ tabelas │ ┃ ║ ║ ╱ ╲ ╱ ╲ ║ ║ ┏┥banco de dados┝┓ ┃ │ ☑ índices │ ┃ ║ ║ ☑ usuários ║ ║ ┃└┤template1├───┘┃ ┃ │ ☑ visões │ ┃ ║ ║ ☑ grupos ║ ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ sequências │ ┃ ║ ║ ☑ slots de ║ ║ ┏┥banco de dados┝┓ ┃ │ ☑ … │ ┃ ║ ║ ☑ replicação ║ ║ ┃└┤template0├───┘┃ ┃ └──────────────┘ ┃ ║ ║ ☑ pg_control ║ ║ ┗━━━━━━━━━━━━━━━━┛ ┗━━━━━━━━━━━━━━━━━━┛ ║ ║ ║ ║ ║ ╠═════════════════╝ ╚═════════════════════════════════════════╣ ║ ║ ╠═╡pg_wal/╞══════════════════════════╗ ╔══════╡pg_tblspc/╞═╣ ║ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ║ ║ ║ ║ │…++++│→│…++++│→│…++++│→│…++++│→… ║ ║ ║ ║ └┤/…1├┘ └┤/…2├┘ └┤/…3├┘ └┤/…4├┘ ║ ║ │ ║ ╚════════════════════════════════════╩═════╩════════╪══════════╝ │ ↓ ╔╡/…╞╗ ║ ║ ╚════╝
Tablespaces têm dois propósitos principais e uma praticidade extra:
- expandir o espaço de armazenamento disponível para o PostgreSQL com mais volumes
- apresentar armazenamentos de perfis físicos diferentes (SSD, HDD...) para o PostgreSQL
- agrupar parâmetros de I/O
À medida que o volume de dados cresce, pode ser difícil aumentar também o volume original do diretório de dados e o seu respectivo sistema de arquivos. A solução é adicionar novos volumes independentes, com sistemas de arquivos também independentes, e então informar o PostgreSQL da disponibilidade dessas novas localizações. Assim é possível aumentar o espaço disponível para o PostgreSQL com trabalho e indisponibilidade mínimos.
Mídias físicas podem ter propriedades diferentes, como velocidades de leitura e escrita, latência, política de cache e apoio de bateria, assim como custo e disponibilidade. Por isso é útil mover dados que sofrem alguns padrões de leitura e escrita para mídias mais adequadas para esses padrões ou que minimizem o custo. Por exemplo, tabelas que recebem apenas leituras sequenciais podem ser armazenadas em RAID 10 de HDDs baratos; e índices que recebem muitas pequenas leituras aleatórias podem ser movidos para SSDs de baixa latência.
Dado que existem as diferenças físicas das mídias abaixo dos tablespaces e que o PostgreSQL não tem forma de detectar isso automaticamente, os parâmetros de I/O que são usados pelo otimizador de consultas precisam ser fornecidos. Tablespaces, portanto, são o ponto ideal para isso.
Os parâmetros disponíveis atualmente são:
- effective_io_concurrency
- random_page_cost
- seq_page_cost
Nota: alguns sistemas operacionais disponibilizam interfaces de consultas primitivas, mas ainda assim é impossível descobrir programaticamente os parâmetros de um RAID de hardware ou o que se encontra através de uma SAN e dentro de um storage remoto. Então o PostgreSQL assume que o DBA irá informar as informações que o otimizador precisa através desses parâmetros.
Usando \db
ou \db+
:
[[local]:5432] postgres@postgres=# \db+ List of tablespaces ╔════════════╤══════════╤══════════╤═══════════════════╤═════════╤════════╤═════════════╗ ║ Name │ Owner │ Location │ Access privileges │ Options │ Size │ Description ║ ╠════════════╪══════════╪══════════╪═══════════════════╪═════════╪════════╪═════════════╣ ║ pg_default │ postgres │ │ │ │ 23 MB │ ║ ║ pg_global │ postgres │ │ │ │ 623 kB │ ║ ╚════════════╧══════════╧══════════╧═══════════════════╧═════════╧════════╧═════════════╝
Considerando que já exista um diretório montado em algum ponto e que o usuário do banco de dados já tem permissão de leitura e escrita nele, criar um tablespace é:
[[local]:5432] postgres@postgres=# CREATE TABLESPACE tablespace_hdd LOCATION '/var/lib/pgsql/16/gtablespace_hdd'; [[local]:5432] postgres@postgres=# CREATE TABLESPACE tablespace_ssd LOCATION '/var/lib/pgsql/16/gtablespace_ssd';
IMPORTANTE: O diretório do tablespace não deve estar localizado dentro do
diretório de dados do PostgreSQL, já que isso traz problemas para várias
atividades de backup, restauração e monitoramento. Por exemplo, se o diretório
de dados for /var/lib/pgsql/16/gdata
e o diretório de tablespace for
/var/lib/pgsql/16/gdata/tablespace_ssd
, o PostgreSQL irá emitir um alerta durante
o CREATE TABLESPACE. Neste caso, monte o volume em um diretório ao lado, como em
/var/lib/pgsql/16/gtablespace_ssd
(note a falta do /data/
).
[[local]:5432] postgres@postgres=# ALTER TABLESPACE tablespace_hdd set (effective_io_concurrency = 16); [[local]:5432] postgres@postgres=# ALTER TABLESPACE tablespace_ssd set (effective_io_concurrency = 200);
[[local]:5432] postgres@postgres=# CREATE TABLE tabela () TABLESPACE tablespace_hdd;
[[local]:5432] postgres@postgres=# ALTER TABLE tabela SET TABLESPACE tablespace_ssd;
[[local]:5432] postgres@postgres=# ALTER TABLE ALL IN TABLESPACE tablespace_ssd SET TABLESPACE pg_default;
[[local]:5432] postgres@postgres=# CREATE DATABASE teste TABLESPACE tablespace_hdd;
[[local]:5432] postgres@postgres=# ALTER DATABASE teste SET TABLESPACE pg_default;
[[local]:5432] postgres@postgres=# ALTER DATABASE teste SET default_tablespace = 'tablespace_ssd';
[[local]:5432] postgres@postgres=# DROP TABLE tabela; [[local]:5432] postgres@postgres=# DROP DATABASE teste; [[local]:5432] postgres@postgres=# DROP TABLESPACE tablespace_hdd; [[local]:5432] postgres@postgres=# DROP TABLESPACE tablespace_ssd;