Pg conf India 2025 

~ SqlAsylum ~ 1 Comment

Listed below is my summary for the sessions I attended at PG Conf India 2025.   I really appreciate the organizers, volunteers and sponsors that made this conference a success.  It was great to see friends and to meet new friends at the event.  I can’t say whether I’ll be able to attend next year but I hope so!   

Along with these sessions I had many good hallway track discussions.  Mostly around the things I was speaking on and helping others to network and meet people in the community. I was happy that many of the people in the community in India knew each other well. It’s good to see such a vibrant community of people!  

Conference Site:  https://pgconf.in/conferences/pgconfin2025

Full Schedule: https://pgconf.in/conferences/pgconfin2025/schedule/events

Keynote,  Peter Zaitsev from Percona 

Discussed Open Source and how far it’s come over the years.  

Key Takeaways 

  • Different Perspectives
  • Open Source is not enough for the users, Define your needs to better meet demand. 
  • Consider Vendor lock-in and what it means to you and your company. 
  • Moving Data is Expensive
    • Agree!
  • Best Technologies don’t always win.
    • Agreed! 

Understanding WAL Logging 

Youtube link: https://youtu.be/0EwUqQawwUU?si=x-HohmeNzACVO6Un

By Vigneshwaran C 

This was a good overall view of the Wal logging process.  I know this process well so I could easily follow along with the session.  

Key Takeaways

  • The background process picks up the buffer and flushes it to disk 
  • Checkpoint writes from the buffer to disk, flushes the buffer, and advances the position. 
  • Crashes uses the pg_ctrl file to redo the timeline and restore from a specific point. 
  • Good Questions in the Session 

Hacking PG Executor 

Youtube link: https://youtu.be/ywK08yHS2Mk?si=aJEEj4jok_uvthOa

By Amit Langote 

This was a good overview of how PG looked at the execution of queries. Breaking down how each of the different sections of the executor worked.  I liked the deep dive into the executor, I did feel it needed better example queries from real world examples.  The presenter suggested what is going to slow down performance, but didn’t relate it directly to real-world queries. He mentioned ways to fix it, but it was very low-level and needed something more to relate to everyday queries.  

Key Takeaways 

  • Cache misses stall executions,  Avoiding this is important. 
  • Branch Misprediction causes slowdowns, unpredictable branches. 
  • Reduce the mispredictions when you can 

Experimental approach towards Replication in Postgres 

Youtube link: https://youtu.be/rSB5-mWwar4?si=-V-90SkiS8Z8KOzW

By Balaji Venkat

I enjoyed the experimental nature of this presentation.  It brought up some great ideas of where PG should go with replication and how to make it much better in the future.  This was an Academic idea that the team is working on but it’s most likely a few years in the future before we will see something like this. 

Key TakeAways 

  • Multicast for Replication.  
  • Multicast can send once instead of many times. 
  • Multicast is less reliable but steps can be taken to handle the use cases for this. 
  • Full mesh is something possible for replication in the future with this idea.  

PostgreSQL 17’s Login Event Triggers: A Developer’s Toolkit for Enhanced Security

YouTube link: https://youtu.be/ml2H-Y5itXE?si=DY3pSA60lH4-wzaN

By Chethan G

This session talks about the new login event trigger for PG 17.  This might be the most dangerous new feature. The presenters did a good job of warning that misconfiguration and setup will prevent all logins to the server. So use cautiously. I can see some use cases for this related to audit but personally, the danger for this one is very, very high, and I would avoid it for most use cases. 

Key TakeAways

  • Be very careful using this. 
  • Fires when a user connects and runs whatever script you want. 
  • Per db setting, but enabled via cluster level.  
  • Fires after auth but before init 
  • To recover, you would need to use Single User Mode or keep a session active. 
  • Block on IP is a use case here, but I would suggest that you should do something outside the DB with a better tool designed for this.

PostgreSQL: Page Buffer Overview

YouTube link: https://youtu.be/2afOmYfqu-8?si=tpXZpN3l8wJqpCMp

By Harish Perumal 

This was a good session and an overview of the Page Buffer, talking about the structure inside it and how it’s used.  I enjoyed the session and would suggest watching the recording to get some more insights.  

Key Takeaways: 

  • Double buffer style,  PG Stores in the OS, and in PG Cache 
  • Portability across OS is important 
  • Review Effective Cache Size 

Lightning Talks 

I can’t list all the talks, but I did enjoy the talks.  

Key Takeaways: 

  • V18 PG is working on new plans with partitioning(exciting!)
  • Wordle with Pavlo is always interesting! 
  • Pgextensions.org: Good way to find out what extensions work with what cloud providers. 

Securing PostgreSQL From External Attack

YouTube: https://youtu.be/BRCh6PPVkKY?si=_UmAxKWZvcHq0GLQ

By Bruce Momjian 

Presentations by Bruce are always insightful and you are going to learn something. I was presenting right after Bruce so we worked together to catch up on time since the event was running behind. I appreciated him giving me enough time for my presentation.  I highly suggest watching this youtube since it’s got great overall information on security and the things you should be concerned with. 

Key takeaways: 

  • Understand where you stand right now and what holes you might have.  The review is good. 
  • Migrate to Scram256 for passwords, better, more solid protection. 
  • Need more SSL usage in the system
  • Move to VerifyCA for connections.  

Speaking and Community Involvement for the Introvert

YouTube: https://youtu.be/LgoZsH4Ifgg?si=ntsXrwCbk4VHb87z

By Pat Wright 

This was my first time speaking internationally.  I’ve done this presentation several times, but haven’t done it outside the US.  I did make some last-minute adjustments to try and make sure I related to my audience. The presentation is very much about community and how to present and speak in your community, so I wanted to make sure the stories fit the crowd.   If you are interested in improving your career and helping out in the community, you should check out the recording.  

Optimising Column Order for Better Performance and Maintainability

YouTube: https://youtu.be/JI7EgpV-BUI?si=GSEqzjPdTWwG7sfR

By Amul Sul 

This was an interesting session that focused on column order in tables and how data types can impact performance.  I know the data type discussion from experience with other RDBMS,  I fully agree you should right-size your data types and it will make a difference in performance in the long run.  From a production standpoint, controlling column order is a lot harder and more complex process for a fast-paced production environment. This also depends on how many engineering teams you have and how shared the tables/structures are around the team.  In a monolith with 30+ products and teams, this becomes a complicated thing to deal with. 

Key takeaways: 

  • Data types matter, and you should right-size them as much as possible 

Things your explain plan is not telling you 

Youtube: https://youtu.be/qTJxWa3_SY0?si=XvsFw2gsFKTINkm0

By Ants Aasma 

This was a good overview of what the explain plan tells you and doesn’t tell you.  I enjoyed seeing all the things that were missing and what could be improved upon in the future.   

Key takeaways: 

  • Avoid long queries, update heavy workloads. 
  • Missing – how many rows were searched but not visible.  
  • Killed tuples skipped 
  • Unexpected downsides of uuid keys in postgresql

cbo_stat_dump : Reproduce those complex query planner issues without having to replicate customer data

Youtube: https://youtu.be/APLlONVqZn4?si=LL1zicryym4B9000

By Gaurav Kukreja 

Good presentation about the Cost based optimizer and how it figures out what plan to choose for the system.  This presentation specifically talked about the cbo_stat_dump script that pulls the stats out of one system and applies them to another so that you can test changes and plans in a Dev environment.  I liked the overview of the tool and it looks like it has some good use cases.  It felt like it had some constraints that would keep it from use in my system right now but it’s good to know something like this is available. 

Key takeaways: 

  • You can’t Improve what you can’t measure.  Good Message
  • Extended stats don’t work in this script. 
  • Not great for Regression testing 
  • Used Pg_dump for ddl, does not export create stats command for table. 

Failover Slots in PostgreSQL-17: Ensuring High Availability with Logical Replication

Youtube: https://youtu.be/0ayIewCXwmM?si=DVksrFxQa2JcWIMF

By Nisha Moond

New feature in Pg17, logical replication slots will survive a publisher node failover.  Focuses on fetching and synchronizing the information about logical replication so that it can essentially failover and start the logical replication back up on the new node. We have logical replication in place so it’s important to us moving forward.  

Key takeaways: 

  • Manual versions and Automatic Versions.  
  • Automatic version has parameters to tell how in “sync” it should be. 
  • You can only have auto or manual not both. 
  • Only logical replication slots not physical replication slots 
  • No support for cascading standby sync 

Event Organizing and Meetup Guide

~ SqlAsylum ~ Leave a comment

Greetings!  

I’ve been organizing and putting on events since about 2006.  I first started with a simple event called Utah Code Camp.  That grew from a first-time 50-person event to over 800+ people in 2018.  Takes a while to grow a community!  

I was inspired at my first PASS summit back in 2004 to put on events and help build community. I will be presenting at the PASS summit hoping to inspire others!  Right now It will be presented at the PASS Summit on Nov 6th(Wednesday) 2-3 pm Room 337-339.  I will be presenting this again really soon to a user group or meetup and then I’ll record the presentation and post it on YouTube for all to see.  

I intend some more follow-up blog posts as well breaking this down into smaller sections.  For now, you can find the documents/guides on putting on events on this Page.  

I also HIGHLY recommend checking out the other links on that page.  Jeff and Daniel have put together amazing resources as well! 

If you need assistance putting on an event or help getting started let me know! I would love to schedule a quick call and help you out.  You can reach out through my Calendar at this link. 

Hopefully, I will see you at the PASS Summit next week! 

PG NYC 2024 

~ SqlAsylum ~ 1 Comment

I recently attended the Postgresql NYC 2024 conference. I should be clear that I presented at this event and helped organize/plan it. Any organizer will tell you that when you volunteer with events you frequently cannot attend many of the sessions at the event.  You have a lot of other tasks that take up time and keep you from attending sessions.  I was unable to attend many sessions but listed below are the key points I obtained from the event. 

Session: Latest Developments in Postgresql By Amit Kapila, 

Slides link: https://postgresql.us/events/pgconfnyc2024/sessions/session/1745/slides/152/Latest%20developments%20in%20PostgreSQL.pdf

I was very happy slides were made available for this session,  I took LOTS of notes because it was a good listing of all the new things in PG17. But the slides do a much better job of summarizing all of it, I highly recommend reviewing them. Here are the key items that I am interested in right now. 

  1. Incremental backups, I come from the SQL Server world and these were normal things we did all the time. This would help my larger Databases. I am worried about implementation and pg_combine to bring them back together but I’ll do more testing to determine how well this works in the future. 
  2. Optimizing Tuples, Wal volume, and Vacuum processes.   These are listed separately but I see them all related and all good things for my future uses of the PG database. 
  3. SLRU cache and performance improvements.  This has been a problem for our current system and I’m eager to find out more about this improvement.  
  4. Logical Replication, Sync/Failover slots.  Allowing replication on followers is going to be a big game changer in a lot of systems and I’m looking forward to testing it and finding what works for the current system I am working with.  I can see plenty of use cases for this in the future. 
  5. Logical Decoding speeds up Subtransactions,  Another big one for the current system I am working with.  We have some slowdowns for Logical Replication so this will help greatly. 
  6. Trigger on Login event/connection time.  This could have huge usage in some of the Audit items and things that we want to do related to users connecting.  

Lightning Talks: 

One presentation in the Lightning talks had a great one-liner that I wrote down and I want to use in future presentations.  

“Celebrate Effort and not just output” 

This is a good reminder that we need to celebrate failure,  Just because something didn’t work the way you wanted or didn’t happen the way you expected it’s no reason to not celebrate what you have learned.  I have a presentation on Failure I’ve presented in the past and I should add this to the presentation. 

Christine Momjian

Is an amazing volunteer and member of the community,  she provided a wonderful message to remind us all to remember we are a community and need to be supporting and helping others in the community as much as possible.  She is pushing for not only more ways to connect with others but also to have a committee that can find more ways to help others in the community.  I love to see things like this so we can continue to grow and benefit the Pg community.  

My session: How to Never be on call as a DBA again. 

Session link: https://postgresql.us/events/pgconfnyc2024/schedule/session/1628-how-to-never-be-on-call-as-a-dba-again/

I had a larger crowd than the last time I presented. I was right after lunch as well so keeping everyone awake was a challenge. It went much better than the first run and we had some good discussion around the topic.  I did get called out saying that the presentation isn’t about “never” being on call again. It’s more about how to improve your on-call time. Which I fully agree with but this title will appeal to getting people into the session. 

Things to improve 

  1. Add specific slides for jokes around what people “think” the presentation is about.  Retiring is not the only option. 
  2. Slide order is much better now keep it the way it is. 
  3. Add slides/talk about Large companies vs small companies on call. 
  4. Add slides/talk about lone DBA vs larger DBA teams. 
  5. Add slides/talk around compliance and regulation for documentation. 

I had lots of good discussions in the hallway track, most revolving around the sponsors of the event.  I organize and manage the sponsors for these events so I make sure to understand what is and what is not working for them. I also focus on future events so they can plan budgets accordingly.  I am hopeful right now that I will be able to have PG conf NYC ready to start signing contracts before the PASS Summit in November.  That is aggressive to have it ready to go but it’s the goal right now. 

It was an amazing event in NYC and I am excited for the next event!  I feel like we have really started growing the community and I’m looking forward to continuing that growth in many more events!   I hope to see you at a future event!