--- title: "Locks, partitioning, and bulk loading" publication: "Postgres Weekly" issue: 517 published_at: "2023-08-02" canonical_url: "https://postgresweekly.com/issues/517" --- Plus more on the Postgres trademark dispute, and an alternative way to use JavaScript in Postgres. | #​517 — August 2, 2023 [Web Version](https://postgresweekly.com/issues/517) Together with [![pgAnalyze\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_350,h_100/e_make_transparent/co_white,e_outline:7/lhwcvgc1asd4fzaahty4.png)](https://pganalyze.com/blog/introducing-vacuum-advisor-postgres?utm_source=PostgresWeeklyPrimary08022023) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/xxvuotgyql1uiv57oukr.jpg)](https://timescale.typeform.com/state-of-pg-23/) [The State of PostgreSQL 2023 Survey is Now Open](https://timescale.typeform.com/state-of-pg-23/) — Starting in 2019, Timescale’s annual Postgres survey has become a popular way to monitor the feelings of the broader community. [Here’s last year’s results](https://www.timescale.com/state-of-postgres/2022), if you’re curious. As well as sharing the results, Timescale makes the raw (anonymized) data available for third party analysis too. Timescale 🔓 [A Way to Look at Postgres Lock Conflicts](https://postgres-locks.husseinnasser.com/) — While Postgres has two broad types of lock – shared and exclusive – there are various levels of granularity and a birds’ nest of ways in which different commands affect or conflict with each other through their locking requirements. This site/tool shows all lock types and how they interact with different commands. Hussein Nasser [![\](https://copm.s3.amazonaws.com/13be58a3.png)](https://pganalyze.com/blog/introducing-vacuum-advisor-postgres?utm_source=PostgresWeeklyPrimary08022023) [Introducing the New pganalyze VACUUM Advisor for Postgres](https://pganalyze.com/blog/introducing-vacuum-advisor-postgres?utm_source=PostgresWeeklyPrimary08022023) — pganalyze VACUUM Advisor provides per-table recommendations for autovacuum settings to optimize table bloat, freezing, VACUUM performance, and more. Implement its recommendations to optimize space usage, prevent bloat, and improve query performance. pganalyze sponsor [An Introduction to Table Partitioning](https://fragland.dev/a-guide-to-table-partitioning-with-postgresql-12) — Postgres has support for [three forms of table partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html) where tables can be divided into multiple partitions for a variety of performance, maintenance, or logistical benefits. This post gives some basic examples of the three types of partitioning. Matteo Crosta **IN BRIEF:** - Craig Kerstiens went on the _Real Python_ podcast to [▶️ discuss using Postgres with Python.](https://realpython.com/podcasts/rpp/165/) - [🐦 An interesting Twitter thread](https://twitter.com/thdxr/status/1686399711245156353) about PlanetScale, its use of MySQL, and why a Postgres-equivalent of PlanetScale is hard to bring together. The CEO of PlanetScale turned up on the thread [to note:](https://twitter.com/isamlambert/status/1686412526538588160) _"Until there is a Vitess for Postgres (a decade away at least) doing a PlanetScale for Postgres is near impossible."_ - The quickest way to start an argument in the tech world is to.. run some benchmarks! Perfect Makanju has [benchmarked Postgres running on AWS, DigitalOcean and Google Cloud](https://blog.perfects.engineering/cloud_postgres_performance_1) with rather mixed results. - Few readers will _need_ this, but it's neat to see popular developer YouTuber tackling Postgres in [▶️ PostgreSQL in 100 seconds.](https://www.youtube.com/watch?v=n2Fluyr3lbc) Maybe a video to point folks to when they're asking what you're doing at work all day ;-) - The topic of the latest _PGSQL Phriday_ blogging challenge is [to write about _partitioning vs sharding_](https://www.pgsqlphriday.com/2023/08/pgsql-phriday-011/). - If you're using Vercel Edge Functions, you can now get significantly improved performance when working with Vercel Postgres, [simply by updating your `@vercel/postgres` package.](https://vercel.com/changelog/improved-performance-for-vercel-postgres-from-edge-functions) [Comparing the Performance of Bulk Loading Techniques](https://www.cybertec-postgresql.com/en/bulk-load-performance-in-postgresql/) — A quick comparison of the basic approaches for rapidly bulk inserting data into a table, whether through a single or multiple transactions, whether using `INSERT`s or `COPY`. Unsurprisingly, `COPY` is fastest, being designed for the task, but you can use `INSERT` in a way that means it’s not _too_ far behind. Laurenz Albe [Setting the Record Straight: More Updates on a Trademark Dispute](https://www.postgresql.org/about/news/setting-the-record-straight-more-updates-on-a-trademark-dispute-2682/) — The PostgreSQL Community Association (PGCA) and _Fundación PostgreSQL_ are currently locked in a dispute over EU trademarks relating to Postgres. This is the PGCA’s take, but in the interests of balance, you can also see [what Álvaro Hernández of Fundación PostgreSQL thinks.](https://postgresql.fund/blog/postgres-trademarks-disagreement-proposing-a-solution/) PostgreSQL Community Association [A Possible Way to Implement a Shift Function in PL/PgSQL](https://fluca1978.github.io/2023/08/01/PostgreSQLShiftArray.html) — 'Shift' meaning a function that takes an array and both removes and returns the first/left-most element. Luca Ferrari [A PL/PgSQL Simple Roman Number Translator](https://fluca1978.github.io/2023/07/24/PostgreSQLRomanCalculator.html) — More PL/PgSQL shenanigans. Luca Ferrari Code and Tools [PLJS: JavaScript Language Plugin for Postgres](https://github.com/plv8/pljs) — [PLV8](https://github.com/plv8/plv8) is arguably the ‘go to’ way to use JavaScript as a procedural language within Postgres, but this [QuickJS](https://bellard.org/quickjs/quickjs.html)-based variant, from the same creator, is more compact, easier to maintain, and may be enough for your needs. Jerry Sievert [Multi-Cloud Made Easy. Deploy the Same Postgres on Any Cloud. Start with $300 in Credits](https://www.enterprisedb.com/accounts/register/biganimal?utm_source=postgresweekly&utm_medium=link&utm_campaign=GL-DS--BigAnimal-DSS) EDB BigAnimal sponsor ['Rusting' a New Postgres C Extension to Count Subtransactions](https://bdrouvot.github.io/2023/07/30/rusting-a-new-postgres-c-extension-that-records-subtransactions-counters/) — A subtransaction, or nested transaction, is a transaction that begins _within_ another transaction, and while useful, [performance implications](https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/) exist. [pg_subxact_counters](https://github.com/bdrouvot/pg_subxact_counters) is a new extension written in _both_ C and Rust to count such subtransactions, and it's also useful as a way to compare the implementation of extensions in both languages. Bertrand Drouvot [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/vfyqy4iwrbno39gcnmwo.jpg)](https://www.postgresql.org/about/news/pgadmin-4-v75-released-2681/) - [pgAdmin 4 v7.5](https://www.postgresql.org/about/news/pgadmin-4-v75-released-2681/) ↳ Popular GUI Postgres management tool. - [Prisma 5.1](https://github.com/prisma/prisma) ↳ Node.js and TypeScript ORM. - [FerretDB 1.7](https://github.com/FerretDB/FerretDB) ↳ Imagine if MongoDB ran on top of Postgres.. - [sqlc 1.20](https://github.com/sqlc-dev/sqlc) ↳ Generate type-safe code from SQL in Go. - [pgModeler 1.0.5](https://github.com/pgmodeler/pgmodeler) ↳ Open-source data modeling tool. **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|*).