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
Pupillo
2016-12-10 13:38 GMT+01:00 Francisco Olarte <folarte@xxxxxxxxxxxxxx>:
Hi Tom
On Sat, Dec 10, 2016 at 1:15 PM, Tom DalPozzo <t.dalpozzo@xxxxxxxxx> 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.
