--- title: "INSERT vs COPY for batch ingestion" publication: "Postgres Weekly" issue: 579 published_at: "2024-11-27" canonical_url: "https://postgresweekly.com/issues/579" --- Plus Aurora Serverless 2 can now scale down to zero, plus Postgres folks to follow on Bluesky. | #​579 — November 27, 2024 [Web Version](https://postgresweekly.com/issues/579) Together with [![Timescale logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_1500,h_362/e_make_transparent/co_white,e_outline:30/qjz9oczunihxrp2w6fhc.png)](https://github.com/timescale/pgai) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/zwhhglr0pcibnek7umhs.jpg)](https://github.com/timescale/pgspot) [pgspot: Spot Vulnerabilities in Postgres Extension Scripts](https://github.com/timescale/pgspot) — A security analysis tool for SQL scripts that checks for vulnerabilities and best practices. Created by a Timescale engineer to prevent the reoccurance of vulnerabilities found in TimescaleDB, it’s already resulted in finding an extension-related vulnerability which [Postgres core has fixed.](https://www.postgresql.org/support/security/CVE-2022-2625/) Sven Klemm (Timescale) [Could `GROUP BY` Clause Reordering Improve Performance?](https://danolivo.substack.com/p/could-group-by-clause-reordering) — Could reordering the columns in a `GROUP BY` significantly enhance query performance? Small changes in query structure can indeed result in serious gains. Andrei Lepikhov [![\](https://res.cloudinary.com/cpress/image/upload/c_limit,w_480,h_480,q_auto/copm/1cffa55e.png)](https://github.com/timescale/pgai) [Building AI Apps on Postgres? Start with pgai](https://github.com/timescale/pgai) — pgai is a PostgreSQL extension that brings more AI workflows to PostgreSQL, like embedding creation and model completion. pgai empowers developers with AI superpowers, making it easier to build search and retrieval-augmented generation (RAG) applications. Timescale sponsor [Benchmarking Batch Ingest with `INSERT` vs `COPY`](https://www.timescale.com/blog/benchmarking-postgresql-batch-ingest/) — Two of the simplest approaches to loading data into Postgres en masse are by using `INSERT` and `COPY`. James explains each of their benefits, runs a benchmark of numerous configurations of both, and gives guidance on which approach to use in certain situations. James Blackwood-Sewell **QUICK BITS:** - Hot on the heels of the [slightly problematic](https://www.crunchydata.com/blog/a-change-to-relresultinfo-a-near-miss-with-postgres-17-1) Postgres releases we mentioned last week, [Postgres 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22](https://www.postgresql.org/about/news/postgresql-172-166-1510-1415-1318-and-1222-released-2965/) have now all been released. - 🦋 If you're a user of the Bluesky social network, Craig Kerstiens has put together [a 'starter pack' of Postgres people](https://bsky.app/starter-pack/did:plc:pqezpw5jkucrahb5pdelw5td/3l72fsuwbnu26) you can follow. - Amazon Aurora Serverless 2 now supports [scaling down to _zero_ capacity.](https://aws.amazon.com/about-aws/whats-new/2024/11/amazon-aurora-serverless-v2-scaling-zero-capacity/) Potentially a big saving for low volume or intermittently used databases. [Why Postgres Major Version Upgrades Are Hard](https://peter.eisentraut.org/blog/2024/11/26/why-postgresql-major-version-upgrades-are-hard) — Update the binaries and restart? No, no, no. Tweaks and updates to the underlying data format required a more structured approach – Peter explains why. Peter Eisentraut ▶[The PGConf.EU 2024 Lightning Talks](https://andreas.scherbaum.la/post/2024-11-19_pgconf-eu-2024-lightning-talks/) — A handy roundup of twelve brief talks given at the recent PGConf EU event, along with links to slides and recordings. Topics covered include BM25, an update on the Postgres Performance Farm, pg_duckdb, and WAL-G. Andreas 'ads' Scherbaum [`DELETE`s Are Difficult](https://notso.boringsql.com/posts/deletes-are-difficult/) — Conceptually, deleting data from a table is easy to understand. In practice, it involves a lot of work under the hood and performance issues can quickly rear their head. Radim Marek 📄 [Substituting a Variable in a SQL Script](https://fljd.in/en/2024/11/25/substituting-a-variable-in-a-sql-script/) – Such as when calling them using `psql`. Florent Jardin 📄 [Testing with Go and Postgres using Ephemeral DBs](https://michael.stapelberg.ch/posts/2024-11-19-testing-with-go-and-postgresql-ephemeral-dbs/) Michael Stapelberg 📄 [Understanding and Reducing Replication Lag](https://www.pgedge.com/blog/understanding-and-reducing-postgresql-replication-lag) Ibrar Ahmed 📄 [Talk Fast: The Speed of Various Collations](https://thebuild.com/blog/2024/11/20/talk-fast-the-speed-of-various-collations/) Christophe Pettus 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/okcq9ffd5urluy9olmbc.jpg)](https://pigsty.io/blog/releases/v3.1.0/) [Pigsty v3.1: A 'Batteries Included' Postgres Distribution](https://pigsty.io/blog/releases/v3.1.0/) — [Pigsty](https://pigsty.io/) is a packaging up of Postgres along with a variety of extensions and tools to provide a sort of RDS-like experience on your own machines. v3.1 introduces Postgres 17 as the default version and includes a way to quickly [spin up a self-hosted Supabase instance.](https://pigsty.io/blog/releases/v3.1.0/#self-hosting-supabase) Ruohang Feng 💎 [pg_query 6.0: Ruby Library to Parse, Deparse and Normalize SQL Queries with Postgres' Own Parser](https://github.com/pganalyze/pg_query) — Uses the actual Postgres server source to parse SQL queries and return their internal Postgres parse tree. v6.0 upgrades to using the Postgres 17 parser and the new features it supports (e.g. new JSON functions). pganalyze [Bytebase 3.1](https://www.bytebase.com/changelog/bytebase-3-1-0/) – Database CI/CD and ops tool for teams. [pg_partman 5.2](https://github.com/pgpartman/pg_partman/releases/tag/v5.2.0) – Partition management extension. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. _Thanks!_ --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*).