Base de Dados¶

Segmentação e particionamento¶

Estudo feito a partir da seção E1.3.3 (Segmentação e particionamento) da Especificação.

Sugestão DB-PARTITION-00 (Plano A)¶

Usar Particionamento Declarativo 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 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 ano 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 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 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 serisa_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.