PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the query is 2ms then next day it got to 15ms then day after 30ms.

By query I refer here to a combination of either:

• insert a row into table 2, select a row from table 2, select a row in table 3, update a row in table 3, commit

• insert a row into table 1, select a row in table 3, update a row in table 3, commit

I wonder what might be the reason of that or which settings from the configuration file should I consider setting and how? I observed the problem on Ubuntu machine where database was set and the primary keys were not added. On the other hand on Win which I develop on it was not observed (it was running constantly on average 3ms per query for 7 days).

I noticed that in the new database (on Ubuntu) there were no primary keys on any table, as oppose to the one I develop on. Could the lack of primary keys have the negative impact on this sort of query?

I thought I will ask this question in the mean time as I am moving my whole db from my development machine to the test one.

On development I used PostgreSQL 8.4 (CPU: Intel i7 740QM, RAM: 6GB), on test there is PostgreSQL 9.1 (CPU: Intel i3-2100, RAM: 3.8GB).

UPDATE: autovacuum related parameters:

#autovacuum = on        
#log_autovacuum_min_duration = -1   
#autovacuum_max_workers = 3     
#autovacuum_naptime = 1min      
#autovacuum_vacuum_threshold = 50   
#autovacuum_analyze_threshold = 50  
#autovacuum_vacuum_scale_factor = 0.2   
#autovacuum_analyze_scale_factor = 0.1  
#autovacuum_freeze_max_age = 200000000  
#autovacuum_vacuum_cost_delay = 20ms    
#autovacuum_vacuum_cost_limit = -1

UPDATE2:
It appears that the problem occurs on the development machine as well, but I remember it running fine before. Never the less I did some more testing and did run EXPLAIN ANALYZE on the query, which I takes th

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.

The side effect of this is that queries via primary and unique keys are usually "fast".

Now if you haven't defined primary or unique keys on your tables,

• You don't have a relational table but you have junk (OK, this is a contentious opinion, but a relational model needs keys on all tables).

• queries on this table (in the absence of any other indexes) will become slower as more data is inserted into the table.

So yes, the absence of primary keys will cause this!

StackExchange Database Administrators Q#27519, answer score: 5