# Base de Dados ## Segmentação e particionamento Estudo feito a partir da seção [E1.3.3 (Segmentação e particionamento) da Especificação](/specs). ### Sugestão DB-PARTITION-00 (Plano A) * Usar [Particionamento Declarativo](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) por evento em tabelas `event_territory_{event_type}` (e `event_{event_type}?). #### Vantagens * O particionamento declarativo é mais "transparente" e automático: desde que as partições sejam criadas apropriadamente, `INSERTs`, `DELETEs` e `SELECTs` na tabela particionada adicionam ou removem dados nas respectivas partições. * O particionamento declarativo seria mais impactante tabelas que ultrapassam a memória RAM. * O `event_territory` do PRODES, o conjunto mais volumoso não passaria de 16GB de RAM, estando dentro dos recursos disponíveis. * Esta abordagem não exclui queries que misturam tipos de eventos, o que pode ser útil no futuro. * Ganhos de performance são muitos, por exemplo um `DELETE` passa a ser `DROP TABLE` nas re-importações. * Separa tipos de eventos de alta incidência dos de baixa, acelerando as consultas para tabelas com poucos eventos. #### Modelagem Nova tabela `event`: CREATE TABLE event ( orig_id INTEGER, type VARCHAR, class_1 INTEGER, class_2 VARCHAR, date DATE, coduf INTEGER, uf VARCHAR, codmun INTEGER, mun VARCHAR, geom geometry ) PARTITION BY LIST (type); Nova tabela `event_territory`: CREATE TABLE public.event_territory ( e_id integer, e_type character varying COLLATE pg_catalog."default", e_class_1 integer, e_class_2 character varying COLLATE pg_catalog."default", e_date date, t_type character varying COLLATE pg_catalog."default", t_name character varying COLLATE pg_catalog."default", t_id_orig integer, id_mun integer, id_uf integer, name_mun character varying COLLATE pg_catalog."default", name_uf character varying COLLATE pg_catalog."default", area_ha numeric, geom geometry(Geometry,{srid}), ovlp_ext boolean, ovlp_int boolean, ovlp_extnobuff boolean ) PARTITION BY LIST (e_type); Exemplo de partições: CREATE TABLE event_{e_type} PARTITION OF event FOR VALUES IN (e_type); CREATE TABLE event_territory_{e_type} PARTITION OF event_territory FOR VALUES IN (e_type); #### Implementação * O decorador `@bootstrap` precisará de um argumento do tipo `e_type` especificando o tipo de evento, que será usado para passar à função `create_event_tables()`. * O script SQL `create_event_tables.sql` precisará suportar o parâmetro `{e_type}`, assim como outros scripts SQL. * A função `create_event_tables()` precisará ser alterada para: * Suportar um parâmetro `e_type` a se repassado para o script `create_event_tables.sql` para criar a partição por tipo de event.. * Incluir `ALTER TABLE IF EXISTS event_territory ATTACH PARTITION event_territory_{e_type} FOR VALUES IN ({e_type})` para anexar a partição à tabela principal caso necessário. Isto permite que a modelagem da tabela seja dinamicamente ajustada conforme novos tipos de eventos (novos importadores) sejam incluídos. ### Sugestão DB-PARTITION-01 (Plano B) * Usar [Particionamento Declarativo](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) por evento na tabela `event_territory_{event_type}` e também por data. * Constitui uma continuiação da sugestão `DB-PARTITION-00` que inclui o particionamento por data a ser adotada caso seja necessário aumento ainda maior da performance. * Particionamentos poderão ser feitos por `an`o ou por `ano e mês`. #### Vantagens Particionamento declarativo por data: * Reduz o espaço de busca por eventos quanto menor for a faixa de datas selecionadas. * Escalabilidade independentemente da quantidade de recursos alocadas ao cluster PostgreSQL. ### Sugestão DB-PARTITION-02 * Usar segmentação por evento em tabelas do tipo `event_territory_{event_type}`. * Usar [Particionamento Declarativo](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) por data para cada tabela `event_territory_{event_type}`. #### Vantagens * Aumenta ainda mais a possibilidade de paralelismo à incorporação. * Adiciona flexibilidade pois, no futuro, rotinas de incorporação por evento poderão ser realizadas em _nodes_ distintos da plataforma e depois integrados numa mesma base para consumo. * Assim como nas sugestões anteriores, separa tipos de eventos de alta incidência dos de baixa, acelerando as consultas para tabelas com poucos eventos. * Possui as vantagens de particionamento indicadas nas sugestões anteriores. #### Desvantagens Segmentação por evento: * Futura queries que misturam tipos de eventos tornam-se mais difíceis, necessitando abordagens alternativas, como `JOINs`, views dinâmicas ou mesmo cruzamento feito na biblioteca python, porém, _se for considerano que os tipos de evento tem metodologia e resultados não são comparáveis, não haverá muito significado nesse tipo de cruzamento para computação de eventos conjuntos, o que pode resultar em incoerências, contagens duplicadas etc_. Particionamento declarativo por data: * Aplicam-se as limitações indicadas na [documentação do PostgreSQL sobre Particionamento Declarativo](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) e que não tem um grande impacto no modelo de dados e na implementação das bibliotecas de importação e consulta. #### Modelagem Exemplo de tabelas com o cruzamento de evento com território (`event_territory`): CREATE TABLE public.event_territory_{e_type} ( e_id integer, e_type character varying COLLATE pg_catalog."default", e_class_1 integer, e_class_2 character varying COLLATE pg_catalog."default", e_date date, t_name character varying COLLATE pg_catalog."default", t_id_orig integer, id_mun integer, id_uf integer, name_mun character varying COLLATE pg_catalog."default", name_uf character varying COLLATE pg_catalog."default", area_ha numeric, geom geometry(Geometry,{srid}), ovlp_ext boolean, ovlp_int boolean, ovlp_extnobuff boolean ) PARTITION BY RANGE(e_date); Partições de data: CREATE TABLE event_territory_{e_type}_{year}_{month} PARTITION OF event_territory FOR VALUES FROM ('{year}-{month}-01') TO ('{next_year}-{next_month}-01'); Onde: * São criadas tantas partições de `event_territory_{e_type}_` quanto forem os meses desde um tempo `t0` -- referente ao primeiro mês de ocorrência do dado ou de um `$t0` inicial para toda a aplicação -- até a o mês atual. * `{next_year} = {year}` caso `{year} < 12`, senão `{next_year} = {year} + 1`. #### Implementação * O decorador `@bootstrap` precisará de um argumento do tipo `e_type` especificando o tipo de evento, que será usado para passar à função `create_event_tables()`. * O script SQL `create_event_tables.sql` precisará suportar o parâmetro `{e_type}`, assim como outros scripts SQL. * Os métodos em `query.py` precisarão de um condicional para determinar qual tabela consumir (`FROM`) de acordo com o parâmtro `type_event`. * A função `create_event_tables()` precisará ser alterada para: * Suportar um parâmetro `e_type` a se repassado para o script `create_event_tables.sql`. * Ter um loop que crie dinamicamente as tabelas `event_territory_{e_type}_{year}_{month}` desde `t0` até o mês atual usando psycopg2. ## Organização por prefixos/sufixos ou schemas ### Diagnóstico Até ao menos 19/04/2021, todas as tabelas do projeto estão contidas no schema `public`: alertas=> \d List of relations Schema | Name | Type | Owner --------+-------------------------------------------+----------+--------------- public | amazon_dashboard_canonical | table | alertas public | amazon_dashboard_canonical_cluster_ID_seq | sequence | alertas public | apae_monit | table | alertas_admin public | apae_monit_id_seq | sequence | alertas_admin public | apaf_monit | table | alertas_admin public | apaf_monit_id_seq | sequence | alertas_admin public | bla_base250 | table | alertas_admin public | bla_base250_id_seq | sequence | alertas_admin public | bla_base250_wgs84 | table | alertas_admin public | bla_base250_wgs84_id_seq | sequence | alertas_admin public | deter_canonical | table | alertas public | deter_canonical_id_seq | sequence | alertas public | event | table | alertas public | event_id_seq | sequence | alertas public | event_territory | table | alertas public | event_territory_id_seq | sequence | alertas public | firms_modis_canonical | table | alertas public | firms_modis_canonical_id_seq | sequence | alertas public | firms_viirs_canonical | table | alertas public | firms_viirs_canonical_id_seq | sequence | alertas public | geography_columns | view | postgres public | geometry_columns | view | postgres public | mun_base250 | table | alertas_admin public | mun_base250_id_seq | sequence | alertas_admin public | sad_canonical | table | alertas_admin public | sad_canonical_id_seq | sequence | alertas_admin public | spatial_ref_sys | table | postgres public | territory | table | alertas_admin public | territory_id_seq | sequence | alertas_admin public | tis_monit | table | alertas_admin public | tis_monit_id_seq | sequence | alertas_admin public | tqs_monit | table | alertas_admin public | tqs_monit_id_seq | sequence | alertas_admin public | uce_monit | table | alertas_admin public | uce_monit_id_seq | sequence | alertas_admin public | ucf_monit | table | alertas_admin public | ucf_monit_id_seq | sequence | alertas_admin public | ufs_base250 | table | alertas_admin public | ufs_base250_id_seq | sequence | alertas_admin (39 rows) alertas=> ### Sugestão DB-ORG-00: organização por prefixos/sufixos Consiste na padronização do prefixo/sufixo das tabelas de acordo com o conjunto de dados: * Prefixo: nome do conjunto de dados (`isa`, `deter` etc). * Sufixo: `canonical` caso se tratem dos dados canônicos. * Tabelas `deter`, `firms_`, `amazon_dashboard` por exemplo já estão organizadas assim. * Tabelas que precisam ser alteradas são do conjunto do ISA (e talvez IBGE?). * Exemplo: `tis_monit` passa a ser`isa_tis_monit_canonical`. ### Sugestão DB-ORG-01: organização básica por schemas separando canônicos Consiste em, ao invés de padronizar por sufixo, em utilizar schemas distintos: * Schema `canonical`: usado para abrigar todas as tabelas canônicas (e neste caso as tabelas do ISA ainda precisariam do prefixo `isa_`. * Schema `public`: tabelas de uso do sitema (`event`, `territory`, `event_territory_*` etc). ### Sugestão DB-ORG-02: organização total por schemas * Schema `base`: territórios canônicos. * Schema `event_canonical`: eventos canônicos, não processados. * Schema `event_processed`: dados processados, como tabelas do tipo `event` e `event_territory`. * Schema `public`: outras tabelas.