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

Tomáš Kašpárek

2016-01-19 15:03:05 UTC

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áš

Post by Jonathan Hoser
Dear all,
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