Re: Using indexes for partial index builds

On 3/13/13 7:10 PM, Ants Aasma wrote:
> On Thu, Mar 14, 2013 at 1:51 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> On 3/12/13 9:10 AM, Ants Aasma wrote:
>>> I have a feeling this is an increasingly widespread pattern with a
>>> proliferation of mobile devices that need syncing.
>>
>> If you're doing that with timestamps you're asking for a slew of problems,
>> not all of which can be solved by just adding some random amount of fluff to
>> your criteria. A queue-based solution is often a more robust solution, even
>> if it is harder to implement.
>
> Do you know of anything else besides the obvious issues with having to
> use one clocksource and ensure that it produces monotonic timestamps?

Those issues aren't enough? :)

> My first reaction was also that this is what queues are meant for, but
> the proposed solution seems to work surprisingly well. Unless you can
> point at some glaring hole that I'm missing I would say that it is
> good enough for a rather wide range of syncing problems.

It depends on how critical it is not to miss events. Timestamps in tables are always taken before transaction commit, so you can sometimes have a significant delay. You have to make certain the timestamp can't be changed, and that rows can't be deleted. It's also tricky to make certain you don't see any events twice.

Given all that, and how easy PgQ is to use, I don't understand why anyone would go with timestamps...
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net