Latest / The CTO Podcast with Fexingo: Technical Leadership, Architecture, and Engineering Org / How Supabase Rebuilt Postgres for Real-Time Apps
Transcript
- Lucas: If these conversations are useful for what you're building or running, we hope you'll stick around for the next ten minutes — because today we're talking about one of the more interesting architectural bets in the database world right now. Luna: You're talking about Supabase? Lucas: Exactly. Supabase is often called the open-source Firebase alternative, but what I find more interesting is how they rebuilt a real-time layer on top of PostgreSQL — not by bolting on something, but by deeply integrating with Postgres's own replication mechanisms. Luna: And honestly, if today's conversation is worth a coffee to you, there's a link at buy me a coffee dot com slash fexingo. That's the smallest ask we can make, and it keeps this show ad-free. Lucas: Yeah, we really appreciate that. So let's get into it. Supabase's core product is a hosted Postgres database, but the killer feature is Realtime — the ability to subscribe to database changes over WebSockets and get pushed updates in milliseconds. Luna: How is that different from, say, using Postgres's LISTEN/NOTIFY? Lucas: Great question. LISTEN/NOTIFY is a simple channel-based mechanism — you send a notification string, and all listening clients receive it. But it has limitations. It doesn't give you the actual changed row data, it doesn't handle backpressure well, and it doesn't scale to tens of thousands of concurrent connections on a single Postgres instance. Luna: So Supabase needed something more robust. What did they build? Lucas: They built a dedicated Realtime server written in Elixir, running on the BEAM virtual machine. The key insight is that Elixir's concurrency model — lightweight processes that can handle millions of concurrent connections — maps perfectly to the problem of managing WebSocket subscriptions. Luna: But how does the Realtime server actually get the data from Postgres? Lucas: It uses PostgreSQL's logical replication feature. Essentially, the Realtime server connects to the database as a replication slot and receives a stream of changes every time a row is inserted, updated, or deleted. That's a built-in Postgres capability — the same one used for replicating data to standby servers. Luna: So they're piggybacking on a feature that already exists. That's clever. Lucas: It is. But it's not trivial. One challenge is that a replication slot can only be consumed by one consumer at a time. So if you have multiple Realtime server instances — which you need for fault tolerance — you can't just point them all at the same slot. Luna: How did they solve that? Lucas: They forked the replication slot mechanism. Each instance gets its own slot, and they use a custom PostgreSQL extension called pg_jsonschema to validate and filter changes. But the real magic is in the Elixir layer, where they handle fan-out to potentially millions of WebSocket connections. Luna: Let's talk about the fan-out — how do they prevent the Postgres instance from being overwhelmed? Lucas: That's the backpressure problem. If a client has a slow network connection, or if there's a burst of writes, the Realtime server could buffer up messages and eventually run out of memory. Supabase implements backpressure at the WebSocket level — they monitor the send queue per connection and if it exceeds a threshold, they disconnect the slow client. Luna: That seems harsh. What about just dropping older messages? Lucas: That's another option, and they do have a configurable 'max offset lag' — if a client falls behind more than a certain number of changes, they skip older updates and send only the latest state. But the disconnection approach is the nuclear option for clients that are completely unresponsive. Luna: So the architecture is: Postgres with logical replication, an Elixir Realtime server, and WebSocket connections to clients. What about scaling the Elixir layer itself? Lucas: Supabase deploys the Realtime server on AWS using ECS, behind an application load balancer. The Elixir processes are distributed across multiple nodes using the BEAM's built-in distribution protocol. Each node handles a subset of the connected clients, and they use a PubSub layer — based on Redis or PostgreSQL — to broadcast messages across nodes. Luna: Why would you use PostgreSQL for PubSub when Redis is faster? Lucas: Good point. They actually default to PostgreSQL for PubSub because it simplifies the operational overhead — one less system to manage. But for higher throughput, they recommend Redis. And in their managed offering, they use a dedicated Redis cluster for cross-node messaging. Luna: One thing I've always wondered: why WebSockets instead of server-sent events for real-time updates? Lucas: WebSockets provide full-duplex communication — the client can send messages back, which is useful for things like acknowledging receipt or sending commands. Server-sent events are one-way only. But the bigger reason is that WebSockets work better with existing infrastructure like load balancers and proxies that might buffer or terminate SSE connections. Luna: That makes sense. And Supabase also supports a JavaScript client library that handles reconnection and retry logic. Lucas: Right. The client library is open source and implements exponential backoff with jitter. It also supports presence — tracking which users are online — and broadcast, which lets clients send messages to each other without going through the database. Luna: Let's talk about the infrastructure cost. Supabase runs on AWS, and they've made some interesting choices there. Lucas: Yes. One of the more notable decisions is that they use AWS Graviton instances — the arm based processors — for their compute layer. They claim that Graviton gives them about 20 percent better price-performance compared to Intel-based instances for their workload. Luna: And that's significant for a startup that's scaling fast. Supabase reportedly has over 200,000 users now, and they've been able to grow without a dedicated infrastructure team. Lucas: Exactly. Their entire infrastructure — including the Realtime server, the API gateway, and the dashboard — is managed by a small team of engineers who are mostly focused on product development. The fact that they can rely on managed AWS services and Elixir's fault-tolerance means they spend less time on operations. Luna: But what about the database itself? Postgres can be tricky to scale beyond a certain point. Lucas: Supabase uses a multi-tenant model where each user gets their own Postgres instance — actually, each project gets its own database. They use a custom orchestrator to provision and manage these instances. For larger customers, they offer dedicated instances with read replicas. Luna: So they're not trying to build a multi-tenant database like, say, PlanetScale or CockroachDB. Lucas: No, they're very much a single-tenant Postgres provider. The innovation is in the layer on top — the real-time subscriptions, the auto-generated APIs, the authentication — not in the database engine itself. And that's a deliberate choice because it lets them leverage all the maturity of Postgres. Luna: One potential downside I see: logical replication adds overhead to the primary database writes. Have they talked about the performance impact? Lucas: They have. Logical replication does consume CPU and I/O on the primary, especially during write-heavy workloads. Supabase recommends that users keep their write throughput under about 5000 writes per second per database to avoid noticeable impact. But for most applications, that's more than enough. Luna: And what about the Realtime server's own reliability? If it crashes, do clients miss changes? Lucas: The Realtime server uses the replication slot to track its position. When it restarts, it resumes from the last acknowledged position, so no data is lost. The client library will reconnect and catch up with any missed changes. The only risk is a brief period of unavailability while the server restarts — but with multiple nodes, that's minimized. Luna: It sounds like a well-thought-out system. What do you think is the biggest lesson for other teams building real-time features? Lucas: I think the biggest lesson is to leverage existing database capabilities rather than building a separate event system from scratch. Supabase didn't invent a new replication protocol — they used Postgres's built-in logical replication and focused their engineering effort on the client-facing layer. Luna: And on the choice of Elixir. Not many teams would pick that for a real-time server, but it's a natural fit. Lucas: Absolutely. The BEAM VM's ability to handle millions of lightweight processes makes it ideal for WebSocket workloads. I think we'll see more real-time infrastructure built on Elixir or Erlang in the coming years. Luna: So what's next for Supabase? Are there any architectural changes on the horizon? Lucas: They're working on a few things: better support for multi-region deployments, where a single Postgres database is replicated across regions and the Realtime server can read from any replica. They're also exploring the use of WebTransport as a potential successor to WebSockets for even lower latency. Luna: WebTransport is interesting — it's built on QUIC and HTTP3. But it's not widely supported yet. Lucas: Right. It's early, but Supabase tends to be an early adopter of new web technologies. They were one of the first to support Edge Functions on Deno, for instance. So I wouldn't be surprised to see them experiment with WebTransport in their Realtime layer. Luna: Alright, that's a solid overview. If listeners want to dig deeper, where should they go? Lucas: Supabase has a great blog where they publish detailed architecture posts. The one on 'Building a Real-time Platform with Elixir and PostgreSQL' is especially good. And of course, their GitHub repo is open source. Luna: Thanks, Lucas. I think we've covered a lot of ground today. Lucas: Yeah, it's a fascinating case study in how to build modern real-time infrastructure without reinventing the wheel. Until next time.