Anuj Upadhyay’s Post

New post! I talk with Philippe Noël about ParadeDB, the experience of building as a PostgreSQL extension, pg_duckdb, pg_lakehouse, and more...

🚀 Speed Up Compound Full-Text Searches in PostgreSQL by 300x https://lnkd.in/ebjhhFAv

I published a 2 minute read on my blog about an easy way to get query specific metrics from PostgreSQL queries into an OpenTelemetry setup: https://lnkd.in/eGVX8_iB Let me know what you think!

Andrei Lepikhov: Why PostgreSQL prefers MergeJoin to HashJoin? 🔍 Why PostgreSQL Prefers MergeJoin to HashJoin? Unraveling a Common Misconception by Andrei Lepikhov 🔍 Ever wondered why PostgreSQL opts for MergeJoin over HashJoin? It's a hot topic among devs, especially if you're using REST or ORM libraries. Andrei Lepikhov dives deep into this puzzle and busts myths! Many disable MergeJoin due to performance hiccups, but Andrei's investigation reveals the real deal: multi-clause JOINs. Take this query, for example: ```sql SELECT * FROM a JOIN b ON (a.x=b.x AND a.y=b.y AND a.z=b.z); ``` MergeJoin often wins, but HashJoin can be faster. So why the mismatch? It’s more about the optimiser's bucket-size prediction, leading to unexpected MergeJoin nods. For instance, PostgreSQL sometimes mispredicts distinct group counts, making HashJoin seem pricier. Solution? Extended statistics to the rescue! By setting `CREATE STATISTICS`, the query planner gets a clearer picture, reducing those pesky mispredictions. And never forget—proper indexing can make MergeJoin shine in analytic queries. Index those columns and watch MergeJoin soar! So, before you banish MergeJoin into oblivion, give extended statistics a shot. What do you think? Ready to geek out on more? Dive into Andrei’s full analysis on GitHub! 🎉 #PostgreSQL #DatabaseOptimization #MergeJoin #HashJoin #SQLPerformance You can find the full article here: https://postgr.es/p/6zG

My talk on Discovering #PostgreSQL: My Journey into the number 1 #OpenSource relational database. A huge thanks to #PGConfNYC and #PGConfEU, and a special thanks to Stefanie Janine Stölting, Karen Jex & Derk van Veen. I am looking forward to contributing to the Global PostgreSQL Developer Community!

Stefanie Janine: Handling BLOBs In PostgreSQL 🚀 PostgreSQL Geeks Assemble! 🚀 So, you love BLOBs, huh? Well, Stefanie Janine has unraveled the BLOBs conundrum in PostgreSQL, and it’s a byte-sized feast! 🍽️ PostgreSQL doesn’t play by the SQL standard BLOB book; instead, it offers BYTEA. But it's like fitting a square peg into a round hole – only 1 GB per peg! So, what's 1 GB? Think 3,000 five-megabyte selfies or 1,236 fully-loaded Protestant bibles. 📸📚 Beyond 1 GB, PostgreSQL suggests a Shakespearean tragedy with OIDs and file system references. Spoiler: 4 TB per file, max 32 TB overall, or enough to keep a data hoarder awake at night. 🪦 But why? More BLOBs = More headaches. Expect slow backups, inflated memory needs, and transactions that last longer than a Monday morning meeting. Not to mention, you’ll require more layers than an onion. 😂 Janine recommends storing them in your file system instead. It’s limitless and much lighter on performance. The exception? Maybe toss a few small BLOBs into your database salad, but don’t overdo it! Check out Stefanie Janine's insights and keep your databases lean. 🎩 #PostgreSQL #DataOptimization #BLOBs #DatabaseDesign Credit: Stefanie Janine 📜 You can find the full article here: https://postgr.es/p/6EN

PostgreSQL 17 has just been released! 🐘 Postgres continues to evolve impressively, adapting to our fast-changing tech landscape while maintaining its core strength. My advice to tech leaders and founders: Start with Postgres and push it to its limits. More often than not, you'll find the limitation is your own expertise, not Postgres itself. I'm glad I didn't write it off after my first disastrous encounter in 2012 (I accidentally deleted a database as a student!). Now, 12 years later, I'm most excited about the new `JSON_TABLE` feature in version 17. It elegantly bridges the gap between JSON data and strictly-typed relations, streamlining data manipulation. Read more here: https://lnkd.in/eifeR6B6

Human/version sort in PostgreSQL – revisited – select * from depesz;

✨ After a bit of a delay, I'm excited to announce that Part 4 of my blog series on PostgreSQL Internals is finally here! 🎉 In this post, I dive into 📜 The major features and history of WAL (Write-Ahead Logging) 🔍 How PostgreSQL uses WAL for crash recovery 🗂️ Understanding WAL segment files and how they work Check out the full blog post here: https://lnkd.in/d8qCH5zq