Re: huge table occupation after updates

Hi,
you're right, VACUUM FULL recovered the space, completely.
So, at this point I'm worried about my needs.
I cannot issue vacuum full as I read it locks the table.
In my DB, I (would) need to have a table with one bigint id field+ 10 bytea
fields, 100 bytes long each (more or less, not fixed).
5/10000 rows maximum, but let's say 5000.
As traffic I can suppose 10000 updates per row per day (spread over groups
of hours; each update involving two of those fields, randomly.
Also rows are chosen randomly (in my test I used a block of 2000 just to
try one possibility).
So, it's a total of 50 millions updates per day, hence (50millions * 100
bytes *2 fields updated) 10Gbytes net per day.
I'm afraid it's not possible, according to my results.
Reagrds
Pupillo

2016-12-10 13:38 GMT+01:00 Francisco Olarte <folarte(at)peoplecall(dot)com>:

> Hi Tom
>
> On Sat, Dec 10, 2016 at 1:15 PM, Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com>
> wrote:
> ...
> > Reported table size is 1.5MB. OK.
> That's 150 bytes per row, prety normal.
> > Now, for 1000 times, I update 2000 different rows each time, changing
> d0
> > filed keeping the same length, and at the end of all, I issued VACUUM.
>
> And probably autovacuum or something similar kicked in meanwhile. 2M
> updates is 200 updates per row, that's pretty heavy traffic, many
> tables do not get that in their whole lifetime.
>
> > Now table size is 29MB.
> > Why so big? What is an upper bound to estimate a table occupation on
> disk?
>
> Strictly, you could probably calculate an upper bound as row
> size*number or insertions, given an update aproximates an insertion
> plus deletion. Given the original insertion used 1.5Mb and you
> repeated thar about 200 times, I would stimate 201*1.5=301.5Mb as an
> upper bound, but I doubt that's of any use.
>
> Those many updates probably left your table badly fragmented, with few
> rows per page. On a normal usage you do not need to worry, as
> periodic vacuum would mark the space for reuse and the table will not
> grow that big. But issuing an 1k updates on 20% of the table is hardly
> normal usage, if you need this kind of usage maybe you should rethink
> your strategies.
>
> Vacuum full will probably pack the table and ''recover'' the space, it
> should be fast with just 29Mb on disk. Not knowing your intended usage
> nothing can be recommended, but I've had some usage patterns where a
> heavy update plus vacuuum full was successfully used.
>
> Francisco Olarte.
>