managing high load of a postgresql database

Solution 1:

Check that your tables have been vacuumed properly. High CPU usage on a postgres instance is usually a sign that there are a large number of deleted rows in the table.

If you are using auto vacuum, check the table statistics to check that it is actually running

Solution 2:

'High load' probably meand a lot of queries running in parallel. Try running the following query:

SELECT * FROM pg_stat_activity;

This will show what the server is doing at the moment. Maybe you can see something suspicious here? A query which should not be called that often?

Also, for debugging performance problems it is very useful to set the log_min_duration_statement option in postgresql.conf. This will show you the queries which take a lot of time to execute. These are probably those which cause most load. Usually it takes to optimize a single faulty query (by rewriting it, adding indexes or tuning PostgreSQL configuration) to lower server load a lot.