Using Temporary Tables in PostgreSQL with Rails

How we use Temporary Tables in PostgreSQL with Rails. Here at Honeybadger, we have a lot of data, which presents us with a few problems. One of the biggest challenges is removing old data that nobody uses any more, while keeping the good stuff.

Here at Honeybadger, we have a lot of data, which presents us with a few problems. One of the biggest challenges is data culling. Removing old data that nobody uses any more, while keeping the good stuff.

Complicating matters is the fact that our data is structured hierarchically. There are Parents and Children. If we're to maintain data integrity, we have to delete the children before we delete the parent. And the children have their own associations that need to be cleaned up.

If we had a smaller dataset, we could do something like this:

parents.each do |parent|
  parent.children.each do |child|

Or, heck. Maybe even:


But this approach would take forever due to the size of our dataset. The last thing we want to do is create a model instance for every row we're deleting.

Instead, we're going to do things the hard way, using the magic of temporary tables. We'll use the temporary table as a "scratch pad" so all the data stays in postgres.

def delete(ids)
  ActiveRecord::Base.transaction do

    conn = ActiveRecord::Base.connection

    # Delete all children for this parent, then put their ids in a temporary table
    sql_to_delete_children = %[
      with deleted as (
        delete from errors where parent_id in (#{ids}) returning id
      select id into temp child_deletions from deleted 


    # Pop ids from the temporary table, 100 at a time
    sql_to_pop_ids = %[
      with deleted as (
        delete from child_deletions 
        where id in (select id from child_deletions order by id limit 100) 
        returning id
      select id from deleted

    while (children = conn.select_values(sql_to_pop_ids)).any? do

      # Delete any associations belonging to the children

      %w(tickets ratings facets).each do |t|
        conn.execute("delete from #{t} where error_id in (#{children.join(',')})")


    conn.execute("drop table child_deletions")

What to do next:
  1. Try Honeybadger for FREE
    Honeybadger helps you find and fix errors before your users can even report them. Get set up in minutes and check monitoring off your to-do list.
    Start free trial
    Easy 5-minute setup — No credit card required
  2. Get the Honeybadger newsletter
    Each month we share news, best practices, and stories from the DevOps & monitoring community—exclusively for developers like you.
    author photo

    Starr Horne

    Starr Horne is a Rubyist and Chief JavaScripter at When she's not neck-deep in other people's bugs, she enjoys making furniture with traditional hand-tools, reading history and brewing beer in her garage in Seattle.

    More articles by Starr Horne
    Stop wasting time manually checking logs for errors!

    Try the only application health monitoring tool that allows you to track application errors, uptime, and cron jobs in one simple platform.

    • Know when critical errors occur, and which customers are affected.
    • Respond instantly when your systems go down.
    • Improve the health of your systems over time.
    • Fix problems before your customers can report them!

    As developers ourselves, we hated wasting time tracking down errors—so we built the system we always wanted.

    Honeybadger tracks everything you need and nothing you don't, creating one simple solution to keep your application running and error free so you can do what you do best—release new code. Try it free and see for yourself.

    Start free trial
    Simple 5-minute setup — No credit card required

    Learn more

    "We've looked at a lot of error management systems. Honeybadger is head and shoulders above the rest and somehow gets better with every new release."
    — Michael Smith, Cofounder & CTO of YvesBlue

    Honeybadger is trusted by top companies like: