--- title: "Three unconventional Postgres optimizations" publication: "Postgres Weekly" issue: 632 published_at: "2026-01-21" canonical_url: "https://postgresweekly.com/issues/632" --- Plus some interesting alternatives to 'soft deletion' of table rows. | #​632 — January 21, 2026 [Web Version](https://postgresweekly.com/issues/632) Together with [![Microsoft logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_280,h_60/e_make_transparent/co_white,e_outline:7/om9hjsmudy5lsvba59eb.png)](https://posetteconf.com/2026/cfp/) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/amnmicjwg1jkbgmuf3ya.jpg)](https://hakibenita.com/postgresql-unconventional-optimizations) [Unconventional Postgres Optimizations](https://hakibenita.com/postgresql-unconventional-optimizations) — Another fantastic post from Haki Benita. This time, he looks at some optimization techniques off the beaten track, including using `constraint_exclusion` to skip scans when queries contradict `CHECK` constraints, building smaller indexes by indexing lower-cardinality expressions (e.g. truncating timestamps to dates), and enforcing uniqueness with hash indexes via exclusion constraints. Haki Benita [![\](https://res.cloudinary.com/cpress/image/upload/c_limit,w_480,h_480,q_auto/copm/0370b48a.png)](https://posetteconf.com/2026/cfp/) [POSETTE 2026 — CFP Closes Feb. 1](https://posetteconf.com/2026/cfp/) — [POSETTE: An Event for Postgres](https://posetteconf.com/2026/), a free & virtual developer event organized by the Postgres team at Microsoft, is happening June 16-18. New and experienced speakers are encouraged to submit! Don’t delay—the CFP closes Feb. 1. [Get the details](https://posetteconf.com/2026/cfp/). Microsoft sponsor [The Challenges of Soft Delete](https://atlas9.dev/blog/soft-delete.html) — To delete or not to delete, that is the question! If you want to ‘delete’ data in a table without actually losing it, a ‘soft’ delete using a boolean or date flag is a common solution, but this post considers some alternatives, like using a trigger to move a row to an archive table or capturing deleted rows from the WAL for external archival. atlas9 **IN BRIEF:** - Robert Haas has put together his annual [who contributed to Postgres post for 2025.](https://rhaas.blogspot.com/2026/01/who-contributed-to-postgresql.html) Several new names appear since the 2024 list including Dmitry Koval (who implemented [ALTER TABLE .. MERGE PARTITIONS](https://www.postgresql.org/message-id/E1vUkIb-000hmq-0B%40gemulon.postgresql.org)), Sami Imseih, and Greg Burd. - The CFP for Microsoft's annual [POSETTE](https://posetteconf.com/2026) virtual Postgres conference is closing on February 1. Microsoft's Gauri Kasar [shares some tips on putting together a strong proposal.](https://techcommunity.microsoft.com/blog/adforpostgresql/postgres-speakers---posette-2026-cfp-is-closing-soon/4486111) - 🇫🇮 [Nordic PGDay 2026](https://2026.nordicpgday.org/) is taking place this March 24 in Helsinki, Finland. 🎤 ['How I Got Started with DBtune and Why We Chose Postgres'](https://talkingpostgres.com/episodes/how-i-got-started-with-dbtune-why-we-chose-postgres-with-luigi-nardi) — Luigi Nardi, founder of DBtune and a Stanford researcher, joined Claire Giordano to talk about his developer and research journey, why DBtune chose database tuning and Postgres as their focus, and the concept of ‘level five’ autonomy for database management and tuning. There's [a full transcript](https://talkingpostgres.com/episodes/how-i-got-started-with-dbtune-why-we-chose-postgres-with-luigi-nardi/transcript) if you'd rather read than listen. Talking Postgres Podcast [Scaling Vector Search to 1 Billion on PostgreSQL](https://blog.vectorchord.ai/scaling-vector-search-to-1-billion-on-postgresql) — 1 billion vectors, specifically. This is essentially a benchmark of [VectorChord 1.0](https://blog.vectorchord.ai/vectorchord-10-developer-first-vector-search-on-postgres-100x-faster-indexing-than-pgvector), an extension for high-scale vector similarity search ([GitHub repo](https://github.com/tensorchord/VectorChord)). Junyu Chen (VectorChord) ▶[What's New in PostgreSQL 18](https://www.youtube.com/watch?v=_ZObqoXQrD4) — Christophe Pettus, the CEO of [PGX](https://pgexperts.com/), gave a talk for the San Francisco Bay Area PostgreSQL Users Group recently wrapping up some of Postgres 18’s enhancements in just 30 minutes. Christophe Pettus 📄 [Postgres Serials Should be BIGINT (and How to Migrate)](https://www.crunchydata.com/blog/postgres-serials-should-be-bigint-and-how-to-migrate) Elizabeth Christensen 📄 [Understanding `ALTER TABLE` Behavior on Partitioned Tables](https://www.highgo.ca/2026/01/21/understanding-alter-table-behavior-on-partitioned-tables-in-postgresql/) Chao Li 📰 Classifieds [Speed up queries 3x with PostgreSQL 18](https://us02web.zoom.us/webinar/register/WN_0235AXQERaybzWCKYHaovQ?_gl=1*b88s8t*_gcl_au*MTkxNDQxNjcwMC4xNzY3MTE4Mjg4LjE4MTM2ODU3NTMuMTc2NzExODI5NC4xNzY3MTE4Mjk0*_ga*MjEyNzAzODgzOC4xNzU3NTA3Mjky*_ga_L8TBF28DDX*czE3Njg0OTAxNTUkbzM4JGcxJHQxNzY4NDkwNTQ0JGo1OCRsMCRoMA..#/registration). Learn AIO, Skip Scan & replication enhancements. [Register!](https://us02web.zoom.us/webinar/register/WN_0235AXQERaybzWCKYHaovQ?_gl=1*b88s8t*_gcl_au*MTkxNDQxNjcwMC4xNzY3MTE4Mjg4LjE4MTM2ODU3NTMuMTc2NzExODI5NC4xNzY3MTE4Mjk0*_ga*MjEyNzAzODgzOC4xNzU3NTA3Mjky*_ga_L8TBF28DDX*czE3Njg0OTAxNTUkbzM4JGcxJHQxNzY4NDkwNTQ0JGo1OCRsMCRoMA..#/registration) --- 🛠️ Auth0 for AI Agents provides a foundation for developers to build AI agents without compromising security or innovation. [Start building](https://auth0.com/signup?onboard_app=auth_for_aa&ocid=701KZ000000cXXxYAM_aPA4z0000008OZeGAM?utm_source=cooperpress&utm_campaign=amer_namer_usa_all_ciam_dev_dg_plg_auth0_native_cooperpress_native_aud_jan_2026_placements_utm2&utm_medium=cpc&utm_id=aNKWR000002m8zp4AA). **RELEASES AND CODE:** [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/gr2dhjbpo0fskzro2z37.jpg)](https://postgresql-shader.vercel.app/) - [SQL Shader](https://postgresql-shader.vercel.app/) _(above)_ is an interesting experiment by Matias Gonzalez that renders the output of Postgres queries with `x`, `y` and `value` columns onto a shader-style canvas. [More info here.](https://github.com/matiasngf/postgresql-shader/tree/main/pg-shader) - [credcheck 4.4](https://github.com/HexaCluster/credcheck) – Extension that performs checks and validation of credentials during user creation and renaming, as well as password changes. - [pgmoneta 0.20.0](https://github.com/pgmoneta/pgmoneta/releases/tag/0.20.0) – Postgres backup / restore tool, now with Grafana 12 support, improved S3 support, and better locking. - [pgBackRest 2.58.0](https://pgbackrest.org/release.html#2.58.0) – Another popular backup tool, now with HTTP support for S3, GCS, and Azure. [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/vwchrnzyx0prw7llibtd.jpg)](https://github.com/dalibo/pev2) - 📊 [Postgres Explain Visualizer 2 1.20](https://github.com/dalibo/pev2) – A Vue.js component for visualization of Postgres execution plans _(above)_. Now with dark mode support. - [SQLPage 0.42](https://github.com/sqlpage/SQLPage) – Rust-powered SQL-only data app builder that automatically builds a UI on top of your SQL queries. - [pgschema 1.6.1](https://github.com/pgschema/pgschema) – Terraform-style, declarative schema migration for Postgres. - [Rbatis v4.7.0](https://github.com/rbatis/rbatis) – High-performance Rust 'compile-time' ORM with dynamic SQL generation and async support. - 📊 [pgmetrics v1.19.0](https://pgmetrics.io/) – Collect and report Postgres metrics for scripting, automation and troubleshooting. **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|*).