Hello again! If you have been creating web pages for a few years, you know that some tools end up becoming your old trusted companions. For the vast majority of us, the duo formed by MySQL and phpMyAdmin is, literally, like family. We use them daily in every new WordPress installation, we know their prefixes, their memory tables, and we know how to spot a suspicious table just by looking at its name or format.
But, as we told you in our previous article about how we moved to a static Frontend, the development of our new SaaS (Normatia) has forced us to explore new technological horizons. We didn’t make this decision because MySQL is a bad database (it’s still a fantastic and robust tool for 90% of the web), but because for this specific project, we needed the core of our data to do things that completely escape the classic relational model of rows and columns.
Today we bring you a very special post where we tell you why we have embraced PostgreSQL with open arms and how Supabase has made our lives incredibly easier. Let’s get to work!
Why we had to step out of our comfort zone
To understand the change, we first need to understand the problem. In a traditional web project or an online store, MySQL is absolutely perfect for relating highly structured information. A specific user has many Posts, a Post belongs to a Category and has several Tags. Everything fits neatly into tables linked by IDs.
The problem arises when you want to search through that information. In the traditional world, if a user searches for something, we use the classic LIKE %term% operator. If you search for “staircase”, the database scans the texts looking for exactly those 9 letters in that exact order.
But Normatia is not a word search engine; Normatia integrates Artificial Intelligence to search through complex technical regulations. We do not search by exact words, we search by meaning and context. And that is where the classic text search model breaks into a thousand pieces. We needed to store and query mathematical vectors (which is the abstract way Artificial Intelligence “understands” and groups texts), and PostgreSQL is, today, the undisputed king in this field thanks to its extension ecosystem.
What is Supabase and why is it the trendy BaaS?
Once the database engine (PostgreSQL) was decided, we faced another challenge: the infrastructure. Setting up a raw PostgreSQL server from scratch, configuring its backups, securing connections, and managing it manually via console was not in our plans. We are product developers, not pure system administrators, and our time is money.
So we bet everything on Supabase, a platform that falls into what we call Backend as a Service (BaaS). Supabase wraps around PostgreSQL and gives you a layer of visual superpowers and automatic integrations.
To put it simply: Supabase is as if someone took the concept of cPanel, modernized it with exquisite design, and thought of it exclusively for app developers.
From its dashboard, not only do we create and view data tables as we would in phpMyAdmin, but it also solves highly complex problems like user authentication in one fell swoop. In just a couple of clicks and a few lines of code in our Frontend, we had the entire Normatia Login up and running. It supports authentication via email, Google, GitHub, secure passwords, and JWT token generation, all without us having to program the tedious server-side security flow.
Row Level Security (RLS)
One of the things that blew our minds the most when moving from the WordPress mindset to the Supabase mindset was RLS (Row Level Security).
In WordPress, security is often handled at the application layer (PHP). If a user asks to see an invoice, the PHP code checks if it belongs to them before displaying it. In Supabase, security resides within the database itself. You write direct “Policies” on the table that say: “No one can read this row unless the user’s ID matches the row creator’s ID”. It is a massive paradigm shift that ensures that even if someone finds a flaw in your Frontend code, your data remains protected at the core level.
The real magic: pgvector and semantic search
We arrive at the real reason for this Stack change. The “Killer Feature” that made us move is called pgvector. It is an open-source extension for PostgreSQL that allows you to store, compare, and search vectors generated by AI models.
How do we explain this simply? Think of a normal database as a classic library archive where books are strictly ordered alphabetically. If you go and ask the system for “transmittance”, the system will search the ‘T’ drawer and give you the books that have that exact word in the title. If the book is called “Heat loss in windows”, it won’t give it to you, because the word doesn’t match.
However, a vector database powered by pgvector is like having a smart, expert librarian. If you ask about “heat loss”, the librarian “understands” the underlying concept of your phrase and will know to hand you the Building Code articles that talk about “thermal transmittance” and “insulation”, because it knows the semantic concept is the same, even if the literal words don’t match.
For this reason, the combination of Supabase + its native support for pgvector has become the perfect duo for our project’s semantic search engine. When a user enters a query in Normatia, the AI converts their phrase into a vector (an array of 768 numbers in our case), Supabase compares that mathematical vector with the thousands of regulatory articles we have already vectorized in milliseconds, and returns the most relevant ones.
The mindset shift as developers
We won’t lie to you, changing tools you have been using for 15 years is not easy. We had to unlearn how we made complex WP_Query calls to learn how to use the Supabase JavaScript SDK and understand how PostgreSQL works under the hood.
Learning a new database system is always intimidating, and for the first few days, you feel like a rookie figuring out how to do a simple JOIN. But we assure you that the effort is completely worth it when you see the final result and the speed at which your application can cross-reference hyper-complex data.
And you, developers, are you still absolutely loyal to MySQL for all your developments, or have you already started tinkering with PostgreSQL, Supabase, and vector databases in your projects?
