PgBouncer (Connection Pooler)

PgBouncer connection pooler for PostgreSQL. It sits between your application and the database to manage and reuse database connections, reducing memory overhead, lowering query latency, and preventing the database server from crashing during high-traffic spikes
Why its needed? Postgres spawns a new backend process and allocates significant memory (around 10 MB) for every single client connection. if 100s of users connect and sit idle database server will quickly run out of resources or slow down significantly
How it works?
  Now PgBouncer will be postgres server to application and will be client to postgres server
  When a user sends a request, PgBouncer assigns them an idle, pre-existing connection from its pool, eliminating the time-consuming overhead of setting up a brand new connection from scratch

Code

DB_HOST=localhost
DB_PORT=5432            <<<<<< Application talk to PostgreSQL database directly
DB_USER=postgres
DB_PASSWORD=mysecretpassword
DB_NAME=company_db
            

DB_HOST=localhost
DB_PORT=6432            <<<<<<  Pointing to PgBouncer instead of Postgres
DB_USER=postgres
DB_PASSWORD=mysecretpassword
DB_NAME=company_db
            

package main
import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	_ "://github.com"
)
func main() {
	// 1. Grab environment variables
	host := os.Getenv("DB_HOST")
	port := os.Getenv("DB_PORT")
	user := os.Getenv("DB_USER")
	password := os.Getenv("DB_PASSWORD")
	dbname := os.Getenv("DB_NAME")

	// 2. Build the connection string
	connStr := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", 
		host, port, user, password, dbname)

	// 3. Open the connection pool
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatalf("Error opening database: %v", err)
	}
	defer db.Close()

	// Test the connection
	if err := db.Ping(); err != nil {
		log.Fatalf("Database unreachable: %v", err)
	}

	fmt.Println("Successfully connected to the target database interface!")
}