---
title: "Boosting Postgres INSERT Performance by 2x With UNNEST"
published: 2024-11-15T12:00:33.000-05:00
updated: 2026-01-08T08:01:43.000-05:00
excerpt: "Read how you can double your Postgres INSERT performance using the UNNEST function."
tags: PostgreSQL, #Callout-james, performance, PostgreSQL Performance
authors: James Blackwood-Sewell
---

> **TimescaleDB is now Tiger Data.**

If you Google Postgres `INSERT` performance for long enough, you’ll find some hushed mentions of using an arcane `UNNEST` function (if you squint, it looks like a [columnar](https://www.tigerdata.com/blog/building-columnar-compression-in-a-row-oriented-database) insert) over a series of arrays to increase performance. Any performance gains sound good to me, but what's actually going on here?

I’ve been aware of this technique for a long time (in fact, several object-relational mappers use it under the hood), but I’ve never fully understood what's happening, and any analysis I’ve seen has always left me wondering if the gains were as much about data wrangling in the programming language used as Postgres speed. This week I decided to change that and do some testing myself.

💡

This used to be called "Boosting Postgres INSERT Performance by 50% with UNNEST". The performance went from 2.19s to 1.03s, which 52.97% less time, but also 113% faster.  
  
I changed the wording in this article to 2x because I think that's always clearer (thanks /u/a3kov and /u/lobster\_johnson)

## The Introduction: INSERTs in Postgres

At Tiger Data, I work with [time-series data](https://timescale.ghost.io/blog/time-series-introduction/), so I gave my analysis a time-series slant. I want to simulate inserting a stream of records into my database with the `INSERT` statement (yes, I know `COPY` is a thing, see the callout below), and in doing so, I want to minimize the load I create as much as possible (saving my precious CPU cycles for my real-time analytics queries).

💡

If you’re aiming to load data into your database as quickly and efficiently as possible, check out the PostgreSQL `COPY` command—it’s almost always faster than using regular `INSERT`. We benchmarked [Postgres data](https://www.tigerdata.com/blog/postgres-for-everything) ingestion methods in an earlier post.  
  
However, even though `COPY` is faster, many developers still prefer `INSERT` for its flexibility. `INSERT` supports useful features like upserts (`INSERT ... ON CONFLICT`), returning the inserted rows, and has better integration with language libraries. Plus, it can be part of a larger SQL query, giving you more control over the data insertion process.

  

Let’s take a closer look at the `INSERT` queries I tested using a batch size of 1,000, 5,000, and 10,000 records.

In one corner, we have the multi-record `INSERT` variant we all know and love, using a `VALUES` clause followed by a tuple per row in the batch. These queries look long but also pretty easy to understand.

```SQL
INSERT INTO sensors (sensorid, ts, value)
VALUES 
  ($1, $2, $3), 
  ($4, $5, $6), 
   ..., 
  ($2998, $2999, $3000);
```

In the other corner, we have our `UNNEST` variant, using a `SELECT` query that takes one array per column and uses the `UNNEST` function to convert them into rows at execution time.

```SQL
INSERT INTO sensors (ts, sensorid, value) 
  SELECT * 
  FROM unnest(
    $1::timestamptz[], 
    $2::text[], 
    $3::float8[]
)
```

The [Postgres documentation describes `UNNEST`](https://www.postgresql.org/docs/9.2/functions-array.html) as a function that _“expands multiple arrays (possibly of different data types) into a set of rows.”_ This actually makes sense, it’s basically flattening a series of arrays into a row set, much like the one in `INSERT .. VALUES` query.

One key difference is that where the first variant has `batch_size * num_columns` values in the query, the `UNNEST` variant only has `num_columns` arrays (each of which contains `batch_size` records when it’s flattened). This will be important later, so take note!

## The Setup 

I ran the benchmark on a single TimescaleDB 4 CPU/16 GB memory instance (the spec isn't really important for this benchmark) with a very simple schema (the same table I used on the [SkipScan performance post](https://timescale.ghost.io/blog/skip-scan-under-load/)).

```sql
CREATE TABLE sensors (
    sensorid TEXT,
    ts TIMESTAMPTZ,
    value FLOAT8
);
```

I was hoping to use [Grafana k6](https://k6.io/) for all my performance articles, but in this case, it didn’t make sense. I don’t want to measure the time that application code takes to get my data into the format an `INSERT .. VALUES` or `INSERT .. UNNEST` statement needs (especially in TypeScript), I just want the time the database spends processing the statements and loading my data.

I fell back to using good old [pgbench](https://www.postgresql.org/docs/current/pgbench.html) for these tests with a static file for each `INSERT` variant and batch combination. As usual, you can find the files in the [timescale/performance GitHub repo](https://github.com/timescale/performance).

I ran each of the following queries to insert one million records using a single thread:

-   `INSERT .. VALUES` with a batch size of 1,000
-   `INSERT .. VALUES` with a batch size of 5,000
-   `INSERT .. VALUES` with a batch size of 1,0000
-   `INSERT .. UNNEST` with a batch size of 1,000
-   `INSERT .. UNNEST` with a batch size of 5,000
-   `INSERT .. UNNEST` with a batch size of 10,000

I used the `pg_stat_statments` (if you don’t know about this amazing extension, then do yourself a favor and [look it up](https://timescale.ghost.io/blog/using-pg-stat-statements-to-optimize-queries/)!) statistics in the database to extract the `total _planning_time` and `total_exec_time` for each run.

## The Results: INSERT VALUES vs. INSERT UNNEST

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2024/11/Untitled-design--1--3-1.png)

The results were very clear: at the database layer, **`INSERT .. UNNEST` is _2_.13x faster than** `INSERT .. VALUES` at at batch size of 1000! This ratio held steady regardless of batch size (and even with multiple parallel jobs).

-   **The primary savings come at query planning time.** With the `INSERT .. VALUES` approach, Postgres must parse and plan each value individually (remember how many there were?). In contrast, `INSERT .. UNNEST` processes one array per column, which reduces the planning workload by not working with individual elements at plan time.
-   **Execution time is similar between both methods.** The actual query execution was time slightly slower for `UNNEST`, which reflects the extra work that the `UNNEST` function needs to do. This was more than made up for by the planning gain.

As you might expect adding columns makes things even better for `UNNEST`, with 10 float columns (rather than one) we get a massive 5.02x faster So if you've got a wide schema, you're in for even more performance gains (but I wanted to leave this article at what most people could reasonably expect).

If you’d like to see the graphs for the 5,000 and 10,000 batch sizes, then check out the [PopSQL](https://www.tigerdata.com/blog/best-postgresql-gui-popsql-joins-timescale) dashboard.

A reasonable response to this might be, "What if we prepared the `INSERT .. VALUES` query, would that reduce planning time and make it the winner?". Some quick tests (unfortunately, `pg_stat_statements` can't track statistics for `EXECTUTE` queries on prepared statements) show that this is not the case; `UNNEST` is still king.

## Should I use UNNEST?

There’s no question that in terms of **database performance**, `INSERT .. UNNEST` beats `INSERT .. VALUES` for batch inserts. By minimizing planning overhead, `UNNEST` unlocks an almost magical speed boost, making it a fantastic option for scenarios where ingestion speed is critical. One thing to keep in mind is that the overhead of your language and network latency often contribute just as much to the total time you see in your application, but still, your database will be working less, which is always a good thing.

As with any optimization, there’s a trade-off. The key consideration isn’t always just speed; it’s also **usability**. The `INSERT .. VALUES` syntax is intuitive and widely understood, making it easier to adopt and maintain, especially in teams or projects where SQL expertise varies. Pivoting to use `UNNEST` introduces complexity. You’ll need to wrangle your data into arrays, and if you’re using an ORM, you might discover it doesn’t support this pattern at all. If you're writing raw SQL, `UNNEST` might be less familiar to future developers inheriting your codebase.

And while `UNNEST` is fast, let’s not forget about `COPY`, which [remains the undisputed gold standard for ingestion](https://www.timescale.com/learn/testing-postgres-ingest-insert-vs-batch-insert-vs-copy). If you don’t need features like upserts (`ON CONFLICT` clauses), `COPY` will get your data in faster, and with less overhead.

## Final Thoughts for Developers

Think of `INSERT .. UNNEST` as a magic performance hack sitting squarely between traditional `INSERT .. VALUES` and `COPY`. It delivers significant speed improvements for batch ingestion while retaining the flexibility and composability of SQL `INSERT` statements.

At Tiger Data, we love exploring the edges of what Postgres can do and techniques like `INSERT .. UNNEST` remind us why. It’s elegant, fast, and underutilized, but hopefully no longer misunderstood. If you’re aiming to push your database to its limits, we highly recommend adding this pattern to your SQL toolkit. It’s another example of how understanding Postgres deeply can help you get the most out of your system. And if you want to optimize your PostgreSQL database for [time series](https://www.tigerdata.com/blog/time-series-introduction), events, real-time analytics, or vector data, [take TimescaleDB out for a spin](https://www.tigerdata.com/docs/self-hosted/latest/install).