database speed

[Chris Stokes]

Hi all,

WE currently use PG for the local database on our POS workstations in shops. Data is then extracted and sent back to head office. Also product updates etc are regularly sent to the shops. I have noticed over time that the shop PG databases get slower and slower. If I do a pg_dump, recreate the database and then restore the data - all speed problems are gone and the database folder shrinks in size substantially.

From this information could anyone tell me what are the likely causes and what we may or may not be doing in our extractions and updates that are not good PG insert, update, delete procedures

Thanks

Chris Stokes
Senior Systems Consultant
Bass Software

Phone: [phone] (Direct)
Fax: [phone]
Mobile: [phone]

ch****@basssoftware.com <mailto:ch****@basssoftware.com>
www.basssoftware.com <http://www.basssoftware.com/>

Level 5 534 Church Street
Richmond, Victoria, 3121, Australia


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Doug McNaught]

"Chris Stokes" <Ch****@basssoftware.com> writes:

WE currently use PG for the local database on our POS workstations
in shops. Data is then extracted and sent back to head office. Also
product updates etc are regularly sent to the shops. I have noticed
over time that the shop PG databases get slower and slower. If I do
a pg_dump, recreate the database and then restore the data - all
speed problems are gone and the database folder shrinks in size
substantially.

From this information could anyone tell me what are the likely
causes and what we may or may not be doing in our extractions and
updates that are not good PG insert, update, delete procedures

You should periodically be doing:

a) VACUUM (at least once a day, probably) and
b) REINDEX

Are you doing both of these? See the docs if you're not familiar with
them.

The REINDEX is needed because VACUUM doesn't free up index space in
some circumstances. 7.4 (currently in late beta) will fix this.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Martijn van Oosterhout]

On Fri, Oct 31, 2003 at 11:54:55AM +1100, Chris Stokes wrote:

Hi all,

WE currently use PG for the local database on our POS workstations in
shops. Data is then extracted and sent back to head office. Also product
updates etc are regularly sent to the shops. I have noticed over time that
the shop PG databases get slower and slower. If I do a pg_dump, recreate
the database and then restore the data - all speed problems are gone and
the database folder shrinks in size substantially.

From this information could anyone tell me what are the likely causes and
what we may or may not be doing in our extractions and updates that are
not good PG insert, update, delete procedures

The three important steps for maintainence of a postgresql database:

1. ANALYZE
2. VACUUM [FULL]
3. REINDEX

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/ocEAY5Twig3Ge+YRApHeAJ0T5Q+tq6eH1cjyUbwYfo01b1BLVw CfasJ8
yzT0Kc6xd9/YM/qjARRsbuw=
=kMlq
-----END PGP SIGNATURE-----