Hello all,
one of our customers is using PostgreSQL with tables containing millions
of records. A simple 'SELECT * FROM <table>' takes way too much time in
that case, so we have advised him to use the LIMIT and OFFSET clauses.
However now he has a concurrency problem. Records deleted, added or
updated in one process have an influence on the OFFSET value of another
process such that records are either skipped of read again.
The solution to that problem is to use transactions with isolation level
serializable. But to use transactions around a loop that reads millions
of records is far from ideal I think.
Does anyone have a suggestion for this problem ? Is there for instance
an alternative to LIMIT/OFFSET so that SELECT on large tables has a good
performance ?
Thank you for your help
Jan van der Weijde