101–110 of 139 posts
Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.
Earlier quoted context omitted.
Cost is complicated because we autoscale from 2-3 readers up 10+ readers during peak traffic. Instead of running 10 replicas 24/7, we can spin one up and within about 10 minutes it's handling reads. So we have a few instances that only run for about 3 hours a day and others that run maybe 6 hours. That is a big cost savings over running them all 24 hours a day. We couldn't bring up new replicas like that when we were…
Aurora is indeed amazing. However, my experience with it regarding costs is that main cost you have running it is not compute, but IO ($0.2 per million requets [1]). This cost is kind of hidden since to estimate this in the early stages of a project is an art. In one project on my team the IO cost is about 8x more than cost of instances. But imo it is still worth and I never actually calculated how much we would pay…
Right now, on a multiple of the traffic we had before we moved to Aurora we are paying less than half what we used to for IO.
Earlier quoted context omitted.
How much knoweldge is transferable? Isn't Aurora just protocol and SQL dialect compatible, but underneath it has nothing to do with postgres?
Each flavor (and version) of Aurora is compatible with a corresponding version of the open source software. For example Aurora MySQL 1.* is compatible with MySQL 5.6 At my current gig we use it in Prod, but we are also able to run our software during development pointing to locally installed open-source versions of MySQL just fine. I imagine it's the same for Postgres.
But from the application perspective, it all runs pretty seamlessly. We've never had a behavioral difference between development against postgres and production with Aurora. Perf has really been the only difference, and perf in development never represents perf in prod at large scale anyway.
Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.
We were quite unhappy with Aurora because of the terrible performance of writes against tables with secondary indices. What does your workload look like?
Overall our workload is very read heavy. At peak, if we compare our CPU on the writer vs the aggregate CPU on the readers, we do about 10x more read work than writes.
Remind is an education messaging program, so our workload is partially user management (which users belong to which schools and which classes) and partially user generated content (messages being sent). Our user generated content (more like 2-3x read vs write) is all backed by DynamoDB and our user management is in a couple of Aurora database clusters.
Can I bounce this idea off you guys. Would there be a market for a dba to charge maybe 100-200. Just comes in, listens to your DB use cases, and recommends various config/setting changes, hardware, etc? It seems so much better than having a team of programmers study Postgres settings for a week. That was my last experience with it at least.
Why wouldn't an automated interface be able to give similar recommendations ?
you being sarcastic? If only we had an automated interface to make decisions about what code to write, then we wouldn't need programmers. Look how well that turned out IRL. We don't really need many assembly language programmers any more, but now we have all these nifty new programming languages...
Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.
How much knoweldge is transferable? Isn't Aurora just protocol and SQL dialect compatible, but underneath it has nothing to do with postgres?
It has a custom storage layer that isn't too different from a very fancy SAN. Replication is where things get to be very different. All instances use the same underlying store, so replication of storage isn't part of the postgres layer. However, reader nodes need to invalidate cache when writes occur. So they use postgres replication, but the readers skip writing to storage.
Lots of rewritten components under the hood to support this different storage paradigm, but the engine itself is still postgres.
Earlier quoted context omitted.
A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.
Indeed. I too have resorted to implementing my own plan using temporary tables. Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.
Earlier quoted context omitted.
Also, pg has initdb --data-checksums
Doesn't this require a full dump/import? Can you stream from a primary server w/out checksums to a replica that has checksums enabled? Or does this fall into the "everything neat with PostgreSQL requires major downtime" category? (features, version upgrades, etc).
[1] https://www.postgresql.org/docs/current/app-pgchecksums.html
Earlier quoted context omitted.
How much knoweldge is transferable? Isn't Aurora just protocol and SQL dialect compatible, but underneath it has nothing to do with postgres?
Each flavor (and version) of Aurora is compatible with a corresponding version of the open source software. For example Aurora MySQL 1.* is compatible with MySQL 5.6 At my current gig we use it in Prod, but we are also able to run our software during development pointing to locally installed open-source versions of MySQL just fine. I imagine it's the same for Postgres.