DynamoDB to Postgres: Why and How
containership.engineering
containership.engineering
Our primary problem was having to constantly manage and change our provisioned capacity so that we could respond to usage spikes and keep our bill from becoming stratospheric. Worse, anytime we'd add new functionality -- such as nighttime analytics jobs that scanned the table or daily backups, it meant revisiting the parameters for our auto-scaling or risk getting throttled and having our application randomly start spewing errors.
DynamoDB's billing and provisioning model is awful to deal with. When your team is having serious conversations about the repercussions of your data values going from 4kb to 8kb, you very quickly begin questioning your decision to use DynamoDB in the first place.
We actually ended up engineering our system to use a variant of event sourcing. This let us dump DynamoDB and use RedisLabs and S3 in such a way that we were able to handle 1mil DAU with a 10:1 write/read ratio for around $5k/mo -- including front end/backend instances.
This sounds really interesting – do you have a writeup of the system architecture anywhere?
The short version of event sourcing is that instead of looking at your data model in standard CRUD terms, represent your data as an ordered list of changes. Updating an address field on a user model would become something like ChangeEvent{address=...}, rather than a full read/write of a user blob.
To derive the current state of the object, you would replay and apply all events to a blank object. Obviously over time the list of changes could become enormous, so what you do to optimize is periodically create a snapshot of the current full state of the object and only replay events that happened after it was made.
For our persistence layer we used Redis as the input queue for all events. Each object we stored required a list to push changes into, an integer counter to represent the sequence ID of the most recent event in the list, and a string value containing the location in S3 of the most recent snapshot (if it exists). Performance was incredible for writes because a) it's Redis and b) RedisLabs supports semi-transparent sharding.
We used S3 to store the snapshots. We called the creation process a "compaction" because besides storing the most recent full state of the object, we would also store off the list of changes used to make it and purge those events from Redis. This kept our Redis storage usage tightly bounded and our costs down.
This compaction process was also 100% safe with no chance of race conditions or data loss. Our algorithm would fetch the list of changes from Redis, create and serialize the current state of the object as a snapshot, and then write it and the list of changes to S3. Only upon success of those S3 writes would it then issue a command to Redis to purge that object's list of changes. To prevent race conditions on this part of the operation, we would send the sequence ID we are compacting from and in a Redis script we would atomically check to make sure no more-recent compaction has happened, update the snapshot pointer, and flush the list of changes only up to the sequence number we compacted from (changes were free to be added to the list while a compaction process was in progress).
The killer part of this whole setup was that those snapshots and change lists stored in S3 were completely immutable and formed a linked list of the entire history of changes for an object. We could visualize and inspect the entire change history of an object, find bugs and retroactively fix them by changing how an event is applied, and if needed for read performance, cache the S3 snapshots in memcache without any concern for needing to invalidate them.
Concurrent writes to an object became less of an issue as well. Instead of needing to do a CAS write or a transaction, you would just push the change you wanted to make to the object, read it back, and verify it was actually applied. No locking needed.
The major downside to this architecture is that any querying you need to do will always be weakly consistent and require an external index. We had a small Elastisearch cluster for some of our objects that had fields needing indexing.
One last major advantage of this setup was that it was trivial to "clone" production to your local dev machine. Because snapshots were immutable, we set up a tiered database layer that would first try and load snapshots locally, and failing that, would use a read-only IAM user to access S3 to pull down a snapshot from production. All you would then do is load a backup from a RedisLabs database locally and boom, you have an exact copy of production that you can freely modify because any new writes all happen locally.
Believe it or not, the most unexpected cost from this architecture was PUT requests to S3. Depending upon the frequency of your compactions it can start to add up. However, if usage declines, that cost also declines with no impact on the availability of your data.
https://www.confluent.io/blog/introducing-kafka-streams-stre...
What understanding of your access patterns did you have before you decided to engineer the system that you did? Also, I'm curious about developer costs vs compute costs. How much would have been paying for a less developed but less efficient solution?
I was able to justify the existence of a small dedicated backend team by claiming I could deliver 10x the performance at 1/10th the cost of our most popular game's backend. Given that the monthly server costs of that game could be measured in multiple engineer salaries, this proposition was quite attractive. While definitely a bit of hyperbole, we actually ended up hitting those targets when it was all said and done.
Additionally they somewhat recently added an optional caching layer: https://aws.amazon.com/dynamodb/dax/
DAX would have definitely addressed many of our issues, had it been available at the time. Glad they added that.
Is this really a distinction your "scrappy startup type" can afford to make?
> Although since version 3.4.x Mongo passes all Jepsen tests, when our original database was chosen the current version of Mongo at the time did not and was deemed unreliable by the team
Does not pass all Jepsen tests => deeemed unreliable :( Wow. Of all possible reasons, THAT'S the reason MongoDB wasn't chosen? Jepsen, man. Pretty sad so many people just look at Jepsen results like this. Jepsen, really, is about the tests more than the results. It highlights various failure modes that may be difficult or uncommon to replicate in production. These failure modes are important to know about to understand the product you are using, but are not really the end-all when it comes to deciding whether that product is worth using!
Anyway, show me a database that passes Jepsen and I'll show you a database that only accepts writes to a single node.
I know one - CockroachDB :). Relational, strongly-consistent, SQL, elastic, multi-active replication - the dream! (I work on it)
https://www.cockroachlabs.com/blog/cockroachdb-beta-passes-j... http://jepsen.io/analyses/cockroachdb-beta-20160829
If someone can explains those abysmal low numbers ...
Since the time of that analyses, we have done a significant amount of work for speeding up these high-contention scenarios, with quite dramatic differences in some cases we looked at. We pretty much changed our transaction execution model from a more "optimistic" one where transactions can abort each other and induce thrashing, to something resembling more the traditional row locks. So, hopefully, even for these atypical uses cases we should generally perform much better.
First, this is a test in which the Jepsen nemesis is continuously tweaking the network configuration to cause (and heal) partitions between different nodes. You don’t commonly see behavior like this in a real world deployment.
Second, tests like these deliberately try to reach high levels of contention, in which many transactions are trying to operate on the same keys. In real-world situations contention tends to be much lower as each user is operating mainly on their own data. The performance of all transactional systems suffer under high contention (although CockroachDB’s optimistic concurrency model fares worse than most)"
IIRC, in the case that aphyr refers to for these specific numbers, the reads are scans that span multiple shards[1], while the writes are writes to single shards.
[1] even though aphyr says it's just a hundred rows, the tables are split into multiple shards because aphyr in this case was specifically testing our correctness in multi-shard contention scenarios. In production you wouldn't have multi-shard reads crop up until you were doing scans for tables that were hundreds of thousands of rows in size[2]. It's easier to picture if you think of this performance speed difference in the scenario where you are doing full table scans spanning multiple shards on multiple computers while the underlying rows are being rapidly mutated by contending write transactions. The transactions get constantly aborted to avoid giving a non-serializable result, and performance is suffering. We agree that the numbers in this contention scenario are too low, and we are actively working on high-contention performance (and performance in general) leading up to our 1.0 release[3].
[2] Specifically, we break up into multiple shards when a single shard exceeds 64mb in size.
[3] You can follow along on one of the PRs that address this specific performance issue here: https://github.com/cockroachdb/cockroach/pull/13501
you guys should really publish a new blog post with updated performance benchmarks.
Super low throughput numbers is deterring a lot of potential users, imho.
(There are also relatively common cases it doesn't test, e.g. hard power-off.)
If you would rather go back to trusting marketing speak instead, good luck to you.
There are also thousands of problems that happen with no public reporting and no vendor is going to willingly release details of their support cases, especially when they're being paid to fix their own problems.
There's nothing but positive outcomes from jepsen and holding databases to strict standards to see if they actually meet the goals they state.
Do you know your query patterns for your application?
If you know how your application / product need to pull data from the data store, you way more information that will allow you to pick the right data store to fit the problem at hand.
When you don't have any prior query patterns to go off of, picking a relational database to start your application is a good choice. You need the flexibility to allow your schema to change over time. The schema and query patterns will eventually reach a point where changes don't happen all that often and then you can look to other datastores if you need to scale. Plus, are you really going to have THAT much data when you start?
Disclosure: I work on Heroku Postgres. Could be a little biased....
They don't realize just how easily performant Postgres can be - and often so easily by sticking basic indexes where they are needed. Similarly they may not realize the amount of data and load PG or MySQL can handle just fine.
DynamoDB's purpose is to allow you to easily scale a known query pattern. I definitely don't recommend it as a first solution when you don't know how the data is going to be accessed. Or when everything fits on one PostgreSQL box.
We've had a lot of success starting out with a big PostgreSQL box and migrating use cases to DynamoDB when a table gets too big and has a query pattern that fits (essentially K/V lookup with some simple indexes).
Right, because multi-master replication schemes have such a long lineage of being operationally simple. /s
Where did you read this?..
It doesn't generally. You often can increase the total throughput because independent transactions can be batch committed when replicated, which can increase throughput noticeably, but that's more like a constant factor improvement than a real scalability improvement.
What it does however often allow you to do is to:
a) Address latency issues with writers distributed across the globe. If you have masters on each continent your total write rate can often be massively higher than if you have application servers from each of those continents write to a centralized database.
b) Partially replicate your data globally, partially not. Quite often you'll want to have some data available everywhere (typically metadata, security related data, etc), but a lot of the bulk data only available in certain places.
How? Surely to provide ACID compliance all writers need to co-ordinate a lock if they want to update a record. Same with incrementing IDs etc.
> How? Surely to provide ACID compliance all writers need to co-ordinate a lock if they want to update a record.
Well, there's different systems out there. What you want for something like this is asynchronous multi-master. Which, as you observe, allows for conflicts to occur, which need to be resolved somehow.
> Same with incrementing IDs etc.
The solution for those is usually to have each node assign sequences with different start values, have a system that distributes "chunks" of values (that was what BDR, an async MM version of postgres used to do), or avoiding sequences in favor of UUIDs or similar.
If you want to scale up your writes, sharding would be a better solution.
No it's not. Postgres barely has decent replication as is. Logical replication is a good step beyond (while still missing major functionality) but nowhere near what it takes for multi-master replication.
You're overly negative whenever postgres comes up on HN. Obviously the v10 stuff is not multi-master and the project definitely is not claiming it has, but saying that Postgres' replication "barely has decent replication as is" is a bit absurd given that it works quite well in a lot of high-throughput scenarios in a lot of companies, and has so for 5+ years. There's a lot of valid complaints about it you can make (especially around flexibility and operational issues), but it's definitely not just "barely decent".
I'm not saying that you have to be fan. It's fine to say the replication isn't good enough for everything, or just ignore postgres.
> but nowhere near what it takes for multi-master replication.
FWIW, there's async MM built on top of the relevant pieces, and it's used in production. It's not ready for wide audiences, but arguably (and I think one can very validly disagree about that) it's closer than "nowhere near".
I get that you're a committer but "overly negative" is as much your opinion as what I say, and my comments are the truth as I see it. I've even spent an hour talking to Ozgun about databases and moving to Citus before so my statements come from being a user, operator and maintainer of dozens of different database technologies in production for years, including postgres right now, and from all that experience it is definitely the most behind when it comes to replication/HA compared to anything else I use.
All of those threads have plenty of upvotes and other user comments so it's clear I don't stand alone here. Perhaps instead of policing my statements, time would be better spent on improving the features and experience of the product.
To let you know that I find your comments overly negative, and that they might have a bigger impact if formulated in a bit more relative manner? I mean:
> Postgres is probably the weakest of all relational databases when it comes to scalability, both vertical and horizontal.
Doesn't strike me as a particularly useful comment, given the (to me) obvious hyperbole. If you'd instead, at least every now and then, explain what precisely you find lacking, people - like me - could actually draw useful conclusions.
I only said something because I remembered your name from a couple times reading quite negative and undifferentiated comments. And I linked to the comments because I searched for them to validate whether my memory of the past comments was/is correct.
> Perhaps instead of policing my statements, time would be better spent on improving the features and experience of the product.
I do that. Just about every day.
And if you want more feedback:
-> Add (and enable) multi-core queries.
-> Build-in pgbouncer (or better connection management).
-> Enable 1-line command to setup basic replication (whole node or per db level) without having to edit anything else. Look at redis or memsql for inspiration.
-> Enable 1-line command to easily failover, remove hotstandby requirements.
-> Add DDL support to logical replication because I guarantee this will be the #1 source of problems for anyone that tries to use this.
-> Fix the poor defaults in all the configs.
-> Document the existence of pg_conftool and make it a first-class tool that can also edit pg_hba.conf automatically so I no longer have to deal with any config files. Store any other settings in the database cluster itself. Inspiration from sql server on linux or cockroachdb here.
IT/admin UX at this point is more important than the diminishing core upgrades. Postgres is good enough internally but has sorely fallen behind on usability with obsolete defaults, convoluted configs and way too many 3rd party tools needed for basic scaling. Saying use X tool is effectively the same as just use Y database that already does it. MySQL, MariaDB, even SQL Server are not standing still.
Solid in-cluster and cross-dc replication is the reason we stuck with memsql (+ built-in columnstore), not because it was possible but because it actually works easily and reliably with their fantastic memsql ops program. I've shared all of this feedback online over the years (along with many others) so the lack of understanding is really more a reflection of the postgres team and poor priority planning.
> That postgres is behind in scaling (multi-core, replication, sharding, multi-master) against all modern relational databases is verifiable truth.
No, it's really not. It's hardly best of class in them, but the worst? Meh. That's just my point, you're making way to strong statements.
Just to start at your first point: multi-core. Mysql has just about no intra query parallelism, whereas postgres 9.6 has some limited support, and postgres 10 is decent, although far from great. Postgres' inter query parallelism was for a long while better than mysql, then mysql was better for a couple years, in the last couple years it's gone back and forth and depends on the specific use-case. So how is postgres verifyably worse than all other "modern" (whatever that means) relational databases?
> Your judgement is clouded by your proximity to the project.
That's quite possible.
> Here's just 1 prior thread between us: https://news.ycombinator.com/item?id=13841496
I do remember that, but there you're again arguing super reductionist. You're arguing that having to do
CREATE PUBLICATION all_tables FOR ALL TABLES;
Yes, the logical replication stuff has some serious limitations, and that's true. A significant subset already has patches out there for the next version, but that doesn't help current users.
The streaming replication support existing since 9.0 is pretty decent however, and has gotten a lot easier to use in the last releases, and especially with the saner defaults in v10.
> Add (and enable) multi-core queries.
There's some massive progress in v10.
> Build-in pgbouncer (or better connection management).
Indeed.
> Enable 1-line command to setup basic replication (whole node or per db level) without having to edit anything else. Look at redis or memsql for inspiration.
It's two commands for both physical and logical replication now. Not perfect, but not too bad either.
> Add DDL support to logical replication because I guarantee this will be the #1 source of problems for anyone that tries to use this.
Yup, I've argued just that point, and I've implemented it in a precursor of what's now in core for logical replication. I sure hope that's going to largely get into v11.
> Enable 1-line command to easily failover, remove hotstandby requirements.
Not sure what you mean with "hotstandby requirements"? The defaults? Those have been fixed in v10. Not being able to do certain things on a standby?
> Fix the poor defaults in all the configs.
We're making some progress, e.g. that everything's now by default ready to streaming replication, but there's a lot more to do. It's sometimes hard because the many different usecases ask for different defaults.
> Solid in-cluster and cross-dc replication is the reason we stuck with memsql (+ built-in columnstore)
What specifically are you seeing lacking w/ cross-dc replication? I completely agree that there's UX/complexity issues around replication, but I don't see how those affect cross-dc specifically? There's plenty of people quite happily using cross-dc replication, once it's set up.
> I've shared all of this feedback online over the years (along with many others) so the lack of understanding is really more a reflection of the postgres team and poor priority planning.
Or perhaps also a question of funding, time and communication style.
It is far, far easier to add nodes to a Cloudant/Couch2 cluster, set up realtime replication from db1 -> db2, then once the replication has caught up, at your leisure you switch your database connection in your app.
'""' vs '' (single quotes to illustrate the wrapper for the total content).
{"foo":""}
Yes that does suck quite a bit and seems like a pretty bad decision.
I'm quite ok with that. It's such an awesome piece of software.
The guy did not seem to know what he was talking about. Almost felt like he picked Postgres by throwing darts at possible options. Yes, he picked the right choice :), but I feel like he will misuse it[1] and few months later write article how bad it was.
[1] Heh, I've seen Postgres tables that had 4 columns:
- id
- date added
- date updated
- thrift encoded blob containing the data (that was before JSONB, but even then the data was highly relational and that was silly)
It will likely introduce some serious consistency issues that need to be attended to, but that pain might be better than the alternatives.
> When it came to the different types of NoSQL databases, it was pretty clear that a document store best suited our needs. We needed to be able to query based on the data so key/value was out, there was no need to have the ability to make all of the complex connections between data of a graph db, and nobody actually knows what a column db is (jk but our data is not the right use case for it). So document it was. We would have to change our data layout a bit to really work well with most document databases, our current data structure was similar to what you would expect for a relational db with tables etc since dynamo also uses a table structure. I wanted something that was ACID, none of that eventual consistency nonsense. That took a lot of them out of the race right there.
I don't get it. He needed arbitrary querying, relations, ACID, tables. And yet... his first thought was to go to a document-based no-sql database and is only later hit with the sudden realization that his relational data is bet suited in a relational database.
What has gone so wrong here that an otherwise smart and educated engineer makes these kinds of fundamental mistakes? Is it the success of no-sql marketing departments, and the apparent un-sexiness of traditional relational databases? Is it education? The job market? A fear of SQL (that you never really have to write anyway...)? Is it the quick-fix non-relational databases give you?
As far as I can see there are very very very few nails that a PostgreSQL hammer cannot hit. I guess that's part of the point of the article, but why does it need an article to explain this?
How can we stop these kinds of articles from needing to be written?
> The change drastically increased the speed of our API, some requests are up to 6x faster than they were with Dynamo. The account details request, our previously slowest call, took around 12 seconds with Dynamo and was reduced to 2 seconds with Postgres!
sigh
Edit: Not sure why this has been moved to the bottom of the thread. I wasn't trying to single this particular developer out, I was merely commenting on this troubling trend. Apologies if this came across in the wrong way.
Side topic: AWS did agressive move to throw it's resources to University, and pays off hansomely. Those new blood coming to our profession now has the concept of infrastructure ~= AWS (mileage varies between people, but that's the idea).
SQL as a standard is roughly followed, and PostgreSQL is very compliant. That's more than can be said about any non-relational database - each has it's own query language.
I use JSON fields and arrays quite a bit, but that doesn't mean I need a document store. It means I have small portions of data that is non-relational, or is better suited to use arrays/json than strict tables, but I still want everything else consistent, enforced, and also have all the other bells and whistles Postgres gives you (FTS, partial indexes, rich types everywhere, ACID, the list goes on and on).
Being tied to Postgres is often better than being tied to a document store. At least you can dip your toes into non-rel as little or much as you like (where it makes sense), rather than diving in at the deep end and realizing you can't swim.
This school of thought that a SWE can know the entire stack, infra to app, is coming home to roost. You are the technical debt.
Learn your fundementals, and if a relational database isn't part of your foundational knowledge, learn it now or get out.
I mean, sure, he may be a "node guy", but he uses node to make web applications. A large part of that is persistence, i.e databases. For a lot of applications it's one of the most important part of your stack, if not the most important part. It's your treasure trove, it's your crown jewels. Your app is just a thing that shuffles state around in the database.
He knew a fair bit about NoSQL databases, but why did he not even consider a relational one? I'm not trying to single this specific guy out, merely asking what people think is causing the trend of engineers over-using non-rel databases for relational data.
Edit: didn't see your edit. you're spot on.
From the outside, Postgres looks hard. It isn't hard, but it looks it, because it was not designed around a five-minute demo. I use it for everything until I have a very compelling reason not to. But I have an established library of scripts and systems to make it easy, because I am both an infrastructure guy and a software developer so the idea of "I'm going to write a Vagrantfile in two minutes flat and drop in this provisioner" is something that's top-of-mind for me. (Or I just steal the Vagrantfile from another project, whatever.) But I've made that investment and it's paying off--if one hasn't made that investment, it's scary And then doing it in production? Whether it's a hosted service like RDS or doing it yourself, you have to answer a lot of capital-Q Questions along the way and that's effort that seems unrelated to actually getting your software done.
It's not just "why didn't this person think about a RDBMS?". It's that all of these easy-answer-but-not-really systems (fill in your own) are capturing the minds of developers. The quest for abstractions and ways to make ourselves better able to do our jobs is a good one. The uncritical adoption of tools that might not actually do that is not, but that's a cultural thing not limited to datastores.
- By the time you reach the limits of a Postgres setup, you will either be making so much money that porting it is a couch-cushions deal or your company is a smoking crater in a hillside.
- SPAs are fine. SPAs plus server rendering is often a pain in the rear. Walk, then run.
- If you can't express concretely why you want a container fleet, you don't want a container fleet. Your cloud provider's autoscaling will suffice. (This advice is different for non-cloud clients, but the overhead of leaving space in a container fleet for new deploys or for scaling is deadweight and turning around a container in thirty seconds is probably not as important as the blogs you read tell you it is.)
I think this applies to most relatively new (hype cycle) technologies.
Although my business partner and I have talked about a "fundamentals of cloud system design" course on Udemy or something...
Selecting the right framework, platform, model for current needs with reasonable forward looking support takes experience and strong formal fundamentals.
On the bright side, relational is coming back with RDS, cloud-sql, spanner and cockroachdb. With any luck NoSQL will be forgotten :)