Performance degredation on large datasets

[Skelmis]

When using the Admin UI for large datasets (Millions of rows), there is a noticeable hang time before the UI either crashes or loads. Reloading the page when loaded on a table with a small dataset takes a few seconds where as a dataset of a million takes half a minute on my machine. I imagine larger datasets would be worse.

It was initially reported in the discord here but I can also reproduce it.

---

Initial profiling of a pip installed version of the UI point to this:

I dont know why its chonking the GC so much if its paginated, which it is by default but figured it could be useful context to add

[sinisaos]

@Skelmis The main issue with large datasets is LimitOffset pagination (used in PiccoloCRUD). For LimitOffset pagination, the database has to scan and discard all rows up to OFFSET, which leads to poor performance on large datasets where the offset is large. Cursor pagination has much better performance (as discussed here and there is a separate package that does not work with Piccolo API and Piccolo Admin), but there are limitations. Not being able to go to a specific page is, I think, a big problem and limitation for any admin dashboard. I don't think there's much we can do to solve performance issues with large datasets.

[Skelmis]

@sinisaos thank you for that, I remember reading that but at the time I thought it had been potentially merged. I do think however the limitations could be worked around with some form of meta cursor route that routes the cursor for the start of each page, maybe I'll go build a poc for it sometime.

That does make sense though why its slow! Much thanks for clarification

[sinisaos]

@Skelmis No problem. That would be great, but it wouldn't be an easy task because Piccolo Admin uses PiccoloCRUD (FastAPIWrapper) for automatic crud on tables. The easiest way would be to change the pagination type like this using CursorPagination class from this package (be careful because I haven't touched that code for 3 years because there was no interest and I don't know if it all works, although it should). That would be adding additional code to PiccoloCRUD and that wasn't previously accepted (closed PR with forward and backward options). After that, the code in the Vue frontend would also have to change to keep up with the change in pagination type, as currently only LimitOffset pagination works.

[dantownsend]

Was the problem when trying to navigate to say page 1000 or something? Or just loading page 1?

[Skelmis]

Also on page 1 oddly enough

[dantownsend]

Yeah, that's strange.

I tried it locally with 1 million rows, using this (you need the latest from master branch for this to work):

python -m piccolo_admin.example.app --inflate=1000000 --engine=postgres

Apart from it taking about 5 minutes to start up, because it was generating so much fake data, it ran surprisingly OK. My work machine is pretty powerful though, and has 36 GB of RAM.

The tables I was using were also fairly simple. I wonder if there's something about the tables which can make it worse.

Weirdly, one of the things which was really lagging at first was the pagination selector. It's just a HTML select widget, but it had thousands of values in it. For a million rows, and the default 15 rows per page, that's over 60k elements!

[abhishek-compro]

Weirdly, one of the things which was really lagging at first was the pagination selector. It's just a HTML select widget, but it had thousands of values in it. For a million rows, and the default 15 rows per page, that's over 60k elements!

Maybe this is the reason for the lag because its the browser that gets stuck.

[abhishek-compro]

I think its better to show 1 2 3 ... 60000 than to populate options in a select.

[Skelmis]

I believe some of the other ways this is done in other sites is showing like

Always the first page, [current page +&- 2], always final page. It means you can't skip to exact pages, but perhaps there could be a little input field if you want an exact page?

[sinisaos]

I like the idea of ​​an input field with 2 previous and 2 next pages. I played around with it a bit and here are the results. Based on 4.65 million rows in a sqlite database on localhost for the smaller Director table.

director.mp4

For the larger Movie table the performance is a bit worse but still better than it is now.

movie.mp4

[dantownsend]

I like the idea of ​​an input field with 2 previous and 2 next pages. I played around with it a bit and here are the results. Based on 4.65 million rows in a sqlite database on localhost for the smaller Director table.

director.mp4
For the larger Movie table the performance is a bit worse but still better than it is now.

movie.mp4

Nice, something like that would be great!

[sinisaos]

Pagination of the Movie table is more efficient if we use default ordering by primary key (not using OrderBy)

movie_pk_order_by.mp4

[abhishek-compro]

@sinisaos Are you raising a PR or can I look into it?

[sinisaos]

@abhishek-compro I already have a branch in my fork that is ready for PR. If @dantownsend wants and agrees, I can do a PR.