Data editor pagination stability with non‑unique ORDER BY

Direct answer

PyCharm does not guarantee stable row ordering across pages when the ORDER BY clause lacks a unique column. Each page is fetched with a separate LIMIT/OFFSET query, so concurrent inserts, updates, or deletes can cause duplicate or missing rows — this is a property of offset pagination, not a PyCharm bug.

There is no per‑query page‑size override in any current release (tested through 2024.3). The global “Result set page size” setting applies to every data‑editor tab.

Keyset (seek) pagination is not implemented for the data editor, and no public roadmap item or EAP build exposes it as of the 2024.x cycle.

Why instability happens

• Offset pagination re‑issues the query with a new OFFSET for every page.
• When the sort key is not unique, the database may return tied rows in any order.
• Concurrent modifications change the physical order of those ties between page fetches.

Workaround that restores stability

1. Add a unique column (primary key or unique index) as the final term in ORDER BY, e.g. ORDER BY status, id.
2. Set the global page size to a value that balances UI responsiveness and fetch count (Settings → Tools → Database → Data Editor → Result set page size).
3. Verify: open the table, set page size to a small number (10), scroll while another session inserts rows — you should see no duplicates or gaps.

Verification steps you can run now

-- 1. Create a test table
CREATE TABLE t (id SERIAL PRIMARY KEY, status TEXT);
INSERT INTO t (status) SELECT 'open' FROM generate_series(1,100);

-- 2. In PyCharm open the table, set page size = 10, sort by status only.
-- 3. In a second session run:
INSERT INTO t (status) VALUES ('open');
-- 4. Scroll in the first session – duplicates or missing rows appear.

-- 5. Change the sort to: ORDER BY status, id
-- 6. Repeat step 3 – paging stays stable.

One diagnostic detail that would change the recommendation

If you cannot add a unique tie‑breaker (e.g., the table has no primary key and you cannot alter schema), let me know — we can discuss snapshot isolation or materialized view alternatives.