[Spacewalk-list] Database -rhnsnapshotpackage monster-table with monster-index?

[Tomáš Kašpárek]

Hello,

I've looked at my system and 'rhnsnapshotpackage' has 852MB, it's index 
has 995M. I have 18,771,656 records in that table so I'd say there's 
nothing wrong with data files itself.

You can purge records in rhnsnapshotpackage table by deleting records in 
rhnsnapshot table by running following sql query in postgresql console 
(you can run it by running 'spacewalk-sql -i' in shell).

delete from rhnsnapshot;

If you want to keep snapshots based on date you can run:

delete from rhnsnapshot where created < '2000-01-01 00:00:00';

Please not that deleting just from rhnsnapshotpackage table would make 
your snapshot incomplete as information about packages at the time of 
snapshot would be lost.

Other than that I do not really see other option to shrink the table 
than by purging records from that table.

Tomáš

On 01/19/2016 03:06 PM, Jonathan Hoser wrote:
> Dear all,
>
> just a quick question from my end:
> is it just us, or is 'rhnsnapshotpackage' by far the largest table in
> the postgres database?
>
> Currently we are seeing a 61GB table (1.19e+09 entries) and a 56GB index....
>
> If this is abnormal in size, does anyone have a clue/idea/recipe on how
> to shrink it?
> For performance reasons we did try to shift our indices to a nice SSD,
> but this one is... a different animal.
>
> Best
> -Jonathan
>