Would it be overkill to Building a news site on Fastify and sqlite instead of WordPress

[Dopious]

So, I`ve looked in to Fasify in combination with SQLite to build something that don`t use as much resources on hardware to future proof a system from start.

Anyone used this combo with SQLite sharding on a huge userbase setup with one sqlite database per user in the system?

Alternatives?

It needs to be built for 250K users from start.

 

[Outlaw]

Brotherrrrr

SQLalchemy, SQLite is a thing of beauty but for around the same set-up and easier use and integration

Sqlalchemy... Can hook it up to anything with ify at the end of it

SQLite works unbelievably well in-house and out, but SQLAlchemy makes everything so much easier

edit, if you can bridge/work on that small small powerful gap

imb db2 - Bing

Intelligent search from Bing makes it easier to quickly find what you’re looking for and rewards you.

www.bing.com

50-60k a year for going from sqlalchemy to ibm db2

i need to stop going around like ai isn't doing 50% of the work

and start reading properly... Shouldn't be too many more resources but for 250k users there's definitely too many changes to be made

 

[GoldenGlovez]

Nowadays I build most projects using a combination of Astro (Hugo for personal stuff) and leverage Cloudflare's D1 and Workers KV as this offers very good pricing and great scaling, especially for geo-targeted CDN and database access. I have no idea what type of data structure you're going for, but an individual SQLite instance for every user sounds like a nightmare if you ever need to do a multi-user query... you'd have to read/write across numerous different databases.

If you're storing a lot of low-sized sequential blob data for your users (e.g logging, event data), you can keep a relational database on D1 for your authentication/settings and then reference their user ID in a Workers KV namespace for their data. Again, don't really know what your data structure goals are.

 

[Dopious]

I have no idea what type of data structure you're going for, but an individual SQLite instance for every user sounds like a nightmare if you ever need to do a multi-user query... you'd have to read/write across numerous different databases.

I was thinking one might do a temp index db for them all, but most searches are made from a users db only as they would have their own site search. Maybe I am overthinking it as I am not a coder myself in that meaning.

The user has their own news publishing sites like domain/user1, domain/user2, domain/user3 and so on.

 

[GoldenGlovez]

I was thinking one might do a temp index db for them all, but most searches are made from a users db only as they would have their own site search. Maybe I am overthinking it as I am not a coder myself in that meaning.

The user has their own news publishing sites like domain/user1, domain/user2, domain/user3 and so on.

If I understand correctly then PostgresSQL + Multi-tenancy architecture sounds like an option you'd be looking for. An individual database for 250k+ users is going to be a lot of overhead, tech and price wise imo. A multi-tenancy setup would let each user store their website, posts, etc and can offer site search queries while keeping it secure with RLS (so data can't leak through from other users). This was a blog post I found that seems to decently describe such a setup, https://blog.logto.io/implement-multi-tenancy

 

[t2van]

I can only add that overkill is under rated

 

[Dopious]

This was a blog post I found that seems to decently describe such a setup, https://blog.logto.io/implement-multi-tenancy

Thanks, that was a good read.

 

[BlogPro]

I'll second - PostGreSQL with multitenancy.

I actually have worked on several applications with this architecture and currently run 2 mobile apps with 150k users.

At 250k users, you're looking at 250k SQLite files. Now we've also not considered WAL files, indexes, other temp files, media files etc.

And you're looking at over a million nodes in core user data alone. Your server will croak and die a million deaths when you have these many files. I mean imagine the inode issues alone in most Linux systems.

You should also consider that any upgrade you make, or a single table you add will have to be migrated across all 250k database. Even if you wish to add or modify some and it takes half a second per DB, you're looking at 35 hours of migrations.

Skip one and shit hits the fan.

Not to forget backups, security are going to be a nightmare.

Also, how about admin dashboards? Trending posts? Analytics? Will you query each of the SQLite databases individually? Or create another database to store meta data?

At the user level, every query will be open file > run query > close file. The I/O load will be....omg!

 

[Dopious]

I'll second - PostGreSQL with multitenancy.

Ok, any thoughts on Fastify?

 

[BlogPro]

Ok, any thoughts on Fastify?

Only have good things to say about it. I have shipped enterprise level stuff with Fastify. Their plugin system is incredible - https://fastify.dev/docs/latest/Reference/Plugins/

My team was express through and through and over the past several months, we've rapidly shipped through Fastify. It can easily be used to build systems the kind you're aiming for.

 

[Dopious]

My team was express through and through and over the past several months, we've rapidly shipped through Fastify. It can easily be used to build systems the kind you're aiming for.

Nice, so it is time to research a PostgreSQL provider now then, thanks for your input on Fastify, felt nice to have pinned down one thing atleast.

 

[BlogPro]

Nice, so it is time to research a PostgreSQL provider now then, thanks for your input on Fastify, felt nice to have pinned down one thing atleast.

I can try to help here.

If you're going Postgre and want a hands-off approach, might I recommend Supabase?

Their free tier will be enough for a decent proof of concept and it is a managed service that scales seamlessly.

Supabase is like Firebase but for PostgreSQL

It will take care of authentication, API, edge scripts as well.

If you scale and realise their managed costs are out of bounds for you, you can also self-host Supabase (it's free and open source). And moving from their paid to selfhosted is pretty easy.

If you wish to handle auth seperately, then you can go for Neon or Railway, which offer one click PostgreSQL

P.S - Railway has charges for external networking aka Egress charges. Basically you get charged for data that goes outside the network. So if your Fastify is somewhere else, you will be given a public database URL to configure and be charged egress.

However, railway is an entire managed platform built on top of AWS and offers application hosting. It auto deploy updates from GitHub/gitlab etc. And if your application is within Railway, there are no egress charges.

 

[GoldenGlovez]

Their free tier will be enough for a decent proof of concept and it is a managed service that scales seamlessly.

Aye, sound advice. Made the extremely costly mistake of preempting server infrastructure well beyond what ended up being needed on a project long long in the past... never want to repeat that again. @t0mmy may recall that one.

 

[t0mmy]

Aye, sound advice. Made the extremely costly mistake of preempting server infrastructure well beyond what ended up being needed on a project long long in the past... never want to repeat that again. @t0mmy may recall that one.

I forgot all about that bro, it just came flooding back when I read this... what a disaster that turned out to be, bit of shame as what they did finish looked slick as fuck although the problem was they weren't as slick work wise. Crazy I forgot about that road we were about to go down.

 

[BlogPro]

Aye, sound advice. Made the extremely costly mistake of preempting server infrastructure well beyond what ended up being needed on a project long long in the past... never want to repeat that again. @t0mmy may recall that one.

I forgot all about that bro, it just came flooding back when I read this... what a disaster that turned out to be, bit of shame as what they did finish looked slick as fuck although the problem was they weren't as slick work wise. Crazy I forgot about that road we were about to go down.

Been there, done that, cried hard.

I am literally talking to a potential founder who is old-money loaded and wants me to build him an MVP. And this dude somehow lives on ChatGPT.

He has quotes from several of these infrastructure companies ranging from 60k to 100k for managed services, for an app that doesn't even have an MVP architecture established yet.

Because "we'll have a million users within 30 days of launching."

Of course.