DataTable-like widget that supports external data storage and pagination?

[bradlarsen]

tl;dr: Has anyone written a DataTable-like widget that is backed by a database rather than storing all the data internally?

Hi there!

I'm working on a proof-of-concept interactive results explorer application for Nosey Parker, a secrets detector, using Textual.

What I have so far works really well functionally. However, the performance on large result sets (millions of matches and gigabytes of data) is problematic. Specifically, when populating a DataTable widget with 1.5 million rows from a 1.2GB results set using an async Worker, the app's rendering speed drops to a handful of frames per second and becomes very laggy for 40 seconds. After the DataTable is completely populated, the app becomes snappy and responsive again.

An obvious solution to this performance problem would be to swap out the DataTable for a similar widget that does not actually store the entire data set that backs it, but instead relies on an external system (a sqlite or duckdb database in this case) to paginate results as-needed. I have measured, and an approach like this would speed up the load time in my case from 40s down to milliseconds.

It does not look like DataTable supports external data storage or pagination. Has anyone written a widget that would do this? Alternatively, any implementation pointers for adapting DataTable to support this?

Thanks,
Brad Larsen

[bradlarsen]

I just saw this package: https://pypi.org/project/textual-fastdatatable/

"A performance-focused reimplementation of Textual's DataTable widget, with a pluggable data storage backend."

I'm going to give that a try.

[Delengowski]

What was the result @bradlarsen ?

I have a similar use case. I prefer to Textual for portability but I'm thinking a PySide6 will be more performant due to the MVC having its QTableView having methods canFetchMore and fetchMore for on demand loading.

[bradlarsen]

@Delengowski I found https://github.com/tconbeer/textual-fastdatatable, which lets you use Apache Arrow or Parquet as backing data for the table. It is dramatically faster: in an unpublished prototype, I was able to essentially instantly load and render a table of 300k rows. Doing the same with a regular datatable takes many minutes.

On the other hand, that textual-fastdatatable project has some limitations, like not allowing efficient mutation of rows or columns.

I had started coding up my own from-scratch data table that would allow incremental loading, but have not gotten it working.