Loading in In active partition

Vertica will create a new partition key...

Example:

dbadmin=> CREATE TABLE t (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> INSERT INTO t SELECT 201907;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT do_tm_task('mergeout', 't');
                           do_tm_task
----------------------------------------------------------------
 Task: mergeout
(Table: public.t) (Projection: public.t_super)

(1 row)

dbadmin=> SELECT partition_key FROM partitions WHERE projection_name = 't_super';
 partition_key
---------------
 201907
(1 row)

dbadmin=> UPDATE t SET c = 202007;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT do_tm_task('mergeout', 't');
                           do_tm_task
----------------------------------------------------------------
 Task: mergeout
(Table: public.t) (Projection: public.t_super)

(1 row)

dbadmin=> SELECT partition_key FROM partitions WHERE projection_name = 't_super';
 partition_key
---------------
 201907
 202007
(2 rows)

You can drop the partition manually:

dbadmin=> SELECT drop_partitions('t', 201907, 201907);
  drop_partitions
-------------------
 Partition dropped
(1 row)

dbadmin=> SELECT partition_key FROM partitions WHERE projection_name = 't_super';
 partition_key
---------------
 202007
(1 row)

Doc Page:

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/DROP_PARTITIONS.htm

Hi Jim, Thanks for detailed explanation can you please confirm if partition key 201907 is not droped will the data loaded to both partitions or either of 1 active and inactive.

Br,

In the simplest case, the ActivePartitionCount config parameter is 1 and the table's ACTIVEPARTITIONCOUNT is NULL (i.e. not set) . Then the most recently created partition is the Active one. So in my example, 202007 was the Active Partition after running the UPDATE statement.

Example:

dbadmin=> DROP TABLE t;
DROP TABLE

dbadmin=> CREATE TABLE t (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> INSERT INTO t SELECT 201907;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT do_tm_task('mergeout', 't');
                           do_tm_task
----------------------------------------------------------------
 Task: mergeout
(Table: public.t) (Projection: public.t_super)

(1 row)

dbadmin=> SELECT p.partition_key AS active_partition_key
dbadmin->   FROM partitions p
dbadmin->   JOIN strata s
dbadmin->     ON p.partition_key = s.stratum_key
dbadmin->    AND p.node_name=s.node_name
dbadmin->  WHERE p.projection_name = 't_super'
dbadmin->  ORDER BY p.node_name, p.partition_key;
 active_partition_key
----------------------
 201907
(1 row)

dbadmin=> UPDATE t SET c = 202007;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT do_tm_task('mergeout', 't');
                           do_tm_task
----------------------------------------------------------------
 Task: mergeout
(Table: public.t) (Projection: public.t_super)

(1 row)

dbadmin=> SELECT p.partition_key AS active_partition_key
dbadmin->   FROM partitions p
dbadmin->   JOIN strata s
dbadmin->     ON p.partition_key = s.stratum_key
dbadmin->    AND p.node_name=s.node_name
dbadmin->  WHERE p.projection_name = 't_super'
dbadmin->  ORDER BY p.node_name, p.partition_key;
 active_partition_key
----------------------
 202007
(1 row)

Read up on this topic here:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Partitions/ActivePartitions.htm

Hi Jim, for instense if mergout takes huge time how can we forcely delete this inactive partition also please confirm what is the impact if we delete these inactive partition ?

Br,