Re: How would you store read/unread topic status?

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