How to speed up insertion performance in PostgreSQL

How to ⚡️ Speed Up Insertion Performance in PostgreSQL

So, you've been testing the insertion performance in PostgreSQL, and you noticed that after reaching a certain number of rows, the performance drastically declined. Not to worry! We've got some easy solutions to help you increase the insertion performance and keep your project running optimally.

But let's first understand the situation. You've mentioned that you have a table with a single column of the number data type, and there's an index on it. You filled up the database using the following query:

insert into aNumber (id) values (564),(43536),(34560) ...

At first, you were able to insert 4 million rows quickly, 10,000 at a time using the above query. However, once the database reached 6 million rows, the insertion performance took a nosedive, leading to only 1 million rows being inserted every 15 minutes.

Here's the great news! There are a few tricks you can try to improve the insertion performance:

1. Disable Indexes during Insertion

Indexes provide fast lookup and retrieval of data, but they come at the cost of slower insertions. Disabling indexes during the insertion process can significantly speed up the performance. You can temporarily disable the index using the following command:

ALTER TABLE aNumber DISABLE INDEX index_name;

Remember to replace index_name with the actual name of the index on your table. Once the insertion is complete, you can re-enable the index using the ENABLE INDEX command.

2. Increase shared_buffers

In PostgreSQL, the shared_buffers configuration parameter determines the amount of memory used for caching data. By default, it's set to a conservative value. Increasing the shared_buffers value can have a positive impact on the insertion performance. You can modify the shared_buffers value in the postgresql.conf file:

shared_buffers = 256MB

Feel free to adjust the value based on your available memory and requirements.

3. Adjust checkpoint_segments

checkpoint_segments is another configuration parameter that affects the performance during write-intensive operations. It determines how often a checkpoint occurs. Increasing the checkpoint_segments value can help in reducing disk I/O during insertions. Modify the checkpoint_segments value in the postgresql.conf file:

checkpoint_segments = 64

It's essential to find the optimal value for your specific workload. Experimenting with different values will help you identify the sweet spot.

4. Use Bulk Inserts with COPY

If you have large bulks of data to insert, consider using the COPY command instead of multiple separate INSERT statements. COPY performs much faster since it bypasses the need for transaction management and logging for every individual insert. Here's an example:

COPY aNumber (id) FROM '/path/to/data.csv' DELIMITER ',' CSV;

Make sure to replace /path/to/data.csv with the actual path to your data file.

5. Upgrade your Hardware or Optimize Resources

Lastly, consider upgrading your hardware or optimizing your existing resources. In your case, running PostgreSQL on a machine with 5 GB RAM might be a limiting factor. Increasing the RAM or using a machine with more robust specifications might provide a significant improvement in performance.

Remember to monitor your system's resource usage while testing these solutions, as they may require adjustments based on your specific environment.

Now that you're equipped with these solutions, go ahead and optimize that insertion performance!

Let us know in the comments if you found these tips helpful or if you have any other questions or suggestions. Happy coding! 👩‍💻👨‍💻💪☁️