Loading in In active partition
Dear's
i have table in and partitioned with year and month, i have question if i getting update on a record which is an year old. does vertica creates a new partition key with update time or it will create the a year old partition key in which the actual record inserted in db.
e.g.
If record A inserted in 2019-07-01 vertica create partition key at that time with key "201907" and suppose we have received and update that record state changed today so vertica create partition "202007" or it will have same partition key "201907"
Br,
-1
Answers
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,