Upgrading the Database

[Aleksandergreg]

What Database system should we go with? Relational or a no-sql DB - and why?

From Anders:

You are free to choose NoSQL or relational databases. Your chioces should be informed and based on the business domain and the data model of whoknows. (Not personal preference).

If the choice is between PostgreSQL and MySQL your choice for a modern stack should without a doubt be PostgreSQL. Here are a plethora of reasons why:

Where to put the database

The problem with PostgreSQL and other databases is that they take up a lot of resources on the VM if placed with the rest of the application.

There are many ways to solve this issue. You are allowed to use a managed database service.

But your solution should consider not only scalability but also cost.

[Aleksandergreg]

Choosing a DB System

users table: Has id, username, email, password. Crucially, username and email are UNIQUE. This screams relational structure. You need strong consistency and data integrity to ensure usernames/emails aren't duplicated and that user data is reliable.

pages table: Has title (PK, UNIQUE), url (UNIQUE), language (constrained), last_updated, content. Again, this is highly structured data with uniqueness constraints. The content is TEXT, which SQL databases handle perfectly well.

Based on this I think we should stick with a relational DB, and my suggestion would be to use PostgreSQL

Where to put the Database

Azure DB Service for our chosen database, would probably be the easiest, but it looks quite expensive from what I can see. We should ask Anders about how much this would cost, and what is included in our Azure Student free tier.

Our other optsions as I see it:

Virtual Machine (VM) on Azure (IaaS): You provision a virtual machine in Azure and install PostgreSQL on it yourself.
Pros: More control over the operating system, database configuration, and potentially a bit cheaper than a managed service, especially for long-term, predictable workloads.  
Cons: You are responsible for all maintenance, patching, backups, scaling, and high availability. It requires more sysadmin effort.
 
Containers (Docker) on Azure: You containerize PostgreSQL (using Docker) and run it on Azure Container Instances (ACI) or Azure Kubernetes Service (AKS).
Pros: Portable, reproducible environments. AKS provides good scaling capabilities.  
Cons: Requires knowledge of Docker and container orchestration. You still manage some aspects of infrastructure (though less than with VMs). Can be complex to set up high availability.
PostgreSQL on your own hardware: You could host PostgreSQL on your own server.
Pros: Greater control
Cons: Requires sysadmin knowledge. Requires hardware costs. Requires setup and maintenance. Would require one of us to have a machine running at home 24/7. As I see it, this is the least viable option for us, as it would require hardware and a lot of additional knowledge, that I doubt anyone of has at the moment?

[theaskou]

Good points from Aleksander. I think PostgreSQL sounds interesting and relevant. I like that it is open source, and that it seems like a popular solution, so it would be a good investment of our time to learn about it.

I don't think we should go with MySQL due to it's enterprise limitations. But I don't have a better argument atm, if some of you think otherwise.

I also think we should ask Anders on Friday what to do with the database. A DB in a VM or Container on Azure is more work, but we might have to consider it, if an Azure DB is too expensive.

I want to do a bit more research, but these are my initial thoughts. I will write some more reflections after I've done that.

[Aleksandergreg]

@theaskou

So ellaborating my points about why we should stick with an relational database, a relational database kind of acts like a security guard, as it enforces our policy with having unique values, to users can't have the same username etc. This ensures consistensy, and data integrity: that our data is healthy and correct. This is exactly what relational databases are made for.

What I mean with uniqeness constraints is that it fits perfectly in to rows and colums with exact datatypes, like text, numbers, dates etc. Again exactly one of the reasons to use a relational database, as it loves this kind of order.

That it handles text well is simply just that our content, which can be long pages of text, can be saved and recieved effeciently in a relational DB.

Let me know if this doesn't make any sense, and I'll try to describe it in a different manner.

[Aleksandergreg]

In regards of where to put the Database, I can see that we through Github Student Developer Pack have acces to Heroku and DigitalOcean. DigitalOcean requires you to attach a credit card, so I don't really like that solution, but we could look into Heroku

There is also things outside of the student developer pack, such as:

• Google Cloud Platform's Cloud SQL
• PostgreSQL on a Compute Engine VM
• PostgreSQL in Google Kubernetes Engine (biggest issue here seems that we haven't been lectured in Kubernetes yet 😃
• Supabase (Very popular open source alternative, might be worth to look into?)
• Amazon RDS (750 hours of Amazon RDS usage per month. Up to 20GB of storage) Free tier for 12 months
• ElephantSQL

[theaskou]

How did you get that number? Just curious!

[Luke3520]

Hey,

After weighing our options, here’s my top three:

1. Supabase
2. ElephantSQL
3. Heroku

I lean towards Supabase because it meets our needs with an always-on, fully managed PostgreSQL service that’s simple to set up. Also we don't have to worry about dyno sleep delays or attaching a credit card. For context, on Heroku’s free tier, the app "sleeps" after about 30 minutes of inactivity and is limited to roughly 20 concurrent PostgreSQL connections. This could lead to latency spikes or connection issues during sudden surges (like 100 simultaneous users). If Anders sends heavy traffic, we might need to implement connection pooling with tools like PgBouncer to keep things running smoothly(which also could be fun to do).

Overall Supabase gives us the consistent performance and low latency we need.
PS... I was all for Heroku, but their free-tier seems a bit scuffed. What do you all think?
https://devcenter.heroku.com/articles/free-dyno-hours

[Aleksandergreg]

I got the size of our current db by doing:
du -h your_database.sqlite

Then I googled, and found out that an PostgreSQL db with full text search is roughly 3x the size of an SQLite3 with the same

[Aleksandergreg]

And in regards to your question @Luke3520 we have access to Heroku by our student developer pack from github, so we get I think it was 14 dollars a month, similar to the deal with Azure.

Let's ask Anders today about how much it would cost on Azure, and if not the Supabase, based on your arguments, seems like a really good solution

Great discussion, I would say it's a wrap when we have asked Anders today?

[Luke3520]

Even with our GitHub Student Developer Pack, I think our Heroku app will still be affected by dyno sleep and the approximate 20 concurrent PostgreSQL connection limit. Maybe we can use our credits to upgrade to the Hobby plan that keeps dynos always on. That would put my worries about latency issues at rest.

I think it’s a good idea to ask Anders how much traffic he’s planning to simulate.

[theaskou]

I read about Vector databases which sounds super relevant to us, if we are to expand the search results later on. It's way to embed unstructured data into multidimensional coordinates where things that closer related, will be positioned closer to each other. When the embedded space has been constructed, you will see the results that are most closely related to what the user has searched for.
It's pretty advanced, and I'm not sure how hard it would be to implement - but looks like there's an extension for PostgresQL that we could check out! Another +1 for PostgreSQL in my opinion.

[theaskou]

Here's an article about the vector extension for PostreSQL: https://www.timescale.com/learn/postgresql-extensions-pgvector