DEV Community: egor romanov

pgvector vs Pinecone: cost and performance

egor romanov — Mon, 23 Oct 2023 15:49:47 +0000

At Supabase, we believe that a combination of Postgres and pgvector serves as a better alternative to single-purpose databases like Pinecone for AI tasks. This isn't the first time a Postgres-based solution has successfully rivaled specialized databases designed for specific data types. Timescale for time-series data and Greenplum for analytics are just a few examples.

We decided to put Postgres vector performance to the test and run a direct comparison between pgvector and Pinecone.

What is Pinecone?

Pinecone is a fully managed cloud Vector Database that is only suitable for storing and searching vector data. It offers straightforward start-up and scalability. It employs a proprietary ANN index and lacks support for exact nearest neighbors search or fine-tuning. The only setting that allows you to adjust the balance between query accuracy and speed is the choice of pod type when creating an index.

So, before we dive into their performance, let us first introduce Pinecone's offerings.

Pinecone has 3 Pod types for indexes

An index on Pinecone is made up of pods, which are units of cloud resources (vCPU, RAM, disk) that provide storage and compute for each index.

Type	Capacity / Vectors	QPS	Accuracy	Price per unit per month
s1	5,000,000 768d (~2,500,000 1536d)	Slowest	0.98	$80
p1	1,000,000 768d (~500,000 1536d)	Medium	0.99	$80
p2	1,100,000 768d (~550,000 1536d)	Fastest	0.94	$120

Pods can be scaled in 2 dimensions: vertically and horizontally:

vertical scaling can be used to fit more vectors on a single pod: x1, x2, x4, x8;
horizontal scaling increases the number of pods or creates replicas to boost queries per second (QPS). This works linearly for Pinecone: doubling the number of replica pods doubles your QPS.

Benchmarking methodology

We utilized the ANN Benchmarks methodology, a standard for benchmarking vector databases. Our tests used the dbpedia dataset of 1,000,000 OpenAI embeddings (1536 dimensions) and inner product distance metric for both Pinecone and pgvector.

To compare Pinecone and pgvector on equal grounds, we opted for the following setups:

pgvector: A single Supabase 2XL instance approximating ~410$/month (8-core ARM CPU and 32 GB RAM). An HNSW index with the following build parameters: m='36', ef_construction='128'.
Pinecone: Vertically scaled pod to the minimum option that fits the dbpedia dataset into the index on a single pod. We then added replicas to match the budget (slightly exceeding in all cases with ~$480/month).

To reduce network latency, we placed our clients in the same cloud provider and region as the database. Experiments were run in a parallel configuration, varying the number of concurrent clients from 5 to 100 to determine the maximum QPS for each setup.

Measuring accuracy in Pinecone

There is no available information on Pinecone's proprietary ANN index. Likewise, they doesn't provide information about query accuracy, nor does it support exact nearest neighbors search (KNN). So to measure Pinecone's accuracy, we had to compare its results with pgvector's exact search (KNN without indexes) for the same queries. This seems to be the only way to measure Pinecone's index accuracy.

Benchmarking Results

Pinecone with s1 pod type

As the index can fit in a single s1.x1 pod ($80/month), we created five additional replicas. Our Pinecone setup consisted of six s1 pods (totaling $480/month). We measured Pinecone's accuracy for the dbpedia dataset using the s1 pod type, achieving a score of 0.98 at the 10 nearest neighbors (accuracy@10).

To match the measured .98 accuracy@10 of Pinecone s1 pods, we set ef_search=32 for pgvector (HNSW) queries, and observed the following results:

The pgvector HNSW index can manage 1185% more queries per second while being $70 cheaper per month.

Interestingly, we'd often heard that the pgvector IVFFlat was too slow until the HNSW support was introduced. However, even the pgvector IVFFlat index on the same compute exceeds the Pinecone s1 pod and manages 143% more queries per second:

p1 pod type

With Pinecone p1 pods, we can fit the dbpedia dataset into the index on a single p1.x2 pod ($160/month). So, by adding two more replicas, we maintained our budget. Therefore, our second experiment involved a Pinecone setup scale of three p1.x2 pods (totaling $480/month). The measured accuracy@10 for the p1.x2 pod and dbpedia dataset was 0.99.

To match the .99 accuracy of Pinecone's p1.x2, we set ef_search=40 for pgvector (HNSW) queries.

pgvector demonstrated much better performance again with over 4x better QPS than the Pinecone setup, while still being $70 cheaper per month. As Pinecone can linearly scale by adding more replicas, you can estimate that you would need 12-13 p1.x2 pods to match pgvector performance. This equates to approximately $2000 per month versus ~$410 per month for a 2XL on Supabase.

p2 pod type

This is Pinecone's fastest pod type, but the increased QPS results in an accuracy trade-off. We measured accuracy@10=0.94 for the p2 pods and the dbpedia dataset. It is possible to fit the index on a single p2.x2 pod (240$/month), so we could add 1 replica. Thus, Pinecone's setup for the third experiment consisted of two p2.x2 pods (totaling $480/month).

To match Pinecone's .94 accuracy, we set ef_search=10 for pgvector (HNSW) queries. In this test, pgvector's accuracy was actually better by 1% with .95 accuracy@10, and it was still significantly faster despite the better accuracy.

Here's something important to highlight: pgvector is faster than Pinecone's fastest pod type, even with an accuracy@10=0.99 compared to Pinecone's 0.94. Pinecone's most expensive option sacrifices 5% accuracy just to match pgvector's speed.

Additional thoughts on Pinecone vs. pgvector

It's only fair to note that Pinecone may be cheaper than pgvector since you could use a single p1.x2 pod without replicas, costing about $160 per month, and you would still achieve approximately 60 QPS with accuracy@10=0.99. For pgvector on Supabase, this means you might not be able to fit the index in RAM as you may use a large ($110) or XL ($210) compute add-on and will fall back to KNN search without any indexes. However, this translates to potentially adding more vectors, similar to the s1 pod on Pinecone.

Real user stories indicate this might not be problematic. For instance, Quivr expanded to 1 million vectors without using any indexes:

pgvector's hidden cost-saving benefits

There are also a couple of benefits from a developer experience perspective that we often take for granted when using Postgres:

Postgres offers numerous features applicable to your vectors: database backups, row-level security, client libraries support and ORMs for 18 languages, complete ACID compliance, bulk updates and deletes (metadata updates in seconds).
Having all your data in a sole Postgres instance (or a cluster) reduces roundtrips in production and allows running the entire developer setup locally.
Implementing additional databases can increase operational complexity and the learning curve.
Postgres is battle-tested and robust, whereas most specialized vector databases haven't had time to demonstrate their reliability.

Start using HNSW

All new Supabase databases automatically ship with pgvector v0.5.0 which includes the new HNSW indexes. Try it out today and let us know what you think!

More pgvector and AI resources

🚀 Learn more about Supabase

Test Design using the Interface-Model-State Method

egor romanov — Sun, 04 Jun 2023 16:43:57 +0000

Introducing yet another method for developing functional test cases. Explore what happens when you base your approach on the architectural diagrams of the system being tested.

By reading this blog post, you will

Master the Technique for Simplifying Complex Structures, which involves deconstructing systems into components, making it easier to prepare functional test plans.
Enhance your Analytical Thinking through a step-by-step problem analysis framework.
Gain a solid understanding of Test Design Strategies by considering both positive and negative scenarios in functional test case design.
Add a systematic and comprehensive approach to test design to your skillset, essential for anyone interested in test design and software testing.

Introduction

I work in the field of automated testing and have repeatedly gone through the process of entering mature projects. Unfortunately, this usually takes more time than desired because a tester must have a good understanding of the business requirements, logic, and technical architecture of the systems being tested.

During one of my interviews, I was asked a rather common question in the field: "How do you write test cases, or in other words, what is your method for developing tests that cover the functionality of the product?" Surprisingly, the question stumped me. I had read about the traditional approaches and an interesting concept from the book "How Google Tests Software," but none of them were fully applicable in my case.

That's when I decided to formulate my own methodology and share it with others. I hope you'll find this article helpful, even if you're already using something similar.

Meet my lovely ginger cat to brighten your mood 😺

The Problem

Imagine you've just joined a project. Everyone is smiling, welcoming the newcomer, and always ready to help. As a first step, you open the documentation and diagrams to understand how everything works. And you're faced with something like this:

Gathering your strength, you start deciphering the "magic" hidden behind all these technicalities. Often, the system's API is not documented at all, and even when it is, it's hard to find in the architectural diagrams. The same applies to the business entities your project is dealing with. It's nearly impossible to find them within the diagrams. Lastly, we all know from experience that components can be in different states (simply available or not), as can the models: the mere presence of a status field automatically adds several states to an object. Listing all these states in the diagrams is something I've never seen (call it bad luck).

Now, let me tell you what I do in such situations.

The Essence

I start with the idea that any system being tested can be viewed as a set of components (these could be microservices, packages/modules, classes, etc.) and models that are either stored within it or passed through it. With this premise:

The components operate with a set of models, such as a user or order.
Components and models can have states. For example, a user can be logged in, deleted, and so on. Some components may be available, unavailable, or "slow".
Components have some interface (GUI, REST API, GRPC, event subscriptions, CLI, a set of public methods, etc.), which consists of methods (or event subscriptions), their parameters, and the results of their calls – i.e., returned responses, additional calls, dispatched events, or changes to the state of models or the component itself.

Thus, if you create a system diagram consisting of components, marking their models, interfaces, and states, you can, first and foremost, visualize how the system works and what can affect it. Secondly, you can use combinatorics to develop a functional test plan for that system.

Let's examine an example

I. Creating a scheme

Let's imagine that we need to test a component of our system responsible for handling orders: the "Order component".

a. First, let's determine the components that interact with our "Order component"

It has a database with orders, interacts with an external "Payment provider" service for processing payments, and there is also an event bus where our component sends events about API calls.

All our components may be in one of the following states: available, unavailable, slow (requests are processed slowly). This includes the event bus.

b. Now let's picture the models this part of our system operates with

Undoubtedly, it is the Order entity. If we think a little, only a user can create an order in our application, meaning that the order stores information about the user, and the API of our service can also be used by the user. So the second model is User. Moving forward, orders also contain information about their contents, i.e., the products, so the third model is Product.

Let's figure out in which states our models can be:

User can be in one of the following states: non-existent, logged in, not logged in, deleted. Also, if they exist, they may be in one of 2 states: bank card information is provided or not.
Product can also be in one of the states: non-existent, has a quantity available (0 or more), deleted.
Order can be in the states: non-existent, created, deleted, paid, and closed.

c. Let's determine what interfaces our system components have

I. The "Order" service we are testing:
- We can create an order;
- Cancel or delete an order;
- Pay for an order;
- Close it.

For simplicity, let's leave only the "payOrder" method for analysis. It accepts the order ID as an argument. However, from the business logic perspective, only a user can call this method. Additionally, the order contains information about the product and the user who owns the order by its ID. So the complete list of business arguments for the payOrder method is:

User who called the method;
Order;
Products in the order;

Now let's figure out what happens when the payOrder method is called:

Validation of arguments;
Calling an external service to make a payment and awaiting a response;
Updating the order status if it has been successfully paid;
Sending an event to the data bus about whether the payment was successful or not;
Returning a response: the payment was successful or not.

An important note: as testers, we don't know the exact order of these events. Keep this in mind and take it into account.

Models that the service works with, based on the arguments of the tested method, are User, Order, Product.

II. The "Orders DB" database:

It supports CRUDL for orders. Orders contain information about products and their owner users. Responses to calls are success or failure, as well as the absence of response. So the models are the same: User, Order, Product.

III. External integration - "Payment provider":

Used by the Orders component only when calling the payOrder method. It has an important API for us, in the form of a single method called payByCard, which accepts the order number, its amount, and the user's bank card information. Responses to calls are also success or failure, as well as the absence of response. So, as per our business understanding, the models used are User and Order.

IV. Also, we have the "Event Bus":

The Order component sends events about API calls to it. For payOrder calls, there will be two events depending on the status: orderPaid and orderPayFailed. These events contain information about the order and the reason for failure for orderPayFailed.

Phew, it seems to be ready. Now we have a clear scheme in front of us that shows how our system works, its components and their connections, the business entities they manipulate, and their interfaces. The only thing left is to add a scheme for what happens when the tested component methods are called.

II. Test Design

We have already looked at what actions should take place when we call the tested method. Let us recall and analyze the order of these actions:

a. Argument validation

Let's go step by step. We pass metadata about the user who called the method. We pass the order ID, which in turn also contains the user who created the order. Thus:

Our method should return an error and attempt to send an orderPayFailed event if the user who called the method is invalid, meaning, for example, deleted;
Next, the order must be validated to collect information about it. The component needs to access the database. If the database is unavailable, we must return an error and send an event to the bus that the payment failed (and if we think about it for a second, we should also trigger alerts and metrics about this because it seems abnormal, so we noticed another moment worth checking);
We collected the order data, compared it, and the order must exist, that's the first thing. Secondly, it must be in only one state - Created, otherwise we send the failure event and return an error. And finally, the user who called the method must match the one who created the order, otherwise we have an error and a failure event.

It seems ready, let's look over everything one more time, and… We notice that the order contains products, and products can also have different states. What if we are trying to place an order containing products that have already been deleted? And if the number of products in the order is greater than what we have left? Perhaps it is worth adding another component to our scheme and adding a step similar to checking with the database, only we will send a request to reduce the quantity of remaining products. If this call is unsuccessful, we must go through the negative scenario. This way, we found out that we missed an entire interaction, but by describing the scheme competently, we were able to notice this at an early stage - when composing test cases.

That's better now. If all checks have passed, we move on. Next, we must call an external service to pay for the order using a card. Stop. We have two more user states: card information present or not. This also needs to be validated, and if there is no card data, go through the negative case again.

That's it for now. With validation finished, let's move on to the next step. Additionally, for negative scenarios, we must check that further steps have not been executed, subsequent calls have not been made, and nothing has changed in the database.

b. Calling an external service to make a payment

Let's start with the states in which the service can be. It can be available or not (in fact, it can also respond slowly, this option should also be considered, but we will skip it to speed up).

If the service is unavailable, the negative branch is executed, returning an error and sending an orderPayFailed event. In real life, it would be worth adding an alert and metric check and adding components to the scheme responsible for this.
If the service is available, we need to check the two possible responses - success and failure. If we get an error in response, the negative branch goes on; if success, we move on. It's worth thinking about the cases where we might get a negative response. It could be an invalid card, payment amount issues, or defects on the payment provider's side. These cases need to be checked.
The third option, which is also possible, is the absence of a response. It is necessary to check how our system and the tested Order component will react to this situation.

If this part is successful, the following actions should be performed in parallel; the order does not matter much to us here, but with one nuance. Our component must succeed in the next steps because the user has already paid for the order.

c. Updating the order status

The tested component must access the database and update the order status to Paid. Here again, it is necessary to understand how the Order component will behave, depending on the state of the database. What will happen if it is available? If not? Once again, I note that in all the negative cases we have considered so far, we should check that the tested application did not try to update the order.

d. Sending an event to the data bus about whether the payment was successful or not

We have already checked the sending of payment error events in the negative cases reviewed earlier. I will only point out here that we should check what exactly is being sent in the orderPayFailed event. Is the error there correct? If everything went well, we need to send an orderPaid event about successful payment and check its body. Again, all these cases, both positive and negative, should take into account both a good scenario where the bus is available and a bad one where it is unavailable or slow. Do we have a buffer for such cases, and will we not lose events?

e. Returning a response: whether the payment was successful or not

Should happen in any case. Again, it is worth checking the error texts and codes in any of the cases.

Conclusion

We've obtained a set of scenarios that can arise when working with the tested component. We have described the main business segments of our system. These include models, component interfaces, states in which components and models can be, as well as interactions that occur when using methods from the tested interface. Then, we can notice that we used combinatorics for models, interfaces, components, and their states to form a set of test cases.

Thanks to this approach, we got a fairly extensive test plan. Since we used diagrams for everything and applied them when studying the functionality, we were able to notice that we initially missed a couple of points.

When I use it

This approach has been particularly helpful for me when I had to test critical parts of functionality where I wanted to be highly confident.

However, I actually refer to it always: both when I need to thoroughly test a feature, and when I need to figure out something new. Even when I need to quickly check something, I also use it but draw very abstract diagrams on paper or just keep them in my head.

Unit, Integration, E2E?

The considered example focused on component and integration testing to a greater extent.

But we could easily turn the cases, for example, into end-to-end tests. For the diagram drawn above, we would need to consider who is subscribed to the orderPaid and orderPayFailed events. Investigate what happens to subscribers upon receiving these events. We'd add, for example, withdrawal from reservation or, on the contrary, writing off products in the product component, sending notifications in the notification component using external services, and so on.

To turn the set into unit tests, we simply need to mock and stub everything around (although some say there are no stubs in unit tests) 👍

In addition, a UML diagram or a set of screens from an app can serve as a scheme. How to draw this is a matter of taste, convenience, and the level of the tested component.

Closing thoughts

Did I confuse you too much? :) Even so, this methodology only seems complicated at first glance. You can read it again and simultaneously put a piece of your system next to it. Break down everything exactly the same way for it. I'm confident that everything will go smoothly. In this case, practice is easier than theory 😉

I hope my article will be useful to you!

Share how you approach test design. I would also be happy to hear your opinion on how my thought process works. Drop me a message and let's discuss this topic.

Find me on Twitter, LinkedIn.

And visit my website to find my public activities or read about my projects.

Good luck to everyone!

Building a Startup from Scratch: My Mistakes as CTO

egor romanov — Wed, 01 Feb 2023 13:16:04 +0000

When I was first approached to help build the technical side of a new startup, I had yet to learn what I was getting into. I was invited by a friend to audit the solution that the previous technical lead and developer had started. Still, due to unforeseen circumstances, both of them decided to leave the project. I was left with a barely started product and no team to continue the work.

The startup was developing an app to help users find the best deals and businesses to make the most of their time and money. The app was supposed to connect users with companies that had excess inventory or capacity during off-peak hours, allowing them to take advantage of discounts and deals. The requirement was to build a mobile app for iOS and Android, as well as a web admin portal for business owners to manage their offerings and communicate with customers. Additionally, all purchases had to go through our app.

With no team in place and a tight deadline, I knew I had to act fast. I started by assembling a team of engineers to build the backend, admin web portal, and mobile apps. While we had a clear vision of what we wanted to achieve and a solid plan in place, I knew that the vision and plan would change multiple times in the future. Finding the right engineers took more time than I had expected, and adjusting our strategy accordingly was crucial, but I was able to build a great team that could execute our vision and adapt to changing circumstances.

Building a Scalable Backend with Microservices: Our Experience

When I started building the backend for our startup, I knew scalability and adaptability would be the key. After an extensive search, I was able to find a highly skilled backend developer with experience in Node.js. Together, we decided to build our backend using a microservices architecture. We made this decision based on the dynamic nature of our startup requirements and the additional time we had before finding a mobile and web developer.

I had some experience with infrastructure, so I took on the task of setting up the cloud, Kubernetes cluster, monitoring and logging, and coding infrastructure. We used GitLab for version control and a CI/CD pipeline to automate the build, test, and deploy process. We chose JSON-RPC as the communication protocol and Node.js for the backend. Our backend developer chose MongoDB as the database, while I would have preferred Postgres.

We ended up with several microservices, including:

Products: This microservice contains information for managing the products and deals offered by partners, their retail locations, and promotions. Through it, one can create, update and moderate them. It also handles the launch and stopping of campaigns.
Business Users: This microservice managed the companies and their employees and data in the Auth service on the admin panel side.
Orders: It is responsible for handling the cart and lifecycle of orders, as well as integrating with the payment system.
Gate: The microservice sits at two entry points from clients to the backend (from mobile devices and from the admin panel). It maintains a websocket connection between the client and the backend, directing requests either to the authentication service or to the facade.
Admin Facade and User Facade are facade microservices for the backend. They distribute requests from clients to the services. It encapsulates the internal structure of the system and only grants access to the methods that are available to the client.
Auth: This microservice was responsible for taking user authentication and authorization.
File: The service was responsible for managing static resources (such as products photos or legal documents with partners), and it integrates with Yandex for data storage.
App Users: This microservice was responsible for managing mobile app users. Among other things, it stores meta information about users: last seen, friends, etc.
Settings: This microservice was responsible for managing the settings of the app.
Marketing: This microservice was responsible for managing the marketing campaigns and promotions, and recommendations.
The Email, Push, and SMS notification services are responsible for integrating with respective vendors.

The platform had several external integrations: for payment with the vendor CloudPayments, for notifications with push, SMS, and email services, and for using static files (e.g. images) with Yandex Cloud Object Storage.

Our architecture

It took us 2–3 months to find a mobile and web developer, but by that time, we had a solid backend infrastructure in place. We were able to change concepts and requirements multiple times during the development process, and the microservices architecture made it easy to adapt our backend accordingly.

Our mobile developer was terrific and did a great job reworking the mobile apps a few times to match every new vision our CEO and design team had. Communication between client apps and the backend occurs through a websocket using the json rpc protocol. We used Vue.js on the frontend and React Native on the mobile side, which helped with consistency and code sharing in the team.

It was overall great that we used JavaScript everywhere, as it helped engineers to read code of each other and make changes required to update how services were communicating, especially with the mobile app and web app.

An Introduction to Supabase

When we launched our startup, we posted a blog about the challenges we faced building the tech company. We received a lot of feedback from our community, some of which was negative, but also a lot of it was constructive. One of the pieces of feedback that stood out to me was the suggestion to use a service like Firebase to simplify our backend.

At the time, I didn’t think using Firebase was a good idea, as it felt like a huge vendor lock and I was worried about losing control over our data and infrastructure.

Spoiler: a few months later, our startup failed to gain traction, and we had to close it down. It was during this time that I came across Supabase while browsing through the latest Y Combinator batch. Supabase felt like the solution I should have found when I was starting my work on the startup.

It’s an open-source platform that aims to simplify the process of building a scalable and secure backend for web and mobile apps. Built on top of Postgres, it provides a set of tools and services for managing the database, authentication, realtime data sync, and storage objects while still giving you control over your data and infrastructure. Some of its key features include:

Automatic API generation: Supabase automatically generates REST, GraphQL, and realtime websocket notifications for your Postgres database, allowing you to quickly and easily access your data from the web and mobile apps.
User authentication and authorization: Supabase provides built-in support for user authentication and authorization, making it easy to secure your app and protect sensitive data.
Realtime: Supabase can keep your web and mobile apps in sync with the database, eliminating the need for manual data refresh.
Storage: you can store large objects, like images or documents, and you can also make resize image requests.
Scalable and secure: Supabase is built on top of Postgres, it can be easily scaled vertically and horizontally and has security features such as encryption and RLS.

https://supabase.com/docs/guides/getting-started/architecture

Supabase is an excellent choice for startups and small teams who want to build a backend quickly and easily without having to worry about the complexities of setting up and maintaining the whole infra themselves. And even in a big tech company, when you launch a new service, you should consider Supabase or similar OSS projects.

What if Supabase

In this section, we’re shifting gears and imagining how our startup would have been different if we had used Supabase from the start. Instead of spending a few months building microservices, we could have been focusing on what really mattered: our users and our product. I would invest all my time in searching for a mobile developer, and instead of infra, I would be able to focus on the backend. Supabase would have made setting up and managing a database a breeze, with built-in services that would have replaced most of our microservices. It would have saved us time, money, and headaches. And it wouldn’t have lost us the ability to adapt to changes and requirements, which was one of our most significant advantages. Unfortunately, we didn’t know about Supabase back then, but maybe you do, and it can change your startup’s story.

Let’s look at what we could have replaced with different Supabase features.

Auth

First, Supabase’s built-in authentication and user management service could have replaced our separate auth and user management microservices. This would have given us user registration, login, and manage user roles and permissions out of the box. Additionally, Supabase’s support for role-based access control (RLS) would have allowed us to implement fine-grained access to our data. For example, users could only view their own orders, while business owners could edit their offerings, and administrators could access all data.

Create an RLS policy in Supabase so that company owner can manage his employees

Storage

Using Supabase’s built-in file storage would have simplified the process of handling file uploads, downloads, and management, eliminating the need for additional storage solutions. Instead of having a separate file microservice, Supabase’s built-in file storage would have allowed us to take advantage of its features, such as image resizing for product images, allowing us to create previews on the fly. Additionally, we could have used Supabase’s file storage to securely store and manage legal documents that needed to be signed by our business users, providing us with a centralized location to store and access all these important documents without the need for additional third-party services.

// Request a small resized image of a product from Supabase Storage:
await storage.from('mama_jane').download('pizza.jpeg', {
      transform: {
        width: 200,
        height: 200,
        format: 'origin',
      },
})

Gateways And Facades

With Supabase, we could have said goodbye to our two gateway and facade microservices, responsible for handling communication between the mobile app and web app with the other microservices. Supabase’s automatic PostgREST and GraphQL API would have taken care of all that, allowing us to focus on other things.

# Retrieve a feed with products from app using Supabase generated GraphQL API
{
  retailersCollection(
    filter: {active: {eq: true}},
  ) {
    edges {
      node {
        name
        productsCollection {
          edges {
            node {
              id
              title
              imageUrl
              price
            }
          }
        }
      }
    }
  }
}

Products

When it comes to our products and settings microservices, it mainly functioned as a data owner, handling CRUD operations for our products. But with Supabase, we could have skipped these services altogether and instead used the power of Postgres directly. This would have allowed us to handle some of the more complex operations, like product updates that required transactions, directly in the database. And as for the internal hooks and cron jobs that were a part of this service, Supabase’s support for pg_cron, triggers, webhooks, and serverless functions would have done the trick just as well.

Notifications

We could also have replaced our push, sms, and mail microservices with serverless functions and triggers on tables within Supabase. For example, we could have set up a trigger on the orders table to send a push notification to the user when their order is confirmed. The same goes for sending SMS and emails. We could have used triggers to automatically send messages when certain events occur, such as a user’s account being created or a new product being added. This would have greatly reduced the complexity of our architecture and made it much easier to adapt to changes in requirements. Imagine our marketing manager wants to run a campaign and send push notifications to users who have not made an order in the last 30 days. With Supabase, this could have been easily achieved by creating a simple trigger on the orders table.

Marketing campaigns

By the way, this example shows us that we no longer need marketing service too. This is because we could have set up automated campaigns triggered by specific actions, like in the example above. Or by introducing a new table called marketing_campaigns. Our marketing manager could then simply insert a new row to the table with parameters, such as what users to notify. A trigger on that table would invoke a serverless function to send out push notifications.

--Trigger to send push notifications after the
--marketing specialist adds a marketing campaign to the database.
create or replace function insert_marketing_campaign() returns trigger as $$
begin
    insert into marketing_campaigns (message, user_group, start_date)
    values (new.message, new.user_group, new.start_date);
    perform send_push_events();
    return null;
end;
$$ language plpgsql;

create trigger insert_and_send_push
after insert on marketing_campaigns
for each row
execute function insert_marketing_campaign();

Admin studio

In retrospect, I realize that building a custom admin portal for our business clients may not have been the best decision. Despite my initial reservations, my partners insisted on its development. However, as it turned out, our clients were not quite ready to navigate a new and unfamiliar interface. The Supabase dashboard would have made it easy for our sales team to manage our business customers’ offerings. Maybe I had convinced my partners to hold off on developing a separate, custom-built admin portal until we had more user traction and a better understanding of their needs.

User management in the Supabase dashboard

Orders

And it leaves us with the orders service only. We could have technically replaced it with serverless functions and triggers, but I would have preferred to keep it as is. The main reason for this is that I am more comfortable like that as it is a very sensitive one. But, if we had used Stripe as our payment provider, we could have taken advantage of Supabase’s new wrappers functionality that uses postgres_fdw to send queries directly to Stripe from within Postgres, and that could have made a deal. However, with a bit of extra time and effort, it is possible that we could have created our own wrapper for our payment provider to integrate it with Supabase.

// Listen for stripe events using Supabase Edge Functions 
// (to keep track of invoice-paid events, for example)
serve(async (request) => {
  const signature = request.headers.get("Stripe-Signature");
  const body = await request.text();

  let receivedEvent;
  try {
    receivedEvent = await stripe.webhooks.constructEventAsync(
      body,
      signature!,
      Deno.env.get("STRIPE_WEBHOOK_SIGNING_SECRET")!,
      undefined,
      cryptoProvider
    );
  } catch (err) {
    return new Response(err.message, { status: 400 });
  }

  console.log(receivedEvent);
  return new Response(JSON.stringify({ ok: true }), { status: 200 });
});

Other thoughts

Our startup also relied heavily on geographical data. We could have leveraged the power of PostGIS, a spatial database extender for Postgres, to handle all of our geographical data needs. This would have allowed us to easily incorporate features such as location-based searching and mapping within our app. Overall, utilizing Supabase and its integration with PostgresSQL would have greatly simplified our architecture and allowed us to focus on developing our app’s core features. Using Supabase could have been a game changer for our startup. I don’t think it could save us from closing, but it would definitely save us some money on the development and infrastructure.

Comparison

Using Supabase, we would have eliminated the need for a Kubernetes cluster for both production and staging, as well as our managed MongoDB instance and monitoring infrastructure (we used elastic stack). This would have reduced the infrastructure costs for our startup. In addition, it would have allowed me to focus on searching for only one developer to work on our mobile apps rather than needing to find several engineers to work on microservices and the admin portal. This would have resulted in cost savings of up to 6–7 thousand dollars per month, which could have been invested in other business areas. Overall, Supabase offered a simpler and more cost-effective alternative for our startup and is worth considering for any startup or service within a big company. At least, this is my opinion.

An example of what our architecture may have looked like if we used Supabase.

And what’s important is that I am not afraid of being vendor-locked. You can work with Supabase using a variety of programming languages, including JavaScript, Dart, Python, or Go. This allows for flexibility in building and maintaining your application. Additionally, Supabase is designed to scale, making it suitable for both small startups and large enterprises. It can be used in the cloud or on-premise and integrated with other open-source projects. This allows for a high degree of customization and flexibility in building and deploying your application.

Conclusion

In conclusion, building a microservices architecture can be a challenging and costly endeavor, as we experienced in our own startup journey. However, Supabase offers a simpler and more cost-effective alternative, with built-in features that can replace many of the microservices that a typical startup would need. From user management and file storage to realtime APIs and automatic data management, Supabase has the potential to save both time and money.

While I could not use Supabase in our own startup, I hope our experience and insights will encourage others to consider it a viable option for their own projects. While Supabase may not be the best fit for every project, it’s worth considering as an option or at least looking at the market for other alternatives that may better suit your needs. I would highlight Pocketbase as a possible one, but I would still choose Supabase for the majority of projects. And only use Pocketbase in certain infrastructural development projects where a significant amount of custom Golang code is required.

P.S.:

It has been some time since I wrote the first draft of this article, and as I reflected on how Supabase could have helped my previous startup, I couldn’t help but desire to be a part of the Supabase team. Imagine my surprise when I received an email from them announcing that they were hiring for a QA position. I immediately applied, and to my delight, I received an offer. Now, as a member of the Supabase team, I have had the opportunity to work with the platform on several personal projects and witness firsthand how it assists other startups and large companies in building their products. It is exciting to be a part of a company that is making such a significant impact in the tech industry.

If you haven’t tried out Supabase yet, you should give it a try! And if you liked the design of an app, don’t mind reaching out to choice.studio!