There are various methods for ingesting data into your Postgres database. Each one has different advantages and performance considerations, which leads us to the question: which one should you choose?
This blog post benchmarks different data ingestion methods in Postgres, including single inserts, batched inserts, and direct COPY from files. Additionally, we will explore best practices to optimize bulk data loading performance and examine an alternative: nested inserts.
But before we start testing, let’s understand how inserts work in Postgres.
Postgres Insert Workflow
When you send an INSERT command to the Postgres server through the established database connection, the server processes it in several steps.
First, upon receiving the INSERT query, Postgres analyzes its structure and meaning. This analysis involves breaking down the query into its components, such as the INSERT keyword, the table name, column names, and the values intended for insertion.
Next, Postgres checks if the target table and specified columns actually exist. This step ensures that the INSERT operation can proceed without errors related to missing tables or columns.
Permissions are then checked at both the table and column levels to ensure the user has the necessary privileges to perform the INSERT operation.
Following this, Postgres validates the data types of the inserted values, comparing them against the data types of the target columns. This validation is essential for maintaining data integrity and preventing errors caused by type mismatches or constraint violations.
Once all validation checks are completed, Postgres inserts the row into the target table. This insertion involves writing the new row to the appropriate data blocks within shared buffers and updating internal data structures accordingly.
If there are indexes defined on the target table, Postgres may also need to insert or update index entries.
The INSERT operation typically occurs within the context of a transaction. Postgres ensures that the changes made by the INSERT statement are either committed or rolled back in case of any error, maintaining transactional consistency.
Comparison Between Inserts, Batched Inserts, and COPY
Feature
COPY
Inserts
Batched Inserts
Description
Move data between Postgres tables and thestandard file system. COPY TO is used for writing files, and COPY FROM is used to read files and insert them into a table. COPY also supports writing to standard out and other commands.
Single row insertion into tables.
Bulk insertion of multiple rows in a single statement.
Performance
Very fast for bulk data operations such as reading a CSV file.
Slower for large volumes of data.
Faster than individual insert but slower than COPY.
Network Overhead
Reduced due to bulk transfer of data within a single transaction. If the server is remote, COPY isn't affected much by network latency as it streams rows as fast as the DB can write and the network can transfer them.
Increased due to multiple network round trips for each insert command.
Reduced network usage as compared to individual inserts.
Schema / Constraint / Permission /Validation
One-time data validation at the start of data loading.
Validation is done for each individual insert command.
Batch inserts involve validating data once for a group of records rather than individually for each record in single inserts.
Memory Usage
The COPY operation divides data into batches, effectively minimizing the memory footprint. In handling large tables, Postgres uses a ring buffer rather than the shared_buffer pool for reading or writing operations, making it less memory intensive.
Inserts consume more memory resources. Each command goes through parsing and planning on the server. This entire operation, including the parsed statement itself, is stored in shared_buffers memory.
Memory footprint is typically lower compared to single inserts.
Benchmarking Postgres COPY, INSERT, and Batch Insert
Hardware information
We set up an account on Timescale Cloud (you can try it for free for 30 days) and configured an instance with the following specifications:
CPU: 8
Memory: 32 GB
Additionally, we established another EC2 instance (Ubuntu) within the same region dedicated to data generation and loading.
Note: the Postgres instance in Timescale Cloud is automatically optimized according to the instance's hardware specifications.
After creating these files, we executed the psql command to load them. It's important to note that before each load run, we performed two steps:
Truncated the table
Restarted the Timescale service to release occupied memory
We conducted tests for 1 million, 25 million, 50 million, 75 million, and 100 million rows, and the results are as follows:
Results
Mode
Total Time (Seconds)
Rows
1 Million
25 Million
50 Million
75 Million
100 Million
COPY
4.306
73.06
165.75
232.49
316.06
Batch Inserts
32.487
566.43
1207.62
1796.93
2653.12
Single Inserts
1067
23964
47976
72591
94623
Here are some graphs illustrating how each method scales with growing datasets.
The execution time for single inserts increases rapidly as the number of rows increases.
The graph above shows the trend for batched inserts, each containing 20,000 rows. We also experimented with inserts using various batch sizes—the resulting graph is depicted below.
Here, we can see that insertions perform optimally with batch sizes of 20,000 and 40,000, beyond which the time taken increases. Possible factors contributing to this include hardware considerations (memory, I/O) and network bandwidth limitations.
The copy operation displays a linear growth trend and is faster than other insertion methods.
Comparison for COPY vs. batched inserts (batch size = 20,000 rows)
When comparing all three insertion methods, individual inserts significantly skew the overall graph, rendering them inefficient and time-consuming. In contrast, COPY operations require less time as they enable rapid insertion of large data volumes by directly reading from a file or stream, bypassing much of the overhead linked with individual insert statements.
Best Practices for COPY Inserts
The max_wal_size and checkpoint_timeout parameters control the maximum size of WAL files before a checkpoint occurs. Increasing their value can reduce the frequency of checkpoints and thereby increase data loading speeds.
Unlogged tables are not WAL-logged, which makes them faster for write-intensive operations. However, they are not crash-safe and should only be used for transient or temporary data where durability is not a concern.
When performing truncate and load operations on a table, it's recommended to create indexes, constraints, triggers, and foreign keys after the data load operation. This minimizes overhead during the initial bulk load process and speeds up data ingestion.
Consider timescaledb-parallel-copy to improve load speed by parallelizing copy operation, especially when using Timescale. It distributes data across multiple connections, maximizing throughput during bulk data import.
After performing a bulk load operation, it's essential to run the ANALYZE command on the table to update statistics. While this step is not directly related to data loading, it is essential for the Postgres planner to make informed decisions about query execution plans.
Instead of inserting rows individually, consider using batched inserts for improved performance.
Note: the COPY command does not directly support the UPSERT functionality in performing updates or inserts.
Another approach for bulk insertion involves utilizing nested inserts via the UNNEST() function in Postgres. You can use this function to expand an array into individual rows, transforming it into a tabular format where each array element corresponds to a distinct row. The UNNEST() function is particularly useful when managing array data in Postgres.
The first step is to generate the necessary data set using the Time Series Benchmark Suite Time Series Benchmark Suite.
We assume you have already installed the TSBS utilities. To generate a dataset of 1 million rows, you can run the following query.
tsbs_generate_data --use-case="iot" --seed=123 --scale=150 --timestamp-start="2024-01-01T00:00:00Z" --timestamp-end="2024-01-02T00:00:00Z" --log-interval="10s" --format="timescaledb" > data1m
The command above will generate data in CSV format. To demonstrate nested inserts, we first need to convert this data from CSV to column arrays and then back to CSV. To create the column arrays, we must first insert the data into the database using the following steps.
After running the tsbs_load_timescaledb command, it will create three PostgreSQL tables and ingest data into each accordingly.
For this test, we only need the readings table, so we'll start by converting its data to column arrays. To do this, we'll use an intermediate PostgreSQL table and leverage a Common Table Expression (CTE) for the conversion.
CREATE TABLE aggregated_readings (
time TIMESTAMP WITH TIME ZONE[],
tags_id INTEGER[],
latitude DOUBLE PRECISION[],
longitude DOUBLE PRECISION[],
elevation DOUBLE PRECISION[],
velocity DOUBLE PRECISION[],
heading DOUBLE PRECISION[],
grade DOUBLE PRECISION[],
fuel_consumption DOUBLE PRECISION[]
);
WITH numbered_rows AS (
SELECT *,
row_number() OVER (ORDER BY time) AS rn
FROM readings
), grouped_rows AS (
SELECT CEIL(rn / 50.0) AS batch,
array_agg(time) AS time,
array_agg(tags_id) AS tags_id,
array_agg(latitude) AS latitude,
array_agg(longitude) AS longitude,
array_agg(elevation) AS elevation,
array_agg(velocity) AS velocity,
array_agg(heading) AS heading,
array_agg(grade) AS grade,
array_agg(fuel_consumption) AS fuel_consumption
FROM numbered_rows
GROUP BY batch
)
INSERT INTO aggregated_readings (
time,
tags_id,
latitude,
longitude,
elevation,
velocity,
heading,
grade,
fuel_consumption
)
SELECT time,
tags_id,
latitude,
longitude,
elevation,
velocity,
heading,
grade,
fuel_consumption
FROM grouped_rows;
This query first numbers rows from the readings table and then groups these rows into batches of 50. It aggregates the data into arrays for each batch and inserts these aggregated values into the aggregated_readings table.
Now that we have the dataset in column arrays, we can proceed to save it in a file on the filesystem.
Note: we are now saving the newly generated column arrays as a CSV file.
COPY aggregated_readings TO '<FILESYSTEM_LOCATION>/readings_aggregated.csv' WITH (FORMAT CSV, HEADER);
Data output
The sample output of a single row within the staging table appears as follows.
Note that each array consists of 50 data points. This means that when we use the UNNEST() function, a single row will populate 50 rows.
Now that our dataset is ready, proceed to create the file_fdw extension. If you're not familiar with file_fdw, it's a PostgreSQL foreign data wrapper that lets you access and query data stored in external files, such as CSV or text files, just like regular database tables. You can learn more about file_fdw in the PostgreSQL docs.
CREATE EXTENSION file_fdw;
After creating the file_fdw extension, it's time to create a foreign table.
CREATE FOREIGN TABLE readings_aggregated_foreign_table (
time timestamp with time zone[],
tags_id integer[],
latitude double precision[],
longitude double precision[],
elevation double precision[],
velocity double precision[],
heading double precision[],
grade double precision[],
fuel_consumption double precision[]
)
SERVER file_server
OPTIONS (
filename '<FILESYSTEM_LOCATION>/readings_aggregated.csv',
format 'csv',
header 'true'
);
Create the final table where we will ingest data for the nested inserts benchmark.
CREATE TABLE normalized_readings (
time timestamp with time zone,
tags_id integer,
latitude double precision,
longitude double precision,
elevation double precision,
velocity double precision,
heading double precision,
grade double precision,
fuel_consumption double precision
);
Create a SQL file on the filesystem and add the insert query to it.
INSERT INTO normalized_readings (
time,
tags_id,
latitude,
longitude,
elevation,
velocity,
heading,
grade,
fuel_consumption
)
SELECT
unnest(time) AS time,
unnest(tags_id) AS tags_id,
unnest(latitude) AS latitude,
unnest(longitude) AS longitude,
unnest(elevation) AS elevation,
unnest(velocity) AS velocity,
unnest(heading) AS heading,
unnest(grade) AS grade,
unnest(fuel_consumption) AS fuel_consumption
FROM
readings_aggregated_foreign_table;
Now, it's time to run the benchmark using the following command:
psql -c '\timing' -f nested-inserts.sql
Results:
Mode
Total Time (Seconds)
Rows
1 Million
25 Million
50 Million
75 Million
100 Million
Nested Inserts
4
128
256
393
533
Notes
When inserting data, we need to ensure that the columns are arranged in the same sequence as the main table.
To work properly, the input data type must be formatted as arrays for the UNNEST() function.
The insertion time doesn't incorporate network round trips since the inserts and processing occur directly within the database.
Conclusion
Our benchmarking tests of PostgreSQL data insertion methods reveal significant differences in performance, making the choice of method critical based on the specific use case and data volume.
Single inserts: This method is straightforward but becomes impractically slow as the data volume increases. It's best suited for scenarios where data is inserted infrequently or in small amounts.
Batched inserts: This approach strikes a balance between simplicity and performance. Compared to single inserts, it significantly reduces network overhead and transaction costs. However, it still falls short of the performance offered by the COPY method for very large datasets.
COPY: The COPY method is the fastest for bulk data operations. Its efficiency in handling large datasets makes it the preferred choice for initial data loads or when importing data from external sources. The linear growth in execution time with increasing data volumes demonstrates its scalability.
Nested inserts with UNNEST(): This method offers a different approach to bulk data insertion, leveraging array operations to minimize overhead. It shows promising performance, especially for structured data that can be naturally represented in arrays.
Combining all your favorite things about PostgreSQL (unparalleled reliability, full SQL support, and a broad ecosystem of tools and connectors) with features that engineer it for higher performance, efficiency, and cost savings (think columnar compression, automatic partitioning, and always up-to-date materialized views), Timescale makes PostgreSQL powerful—so you can use it for everything. Create a free Timescale account and try it today.
Scale PostgreSQL via Partitioning: A Dev’s Intro to Hypertables
Boosting Postgres INSERT Performance by 2x With UNNEST
