Migrating Postgres: from Render to Neon

Hi and welcome to Database DevOps Academy #7!

Each week, we share Database DevOps insights and best practices for modern engineering organizations. If you enjoy this newsletter, we ask you to subscribe and share. ??

In this Issue, we are taking you along our journey of how and why we migrated our Postgres to Neon. Enjoy! ??

Background

A few months ago, we announced?SQL Chat, a SQL client using natural language to interact with the database. It's offered as a service at?sqlchat.ai?and is also?open-sourced?for self-hosting.

Recently, we added the account and subscription feature for SQL Chat, and we needed a database to store user info. The following is the story of how SQL Chat Postgres transitioned from?Render?to?Neon.

Starting with Render

The existing SQL Chat app is using Next.js and runs on Vercel. We pick Postgres as the database and use Prisma as the database client. As to the Postgres hosting provider, since we already?use Render extensively at Bytebase?and are pretty happy with it, so we just spun up a Postgres instance there as well.

The Problem

The testing phase went smoothly, however, soon after we deployed it to production, we hit the 100 max connection limit.

The common solution is using PgBouncer and Render does provide a?pre-built template. The instruction is for deploying a PgBouncer as a private service. However, since SQL Chat is hosted on Vercel, we need to make PgBouncer public. We tried to run Render's PgBouncer Docker image as a public web service, but we were not able to make it work ??.

Switching to Neon

Around the same time. Vercel and Neon?announced a partnership, which allows provisioning a Neon database straight from the Vercel project. We have known Neon since its inception, so we took a deeper look and gladly found that Neon provides?built-in connection pooling.

We first tried to provision a Neon database from the Vercel console.

We originally thought Vercel will establish a private connection to Neon. But from the dashboard, Neon database is still exposed as a public URL. Realizing this, we turned to provision the database from the Neon console directly.

Then we changed our Prisma config to point the database URL to Neon's connection pooling endpoint, deployed it, and hit the following error when SQL Chat tries to migrate the database schema.

Error undefined: Database error
Error querying the database: db error: ERROR: prepared statement        

Fortunately, Neon's doc has already provided the?instruction?to deal with this. Prisma allows using connection pooling endpoint for normal database access, on the other hand, Prisma migrate requires a direct database connection. And since Prisma 4.10.0, there's a?directUrl?setting to do that. We thus made the change and deployed it, and everything worked!

To further consolidate the stack, we also moved the SQL Chat sample database to Neon. Notice we also use the connection pooling endpoint.

Last week, we received our first bill from Neon. Neon's pricing is usage-based, $8/month for hosting 2 Postgres instances with connection pooling included, not a bad deal.

Summary

This article is not suggesting that Render is inferior to Neon. In fact, most of our Go-based backend infra is still running on Render, connecting to the Render Postgres instance. Render has done a good job filling the gaps left by the classic Heroku.

On the other hand, there are 2 main reasons for adopting Neon in SQL Chat:

1. The power of Vercel as a distribution channel. SQL Chat chooses Next.js because of its dominant ecosystem. And Vercel as the primary sponsor is the go-to place to host Next.js app. After seeing the Vercel, Neon partnership, we know the integration between Vercel and Neon can only get better.
2. Neon provides a good database DX:

• Built-in postgres connection pooling which is a requirement for any web app with decent traffic.
• Good documentation around the common integration path. When we hit that Prisma error, the instruction is right there. The?data import doc?is also easy to follow to migrate the data.

BTW, our flagship product?Bytebase?is meant for teams to manage database changes in the same way as managing code changes using GitLab/GitHub. This experience of building SQL Chat on Vercel + Neon also teaches us to deliver a better product to our developers and DBAs.