Mathieu Nebra <mateo21 'at' siteduzero.com> writes: >> (That said, I believe PostgreSQL diffs tuple updates, so in practice >> PostgreSQL might not be writing anything if you run an "update" with >> the same value. I will let someone more intimate with the internal >> details of updates to comment on this.) >> >> Secondly, an update should not take "a few seconds". You might want to >> investigate this part before you turn to further optimizations. > > Yes, I know there is a problem but I don't know if I am competent enough > to tune PostgreSQL for that. It can take a while to understand the > problem, and I'm not sure I'll have the time for that. Short story: run the query in psql prepending EXPLAIN ANALYZE in front of it and copy-paste the output in reply to that list. Long story: there are a lot of interesting material in PG official documentation about optimization. It is very worth a read but it's longer than a short story. In my experience, database performance can be degraded orders of magnitude if not configured properly. > I am, however, opened to suggestions. Maybe I'm doing something wrong > somewhere. > >> >> In our application we defer the updates to a separate asynchronous >> process using a simple queue mechanism, but in our case, we found that >> the updates are fast enough (in the order of a few milliseconds) not >> to warrant batching them into single transactions. > > A few milliseconds would be cool. That also depends on the query. If your update selects rows not according to an index you're going to be in trouble if the table hosts a lot of data, but that's fair. So you might just need an index. That might also be related to row bloat. Your query with EXPLAIN ANALYZE would tell what postgres does (if it uses an index or not). > In fact, defering to another process is a good idea, but I'm not sure if > it is easy to implement. It would be great to have some sort of UPDATE No article on the site du zéro explaining how to implement producer-consumers? :) But that must really be thought before implementing. It's not worth piling queries in memory because it will create other problems if queries are produced faster than consumed in the long run. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
