How bad is it to delete from a Greenplum AO table

greenplum

We have a few AO tables (which nowadays means "Append Optimized", not "Append Only"). Normally, we just insert into these tables.

As part of a significant system change, I need to do a fairly large delete from these tables. It's only about 2% of the data volume, and only over a small range of partitions. Eventually those partitions will roll off the end of our retention policy.

I can't find any information on the consequences of executing a delete on an AO table.

Best Answer

The main difference between heap table and AO tables is how the visibility data is stored. In heap tables visibility information is the header of each record, AO tables have a separate visibility map structure.

Once there have been UPDATE or DELETE operations on an AO table this visibility map needs to be processed. This will incur a very small addition load for reading the map and processing it. The processing is just a set/not-set check so it's a lot lighter than the range check for MVCC visibility on heap tables.

With a small percentage of rows being dropped the effects on scan operations will be negligible. If you VACUUM an AO table where there have been UPDATE or DELETE operations then the table will be compacted, this will create a lot of disk IO and may require quite a bit of space.

Related Solutions

Greenplum – Does It Support Dynamic SQL?

I cannot tell for sure, but there is a hint in the CREATE FUNCTION documentation:

langname The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum Database.

In turn, the referenced place says

The PL/pgSQL language is installed by default in Greenplum Database.

Now the EXECUTE ... USING is a PL/pgSQL feature, so the only question is which PostgreSQL version it comes from. The documentation points to 8.2 - and this way it looks like you are out of luck.

I've contacted the Greenplum support to get a definitive answer, but never got an answer.

As for working around the lack of that construct, you can obviously concatenate together any SQL string you want and execute it. Be careful to correctly quote the passed-in values and avoid SQL injection.

Greenplum – How to Add Multiple Partitions Using Dynamic SQL

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below.

CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
   p_part_dates date[];
   part_cntr int;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     part_cntr := 0;
     FOR q_add IN EXECUTE adder LOOP
       part_cntr := part_cntr + 1;
       p_part_dates[part_cntr] = q_add.dd;
       -- SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     for i in 1..part_cntr loop
       select dv_util.create_partition(p_schema,p_table,p_part_dates[i]) INTO result;
     end loop;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;

Best Answer

Related Solutions

Greenplum – Does It Support Dynamic SQL?

Greenplum – How to Add Multiple Partitions Using Dynamic SQL

Related Question