A very explicit design goal for pgEdge Distributed PostgreSQL is to support distributed database access from the edge. (It may even be why we called the company pgEdge!) In this post we discuss how developers working with edge platforms like Cloudflare Workers, Fastly Compute@Edge, Vercel and similar can utilize a pgEdge cluster to significantly reduce database access times from their applications. 

Until pgEdge there has not been a great solution for relational database access from code deployed as edge workers or edge functions.   While the edge platforms can make the static portions of applications really sing, each database access adds potentially hundreds of milliseconds to each request, and multiple requests may be needed for an individual web page.  Therefore even a 3 or 5 node pgEdge cluster can dramatically improve page loads for geographically dispersed users.

In additional blog posts in coming weeks we will show in detail how to integrate with several of the popular edge platforms like Cloudflare Workers and Compute@Edge.  Here we want to cover the general principles to get you started.

This post assumes you have already deployed a pgEdge cluster using either pgEdge Platform or pgEdge Cloud. If not check out our Getting Started guide for pgEdge Platform or use the pgEdge Cloud cluster creation UI if you are a pgEdge Cloud beta user.

Here’s what your overall application architecture will look like when using (for instance) Cloudflare Workers with your pgEdge cluster:

Conceptually this will be similar when using other edge platforms like Fastly, Vercel or Netlify.

Accessing pgEdge nodes from Workers or edge functions

For most of the edge platforms all requests and responses are over HTTP/HTTPS.  As a result you will need to configure PostgREST or pgrest on your pgEdge nodes.  See the pgEdge documentation or contact us if you need further information on how to do this.

Cloudflare Workers has a tunnel to directly access a number of different types of databases, including Postgres over port NNNN.   This may be a more convenient option for some applications.

Connecting to the nearest pgEdge node

The only additional step you need in your worker or edge function code beyond what’s needed to access other relational databases is to determine which edge data center or POP the request is being processed at and map it to the nearest pgEdge node.   By convention both cloud and edge providers use the three letter IATA airport codes to identify their regions, data centers and POPs (e.g. IAD, DUB, SYD).

Cloudflare Workers makes this available in the “colo” field of the incoming Request.

Fastly makes this available in the FASTLY_POP environment variable.

To find the closest pgEdge node to where the request is being processed, you need to:

  1. Make the configuration details of the pgEdge nodes in your cluster available to your Worker or cloud function.  You will want to identify the node location (by IATA code), the latitude and longitude of the location, and the connection string to access the database server. Use environment variables, your edge platform’s KV store, or a flat file (on say R2) to feed the cluster configuration info to your Worker or edge function.

  2. Use a library like “geolib” to compute the nearest pgEdge node

  3. Use the connection string for that node to connect to it.

pgEdge Cloud maintains a list of all the AWS, Azure and Google cloud regions with their IATA codes and approximate physical coordinates. 

Send your SQL query, or make an application specific PostgREST API call

This is the easy part, just like accessing other relational databases.

Cloudflare has an excellent blog post discussing how to build applications with PostgreSQL and Cloudflare Workers.  Basically all of this is applicable to pgEdge, subject to what you see written above.

Stay tuned for more edge integration posts!

Over the course of the pgEdge beta period we will be rolling out tighter integration with several leading edge platforms – stay tuned.  And as mentioned above, in coming weeks we will provide more detailed posts addressing each of these platforms, along with example code to get you going even more quickly.