Apr
06
2023
--

PostgreSQL: Pgpool-II Use Cases and Benefits

Pgpool-II

PostgreSQL is a popular open source relational database management system many organizations use to store and manage their data. One of the key benefits of using PostgreSQL is its reliability, scalability, and performance. However, as the size of your database grows, it can become challenging to manage and optimize its performance.

Pgpool-II

This is where the pgpool-II comes in. Pgpool-II is a powerful tool that can help you manage and optimize the performance of your PostgreSQL database cluster. In this blog post, we will explore the benefits of using pgpool-II for PostgreSQL.

1. Load balancing

One of the primary benefits of using pgpool-II is its ability to distribute incoming client connections across multiple PostgreSQL servers, allowing you to balance the load and increase the capacity of your database cluster. This means that as your application grows and more clients connect to the database, pgpool-II can distribute the load across multiple servers, preventing any one server from becoming overwhelmed.

2. Connection pooling

Pgpool-II can maintain a pool of idle database connections that can be reused by multiple clients, reducing the overhead of establishing new connections and improving application performance. This is particularly useful for applications that frequently open and close database connections, as it reduces the time it takes to establish a connection.

3. Query caching

Pgpool-II can cache frequently used queries in memory, reducing the load on your PostgreSQL servers and improving response times. This means that when a query is executed, pgpool-II can check the cache first to see if the results are already available rather than sending the query to the database server. This can significantly improve query response times and reduce the load on your database servers. Two benchmarks from users can be found here: [1] [2]

4. High availability

Pgpool-II can automatically redirect client connections to a backup server when a PostgreSQL server becomes unavailable. Furthermore, the online recovery feature allows new standby nodes to be added seamlessly. Moreover, Pgpool’s watchdog module ensures that if one node goes down, another node takes over its place, resulting in minimal to no interruption for client applications. With automatic failover, online recovery, and watchdog, your application can continue functioning without interruption or manual intervention, even if one or more database servers or pgpool-II servers go down.

5. Online recovery

Pgpool-II allows you to perform online recovery of a failed PostgreSQL server without interrupting service, minimizing downtime and ensuring data integrity. This means that if a server fails, you can perform recovery operations without interrupting the service, allowing your application to continue operating as normal.

In conclusion, the pgpool-II extension provides a range of powerful features that can help you to optimize the performance, availability, and scalability of your PostgreSQL database cluster. Whether you need load balancing, connection pooling, query caching, high availability, parallel query execution, or online recovery, pgpool-II has you covered.

By using pgpool-II, you can improve the reliability and performance of your PostgreSQL database cluster, ensuring that your application continues to function smoothly as your database grows. Percona fully supports Pgpool-II, and you can download the latest builds from our repository.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

Nov
02
2022
--

First PostgreSQL Conference Ever: What You’ve Missed at PGConf.EU 2022

PostgreSQL PGConf.EU 2022

I just became part of the PostgreSQL ecosystem and was really curious to get in touch with the community. What are the best ways to interact with a community? Mailing lists for sure, forums, chat channels, or if possible an in-person conference. So I checked the events site of PostgreSQL and found an event nearby — PGConf.EU in Berlin. After reaching out internally, I was told this is one of the biggest or even the biggest PostgreSQL conferences in the world. So it seems I’ve made a good decision.

I was worried and also a little scared about going to a conference after almost three years of not attending any conferences and not seeing too many people. It took me almost six hours including a delay of over two hours (thank you for traveling with Deutsche Bahn!) to make it to Berlin. I was really impressed and surprised when I finally made it to the Berlin Marriott Hotel. What a nice venue for a conference.

View inside the hotel from the 9th floor down to the first floor

The check-in already had a great surprise waiting for me. “Sorry, Mr. Wagner but we’re fully booked and instead of your regular room we can only give you an executive suite”. What a bummer, but I took it nevertheless. I went to the room and almost immediately fell asleep, excited for the next days to come.

Let the show begin

The first day of the conference started with way too much food due to a really great breakfast. After that, I was waiting for the first keynote and opening speaker to come. The room was really crowded and I was wondering what “sold-out” actually meant for a PostgreSQL conference. Magnus Hagander and Vik Fearing did a great job in starting, organizing, and hosting the event. They quickly unveiled that 604+ people had shown up for the conference — what an impressive number.

Picture showing the number 604 attendees

The welcome and opening took about 45 minutes and directly afterward we had the first conference talk of the week: “Efficient Graph Analysis with SQL/PGQ”. It was a super interesting but also a hard topic for me, as this presentation was super technical. I had a hard time following parts of the presentation as I completely missed terms or background information. I definitely plan to look at the slides again and get my feet wet with this topic.

During the first coffee break of the day, I was able to wander around and started to explore the conference venue/booths and I was also able to spot my hungry colleague Umair. Can you spot him in the picture below?

Picture of the lunch break area

The second half of the morning started for me with “Changelog vs Two-dimensional time – what works better?” from Hettie Dombrovskaya. It was a very inspiring presentation as you could feel the passion and deep knowledge of Hettie in every sentence. She basically tries to implement “Time travel”, as she said. She explained bitemporal functions and also the difference between effective time vs asserted time which finally lead to time regions — a small travel in time.

From there I went to “PostgreSQL at GitLab.com”. Given that I’m a huge fan of the communication and transparency policies of GitLab, I had very high expectations about this presentation and I wouldn’t be disappointed. Alexander Sosna and Jose Finotto presented the journey of GitLab using PostgreSQL and also some limitations they’ve hit and how to overcome them. It was interesting to see that a simple use-case split between CI and the rest of the data actually led to such dramatically improved statistics and usability.

Leaving the lunch break behind, I had the first chance to see Hans-Jürgen Schönig in action during his presentation about “Performance tips you have never seen before”. He’s the CEO of CYBERTEC and a very passionate person on- and off-stage. He really engaged with the crowd and was able to get a lot of people smiling whenever he gave some real-world examples while presenting his tips.

Jan Birk presented “Administering large scale PostgreSQL installations” and explained how the Danish Ministry of Education handles 400 clusters divided into 100 products. He really went down the route and explained how they use different tools and extensions such as pgbouncer, pgBadger, barman, patroni, and many more.

As Microsoft had a booth at the conference and was always talking about Citus, I was curious about what they do and joined “Distributed Postgres: How to build a multi-tenant SaaS app with Citus”. It was interesting to see how Charles Feddersen presented the capabilities of Citus on how to scale out as needed and also how resharding works along those lines. Starting small and scaling up whenever you need it is a very common use case nowadays. I’m happy that Citus was open-sourced and can now be developed also by the community. 

I basically stayed in the sponsor’s room, as I was also interested to learn what EDB has to offer when they talk about “EDB Postgres Distributed – Evolution of Replication and High Availability”. Stacy Scoggins presented the solution of EDB as an answer to HA for PostgreSQL. They’ve developed a very nice failover solution into PostgreSQL, which makes it almost transparent. The only cost factor that I’m seeing is the downside of vendor locking, as the code isn’t open. 

Social event

The first day closed up with a social event that took place at the “Alte Münze” — basically a former mint in Berlin_mitte on the banks of the Spree. The venue was nice and a lot of people made it to the event. Underlined by some music, hand food, and drinks, you were able to engage with all parts of the community and I was able to make new friends inside the PostgreSQL community. After a very long day, I finally made it back to the hotel after midnight, waiting for the next two days to come. 

The day after the social event

The second day of the conference started a bit slower. You could tell by looking into people’s faces and eyes during the breakfast and the first few sessions of the day, that for some the social event had taken a little longer — so basically it was an overall success.

Gregory Stark talked about “The Future of Observability in Postgres” and how he envisions standardizing those. He presented his ideas that are needed for the PostgreSQL core to make that happen. Having an open standard, that could easily be used by modern tools across the PostgreSQL ecosystem, sounds like a really great idea to me. 

Being new in the community, I had to join the talk from Melih Mutlu about “Takeaways from the First 6 Months of Hacking on Postgres”. He explained his journey and obstacles getting his first patch landed in the community. He also touched on the current development practices and tools the community is using – I personally chimed in here by asking the GitHub vs Mailing List question, which directly led to some controversial discussion. I’ll follow up on that topic with the community via the mailing list. My personal mantra here is “To improve is to change; to be perfect is to change often” by Winston Churchill.

Photo of the lunch and coffee break area

After the break and some coffee later it was time to learn more about “Neon, cloud-native storage backend for PostgreSQL” by Heikki Linnakangas. He explained the architecture and functionality of the serverless open source alternative to AWS Aurora for Postgres. It separates storage and compute and also distributes the data across a cluster of nodes. Coming with a strong storage background, also in distributed storage, this was a really interesting talk to me and I will definitely follow up with their ongoing work.

I mentioned in the first part that I’m not yet used to all the terms and functionality of PostgreSQL, so I took the chance to listen to Karen Jex on “Everything You Wanted to Know About Databases as a Developer but Were Too Afraid to Ask Your DBA”. This session was really helpful for everyone who is new to databases or PostgreSQL in general. Karen did a great job in going through the various important basic methodologies and functions. I personally took a lot out of this presentation and the slide deck might be helpful to more beginners out there.

Encryption is important and as I just saw a mail thread on TDE (Transparent Data Encryption) on the hackers mailing list, I was curious to learn what Stephen Frost had to say about “On the Road to TDE”. Stephen presented the various different ways to encrypt your data as well as what would work best for Postgres — spoiler: TDE. This is an ongoing effort for over three years already but the broad community is interested in encryption, so this definitely needs to get some hands working on it, to finally build it into Postgres. Let’s see if and how Percona can support this effort, as data encryption is nowadays super important to almost everyone.

Robert Haas presented “How PostgreSQL Resists Automated Management (And How to Fix This)”. He showed some ways external software would like to manage PostgreSQL but fails and what’s the causing factor for it. He also went into his future vision of how this scenario could be improved and implemented into PostgreSQL.

Keynotes

Now Berlin was united again (the room was split up into two meeting rooms during regular sessions) and we were able to listen to the keynotes from the main sponsors of the event. Hans-Jürgen Schönig, Stephen Frost, Marc Linster, and Chris Engelbert did a great job in representing their companies but also the community.

The presentation that stuck out the most to me was the one from Marc about “Accelerating Postgres’ success in the modern enterprise”. He prepared slides that underlay the overall success of PostgreSQL and where this is coming from. Also, the undeniable facts of StackOverflow, where Postgres finally reached the top of the mountain as being the most important database to developers.

The day concluded with Umair and I stepping out for dinner — ok just burgers in the mall next to the hotel — and spending some time getting to know each other better and discussing what we’ve seen and heard at the conference. We both agree that the conference was going great and that the community is highly engaged and further growing.

Also, some sightseeing couldn’t be missed overall before calling it a day.

Umair and Kai in front of the Berlin Gate

Time flies by — The last day of the conference

The last day of the conference started with Robert Haas again talking about “Moving pg_basebackup Forward”. You were able to feel his passion for this extension in every sentence. He showed us the newest features that made it into PostgreSQL 15 and, as such, the significant improvements to pg_basebackup. Two of which are the server-side compression and bucket targets. We also talked about parallel backup — and why this isn’t a good idea most of the time — and incremental backup. There is still a lot of work ahead but the agenda looks very promising.

You might have noticed in the second half that I was quite interested in the new Neon project, so I couldn’t resist joining “Why we build Neon” which was presented by the CEO Nikita Shamgunov. He very openly showed how they’ve built up the current company and what happened within the last year. Pitch decks to investors as well as the founding numbers were presented openly to the audience. Why does this all matter and why is serverless important? — Nikita was able to give good insights into this new startup. 

One of the gold nuggets of the conference has been this talk from Claire Giordano on “How to make your Postgres blog posts reach a ton more people”. She basically presented, in 45 minutes, a condensed version of her fourth training session in an interactive and interesting way to the audience. If there would be only one thing for you to remember from the talk, it’s “Empathy”, Claire said.

Slide showing empathy for your readers

If you want to know how to write great blog posts and reach more people, you have to take a look at her slides

During the social event, I was talking to Harald already about high availability, and I didn’t know he had a talk about that exact topic on Friday. So this talk was a must-have after our conversation on Tuesday. Harald talked about “reasonable availability” and why HA isn’t always a good idea and sometimes even leads to more downtime and outages compared to a simple single-node system. 

Before we reached the end of the conference, it was time for the lightning talks. 

Flip chart showing the list of lightning talks

I was only able to be present for the first half of the session, as I had to catch my train back home, but those lightning talks are usually known to be really good. Given the fact that people have to condense their message into five minutes, they leave out all the blur and filler words in between, so you directly learn about the important things people want to talk about.

Simon Riggs started by announcing that he has retired from actively contributing code to the community moving forward but he still wants to support the community wherever and whenever he can. He received very long applause and a standing ovation, and you could feel the thankfulness from the crowd for everything he’s done over the years for the project and the community.

I’m sad that I missed the talk from Umair about open source licenses but I learned it was a distilled version of these slides. So if you want to learn more about this topic, take a look at the slides or reach out directly to @pg_umair on Twitter. 

Conclusion

PGConf Europe in Berlin was a great conference, with the chance to learn a lot, meet new people, and make friends. If you ever consider joining a PostgreSQL conference, PGConf Europe should be put on the top of your list. I personally saw that the community is welcoming and open and everyone who is interested is encouraged to participate. As this was my first PostgreSQL conference — ever — this is just the beginning of my journey. I’ll be back…

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com