API: Return resource count about firefly HOT 3 CLOSED

I imagine the API will likely be designed such that it will never return more than x number of items for a given request, where x is some default maximum value that we deem "safe" enough to keep the database responsive.

If we want to get the total count of a particular thing, without actually getting all those things, I would highly recommend a separate API request specifically for the count. This could be done with either a different API endpoint (REST resource) or with a query param on the endpoint. I think it would be a bit cleaner if it were an entirely separate thing.

Having the count be computed independently from fetching all of the rows themselves allows us flexibility to change how the count is computed/stored/retrieved completely separately from the actual query to fetch the data. For certain tables that will always be small, the query may simply do a select count(*) every time.

We should think at scale here though, and assume that some members may have many millions of records. We don't want someone loading the UI page to bog down the whole database while it goes and tries to count all those rows every time.

Also, counting rows is known to be a thing that PostgreSQL is not good at with large tables https://wiki.postgresql.org/wiki/Slow_Counting This may have changed in newer versions. The last time I ran into problems with PostgreSQL counting rows was probably 4 years ago, so things may have improved or there may be other ways now. But it's definitely something we need to spend some time researching and testing if we want to go this route.

from firefly.

re-opening this after discussion with @shorsher - the UI needs the ability to get the counts scoped to different resource types, within individual namespaces, but will mitigate the performance issue by always performing queries bounded to a time window.

from firefly.

#150 raised with the API work @shorsher
Once the UI explorer side has worked out where the counts are needed, and we've added filtering in the table/timeline experience, we'll want to come back round and make sure we have the right indexes in place on timeboxing/filtering.
Rather than putting those in speculatively, I'd like to use the UI to test them - maybe combined with loading up a few million transactions into a system first to do some PSQL profiling.

from firefly.