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/14/tablespace_hdd'; [[local]:5432] postgres@postgres=# CREATE TABLESPACE tablespace_ssd LOCATION '/var/lib/pgsql/14/tablespace_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/14/data e o diretório de tablespace for
/var/lib/pgsql/14/data/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/14/tablespace_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;