Spent some time on my #ActivityPub project today getting #PSQL set up. There's a lot of random setup that goes into PSQL and it isn't especially well documented (or rather, it is so well documented there is simply too much of it).
Whether I stick with it is a separate question. Comparing it to the other options and including the learning curve it is head and shoulders the best pick for me for right now, however, at least for a production-level database.
There are some other good options I may investigate later, but PSQL:
Is Fast.
Is Free.
Is well supported.
I am intimately familiar with the ins and outs and how to optimize it.
I already have a lot of things on this project I am learning fresh, so it makes sense to limit the scope to be a bit more manageable for now and I can revisit it later.
MariaDB — I am familiar with the basics but only at a high level. Reasonable driver support. Some very nice features for the use case, however, something to look into later.
Firebase and other hosted solutions — would work well enough, but would be expensive to run at scale and most options here tie you to a specific cloud provider.
CockroachDB — Mostly seems like overkill and adds complexity.
Mongo — I've been around too long, the name is still a curse.
It seems like doing table partitioning would be an obvious choice for mastodon, but I don't see it anywhere in their setup. Does anyone know if someone has done the legwork to make this work and what their results were?
@renchap@thisismissem@hrefna@Gargron Not really. DB migrations put aside, the main thing the application has to do is to add the partition key into the where clause to ensure performances stay good.
e.g. if you partition table toot by user_id, you have to run something like SELECT message FROM toots WHERE toot_id = foo AND user_id = bar.
This prevents the query planner from scanning all the sub tables.
@Arcaik@renchap@thisismissem@hrefna Uniqueness constraints are not asserted across partitions so it requires a bit more awareness at the application layer. Our code also has to work for all installs and not just ones with DevOps engineers.
@Gargron@renchap@thisismissem@hrefna What I'm saying is that one could make Mastodon “partitioning ready” with a few changes and let people who know what they are doing implement it if they need it.
Add comment