TimescaleDB VS pgbouncer

> based on the time period

Is it the kind of thing where the TimescaleDB extension would make sense?

https://github.com/timescale/timescaledb

Timescale — Open-source time-series SQL database optimized for fast ingest and complex queries.

Compression and other features use the non-Apache license:

https://github.com/timescale/timescaledb/tree/main/tsl

Don't forget PostgreSQL extensions. For something like a chat log, TimescaleDB (https://www.timescale.com/) can be surprisingly efficient. It will handle partitioning for you, with additional features like data reordering, compression, and retention policies.

Offboard it to Postgres specialists like https://www.timescale.com/

TimescaleDB{:target="_blank"}: an extension of PostgreSQL that adds time-series capabilities to the relational database model. It provides scalability and performance optimizations for handling large volumes of time-stamped data while maintaining the flexibility of a relational database.

Have some experience with https://www.timescale.com in this context

Before migration, we were using PgBouncer. We hosted multiple databases (for multiple applications) per cluster, and it was often the case that a single application required 300 or even 400 hundred connections alone. Hence, the connection pooler was a natural solution to the issues we had. We were really happy with it as it was simple to integrate with, and it did the job, yet we decided not to use PgBouncer anymore as AWS does not offer it as a managed service, and the entire point of migration was to not self-host database anymore. We were left then with RDS Proxy as the only available solution. It looked pretty straightforward to add, and since it was the dedicated option for RDS, we expected that things would work out-of-box, assuming that we keep the same config as for PgBouncer (which mainly was disabling prepared statements and using transaction-level advisory locks over session level ones). Well, it turned out that we were wrong.

Thanks to MongoDB drivers all consistently providing connection monitoring and pooling functionality, external connection pooling solutions aren't required (ex: Pgpool, PgBouncer). This allows applications built using MongoDB drivers to be resilient and scalable out of the box, but based on what we understand regarding the number of connections applications establish to MongoDB clusters it stands to reason that at a certain point as our application deployments increase, so will our connections.

Pgbouncer - resolvia o problema do limite de conexões no postgres. Mas a API “saudável” manteve o número de conexões baixo o suficiente.

Pgbouncer maintainer here. Overall I think this is a great description of the tradeoffs that PgBouncer brings and how to work around/manage them. I'm actively working on fixing quite a few of the issues in this blog though

1. Named protocol-level prepared statements in transaction mode has a PR that's pretty close to being merged: https://github.com/pgbouncer/pgbouncer/pull/845

A common solution is connection pooling. Supabase currently offers pgbouncer which is single-threaded, making it difficult to scale. We've seen some novel ways to scale pgbouncer, but we have a few other goals in mind for our platform.