Jan Krcmar wrote:
>> You might consider partitioning this table by date, either by day or by
>> week, and instead of deleting old rows, drop entire old partitions
>>
> this is not really good workaround...
>
It is in fact the only good workaround for your problem, which you'll
eventually come to realize if you struggle with this class of problem
for long enough. You can continue to fight with autovacuum forever, but
it's a battle you'll never quite win if you're deleting 2GB per day.
Even if you get vacuum running often enough to clean up the space,
you'll still have a constant struggle to keep your indexes working
efficiently.
Or you can partition by day or week and make the entire problem go
away. Dropping an old partition requires no vacuum cleanup and leaves
behind no index issues. It really is the right solution here if you
want to solve this problem once, rather than continuing to fight it a
little every single day forever.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us