Fixing size bloat in Greenplum tables

I noticed recently that some queries on a table were running very slow. Simple counts were taking longer on what appeared to be smaller tables. I say appeared because the tables had 1/5 the number of rows as other tables but queries were slower. The raw data files contained about 7 GB of data. To see what the Greenplum system had for table size I ran this query:

select pg_size_pretty(pg_relation_size('schema.table_name'));

The answer was 190GB! Clearly there were problems.  This table does get reloaded every month with new data but I truncate the table before reloading it so you aren't supposed to run into issues. Anyway there turned out to be a couple of solutions. One was to run a vacuum full on the table. After running that the table size was reported as 5.635 MB. I did try a vacuum on the table but it had no impact on size. Another solution is to redistribute the data randomly then redistribute by the table key.

ALTER TABLE schema.table_name SET DISTRIBUTED RANDOMLY;
ALTER TABLE schema.table_name SET DISTRIBUTED BY (table_key);

The first statement will not actually redistribute the data. To make the system actually redistribute the data randomly run this:

ALTER TABLE schema.table_name SET WITH (REORGANIZE=true);

Redistributing by a column name will actually physically redistribute the data.

Comments

Post a Comment

Popular posts from this blog

Database, schema, and table sizes in Greenplum

Starting in the 4.x release, you get size info from the gp_toolkit schema. To get the size of all databases and their size in bytes: select sodddatname, sodddatasize from gp_toolkit.gp_size_database; To see the database size in GB, TB, and/or MB. TB: select sodddatname, (sodddatsize/1073741824)/1024 AS sizeinTB from gp_toolkit.gp_size_of_database; GB: select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database; MB: select sodddatname, (sodddatsize/1048576) AS sizeinMB from gp_toolkit.gp_size_of_database; For schema sizes , connect to your database and run: TB: select sosdnsp, (sosdschematablesize/1073741824)/1024 AS schemasizeinTB from gp_toolkit.gp_size_of_schema_disk; GB: select sosdnsp, (sosdschematablesize/1073741824) AS schemasizeinGB from gp_toolkit.gp_size_of_schema_disk; MB: select sosdnsp, (sosdschematablesize/1048576) AS schemasizeinMB from gp_toolkit.gp_size_of_schema_disk; If you want a specific schema only, add
Read more

Greenplum update with multiple tables

Recently ran into an issue using Greenplum to update a column. I had column1 in tableA that needed to be set to the value of column1 in tableB. Finally came across the correct syntax here . This is what I was looking for: UPDATE tableA  SET col1 = tableB.col1  FROM tableB  WHERE tableA.col2=tableB.col2;
Read more

Show running queries on Postgresql/Greenplum

I've got some queries that run for a long time. Sometimes longer than others. I wanted to see how long active queries have been running. Here's a view to consistently do that: CREATE VIEW public.view_activequeries AS ( SELECT age(query_start, backend_start) AS queryage,* FROM pg_stat_activity WHERE current_query NOT LIKE '%IDLE%' ORDER BY queryage DESC); Then you just run: select * from public.view_activequeries; You get a list of all active queries in descending order of how long they have been running, i.e. longest running queries listed first.
Read more