pg_graphql: A GraphQL extension for PostgreSQL

Summary

Supabase has open-sourced pg_graphql, a native PostgreSQL extension written in C and SQL that enables in-database GraphQL schema reflection and query resolution. Because free-tier virtual machines lacked memory headroom for standalone GraphQL processes like Hasura or Graphile, the team designed an in-engine solution to eliminate extra runtime overhead. The extension parses, validates, and transpiles incoming GraphQL operations into single SQL statements that aggregate data into JSON responses. This architecture avoids N+1 query problems, enforces existing row-level security policies, and maps GraphQL requests directly onto PostgreSQL transactions. The public SQL function can be queried directly or exposed over HTTP via PostgREST remote procedure calls.

Context

Supabase free-tier projects run on virtual machines with 1 GB of memory, which left no remaining memory budget after allocating resources for PostgreSQL, PostgREST, Kong, GoTrue, and other services. This memory constraint disqualified existing standalone GraphQL solutions like Graphile and Hasura because they required launching separate processes.

Approach / What changed

Supabase developed and open-sourced pg_graphql, a native PostgreSQL extension written in C and SQL that parses, validates, and transpiles GraphQL queries into single SQL statements directly inside the database. The engine returns aggregated JSON documents via a public gql.resolve SQL function, allowing it to be called directly or exposed over HTTP through PostgREST RPC while leveraging PostgreSQL features like row-level security, prepared statements, and transaction management.

Takeaways

• pg_graphql translates each GraphQL query into a single SQL statement that returns aggregated JSON, avoiding N+1 query overhead.
• Running GraphQL inside PostgreSQL enables direct integration with native database capabilities, including row-level security, prepared statement caching, and transaction rollbacks.
• Relay connections pagination is automatically translated to SQL keyset pagination to maintain consistent page retrieval times.

Related reading