Re: Question about partition table - Mailing list pgsql-general

Hello Yuxia,

As time partitioning is a natively available, I recommend you to use time-partitioning, as it is.

You can find below an example I used to subpartition my transaction table by quaterly partition.

I have a repository table named miniexport.tby_part_followup to pilot partition creation.

    FOR live_part_table IN
      SELECT id_business_unit,
        date_trunc ('quarter', tpf_dt_min) as min_timestamp,
        date_trunc ('quarter', tpf_dt_max + interval '3 months') as max_timestamp
        FROM miniexport.tby_part_followup
        WHERE tpf_bool_part_BU_Quarter_ok = false
        ORDER BY id_business_unit
        LIMIT bi_max_bu LOOP


        --RAISE NOTICE 'Creating partitions into schema posdata2 for BU % ...', live_part_table.id_business_unit::varchar;
        EXECUTE 'CREATE TABLE posdata2.transaction_p' || live_part_table.id_business_unit::varchar || ' PARTITION OF posdata2.transaction FOR VALUES in (' || live_part_table.id_business_unit::varchar || ') PARTITION BY range (transaction_date);';

        -- Create quarter partitions
        dt_curr_timestamp := live_part_table.min_timestamp;
        WHILE dt_curr_timestamp <= live_part_table.max_timestamp LOOP
          -- Set running variables
          SELECT EXTRACT(YEAR FROM dt_curr_timestamp)  INTO int_curr_year;
          SELECT EXTRACT(QUARTER FROM dt_curr_timestamp) INTO int_curr_quarter;

          --RAISE NOTICE 'Creating SubPartition for BU %, QUARTER %', live_part_table.id_business_unit::varchar, int_curr_year::varchar || 'Q' || int_curr_quarter::varchar;
          EXECUTE 'CREATE TABLE posdata2.transaction_p' || live_part_table.id_business_unit::varchar || '_' || int_curr_year::varchar || 'Q' || int_curr_quarter::varchar || ' PARTITION OF posdata2.transaction_p' || live_part_table.id_business_unit::varchar || ' FOR VALUES FROM (''' || dt_curr_timestamp || ''') TO (''' || dt_curr_timestamp + interval '3 months' || ''')';
          EXECUTE 'CREATE UNIQUE INDEX transaction_p' || live_part_table.id_business_unit::varchar || '_' || int_curr_year::varchar || 'Q' || int_curr_quarter::varchar || '_idx01 ON posdata2.transaction_p' || live_part_table.id_business_unit::varchar || '_' || int_curr_year::varchar || 'Q' || int_curr_quarter::varchar || ' (id_transaction, id_business_unit);';

          -- Increment dt_curr_timestamp value
          dt_curr_timestamp := dt_curr_timestamp + interval '3 months';
        END LOOP;

        EXECUTE 'UPDATE miniexport.tby_part_followup SET tpf_bool_part_BU_Quarter_ok = true where id_business_unit = ' || live_part_table.id_business_unit::varchar || ';';

        a_count = a_count + 1;
        --GET DIAGNOSTICS a_count = ROW_COUNT;
    END LOOP;

Moreover, Sébastien Lardière wrote a tooling kit to manage time partitions:

I hope this helps!

Regards,

Thomas