Published at Sep 2, 2024

Testing Postgres Ingest: INSERT vs. Batch INSERT vs. COPY

Written by Semab Tariq

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.

1. 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.

2. 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.

3. Permissions are then checked at both the table and column levels to ensure the user has the necessary privileges to perform the INSERT operation.

4. 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.

5. 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.

6. If there are indexes defined on the target table, Postgres may also need to insert or update index entries.

7. 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

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.

Table structure

CREATE TABLE 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, additional_tags jsonb ); Copy

The table used in the above example is sourced from the TSBS (Timescale Benchmark Suite).

Data generation

You can access the dataset download link by following the instructions in our GitHub repository.

The dataset appears as follows:

1. CSV-based datafile

2024-01-01 00:00:00+00,2539,52.31854,4.72037,124,0,221,0,25, 2024-01-01 00:00:00+00,736,72.45258,68.83761,255,0,181,0,25, 2024-01-01 00:00:00+00,3,24.5208,28.09377,428,0,304,0,25, Copy

2. Single inserts file

INSERT INTO readings VALUES ('2024-01-01 00:00:00+00', 2539, 52.31854, 4.72037, 124, 0, 221, 0, 25, NULL); INSERT INTO readings VALUES ('2024-01-01 00:00:00+00', 736, 72.45258, 68.83761, 255, 0, 181, 0, 25, NULL); INSERT INTO readings VALUES ('2024-01-01 00:00:00+00', 3, 24.5208, 28.09377, 428, 0, 304, 0, 25, NULL); Copy

3. Batch inserts file

INSERT INTO public.readings VALUES      ('2024-01-01 00:00:00+00', 2539, 52.31854, 4.72037, 124, 0, 221, 0, 25, NULL),      ('2024-01-01 00:00:00+00', 736, 72.45258, 68.83761, 255, 0, 181, 0, 25, NULL),      ('2024-01-01 00:00:00+00', 3, 24.5208, 28.09377, 428, 0, 304, 0, 25, NULL),..... Copy

Note: each batch consists of 20,000 rows.

Data insertion

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:

1. Truncated the table

2. 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

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

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

For more Postgres insert performance tips, check out this blog post.

Postgres Ingest Alternative: Nested 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. 

In our demonstration, we'll present each column as an independent array and leverage UNNEST() to assemble the rows for insertion.

Example

Input: [   [1, 2, 3],   ["brown", "black", "white"] ]

Query:

Insert into test select unnest(id::int[]),unnest(color::text[]); Copy

Output:

Data generation

You can access the dataset download link by following the instructions provided in our GitHub repository.

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 CopyThe 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.

--host=localhost --port=5432 --db-name=postgres --user=semab --do-create-db=false --use-hypertable=false --workers=4 --field-index-count=0 --time-index=false --batch-size=20000 --partition-index=false --file=data1m --pass=123 time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s Copy Summary: loaded 11672567 metrics in 3.981sec with 4 workers (mean rate 2932044.32 metrics/sec) loaded 2334285 rows in 3.981sec with 4 workers (mean rate 586351.49 rows/sec)Copy

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[] ); Copy

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 ) Copy

INSERT INTO aggregated_readings (     time,      tags_id,      latitude,      longitude,      elevation,      velocity,      heading,      grade,      fuel_consumption ) Copy

SELECT time,         tags_id,         latitude,         longitude,         elevation,         velocity,         heading,         grade,         fuel_consumption  FROM grouped_rows; Copy

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); Copy

Data output

The sample output of a single row within the staging table appears as follows.

head readings_aggregated.csv -n 3 time,tags_id,latitude,longitude,elevation,velocity,heading,grade,fuel_consumption "{""2024-01-01 00:05:30+00"",""2024-01-01 00:05:30+00"",""2024-01-01 00:05:30+00"",""2024-01-01 00:05:30+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00"",""2024-01-01 00:05:40+00""}","{131,124,129,5142,200,12,139,5288,6379,1517,5,157,8,290,148,6527,9088,7063,3,9,10,5582,8421,21,7468,9928,1788,9373,14,25,185,6800,2062,28,29,4221,7763,43,4227,3828,19,36,41,6704,5609,40,6147,44,27,18}","{48.57629,61.82099,34.22759,17.16042,77.85577,52.31222,72.44582,24.52698,18.10941,41.60719,7.76332,21.89935,35.05497,14.0466,66.68699,6.80413,51.1795,8.1376,81.92281,88.00081,36.03459,38.85692,81.88836,17.26936,45.63272,79.57096,60.35604,80.28949,67.17451,29.6149,0.77506,19.70971,89.2005,60.02579,87.53024,57.90384,63.53648,33.826,32.32573,38.45861,73.27291,17.48313,4.08247,41.63766,63.9399,38.92033,45.41427,89.03581,73.74553,39.97216}","{NULL,16.30347,45.52739,113.19143,164.72823,4.70734,68.83193,28.09852,98.68593,57.88521,14.95294,44.61717,36.21703,110.77582,105.76022,166.87621,NULL,56.58639,56.14993,134.70727,113.87054,65.86841,167.82187,16.88434,144.59638,97.87174,4.62806,146.51641,153.59455,83.74102,116.86778,139.50222,10.46947,2.49391,45.08937,77.20091,119.81365,3.91301,118.44771,171.31115,98.05876,100.80816,164.45049,110.78687,141.49945,179.92368,172.40467,91.56164,10.83553,16.00688}","{131,315,130,266,279,164,283,397,NULL,378,110,430,371,122,199,13,413,45,229,282,85,102,334,414,35,165,76,364,287,288,483,521,443,488,150,98,307,297,237,65,193,284,300,155,24,205,223,457,465,453}","{50,47,7,0,41,11,NULL,24,2,17,2,43,29,37,33,0,19,0,15,0,40,21,3,0,11,47,0,63,0,15,8,3,10,49,22,9,0,18,5,23,59,8,11,0,10,42,22,37,8,17}","{47,200,20,304,28,228,184,296,183,141,342,265,76,66,249,101,323,25,347,112,298,65,325,178,201,347,180,65,207,281,55,301,163,299,142,156,302,133,296,168,281,175,251,59,100,275,74,338,179,40}","{5,39,9,27,3,21,3,13,13,0,20,13,7,12,19,21,0,0,3,0,4,10,7,2,28,6,15,3,1,3,30,4,21,17,0,15,8,15,16,0,0,34,4,15,22,1,7,12,24,33}","{6,45.7,33.1,0,14.3,0.9,30.3,26.9,35.2,47.6,10.6,22.5,12.6,42.5,5.9,7.5,23,30.8,41.6,32.1,28.7,32.3,14.2,31.5,22.7,31.1,34,43.4,11.5,42.3,0.4,29.4,38.7,19.2,39.7,22.2,13.5,14,23,12.1,27.8,17.1,36,42.1,47.7,8.2,12.1,27.2,40.5,30.9}" Copy

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; Copy 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' ); Copy

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 ); Copy

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; Copy

Now, it's time to run the benchmark using the following command:

psql -c '\timing' -f nested-inserts.sql Copy

Results:

Notes

1. When inserting data, we need to ensure that the columns are arranged in the same sequence as the main table.

2. To work properly, the input data type must be formatted as arrays for the UNNEST() function.

3. 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.

1. 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.

2. 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.

3. 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.

4. 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.

While PostgreSQL shows remarkable baseline ingestion capabilities (~100,000 rows per second), platforms like Timescale—which is built on PostgreSQL—can coordinate multiple ingest processes, making them crucial for higher ingest demands. 

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.

Read more

• 13 Tips to Improve PostgreSQL Insert Performance

• A Guide to Scaling PostgreSQL