PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

In our previous posts in this series, we spoke at length about using PgBouncer  and Pgpool-II , the connection pool architecture and pros and cons of leveraging one for your PostgreSQL deployment. In our final post, we will put them head-to-head in a detailed feature comparison and compare the results of PgBouncer vs. Pgpool-II performance for your PostgreSQL hosting !

The bottom line – Pgpool-II is a great tool if you need load-balancing and high availability. Connection pooling is almost a bonus you get alongside. PgBouncer does only one thing, but does it really well. If the objective is to limit the number of connections and reduce resource consumption, PgBouncer wins hands down.

It is also perfectly fine to use both PgBouncer and Pgpool-II in a chain – you can have a PgBouncer to provide connection pooling, which talks to a Pgpool-II instance that provides high availability and load balancing. This gives you the best of both worlds!

Using PgBouncer with Pgpool-II - Connection Pooling Diagram

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

CLICK TO TWEET

Performance Testing

While PgBouncer may seem to be the better option in theory, theory can often be misleading. So, we pitted the two connection poolers head-to-head, using the standard pgbench tool, to see which one provides better transactions per second throughput through a benchmark test. For good measure, we ran the same tests without a connection pooler too.

Testing Conditions

All of the PostgreSQL benchmark tests were run under the following conditions:

  1. Initialized pgbench using a scale factor of 100.
  2. Disabled auto-vacuuming on the PostgreSQL instance to prevent interference.
  3. No other workload was working at the time.
  4. Used the default pgbench script to run the tests.
  5. Used default settings for both PgBouncer and Pgpool-II, except max_children*. All PostgreSQL limits were also set to their defaults.
  6. All tests ran as a single thread, on a single-CPU, 2-core machine, for a duration of 5 minutes.
  7. Forced pgbench to create a new connection for each transaction using the -C option. This emulates modern web application workloads and is the whole reason to use a pooler!

We ran each iteration for 5 minutes to ensure any noise averaged out. Here is how the middleware was installed:

  • For PgBouncer, we installed it on the same box as the PostgreSQL server(s). This is the configuration we use in our managed PostgreSQL clusters. Since PgBouncer is a very light-weight process, installing it on the box has no impact on overall performance.
  • For Pgpool-II, we tested both when the Pgpool-II instance was installed on the same machine as PostgreSQL (on box column), and when it was installed on a different machine (off box column). As expected, the performance is much better when Pgpool-II is off the box as it doesn’t have to compete with the PostgreSQL server for resources.

Throughput Benchmark

Here are the transactions per second (TPS) results for each scenario across a range of number of clients:

#database #developer #performance #postgresql #connection control #connection pooler #connection pooler performance #connection queue #high availability #load balancing #number of connections #performance testing #pgbench #pgbouncer #pgbouncer and pgpool-ii #pgbouncer vs pgpool #pgpool-ii #pooling modes #postgresql connection pooling #postgresql limits #resource consumption #throughput benchmark #transactions per second #without pooling

What is GEEK

Buddha Community

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

In our previous posts in this series, we spoke at length about using PgBouncer  and Pgpool-II , the connection pool architecture and pros and cons of leveraging one for your PostgreSQL deployment. In our final post, we will put them head-to-head in a detailed feature comparison and compare the results of PgBouncer vs. Pgpool-II performance for your PostgreSQL hosting !

The bottom line – Pgpool-II is a great tool if you need load-balancing and high availability. Connection pooling is almost a bonus you get alongside. PgBouncer does only one thing, but does it really well. If the objective is to limit the number of connections and reduce resource consumption, PgBouncer wins hands down.

It is also perfectly fine to use both PgBouncer and Pgpool-II in a chain – you can have a PgBouncer to provide connection pooling, which talks to a Pgpool-II instance that provides high availability and load balancing. This gives you the best of both worlds!

Using PgBouncer with Pgpool-II - Connection Pooling Diagram

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

CLICK TO TWEET

Performance Testing

While PgBouncer may seem to be the better option in theory, theory can often be misleading. So, we pitted the two connection poolers head-to-head, using the standard pgbench tool, to see which one provides better transactions per second throughput through a benchmark test. For good measure, we ran the same tests without a connection pooler too.

Testing Conditions

All of the PostgreSQL benchmark tests were run under the following conditions:

  1. Initialized pgbench using a scale factor of 100.
  2. Disabled auto-vacuuming on the PostgreSQL instance to prevent interference.
  3. No other workload was working at the time.
  4. Used the default pgbench script to run the tests.
  5. Used default settings for both PgBouncer and Pgpool-II, except max_children*. All PostgreSQL limits were also set to their defaults.
  6. All tests ran as a single thread, on a single-CPU, 2-core machine, for a duration of 5 minutes.
  7. Forced pgbench to create a new connection for each transaction using the -C option. This emulates modern web application workloads and is the whole reason to use a pooler!

We ran each iteration for 5 minutes to ensure any noise averaged out. Here is how the middleware was installed:

  • For PgBouncer, we installed it on the same box as the PostgreSQL server(s). This is the configuration we use in our managed PostgreSQL clusters. Since PgBouncer is a very light-weight process, installing it on the box has no impact on overall performance.
  • For Pgpool-II, we tested both when the Pgpool-II instance was installed on the same machine as PostgreSQL (on box column), and when it was installed on a different machine (off box column). As expected, the performance is much better when Pgpool-II is off the box as it doesn’t have to compete with the PostgreSQL server for resources.

Throughput Benchmark

Here are the transactions per second (TPS) results for each scenario across a range of number of clients:

#database #developer #performance #postgresql #connection control #connection pooler #connection pooler performance #connection queue #high availability #load balancing #number of connections #performance testing #pgbench #pgbouncer #pgbouncer and pgpool-ii #pgbouncer vs pgpool #pgpool-ii #pooling modes #postgresql connection pooling #postgresql limits #resource consumption #throughput benchmark #transactions per second #without pooling

jack son

jack son

1610638240

4 Wheel Parts Coupon Code Military Offers To Save 50% Off

When did you think that to uplift your vehicle’s standards very first thing involves your mind where am i able to get the most effective & suitable spare parts & accessories for it? the solution to the current dilemma is true here 4 wheel parts promo. they’re an exceptional wholesaler of 4X4 jeeps, monstrous truck accessories & spare parts, which supplies your vehicles the crucial looks that you’re seeking out for. They endeavor to provide the most recent accessories which are much suitable for your vehicles because they knew how people much hooked in to their monstrous rides. From spare parts to funky accessories they need got an in depth collection for all monster & jeep lovers. Their top of the road products includes matching apparels, front & rear suspensions, lift kits & bulk more to find. they provide exceptional deals in their different categories, to experience such a reduction provide you with can use 4 wheel parts coupon for it.
4wheelparts coupon
They believe that while doing off-roading or doing any adventurous activity your vehicle must be equipped with the newest accessories which makes your off-roading experience more enthralling & exciting. For the past decades, they’re within the industry & successfully cater to user needs in an exceedingly hassle-free manner. Over 55 years of versatile experience they ruled out the automotive industry. With such exceptional services, they’re successfully clothed lots of visitors into beloved customers. In today’s date, they need a lot of happy costumers & endeavoring to improvise their services more & more. excluding providing top-notch spare parts & accessories, keeping in mind consumer’s needs, they deal altogether styles of jeeps & 4X4 trucks, where you’ll be able to purchase your favorite vehicle. to create your purchase more cost-effective you’ll utilize 4 wheel parts coupon which surely makes your buying experience more intriguing. Apparently, if you came to shop for your first & desired 4x4 they need got a massive collection for you. So choosing from the top-notch collection would be more feasible for you. For extra discount offers you’ll discover a 4wp coupon through the additional discount. Either you’re searching for spare parts, accessories, or trying to find a vehicle we got it all for you.

#4 wheel parts promo codes #4 wheel parts coupon code #4wparts #4wheelparts promo code #4 wheel parts promo code

Autumn  Blick

Autumn Blick

1598839687

How native is React Native? | React Native vs Native App Development

If you are undertaking a mobile app development for your start-up or enterprise, you are likely wondering whether to use React Native. As a popular development framework, React Native helps you to develop near-native mobile apps. However, you are probably also wondering how close you can get to a native app by using React Native. How native is React Native?

In the article, we discuss the similarities between native mobile development and development using React Native. We also touch upon where they differ and how to bridge the gaps. Read on.

A brief introduction to React Native

Let’s briefly set the context first. We will briefly touch upon what React Native is and how it differs from earlier hybrid frameworks.

React Native is a popular JavaScript framework that Facebook has created. You can use this open-source framework to code natively rendering Android and iOS mobile apps. You can use it to develop web apps too.

Facebook has developed React Native based on React, its JavaScript library. The first release of React Native came in March 2015. At the time of writing this article, the latest stable release of React Native is 0.62.0, and it was released in March 2020.

Although relatively new, React Native has acquired a high degree of popularity. The “Stack Overflow Developer Survey 2019” report identifies it as the 8th most loved framework. Facebook, Walmart, and Bloomberg are some of the top companies that use React Native.

The popularity of React Native comes from its advantages. Some of its advantages are as follows:

  • Performance: It delivers optimal performance.
  • Cross-platform development: You can develop both Android and iOS apps with it. The reuse of code expedites development and reduces costs.
  • UI design: React Native enables you to design simple and responsive UI for your mobile app.
  • 3rd party plugins: This framework supports 3rd party plugins.
  • Developer community: A vibrant community of developers support React Native.

Why React Native is fundamentally different from earlier hybrid frameworks

Are you wondering whether React Native is just another of those hybrid frameworks like Ionic or Cordova? It’s not! React Native is fundamentally different from these earlier hybrid frameworks.

React Native is very close to native. Consider the following aspects as described on the React Native website:

  • Access to many native platforms features: The primitives of React Native render to native platform UI. This means that your React Native app will use many native platform APIs as native apps would do.
  • Near-native user experience: React Native provides several native components, and these are platform agnostic.
  • The ease of accessing native APIs: React Native uses a declarative UI paradigm. This enables React Native to interact easily with native platform APIs since React Native wraps existing native code.

Due to these factors, React Native offers many more advantages compared to those earlier hybrid frameworks. We now review them.

#android app #frontend #ios app #mobile app development #benefits of react native #is react native good for mobile app development #native vs #pros and cons of react native #react mobile development #react native development #react native experience #react native framework #react native ios vs android #react native pros and cons #react native vs android #react native vs native #react native vs native performance #react vs native #why react native #why use react native

Vern  Greenholt

Vern Greenholt

1596910860

PostgreSQL Connection Pooling: PgBouncer vs Pgpool-II

In our previous posts in this series, we spoke at length about using PgBouncer and Pgpool-II, the connection pool architecture and pros and cons of leveraging one for your PostgreSQL deployment. In our final post, we will put them head-to-head in a detailed feature comparison and compare the results of PgBouncer vs. Pgpool-II performance for your PostgreSQL hosting!

PostgreSQL Connection Pooling Series

How Do the Features Stack Up?

Let’s start by comparing PgBouncer vs. Pgpool-II features:

PGBOUNCER

PGPOOL-II

Resource consumptionIt uses only one process which makes it very lightweight. PgBouncer guarantees a small memory footprint, even when dealing with large datasets. **Winner!**If we require N parallel connections, this forks N child processes. By default, there are 32 child processes that are forked.**When are connections reused?**PgBouncer defines one pool per user+database combination. This is shared between all clients, so a pooled connection is available to all clients. **Winner!**Pgpool-II defines one process per child process. We cannot control which child process a client connects to. A client benefits from a pooled connection only if it connects to a child which has previously served a connection for this database+user combination.Pooling modesPgBouncer supports three different modes: session (connection returned to pool when client disconnects), transaction (returned to pool when client commits or rollbacks) or statement (connection returned to pool after the execution of each statement). **Winner!**Pgpool-II supports only session pooling mode - efficacy of pooling is dependent on good behavior from clients.High availabilityNot supported.PostgreSQL high availability is supported through Pgpool-II in-built watcher processes. Winner!Load balancingNot supported - PgBouncer recommends use of HAProxy for high availability and load balancing.Supports automatic load balancing - is even intelligent enough to redirect read requests to standbys, and writes to masters. Winner!Multi-cluster supportOne PgBouncer instance can front several PostgreSQL clusters (one-node or replica-sets). This can reduce the cost for middleware when using multiple PostgreSQL clusters. **Winner! **(Note - this advantage is only for specific scenarios)Pgpool-II does not have multi-cluster support.Connection controlPgBouncer allows limiting connections per-pool, per-database, per-user or per-client. **Winner!**Pgpool-II allows limiting the overall number of connections only.Connection queuePgBouncer supports queuing at the application level (i.e. PgBouncer maintains the queue). **Winner!**Pgpool-II supports queuing at kernel level - this can cause pg_bench on CentOS 6 to freeze.AuthenticationPass-through authentication is supported through PgBouncer. **Winner!**Pgpool-II does not support pass-through authentication - users and their md5 encrypted passwords must be listed in a file and manually updated every time a user updates their password.Pgpool-II does support passwordless authentication through PAM or SSL-certificates. However, these must be set up outside the PostgreSQL system, while PgBouncer can offload this to the PostgreSQL server.AdministrationPgBouncer provides a virtual database that reports various useful statistics.Pgpool-II provides a detailed administration interface, including a GUI. Winner!Host-based authenticationSupported. **Tied!**Supported. Tied!SSL supportFull support. **Tied!**Full support. Tied!Logical replicationNot supported through PgBouncer. **Tied!**Supported through Pgpool-II, but this is done by sending the write queries to all nodes, and is not generally recommended. Tied!LicenseISC - very permissive, basically allows all usage. **Tied!**Custom license - equally permissive. Tied!

The bottom line - Pgpool-II is a great tool if you need load-balancing and high availability. Connection pooling is almost a bonus you get alongside. PgBouncer does only one thing, but does it really well. If the objective is to limit the number of connections and reduce resource consumption, PgBouncer wins hands down.

It is also perfectly fine to use both PgBouncer and Pgpool-II in a chain - you can have a PgBouncer to provide connection pooling, which talks to a Pgpool-II instance that provides high availability and load balancing. This gives you the best of both worlds!

Using PgBouncer with Pgpool-II - Connection Pooling Diagram

Performance Testing

While PgBouncer may seem to be the better option in theory, theory can often be misleading. So, we pitted the two connection poolers head-to-head, using the standard pgbench tool, to see which one provides better transactions per second throughput through a benchmark test. For good measure, we ran the same tests without a connection pooler too.

#database #data analysis

Whitney  Durgan

Whitney Durgan

1620681120

CockroachDB vs (MySQL, PostgreSQL, MongoDB & Cassandra)

An introduction to CockroachDB

If you are an entrepreneur or an enterprise IT leader, then you need to plan the technology stack for your software development project. You need to choose the right database for your project. If you are developing a heavy-duty transaction processing system or a high-demand analytics system, you will likely use an RDBMS (Relational Database Management System). You can choose one from several popular RDBMSs.

However, do you want an assurance that your data in an RDBMS will survive even large-scale failures in application systems and servers? This narrows the choice. This is where CockroachDB becomes important. This relatively new RDBMS offers a high degree of survivability to your data. In this article, we review CockroachDB and its features. We assess its pros and cons. Subsequently, we review its use cases. Finally, we compare CockroachDB with some of the popular databases. Read on.

#cockroachdb vs cassandra #cockroachdb vs mongodb #cockroachdb vs mysql #cockroachdb vs postgres #mysql