Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit

Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit


Rate limiting is a critical aspect of web applications that ensures fair usage of resources and prevents abuse. In this blog post, we'll explore how to implement rate limiting for Supabase requests using PostgreSQL and the pg_headerkit extension. This article is part of a series on optimizing Supabase performance, and it builds upon our previous guide on Boosting Supabase Reliability.

Supabase, a powerful backend platform built on top of PostgreSQL, relies on PostgreSQL as its underlying database. By leveraging PostgreSQL and pg_headerkit, we can efficiently control the rate at which requests are made to Supabase, ensuring optimal performance and resource allocation.


Before we dive into the implementation, make sure you have the following prerequisites:

  1. Supabase Project: Ensure you have an existing Supabase project with the necessary API endpoints set up.

  2. PostgreSQL Database: Use PostgreSQL as your backend database for Supabase. If you haven't set up PostgreSQL with Supabase yet, follow the official documentation to get started.

  3. pg_headerkit: You will need to install the pg_headerkit library. Find installation instructions and more information on this library at https://database.dev/burggraf/pg_headerkit.

Setting up the Environment

Before we dive into rate limiting, let's ensure we have the necessary prerequisites in place:

  1. Supabase Account: Make sure you have a Supabase account set up.

  2. Database.dev: Install dbdev using https://database.dev/installer.

Next, let's install and set up pg_headerkit:

SELECT dbdev.install('burggraf-pg_headerkit');
CREATE EXTENSION "burggraf-pg_headerkit" VERSION '1.0.0';

Creating the Rate Limiting Infrastructure

In this section, we'll dive into the process of creating the essential infrastructure for rate limiting within your Supabase-powered application. Rate limiting is a crucial mechanism that allows you to control the number of requests made to your Supabase endpoints, ensuring fair usage of resources and maintaining system stability.

The request_log Table

We begin with the creation of the request_log table. This table serves as a main component for tracking and monitoring incoming requests. Here's how we set it up:

  ip inet NOT NULL,
  timestamp timestamptz DEFAULT NOW()

The request_log table has three essential columns:

  • id: A unique identifier for each log entry, automatically generated.

  • ip: This column captures the client's IP address, helping us identify the source of each request.

  • timestamp: It records the exact time each request was made, ensuring accurate tracking.

The register_request Function

With the request_log table in place, we proceed to create the register_request function. This function plays a pivotal role in the rate-limiting process by logging every incoming request and associating it with the client's IP address. Here's how it's defined:

CREATE OR REPLACE FUNCTION register_request(ip_in TEXT) 
LANGUAGE plpgsql AS $$
  INSERT INTO request_log (ip) 
  VALUES (inet(ip_in));

The register_request function takes the client's IP address as input and inserts a corresponding entry into the request_log table. This action ensures that we have a comprehensive record of all incoming requests, which is essential for rate limiting and analytics.

With the infrastructure for tracking requests established, we're now ready to move forward with the rate-limiting implementation. In the following sections, we'll explore how to set rate limits and enforce them effectively.

Cleaning Old Requests

To maintain the efficiency of our system, it's crucial to regularly clean up old request logs. The clean_old_requests function takes care of this task:

CREATE OR REPLACE FUNCTION clean_old_requests() 
LANGUAGE plpgsql AS $$
  -- Delete request logs older than 12 hours
  DELETE FROM request_log 
  WHERE timestamp < NOW() - INTERVAL '12 hours';

This function ensures that our database remains clutter-free and retains only the most relevant request data.

Implementing Rate Limiting

Now, let's delve into implementing rate limiting within our Supabase-powered application. Rate limiting is essential to prevent abuse and ensure fair resource allocation. We achieve this through the exceeded_rate_limit and check_rate_limit functions.

The exceeded_rate_limit Function

The exceeded_rate_limit function is responsible for checking if a client has exceeded the rate limit, which in this example is set at 5 requests per minute. Here's how it's defined:

CREATE OR REPLACE FUNCTION exceeded_rate_limit(ip_in TEXT) 
LANGUAGE plpgsql AS $$
  request_count INTEGER;
  SELECT count(*) INTO request_count 
  FROM request_log 
  WHERE ip = inet(ip_in) AND timestamp > NOW() - INTERVAL '1 minute';

  RETURN request_count >= 5; -- limit of 5 requests per minute

This function counts the number of requests made by a client within the last minute and returns true if the limit is exceeded.

The check_rate_limit Function

The check_rate_limit function is pivotal for enforcing rate limits. It effectively manages rate limiting by logging the current request using the register_request function and verifying if the rate limit has been surpassed. If the limit is exceeded, it raises an exception:

CREATE OR REPLACE FUNCTION check_rate_limit() 
LANGUAGE plpgsql 
SET search_path = public, hdr, extensions
AS $$
  current_ip TEXT := hdr.ip();
  request_method TEXT := current_setting('request.method', TRUE);
  -- Only log non-GET requests because they are run
  -- in read-only transactions
  IF request_method IS NULL OR request_method <> 'GET' THEN
    PERFORM register_request(current_ip);

  -- Check if the rate limit has been exceeded 
  -- and raise an exception if necessary
  IF exceeded_rate_limit(current_ip) THEN
    RAISE EXCEPTION 'Rate limit exceeded';

This function is a crucial component of your rate-limiting strategy, ensuring that each incoming request is correctly monitored and preventing clients from exceeding their allocated rate limits. It's important to note that this function primarily focuses on rate limiting for insert operations. While rate limiting for GET requests is possible, it may introduce performance concerns, such as making network requests that insert rate-limiting data.

Configuring pg_headerkit with PostgREST

To seamlessly integrate rate limiting with your Supabase-powered application, configure the pgrst.db_pre_request option to utilize the check_rate_limit function as a pre-request action within PostgREST:

ALTER ROLE authenticator 
SET pgrst.db_pre_request = 'check_rate_limit';
NOTIFY pgrst, 'reload config';

This configuration ensures that every request made to Supabase undergoes rate limit validation before execution, guaranteeing a fair and controlled usage of resources. Now, we can test the rate limit by sending a few post requests to a table:

Console log showing a few requests, then:{"code":"P0001","details":null,"hint":null,"message":"Rate limit exceeded"}%

Scheduled Cleanup

Maintaining the performance of your database requires periodic cleanup of old request logs. Schedule the clean_old_requests function to run automatically every midnight:

SELECT cron.schedule(
  '0 0 * * *', -- Run every midnight
  $$ SELECT clean_old_requests(); $$

This automated cleanup process is crucial for keeping your database in an optimal state, free from unnecessary clutter, and ensuring efficient resource management.


In this comprehensive blog post, we've delved into the intricacies of implementing rate limiting for your Supabase-powered applications. Leveraging the power of PostgreSQL and the versatile pg_headerkit extension, we've provided you with a step-by-step guide to ensure fair resource allocation and safeguard your application against abuse.

Rate limiting is a fundamental tool in your arsenal to maintain top-notch performance and deliver a consistently excellent user experience. Armed with the knowledge gained from this article, you're now well-prepared to seamlessly integrate rate limiting into your Supabase application.

Don't stop here; take the concepts discussed in this post and adapt them to your specific use cases. Experiment, explore, and fine-tune your rate-limiting strategy to perfectly align with your application's unique requirements.

If you found this article valuable, you might also be interested in exploring related topics:

For further information and guidance, consider these valuable references. For any questions, feedback, or assistance, please don't hesitate to reach out to me. We're here to help you on your journey to mastering rate limiting in Supabase.


For further information and guidance, consider these valuable references: