We're trying to organize a hackathon, and one of the users has a proof of concept gateway running allowing Red Reader (and other 3rd party apps) to switch to Lemmy with no changes (except for the API url). It's basically providing a reddit-compatible api for lemmy.
See https://imgur.com/a/IF5HYGz
And also https://www.reddit.com/r/apihackathon/comments/13yvzg2/rapihackathon_lounge/jmxcq0u/
Looking at the query I think it only returns a single row per post. So not really duplicate data. It all looks very straight forward and you'd think all the "_id" and "id" columns are indexed.
I asked for an EXPLAIN ANALYZE plan to see what really happens and where the most time is spent.
If it's indexes we'll see quickly. It might strangely be in the WHERE clause. Not sure what Hot_rank()'s implementation is. But we'll find that out too if we can get the plan timings. Without looking at the numbers it's all just guessing.
And I can't run them myself since I don't have access to a busy instance with their amount of production data. It's the thing about databases - what runs fast in dev, doesn't always translate to real workloads.
Thanks. I've asked in there for some EXPLAIN ANALYZE plans, so we can see which parts are slow and why. I also think running pgbouncer or a similar connection pooler would be a good idea. Running a production public facing service with only 5 db connections is asking for trouble.
No, joins are always faster. If you ultimately need to combine the data for the app, the database will be faster than your code can do it, since that's what it was built to do.
Let's get a hackathon going. Maybe it's possible to have a project which allows these apps to work with lemmy. I created a sub on reddit for it: https://www.reddit.com/r/apihackathon/
But maybe that's not the right spot - happy for someone to create a similar community here too.
Where are the bottlenecks? Frontend servers or on the db? I have a lot of experience running postgres at scale.
/u/whupazz is working on a reddit compatible api for lemmy, meaning most 3rd party apps should work with little or no modification. See it working with RedReader already: https://imgur.com/a/IF5HYGz
Follow or help here: https://www.reddit.com/r/apihackathon/comments/13yvzg2/rapihackathon_lounge/jmxcq0u/