Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scalable PostgreSQL Connection Pooler

github.com

Post reply on HN
Print view View original

71–80 of 91 posts

Re: Scalable PostgreSQL Connection Pooler

#71

Post by x4m »

tsenart wrote: post #51
x4m wrote: post #35

Earlier quoted context omitted.

Sure. We use Odyssey as a component of HA cluster in Yandex.Cloud. Odyssey runs on each node of a Patroni-like system. One day we want to implement load balancing functionality in Odyssey. So that your queries go to other node if local is overloaded or lagging long behind primary.

This, please! Native support for read-replicas would be awesome. Ideally it would now if a query is read-only or not without application changes.

There's already working part of libpq protocol for this - target_session_attrs. But the problem with target_session_attrs is that it just takes too long to discover new primary after failolver. We want to fix this within Odyssey.

Re: Scalable PostgreSQL Connection Pooler

#74

Post by staticassertion »

hn_throwaway_99 wrote: post #21
riyadparvez wrote: post #13

Earlier quoted context omitted.

Can someone point to an article that explains the connection issue in detail?

Short of it is that Postgres uses a process per connection, so architectures that spin up and close connections frequently can have serious scalability issues. Note the landing page for the AWS RDS Proxy, https://aws.amazon.com/rds/proxy/ , is as good a discussion as any as to why you'd want to put a pooling proxy in front of Postgres.

You're grey'd out, but based on some quick research this appears correct.

> PostgreSQL is implemented using a simple "process per user" client/server model. In this model there is one client process connected to exactly one server process. As we do not know ahead of time how many connections will be made, we have to use a master process that spawns a new server process every time a connection is requested. This master process is called postgres and listens at a specified TCP/IP port for incoming connections. Whenever a request for a connection is detected the postgres process spawns a new server process. The server tasks communicate with each other using semaphores and shared memory to ensure data integrity throughout concurrent data access.

https://www.postgresql.org/docs/8.2/connect-estab.html

Re: Scalable PostgreSQL Connection Pooler

#77

Post by mildbyte »

derefr wrote: post #29
chatmasta wrote: post #23

Earlier quoted context omitted.

We are building a solution for this problem at Splitgraph [0] – it sounds like we could probably help with your use case, assuming this is for analytical (OLAP) data. You can get it to work yourself with our open source code [1], but our (private beta, upcoming public) SaaS will put all your schemas on a more scalable “data delivery network,” which incidentally, happens to be implemented with PgBouncer + rewriting +…

We're already sharding our datasets onto separate PG nodes using FDWs (mostly due to us running PG on GCP, and GCE VMs having inherent vertical scaling limitations on node-local storage, which we rely 100% on for our workloads.) Also, our "datasets" are all live data. They aren't modified by the users querying our API, but they are constantly appended to (and I mean constantly, i.e. every few milliseconds.) For us, P…

The other founder of Splitgraph here! I've been experimenting recently with this, since we want users to be able to write to Splitgraph images efficiently without having to turn them into PG tables and then re-snapshot them.

My setup is similar to your cold-hot idea (diagram [1]), where the cold layer uses our "layered querying" FDW. A scan through it is basically a scan through a UNION of cstore_fdw files (columnar format) and we can use the object metadata to determine which objects to download/scan through. If the constraints don't match the object boundaries at all, the scan returns empty. This emulates PG partitions but more dynamically (you can change them without a full table lock).

This means that we can have a view on top of the "hot" and the "cold" blocks that will query both of them, but exclude the "cold" OLAP layer most of the time. We can also redirect writes to the view to hit the "hot" OLTP layer instead using `INSTEAD OF` triggers. In my PoC, I record inserts/updates/deletes in that table and then collapse them into one action per PK using a window function, but if the table is append-only, this should be much easier.

I'm not completely sure re: the locks at changeover/flush time. The idea is that we can create a new object (up to some bookmark) in one transaction, attach it to the Splitgraph table in the second transaction, then truncate the "hot" table up to the same bookmark in the final transaction. Duplicate rows can be eliminated by the view, so the final table will look consistent between transactions.

Re: "using a foreign table as a partition of a local parent table", I think it's actually possible, even though we don't use it here: there's a cool setup in [3] where they use it to shard a table onto multiple worker nodes (the coordinator node has the partitioned table, each partition points to a postgres_fdw shim on the same machine that then points to the actual physical partition on the worker).

We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data. Interesting to think about, though at some point it feels like reinventing Presto/Snowflake/Spark.

Hope this helps!

[1] https://imgur.com/a/1p394PI

[2] https://www.splitgraph.com/docs/large-datasets/layered-query...

[3] https://swarm64.com/post/scaling-elastic-postgres-cluster/

Re: Scalable PostgreSQL Connection Pooler

#78

Post by gkop »

whitepoplar wrote: post #57
anarazel wrote: post #52

Earlier quoted context omitted.

> it doesn't really make much sense to prioritize this inside the database server itself. I wouldn't go that far. There's a lot of things that are hard to do within an external pooler, unless the pooler uses a 1:1 connection model. Leading to most setups using 1:1 connection pooling methods. Which in turn puts pressure on postgres to handle large numbers of connections gracefully.

Would an app-side ORM that has built-in connection pooling + PG14 (w/ connection scalability improvements) generally benefit from a connection pooler like Odyssey/PgBouncer, either server-side or app-side, when the number of app servers is low (<5-10)?

As a data point FWIW, I have operated a PG11 cluster (on RDS), client-side pooling only, with 300-450 app servers and max_connections=2000-3000 (can’t remember), and didn’t observe significant backend resource contention or performance degradation, just a memory cost linear to number of connections,

Re: Scalable PostgreSQL Connection Pooler

#79

Post by ddorian43 »

x4m wrote: post #38
colesantiago wrote: post #4

Has anyone used Yandex Cloud [0] in production?, How does it compare to DigitalOcean, AWS, GCP and others? Also the tech from Yandex such as Clickhouse [1] is really interesting, as they recently spun it out of Yandex. [0] https://cloud.yandex.com/en/ [1] https://clickhouse.com/

We are also developing managed databases for US\EU market https://double.cloud/

Do you have a plan to support dedicated servers? Ie: I put ssh keys and you manage the cluster.

Re: Scalable PostgreSQL Connection Pooler

#80

Post by derefr »

mildbyte wrote: post #77
derefr wrote: post #29

Earlier quoted context omitted.

We're already sharding our datasets onto separate PG nodes using FDWs (mostly due to us running PG on GCP, and GCE VMs having inherent vertical scaling limitations on node-local storage, which we rely 100% on for our workloads.) Also, our "datasets" are all live data. They aren't modified by the users querying our API, but they are constantly appended to (and I mean constantly, i.e. every few milliseconds.) For us, P…

The other founder of Splitgraph here! I've been experimenting recently with this, since we want users to be able to write to Splitgraph images efficiently without having to turn them into PG tables and then re-snapshot them. My setup is similar to your cold-hot idea (diagram [1]), where the cold layer uses our "layered querying" FDW. A scan through it is basically a scan through a UNION of cstore_fdw files (columnar…

> We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data.

IMHO, if you're going to do this, I'd recommend not doing this in Postgres itself, but rather doing it at the filesystem level. It's effectively just a tiered-storage read-through cache, and filesystems have those all figured out already.

You know how pgBackRest does "partial restore" (https://pgbackrest.org/user-guide.html#restore/option-db-inc...), by making all the heap files seem to be there, but actually the ones you don't need are sparse files ftruncate(1)'d to the right length to make PG happy? And that this works because PG only cares about DB objects it's not actively querying insofar as making sure they're there under readdir(2) with the expected metadata?

Well, an object-storage FUSE filesystems, e.g. https://github.com/kahing/goofys, would make PG just as happy, because PG could see all the right files as "being there" under readdir(2), even though the files aren't really "there", and PG would block on first fopen(2) of each file while goofys fetched the actual object to back the file.

(IIRC PG might fopen(2) all its files once on startup, just to ensure it can; you can hack around this by modding the origin-object-storage filesystem library to not eagerly "push down" its fopen(2)s into object fetches — instead just returning a file-descriptor connected to a lazy promise for the object — and then have read(2) and write(2) thunk that lazy promise, such that the first real IO done against the virtual file be what ends up blocking to fetch the object.)

So you could just make your pg_base dir into an overlayfs mountpoint for:

• top layer: tmpfs (only necessary if you don't give temp tables their own tablespace)

• middle layer: https://github.com/kahing/catfs

• bottom layer: goofys mount of the shared heap-file origin-storage bucket

Note that catfs here does better than just "fetching objects and holding onto them" — it does LRU cache eviction of origin objects when your disk gets full!

(Of course, this setup doesn't allow writes to the tables held under it. So maybe don't make this your default tablespace, but instead a secondary tablespace that "closed" partitions live in, while "open" partitions live in a node-local tablespace, with something like pg_partman creating new hourly tables, and then pg_cron running a session to note down the old ones and do a VACUUM FREEZE ?; ALTER TABLE ? SET TABLESPACE ?; on them to shove them into the secondary tablespace — which will write-through the catfs cache, pushing them down into object storage.)

Post reply on HN