Thread: Tables grow in size when issuing UPDATEs! Why??
Hi,
I have noticed a strange thing when dealing with postgreSQL.
Foreword: this is all about postgres tables increasing in
size extremely fast when doing updates. My sainness is
decreasing in approximatly the same speed. Please look.
When I do this:
* create database foo
* create table test
CREATE TABLE test (
foo int4
);
and make, for example, 20 inserts into this. Arbitrary data.
Then, I make in another terminal: (user with permission)
cd /var/lib/postgres/data/base/foo
then,
watch -n 1 'ls -al test'
On my system it is now 8k big.
then (a couple of times),
update test set foo=5;
10 times is enough for it to get 16k big. I realised that it
increases in size EXTREMELY fast when only UDPATing. I find
this strange, as I update timestamp in my real database
often and in 5 minutes it is 2 megabytes big and containing
only 18 rows! (NO BULLSHIT!!)
Please help me with this I am quite desperate...
Daniel Åkerud
On Wed, 28 Mar 2001, Daniel ?erud wrote: > Hi, > I have noticed a strange thing when dealing with postgreSQL. > > Foreword: this is all about postgres tables increasing in > size extremely fast when doing updates. My sainness is > decreasing in approximatly the same speed. Please look. > > ... > > 10 times is enough for it to get 16k big. I realised that it > increases in size EXTREMELY fast when only UDPATing. I find > this strange, as I update timestamp in my real database > often and in 5 minutes it is 2 megabytes big and containing > only 18 rows! (NO BULLSHIT!!) > > Please help me with this I am quite desperate... Postgres uses a non-overwriting storage manager. You are going to want to vacuum the table regularly to cut the table back to just rows that are visible.
> On Wed, 28 Mar 2001, Daniel ?erud wrote: > > > Hi, > > I have noticed a strange thing when dealing with postgreSQL. > > > > Foreword: this is all about postgres tables increasing in > > size extremely fast when doing updates. My sainness is > > decreasing in approximatly the same speed. Please look. > > 10 times is enough for it to get 16k big. I realised that it > > increases in size EXTREMELY fast when only UDPATing. I find > > this strange, as I update timestamp in my real database > > often and in 5 minutes it is 2 megabytes big and containing > > only 18 rows! (NO BULLSHIT!!) > > > > Please help me with this I am quite desperate... > > Postgres uses a non-overwriting storage manager. You are going > to want to vacuum the table regularly to cut the table back > to just rows that are visible. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > I noticed it greatly affects the speed. Is there some way you can turn that feature off as I am doing speed analysis of postgresql (well, i'm kinda tweaking). How often should you run it? I guess it is bad to run it after every update. Another thought: doing _only_ inserts and selects wont affect the speed of the db? Anyway, thanks alot! I will read up on it right away. Daniel Åkerud
At 09:26 PM 28-03-2001 GMT, Daniel ?erud wrote: > >I noticed it greatly affects the speed. >Is there some way you can turn that feature off as I am >doing speed analysis of postgresql (well, i'm kinda >tweaking). How often should you run it? I guess it is bad to >run it after every update. > >Another thought: doing _only_ inserts and selects wont >affect the speed of the db? > Yeah. I also noticed that update + indexed select repeatedly on the same row slows down over time, whereas insert + indexed select doesn't seem to slow down for some reason. Maybe it has something to do with the indexes, or the MVCC thingy - scanning through multiple expired rows. The speed goes down quite significantly from the initial peak, so benchmarks on a pristine database aren't really reliable for real world scenarios unless you vacuum every 10000 updates or so ;). But it's a shame because Postgres is blazingly fast at the start. Link.