Posted inDatabase

Odyssey: The Ultimate Solution to Save PostgreSQL from Bottlenecks at Scale

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Background: When PgBouncer Hits Its Limit at 2 AM

At 2 AM, Slack alerts were flashing red. The monitoring system displayed a haunting message: “PostgreSQL: Too many connections”. Despite carefully fine-tuning PgBouncer, when traffic from microservices spiked to 5,000 connections per second, the system began to bottleneck severely.

The issue lies in PgBouncer’s single-threaded architecture. On a 64-core server, PgBouncer can only max out a single core, leaving the other 63 cores idle. This is why I switched to Odyssey. It is a modern connection pooler from Yandex, designed to leverage multi-threaded power for systems under extreme load.

Managing PostgreSQL connections is always a challenge. If you have used MySQL or MongoDB, you will notice that Postgres’s mechanism of spawning a new process for every connection is very resource-intensive. Odyssey was created to solve this problem decisively at an enterprise scale.

Why Odyssey Outperforms PgBouncer

Through practical deployment on systems processing billions of records, I have noticed four major advantages of Odyssey:

  • Multi-threaded Performance: The ability to scale according to the number of CPU cores helps increase throughput by 2-3 times compared to PgBouncer.
  • Intelligent Transaction Pooling: Significantly reduces latency when establishing connections between the application and the database.
  • Granular Resource Control: You can limit the pool size for each specific user or database to prevent a “greedy” service from consuming all connections.
  • Optimized Queuing Mechanism: When the DB is overloaded, Odyssey holds requests in a queue instead of disconnecting them immediately, making the system more stable.

Installing Odyssey on Ubuntu/Debian

To achieve the highest performance, I recommend building Odyssey directly from the source code. This ensures the binary is optimized for your server’s current CPU architecture.

1. Prepare the build environment

sudo apt-get update
sudo apt-get install -y build-essential cmake git libssl-dev libpcre3-dev postgresql-common postgresql-client

2. Compile the source code

git clone https://github.com/yandex/odyssey.git
cd odyssey
mkdir build && cd build
cmake ..
make

Once the make command is finished, the executable file will be located in the sources directory. Move it to the system directory:

sudo cp sources/odyssey /usr/local/bin/

Configuring Odyssey: Best Practices for Production

The Odyssey configuration file uses a format quite similar to a programming language, making it very clear. Below is a config template I often use for high-frequency query systems.

# Define the number of workers based on the number of CPU cores
workers 8

log_file "/var/log/odyssey.log"
log_debug no

listen {
    host "0.0.0.0"
    port 6432
    backlog 4096 # Increase backlog to handle burst traffic
}

storage "postgres_server" {
    type "remote"
    host "10.0.0.5" # DB Server IP
    port 5432
}

database "prod_db" {
    user "web_app" {
        authentication "cleartext"
        password "secret_pass"
        
        storage "postgres_server"
        storage_db "prod_db"
        storage_user "web_app"
        storage_password "secret_pass"

        # Transaction mode for extremely fast connection reuse
        pool "transaction"
        pool_size 100
        pool_timeout 0
        pool_ttl 60
    }
}

Key parameters to keep in mind:

  • workers: Set this to the number of CPU cores. Do not set it too high, as it can cause resource contention (context switching).
  • pool “transaction”: This is the most optimized mode for Web Backends. Connections are returned to the pool as soon as the SQL statement finishes executing.
  • backlog: The default is usually low; increase it to 4096 if you have thousands of requests hitting at the same time.

Monitoring and Operation

Start Odyssey with a simple command:

odyssey /etc/odyssey/odyssey.conf

To check the system health, you can access Odyssey’s virtual console database. This provides real-time performance metrics.

psql -h localhost -p 6432 -d console -c "SHOW STATS"

Pay attention to the waiting_clients column. If this number increases continuously, it indicates that your pool_size is too small or the backend database is processing queries too slowly.

Important Note: The Weakness of Transaction Mode

A common mistake is using pool "transaction" for applications that use Prepared Statements or Temporary Tables. Because Odyssey swaps connections constantly, your next statement might run on a connection that hasn’t initialized the Prepared Statement, leading to logic errors. If your app requires these features, choose pool "session" instead.

Conclusion

Odyssey is not just a replacement tool; it is a leap forward in PostgreSQL performance. Leveraging its multi-threaded architecture makes your system more resilient under heavy traffic surges.

I hope these practical insights help you better optimize your system. If you encounter any difficulties during configuration, feel free to leave a question in the comments!

Share:
Tags: