--- title: "Identity columns: a SERIAL killer?" publication: "Postgres Weekly" issue: 570 published_at: "2024-09-18" canonical_url: "https://postgresweekly.com/issues/570" --- Plus how Postgres 17's performance stacks up so far. | #​570 — September 18, 2024 [Web Version](https://postgresweekly.com/issues/570) **Due to next week's anticipated release of Postgres 17, our next issue may be delayed** till September 26 or 27 (next Thursday or Friday). So if you don't get an issue next Wednesday, no panic – we're holding out for the final release! :-) __ _Peter Cooper, your editor_ 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://events.zoom.us/ev/AjLb_w35d6V0uvJM17v2isooPfEMVuT7FpFLYnZCG-B-lUm6VHzl~Ant50PEyim1XRlgr87yCKEpmx5jC6ZLfkT9EA1KoQmePvBCtO7CFedg0rdvlbFcrfCaikasUw_czu569hCcAikv8jQ) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/gmgpd26qifvuz9s4kgro.jpg)](https://karenjex.blogspot.com/2024/09/optimising-your-database-for-analytics.html) [Optimizing Your Database for Analytics](https://karenjex.blogspot.com/2024/09/optimising-your-database-for-analytics.html) — Your database could be great for day to day operations, but when you need to run some complex analytics queries, some big decisions arise. You could replicate the data to do the analytics elsewhere in a variety of ways, or tweak Postgres to handle things better. A solid high-level look at the concepts involved. There’s [▶️ a video version, too](https://www.youtube.com/watch?v=2i3AiNt1UpI). Karen Jex [Why to Stop Using `SERIAL` in Postgres](https://www.naiyerasif.com/post/2024/09/04/stop-using-serial-in-postgres/) — `SERIAL` is a commonly used convenience for generating an automatically incrementing ID column, but it has a variety of quirks and is Postgres specific. The alternative? [Identity columns](https://www.postgresql.org/docs/17/ddl-identity-columns.html), which are both a part of the SQL:2003 standard and supported by Postgres. Naiyer Asif [![\](https://copm.s3.amazonaws.com/ae35b540.png)](https://events.zoom.us/ev/AjLb_w35d6V0uvJM17v2isooPfEMVuT7FpFLYnZCG-B-lUm6VHzl~Ant50PEyim1XRlgr87yCKEpmx5jC6ZLfkT9EA1KoQmePvBCtO7CFedg0rdvlbFcrfCaikasUw_czu569hCcAikv8jQ) [Hands on Postgres 17: What’s New & Impacting Performance](https://events.zoom.us/ev/AjLb_w35d6V0uvJM17v2isooPfEMVuT7FpFLYnZCG-B-lUm6VHzl~Ant50PEyim1XRlgr87yCKEpmx5jC6ZLfkT9EA1KoQmePvBCtO7CFedg0rdvlbFcrfCaikasUw_czu569hCcAikv8jQ) — Join us for an exclusive webinar on the Postgres 17 release. Explore new enhancements—faster B-tree scans, adaptive vacuum strategies, and streaming I/O updates—and their impact on database performance & monitoring. Register today! pganalyze sponsor [Postgres 17 RC1 vs `sysbench` on Small and Large Servers](https://smalldatum.blogspot.com/2024/09/postgres-17rc1-vs-sysbench-on-small.html) — If, like us, you’re anxiously awaiting next week’s Postgres 17 release, you might be curious at how it’ll stack up performance wise. Mark ran some benchmarks on 15.8, 16.4, 17 beta 3 and 17 RC1, and while there’s some detail to peck through, the summary is _“17rc1 looks great - there are no big regressions and several big improvements.”_ Mark Callaghan **QUICK BITS:** - 🗣️ [Andrew Atkinson](https://postgresql.life/post/andrew_atkinson/) is this week's _PostgreSQL Person of the Week_ interviewee. Andrew is perhaps most famous for his [High Performance PostgreSQL for Rails](https://andyatkinson.com/pgrailsbook) book, and this is probably the most detailed and thorough interview of the series so far. - David Wheeler [shares what's new with the PGXN v2 project](https://tembo.io/blog/pgxn-v2-update), a bold effort to rethink the ecosystem behind, and distribution of, Postgres extensions. - Stefan Fercot checks in [with an update on last week's PGDay UK 2024](https://dataegret.com/2024/09/back-from-pgday-uk-2024/) and what happened there. [Implementing Hybrid Search with Postgres and `pgvector`](https://jkatz05.com/post/postgres/hybrid-search-postgres-pgvector/) — Jonathan sets out to show us how to use pgvector in implementing hybrid search systems, but notes that he doesn’t yet want to answer whether you _should_ – that’s a topic for a future post. Jonathan Katz [Window Functions for Data Analysis with Postgres](https://www.crunchydata.com/blog/window-functions-for-data-analysis-with-postgres) — Window functions come in handy when you want to group or compare between rows you’ve already retrieved in a query – for instance, to calculate running totals, perform rankings, or work out rolling averages. They essentially provide _higher order_ querying. Elizabeth Christensen (Crunchy Data) 📰 Classifieds Network with thousands of data professionals at [PASS Data Community Summit in Seattle](https://passdatacommunitysummit.com/sessions/?utm_source=postgresweekly&utm_medium=email&utm_campaign=es-202411-pass-summit-pg-weekly-ad-sept-em-all%20). Browse the schedule to discover over 170 sessions. --- [Dagster+](https://dagster.io/lp/dagster-plus-trial?utm_source=postgres_weekly&utm_medium=email&utm_campaign=dagster_free_trial) makes it easier to ingest & replicate data from Postgres with Embedded ELT. Start building now with your [Dagster+ free trial](https://dagster.io/lp/dagster-plus-trial?utm_source=postgres_weekly&utm_medium=email&utm_campaign=dagster_free_trial). --- 🐘 Are you one of those devs without an opinion on PostgreSQL? Right. Tell us what you really think in the [2024 PostgreSQL Survey!](https://form.typeform.com/to/wtUFIZwA) 📄 [Intel Demonstrates Up to 48% Improvement for AVX-512 Optimized Postgres](https://www.phoronix.com/news/Intel-Bit-Count-AVX-512-Postgre) Phoronix 📄 [In-Database AI Agents: Teaching Claude to Use Tools with Pgai](https://www.timescale.com/blog/in-database-ai-agents-teaching-claude-to-use-tools-with-pgai/) Haziqa Sajid (Timescale) 📄 [Migrating from Temporal to a Postgres-Based Task Orchestrator](https://www.nango.dev/blog/migrating-from-temporal-to-a-postgres-based-task-orchestrator) Robin Guldener (Nango) 📄 [Optimizing Postgres with Composite and Partial Indexes](https://stormatics.tech/blogs/optimizing-postgresql-with-composite-and-partial-indexes) Semab Tariq 📄 [Postgres Materialized Views from Parquet in S3 with Zero ETL](https://www.crunchydata.com/blog/postgres-materialized-views-from-parquet-in-s3-with-zero-etl) – With _Crunchy Bridge for Analytics_, specifically. Marco Slot (Crunchy Data) 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/c4wakjbajwvmy8owq9yc.jpg)](https://github.com/achristmascarl/rainfrog) 🐸 [Rainfrog: A New Database Management TUI for Postgres](https://github.com/achristmascarl/rainfrog) — We love `psql` as much as anyone, but are always keen to see new takes, and this Rust-powered terminal UI is an elegant one. You get a query editor with highlighting, Vim-esque shortcuts, and can quickly switch between schemas. Carl Liu 📱 [Selectable: An In-Development Android Postgres Client](https://getselectable.com/) — A new freemium Android app for interacting with Postgres that’s admittedly still in its ‘early stages’ but available now on the Google Play Store to experiment with. Selectable Software - 🌐 [PostGIS 3.5.0 Beta 1](https://postgis.net/2024/09/PostGIS-3.5.0beta1/) – The latest version of the popular geospatial extension gets ready for Postgres 17. We expect a final release soon after Postgres 17. - [E-Maj 4.5.0](https://www.postgresql.org/about/news/announcing-e-maj-450-2930/) – Fine-grained write logging and time travel on subsets of a database. - [Pongo 0.14.4](https://github.com/event-driven-io/Pongo) – Postgres driver for Node.js that presents as a MongoDB-style API. - [Good Job 4.3](https://github.com/bensheldon/good_job) – Postgres-backed job queue for Ruby on Rails. - [pgmoneta 0.14](https://www.postgresql.org/about/news/pgmoneta-014-2932/) – Backup / restore 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|*).