We are still using Pgpool-II and it's been very solid, but would be interested in moving to PgDog.
Would love to hear the advantages of moving to PgDog.
PgDog is funded and coming to a database near you
191–200 of 275 posts
Re: PgDog is funded and coming to a database near you
#192Earlier quoted context omitted.
Yes, but that's not a shopping cart, or a checkout workflow, nor a web store with heavy analytics.
It was one of the top real estate portals in the world. A lot of geolocation searches. New search every time someone moves the map. A ton of data sent to the client. Analytics in every page view. No clue how a shopping cart or checkout flow would drastically increase database load. It should just be basic CRUD. Building a shopping cart is something every student makes. Pages in a web store can be cached relatively ea…
You don't see how adding functionality that requires writing to the database rather than just reading from a cache could "drastically increase database load"?
Re: PgDog is funded and coming to a database near you
#193Earlier quoted context omitted.
Is that still a lot? Feels like a single 64-core, 256GB RDS instance with some caching should handle that fine. RDS has instances up to 192-core and 768GB.
Keep in mind they’re doing real-time logistics and messaging, as well as type-ahead search and managing ads and promotions
I think the real-time logistics is likely the thing taxes a Postgres database.
Everything else seems normal DB CRUD that a single beefy instance with a few replicas should handle easily. Type ahead search is no doubt using a different service and not directly querying Postgres.
Re: PgDog is funded and coming to a database near you
#194Reminds me of long ago, before Postgres even had things like parallel scan to utilize multiple CPU cores on a single machine, I used to have Python helpers to split up queries by ranges of IDs. If a query was complicated, I'd EXPLAIN it first then pick either the innermost or outermost index scan, and often get a linear speedup. But it was quite manual, required using temp tables for SELECTs, and ofc had no consistency.
Re: PgDog is funded and coming to a database near you
#195Earlier quoted context omitted.
I've extensively used Dynamo (internally at Amazon and externally) and even founded a DB startup with it at it's core. Boiling down scalability of Postgres vs Dynamo as it's written in blog is a bit terse. Dynamo scales writes horizontally with the keyspace, forever. Postgres simply can't, and no number of layers between the machines and the developer changes that. Sharding, pooling, Citus are all layered on top of a…
Except that dynamo is still just glorified mysql? https://news.ycombinator.com/item?id=18871661 I don’t think the backend matters. It’s the frontend wrapper that makes or breaks HA.
If Dynamo is glorified MySQL then Hacker News is also glorified MySQL. The system is the whole system, not just one part of it.
Re: PgDog is funded and coming to a database near you
#196Earlier quoted context omitted.
Amazon does 20k peak, or 20k average? Website visitor peaks could easily be two orders of magnitude higher traffic than average for a few minutes.
I worked at a company that had billions of views per year on a single big Postgres instance. Extremely read heavy with many queries needed for a page load. You can cache a lot of things.
Scaling (asynchronous) reads is much easier than scaling writes.
Re: PgDog is funded and coming to a database near you
#197Three real-world issues I've run into recently with PgBouncer + Postgres are: 1. pool exhaustion from idle connections inside open long-running transactions 2. SQLAlchemy's client-side pool using dead connections that PgBouncer had already killed, causing periodic request errors 3. Some tasks have to bypass PgBouncer when they use SET or prepared statements I've already sharded large datasets at the application layer…
#1 is a problem with the client's code, I don't know any easy workaround. Usually a long-running transaction means you're accidentally waiting on stuff like RPCs in the middle, or maybe doing something that doesn't really need to be in a xact.
#2, shouldn't the clientPgBouncer connections stay open?
#3 is why I just use client-side pools instead of PgBouncer, but that gets annoying when you have a replicated service so you have to think about the sum of connections across all pools, so I get why people use PgBouncer.
Re: PgDog is funded and coming to a database near you
#198I am trying to gain a basic understanding of this: Right now I have a 4TB DB on one large box. Is the idea that using a proxy tool like PGDog I could spin up 8 smaller boxes handling ~500GB each and then one medium box for the proxy? Right now I have a project that has very heavy write traffic from multiple services and a web app that reads from this. We are starting to hit the point where no amount of indexing, quer…
That's the idea of sharding. If you read the pgdog docs, you'll notice you need to tell it which shard server to route your request to - it doesn't just magically work. It's still providing value by reusing connections, which are particularly expensive in postgres.
Because it's not magic, you do still have to know what's going on under the hood, e.g. no cross-shard transactions.
I'd see if my application can benefit from read replicas before doing sharding, because sharding is difficult (if you care about data consistency). With replicas, each replica does have a full copy of the data and you only write to the master - you have to decide which transactions are suitable for running against replicas, which can lag slightly behind realtime. E.g. reading data to build a webpage is probably safe to do from a replica - any read-modify-write is not.
Re: PgDog is funded and coming to a database near you
#199We sharded over 20 TB that we know about. This is probably a typo, right? 20TB isn't that big. I would imagine they've sharded a lot more than that
If you think 20TB "isn't that big" I want to know what size of DBs you're working with 0_0
It's big but it's not so big it wouldn't fit on SSD on one particularly beefy server (two for redundancy). Sharding this would be more about the transaction rate. Actually, sharding would always be about the transaction rate.
Re: PgDog is funded and coming to a database near you
#200Earlier quoted context omitted.
> you should own that layer yourself inside of your infrastructure Unless you have millions of users, you don't really need this. It would be nice to have but its not a pressing need. So why invest into developing something that you only need once you are at massive scale? At this point you might as well switch away from Postgres because you'll surely have the manpower to do it. Even with a proxy like PgDog the Postg…
Doesn’t PgDog also handle the sharding by proxying the writes? Maybe I missed something but I thought this is their value prop. It’s not just another PgBouncer.
From the docs you have to tell it which shard to access - it doesn't automagically rewrite your statements.