How I Built A SQL Editor For A Vector Database (pgvector)

Intro

I’ve used pgvector for a bit, and though it’s convenient to have a vector database embedded right in Postgres, the seemingly random 1536-dimensional vectors I was getting as output from making approximate queries in TablesPlus were unhelpful. And each modification of an approximate query, embedding strategy, or vector database configuration can involve a Jupyter notebook with multiple experiments. To resolve this, I set out to make a developer tool that would make tuning and visualizing pgvector embeddings and queries easy and intuitive. After 6 months of development, I’m pretty pleased with the results: dbSurface is an open source, self-hosted, projection enabled PostgreSQL editor. It allows you to reduce millions of vectors to 2d, explore and query across your projections, color projections by column values, check query precision with built-in metrics, and a few other things. Here’s a bit about the process of building dbSurface and what I learned…

The Architecture

Firstly, I wanted to make the app run locally as this was a SQL editor I was making. I had made a prototype that was an electron app and could display a random sample of a few thousand vectors from a postgres table, but randomly sampling vectors felt lacking. However making a 2d projection of millions of points has to be offloaded to a cloud worker, so I landed on a hybrid setup which consists of: a cloud worker to execute dimensionality reduction and save the 2d projections and a locally hosted app where the user could query directly to their database and have their query results locally overlaid onto the projection. I also switched from an Electron app to a Docker-hosted Next.js app. (This turned out to be a good decision–Next.js felt a lot smoother than with Electron.)

The Stack

I think I’m lucky I made this app in 2025 because the tools to make this app work fast and look good feel like they’re just coming of age. I used a Next.js framework with React, TypeScript, and ShadCN components on the front end–(this is the way to go to make sleek design easy). A tRPC API layer, and two separated Next.js backends: one hosted locally for making queries to your database and a separate set of API routes hosted on Vercel for cloud offloaded work. tRPC handled the backend split well once I found the right GitHub issue on how to configure the provider (though this took some digging).

I used Supabase as my cloud based database, Drizzle as an ORM and a query layer for the Supabase queries, and Cloudflare r2 to store projections. (At first I used Supabase Storage, but r2’s unlimited egress bandwidth helps a lot when loading out a few hundred MBs for the largest projections). Drizzle actually felt easier than working with the built-in Supabase client side API — it gives you an ORM, handles type safety for your queries (which is not really straightforward to setup using the Supabase API unfortunately), and since you’re database calls are in the backend there’s a little less security pressure on RLS. The downside is that every tiny database call has to go to the backend, but that’s just one time work of writing a small route for each CRUD operation.

For dimensionality reduction I used GPU-UMAP, which can reduce 1 million 1536-dimensional points ~2mins (blazing fast). NVIDIA’s GPU-UMAP implementation also allows you to specify the number of batches to split the reduction into (so you don’t have to put 50 million 2000 dimension points in RAM at once), which is very convenient. I chose Modal as my serverless GPU container provider. Modal handled the GPU enabled UMAP function pretty cleanly, and also allowed me to span out a set of concurrent containers to query the users’ table. (Querying the user’s database, not dimensionality reduction, is actually the bottleneck using GPU-UMAP).

To display zoomable projections of millions of points without any lag GPU enhanced rendering becomes necessary. To power this I used deck.gl. deck.gl is an API that lets you build GPU-powered visualizations directly with React components which was incredibly helpful. It wasn’t necessarily easy making the set of scatter plots organized into a zoomable quadtree, but once I got the hang of the library I was able to implement this in only a few hundred lines of code, most of which were React. And I didn’t have to touch the WebGPU library itself.

I also used Inngest, which is a service mainly dedicated to handling webhook ingestion, but after working with it I wouldn’t recommend using a service to handle webhooks if your flow isn’t too complicated. I couldn’t get Inngest to work on some jwt payloads, so I wrote that handler out manually and it works just fine and wasn’t difficult to make. Finally, for auth I used Kinde Auth. Kinde felt modern and easy to work with, so no complaints.

Some Lessons

The stack I described above (along with a good dimensionality reduction algorithm) was critical to getting this app out, but I didn’t come across it immediately. Here’s what I learned in the process of finding the right tools:

Avoid Small/New Projects

For dimensionality reduction I initially put in a fair amount of research and settled on ParamRepulsor (a variant of pacmap which is a dimensionality reduction algorithm that some say is better than UMAP at preserving local structure). ParamRepulsor seemed cool as it could theoretically be GPU powered because it is implemented in PyTorch and claimed it could handle the online setting where new data can be added to a previously created projectoin (potentially opening doors for a live projection of your database vectors). However, in practice the algorithm was extremely finicky with hyperparameters to the point that it felt like the implementation contained bugs (just showing straight lines of points seemingly random sometimes). Also the GPU enhancement was not as built out as NVIDIA’s GPU-UMAP implementation. I thought I was being creative, but I found nice docs and a clean, well-tested implementation are often more important than (supposedly) incrementally better benchmarks.

I made a similar mistake with a little React library which I used for making multiple editor tabs. This tab sidetrack ended up being an enormous time sink. After a little up front benefit from the quick and easy setup the docs promised, I was left batting css styles in order to make the tabs look right after every readjustment of the main page. After stringing tabs along in the project for months, I finally gave in and decided to drop them. I’ll just mention this as an aside here because it’s such a common piece of advice, but unnecessary features should not be included in an initial launch. There is still a long document with unimplemented features and optimizations for dbSurface, many of which I had hoped to include in the initial project launch. But when I approached the 6 month mark of developing I started ruthlessly cutting these. I still hope to get to many of these eventually, but taking a step back I realized I already had a more than useful and polished tool that I wanted to get out. Striking the right balance on what to include or not in an initial launch isn’t easy, but there’s a certain point that you just have to go all in on actually getting a project done.

Pick A Tool (Or Write A Feature) When It Can Be Tested

I wanted to use Clerk for auth off the bat. I’d used it before and liked the developer experience and nice looking components. However, after configuring its Middleware, setting up webhook syncs from clerk to my database, and preparing a Clerk satellite route setup to handle two separate domains, when I actually tried to put Clerk in production I realized that Clerk doesn’t work with a localhost domain. This maybe should’ve been obvious if I had dug into the docs and saw that localhost couldn’t easily get the certificates Clerk requires, but I didn’t piece it together. This to say that I really should not have been doing any auth until later. Write a feature, test it, then move on. Half-implemented setup can cause wasted time like I did with auth and causes hard to trace bugs as you have sort’ve half baked code in your codebase which I was liable to forget was there and exactly which threads I left hanging. After briefly exploring Auth0 and dropping it due to their dated feeling docs and thin setup instructions, I stumbled on Kinde Auth. This turned out to be a great find as it felt like a modern tool and had the exact separated client and server side setup I was using nicely documented.

— — — — — — — —

I’ll also touch on some general software engineering lessons I picked up through this project. This project constitutes a substantial portion of my software engineering experience now, and took me from feeling somewhat able to get a project off the ground, to feeling confident about spinning up a new tool if I ever wanted to. That confidence is mainly just from working through many individual bugs and pushing the right buttons to see results, but there are some dev workflows and problem solving techniques I think are worth sharing.

My Dev Workflow

There’s no replacement for perseverance. Every impossible bug will look trivial in hindsight and getting there is only a matter of keeping at it for as many days as it takes and approaching it from every possible angle. However, knowing and using all the resources available to you are going to make the process a lot easier.

My toolkit:

• ChatGPT: The obvious first go to for any issue. It excels in making a first draft of code, but small tweaks in an already complicated codebase are probably not worth explaining all the context to gpt. For any syntax or more straightforward code error, gpt should be the go to. I’ve found in-editor AI VSCode extensions to be a bit underwhelming for anything other than straightforward autocomplete. After getting used to talking with o3 or 04-mini-high, the in-editor models feel frustratingly unhelpful compared to those more powerful ones. Also don’t forget to use the gpt deep research feature occasionally when considering what tool or approach to take on a larger issue. This can be helpful to get a breakdown of the internet landscape on a certain topic, but be a little wary of all of the information it gives. Even when it cites sources it can get things wrong.
• Docs: It’s generally worth reading through these a bit or asking gpt to give you summaries of them. Rarely should you be developing on gpt alone (though I admit I was sometimes guilty of this when it came to certain well known libraries.) And going through docs thoroughly can pay off. Two examples of this were when I discovered batched GPU-UMAP in the CUML docs, and when I found the exact separate client and server setup I was using described in the Kinde docs. I’m also pretty sure there are some cool Modal functions that I came across just by reading through each of them. However, there are times when even the docs aren’t enough…
• The Source Code: For deck.gl I had to dig into the source code and basically overwrite certain things, so getting a pretty good understanding of the part of the codebase was necessary. But even outside of digging for necessary information, open source projects are a good place to look for inspiration (or just code) on how to build certain parts of your app. It took me a while to remember that Supabase was an open source tool which already implemented a nice looking React based SQL editor. Once I started looking through that repo it gave me a solid framework for the code editor, results table, and especially autocomplete functionality. Looking through these repos also has the added benefit that you notice some nice design patterns and can try to let the clean, production quality code rub off on you.
• Your Roadmap Document: I kept a running document with my long term plan as well as the next few steps ahead throughout building this tool. Problems that seem really big just have to be broken down into smaller steps. (And don’t make the mistake of thinking about the final product and how far away it is–this terrified me sometimes. Hitting that nice flow where I was pleased with every small bit of progress was hard to come by, but something I strived for.)

Conclusion

So that was what was involved in building out dbSurface. If there’s anything you can relate to, have feedback on, or have questions about please feel free to comment! I’m also curious about your thoughts on the results of this project. Check out the source code or pull the docker image and try the app yourself and share your thoughts!

-Zach

Technical Postscript: Quadtrees

One of the hardest individual components of this project was implementing the quadtree for splitting points into separate scatter plots. I could go into some detail here but I’ll just briefly mention that the resources on quadtrees I’ve found online describe that denser parts of point map get split into four different nodes (I’ve implemented this), however I haven’t found much information on how to actually determine what points from a parent to flush to the leaves. I needed each zoom layer to contain progressively more detail so I couldn’t dump all the data to the leaves, however flushing points randomly doesn’t work too well. If certain very dense tiles are next to less dense tiles it can happen that edge artifacts appear. This is because the dense tile flushes points which should appear next to a sparser tile, down into leaves. Zooming in further the gap will fill in, but there can be an akward in between. I’ll leave more details and diagrams for perhaps another post, but I’ve landed on an adaptive density sampling technique for point flushing that works pretty well (though it isn’t perfect in rare cases). But if anyone is familiar with resources on this issue lmk!