[WIP] - Partitioning support

resolves https://github.com/dbt-labs/dbt-postgres/issues/168

Problem

This is a draft PR adding partitioning support for Postgres as stated on https://github.com/dbt-labs/dbt-postgres/issues/168. It doesn't use dbt coding best practices and lacks, but it works. I'm opening it to gather feedback on how to proceed.

Syntax is copied from on dbt-bigquery partitioning:

{{ config(
    materialized = 'table',
    partition_by = {
      "field": "created_at",
      "granularity": "month"
    }
) }}

select generate_series(current_date - interval '1000 day', current_date, '1 month'::interval)::date as created_at,
        'hello' as dummy_text

Benefits this brings:

Postgres doesn't allow to partition an existing table. If dbt creates an initially partitioned table, a DBA can then manually tune them or use pg_partman extension over it. That's not possible unless dbt is able to create partitioned tables.
When using insert+write incremental strategy, you can change the access method of old partitions to Citus columnar or Hydra, which don't allow deletes or updates. Incremental methods are currently not currently usable on those environments.

Moreover, creating a custom incremental strategy replacing partitions is then relatively easy as shown in https://github.com/dbt-labs/dbt-postgres/issues/168. That incremental strategy is out of scope for this PR as there are a lot of possible variations.

IMO this patch is already in a stage that can be reviewed. I would help on some best practices and adding tests though.

Solution

To Do:

[ ] Contract config is not enforced
[ ] Tests
[ ] Partitioning means executing the SQL model twice, or temporarily storing it twice. This need to be documented.
[x] Partition names concatenate the start day of the partition (regardless of granularity). Names longer than the maximum 64 characters are still not handlded correctly.
[x] Add a default partition to handle uncovered ranges. Without specific partition support on incremental strategies, one must handle creation of future partitions. This can be done via a cron job or manually. Having a default partition means inserts won't fail. Handle incremental partitions
[ ] handle adding of new fields to the parent table

Checklist

[X] I have read the contributing guide and understand what's expected of me
[X] I have run this code in development and it appears to resolve the stated issue
[ ] This PR includes tests, or tests are not required/relevant for this PR
[X] This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX

dbt-labs / dbt-postgres