And for many projects, Postgres is still cheaper than both. Having used both, I would much, much rather do the work to fit my project in Postgres/CockroachDB than use either Spanner or DynamoDB, which have WAY more footguns. Not to mention sudden cost spikes, vendor lock in, and god knows what else.
AWS and GCP (and Azure, and Oracle cloud, and bare Kubernetes via an operator, and...) support Postgres really well. Just...use Postgres.
> And for many projects, Postgres is still cheaper than both.
ok? and sqlite3 in memory is even cheaper than postgres!
if you can use (and support correctly) postgres then you should use it, obviously there's no point using a globally scalable P-level database if you can just fit all your data on one machine with posthgres.
Except for projects for which NoSQL is a better fit than a RDBMS, no?
If I'm writing a chat app with millions of messages and very little in the way of "relationships", should I use Postgres or some flavor of NoSQL? Honest question.
Postgres. NoSQL databases are specialized databases. They are best-in-class at some things, but generally that specialization came at great cost to their other options. DynamoDB is an amazing key-value store, but is severely limited at everything else. Elasticsearch is an amazing for search and analytics, but is severely limited at everything else. Other specialized databases that are SQL-full are also great at what they do, like Spark is a columnar database that has amazing capabilities for massive datasets where you need lots of cross-joins, but that severely limits it's ability to act in a lot of roles, because they traded latency for throughput and horizontal scalability, and you're restricted in what you can do with it.
The super-power of Postgres is that it supports everything. It's a best-in-class relational database, but it's also a decent key-value store, it's a decent full-text search engine, it's a decent vector database, it's a decent analytics engine. So if there's a chance you want to do something else, Postgres can act as a one-stop-shop and doesn't suck at anything but horizontal scaling. With partitioning improving, you can deal with that pretty well.
If you're writing fresh, there is basically no reason not to use Postgres to start with. It's only when you already know your scale won't work with Postgres that you should reach for a specialized database. And if you think you know because of published wisdom, I'd recommend you set up your own little benchmark, generate the volume of data you want to support, and then query it with Postgres and see if that is fast enough for you. It probably will be.
Golden Rule of data: Use PostgreSQL unless you have an extremely good reason not to.
PostgreSQL is extremely good at append-mostly data, i.e like a chat log and has powerful partitioning features that allow you to keep said chat logs for quite some time (with some caveats) while keeping queries fast.
Generally speaking though PostgreSQL has powerful features for pretty much every workload, hence the Golden Rule.
100% this, and even though I work for Google I absolutely agree. BUT, for the folks that need it, PostgreSQL just DOESN'T cut it, so it's why we have databases like DynamoDB, Spanner, etc. Arguing that we should "Just use PG" is kinda a moot point.
I think I said this in another comment, but I'm not shitting on Spanner or DDB's right to exist here. Obviously, there are _some_ problems for which a globally distributed ACID compliant SQL-compatible database are useful. However, those problems are few and far between, and many/most of them exist at companies like Google. The fact is your average small to medium size enterprise doesn't need and doesn't benefit from DDB/Spanner, but "enterprise architects" love to push them for some ungodly reason.
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.
this is what I've done sqlite3 for my personal stuff, postgres for everything else. I'm far from a "120 million requests per second" level though, so my experience is limited to small to mid-size ops for businesses.
Millions is tiny. Toy even. (I work on what could be called a NoSQL database, unfortunately "NoSQL" is a term without specificity. There's many different ways to be a non-relational database!)
My advise to you is to use Postgresql or, heck, don't over think it, sqlite if it helps you get a MVP done sooner. Do NOT prematurely optimize your architecture. Whatever choice results in you spending less time thinking about this now is the right choice.
In the unlikely event you someday have to deal with billions of messages and scaling problems, a great problem to have, there are people like me who are eager to help in exchange for money.
Lots of people like to throw around the term "big data" just like lots of people incorrectly think that just because google or amazon need XYZ solution that they too need XYZ solution. Lots of people are wrong.
If there exists a motherboard that money can buy, where your entire dataset fits in RAM, it's not "big data".
I've found it's pretty easy to massage data either way, depending on your preference. The one I'm working on now ultimately went from postgres, to mysql, to dynamo, the latter mainly for cost reasons.
You do have to think about how to model the data in each system, but there are very few cases IMO where one is strictly 'better.'
You can also create arbitrary indices on derived functions of your JSONB data, which I think is something that a lot of people don't realize. Postgres is a really, really good NoSQL database.
Sure. Suppose that we have a trivial key-value table mapping integer keys to arbitrary jsonb values:
example=> CREATE TABLE tab(k int PRIMARY KEY, data jsonb NOT NULL);
CREATE TABLE
We can fill this with heterogeneous values:
example=> INSERT INTO tab(k, data) SELECT i, format('{"mod":%s, "v%s":true}', i % 1000, i)::jsonb FROM generate_series(1,10000) q(i);
INSERT 0 10000
example=> INSERT INTO tab(k, data) SELECT i, '{"different":"abc"}'::jsonb FROM generate_series(10001,20000) q(i);
INSERT 0 10000
Now, keys in the range 1–10000 correspond to values with a JSON key "mod". We can create an index on that property of the JSON object:
example=> CREATE INDEX idx ON tab((data->'mod'));
CREATE INDEX
And we can check that the query is indexed, and only ever reads 10 rows:
example=> EXPLAIN ANALYZE SELECT k, data FROM tab WHERE data->'mod' = '7';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tab (cost=5.06..157.71 rows=100 width=40) (actual time=0.035..0.052 rows=10 loops=1)
Recheck Cond: ((data -> 'mod'::text) = '7'::jsonb)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx (cost=0.00..5.04 rows=100 width=0) (actual time=0.026..0.027 rows=10 loops=1)
Index Cond: ((data -> 'mod'::text) = '7'::jsonb)
Planning Time: 0.086 ms
Execution Time: 0.078 ms
If we did not have an index, the query would be slower:
example=> DROP INDEX idx;
DROP INDEX
example=> EXPLAIN ANALYZE SELECT k, data FROM tab WHERE data->'mod' = '7';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on tab (cost=0.00..467.00 rows=100 width=34) (actual time=0.019..9.968 rows=10 loops=1)
Filter: ((data -> 'mod'::text) = '7'::jsonb)
Rows Removed by Filter: 19990
Planning Time: 0.157 ms
Execution Time: 9.989 ms
Hence, "arbitrary indices on derived functions of your JSONB data". So the query is fast, and there's no problem with the JSON shapes of `data` being different for different rows.
Either way can work. Getting to millions of messages is going to be the hard part, not storing them.
As with all data storage, the question is usually how do you want to access that data. I don't have experience with Postgres, but a lot of (older) experience with MySQL, and MySQL makes a pretty reasonable key-value storage engine, so I'd expect Postgres to do ok at that too.
I'm a big fan of pushing the messages to the clients, so the server is only holding messages in transit. Each client won't typically have millions of messages or even close, so you have freedom to store things how you want there, and the servers have more of a queue per user than a database --- but you can use a RDBMS as a queue if you want, especially if you have more important things to work on.
This is going to feel like a non-answer: but if you need to ask this question in this format, save yourself some great pain and use Postgres or MongoDB, doesn't really matter which, just something known and simple.
Normally you'd make a decision like this by figuring out what your peak demand is going to look like, what your latency requirements are, how distributed are the parties, how are you handling attachments, what social graph features will you offer, what's acceptable for message dropping, what is historical retention going to look like...[continues for 10 pages]
But if you don't have anything like that, just use something simple and ergonomic, and focus on getting your first few users. There's a long gap between when the simple choice will stop scaling and those first few users.
just migrated off of PG to ddb as the main db for my application (still copying data to SQL for data analytics). Working with distributed functions and code hosted on lambdas, the connection management to SQL became a nightmare with dropped requests all over the place.
Yeah I have been using Supabase recently and I really like it. You still get the “serverless” benefits but at the end of the day it is just a Postgres database with some plugins. It is super easy to figure out where the data is coming from/going to.
Meanwhile at work I have a cowoker who loves to create AWS soup where they use an assortment of lambdas/api gateways/sqs queues/sns topics to accomplish tasks such as taking files from one s3 bucket and putting them in another s3 bucket owned by a different team. Their justification of this was that it was generic so other teams could use it, but it is a pain to maintain and make changes to.
Not to be that guy, but why lambdas? I'm genuinely curious. I've never found the "cost savings" (big air quotes) worth it in comparison to the increased configuration/permissions complexity. Especially when Fargate exists, where you can just throw a docker container at AWS, what do Lambdas add? The zero scaling?
With CDK, I can get an ECS service up and running in the same amount of time it'd take to create a lambda function behind API gateway or triggered by SQS/cron. Deploys are easier, cost savings are real, permissions/configuration are the same level of complexity unless you're cutting corners. I'd only use ECS for stuff I know would be high sustained throughput, long duration(>15m) tasks, or things that absolutely need more persistence between executions.
Serverless is great if you recognize that it's just somebody else's container runtime. I wish there was better tooling for Docker based Lambdas though. I hate whole S3 deployment dance for zip file based Lambdas (yes SAM does it for you now but it's still there).
EC2-backed ECS has a great use case for things that you can run ephemerally in a container but require a persistent data store.
Why not? The setup I’m experimenting with for an API right now is basically a single Lambda that’s accessible through a function URL (so no ELB/ALB) + an RDS instance. Spinning up additional environments is a single Cloudformation call and deployment artifacts should work with both Docker containers or S3 (depending on the Lambda execution environment).
Seems like a leaner setup than using ECS/Fargate + LBs to me. Have I overlooked something?
One of lambda's ideal use cases is personal projects. Personal projects usually serve very few requests so lambda's ability to scale to zero results in cost savings.
I totally believe you, I just can't see how it becomes easier than chucking a container on Fargate or something. Maybe I've just been scarred by lambda rat's nests in the past.
Yeah, the "proper" way to do Lambdas, shown in so many fancy architecture diagrams, is a rat's nest. I don't like APIs on Lambda unless you can shove them into one container with a catchall proxy on API Gateway. They really shine if you're processing SQS messages or EventBridge events. If you aren't using other AWS services and aren't cost engineering, then Lambdas probably aren't worth the headache.
Lambda is the most expensive thing you can do if you have more than 25% utilization. Fargate is extremely close to modern on-demand EC2 pricing (m7a family).
Right, running ECS on EC2, not Fargate on EC2. When ECS launched it only had the EC2 launch type (where as you said you must manage your machines). Fargate then came along for both ECS and EKS where Amazon managed the machines for you.
But that's kind of a moot point. I mean, if you're even looking at the likes of DynamoDB or Spanner, it's because you need the scale of those engines. PostgreSQL is fantastic, and even working for Google, I 100% agree with you. Just use PG...until you can't. Once you're in the realm of Spanner and DynamoDB, that's where this discussion becomes more of a thing.
Not necessarily true. DynamoDB on demand pricing is actually way cheaper than RDS or EC2 based anything for small workloads, especially when you want it replicated.
Postgres and Spanner do different things, in different ways, with different costs, risks, and implications. You could "just use" anything that is completely different and slightly cheaper. You could use a GitHub repository and just store your records as commits, for free, that's plenty cheap and works for small projects. But not really the same thing, is it?
My point is that I've seen very, very few situations (I can think of two in my entire career so far) where a "hyperscale NoSQL database" was actually the right choice to solve the problem. I find that a lot of folks turn to these databases for imagined scale needs, not actual hard problems that need solving.
DynamoDB is fantastic for not doing things at scale. It costs a few pennies, there is nothing to set up, it's all managed for you, it is insanely reliable, and it just works. I use it for all kinds of crap that an entire RDBMS is way overkill for.
I don't think the API you interface with fundamentally changes the point that Spanner is hard to recommend from an engineering perspective at anything except the absolute most massive of scales, and even then it will create nearly as many problems as it solves. I'm not saying spanner is _wrong_ or shouldn't exist, but it's very difficult to be in the position where Spanner is the critical key to your application's success and not replaceable by <insert other, cheaper database here>.
Sure. Spanner is expensive, and your primary job as an engineer (if you work for an enterprise like most of us do) is to generate business value. So, if nothing else, you will run into the cost problems of Spanner. There are also other problems; iirc both DynamoDB and Spanner shard their key spaces, and each shard gets the same quota, and the key space shards all have to be the same size. This means that even though you might have paid for 1000rps, for example, that RPS volume is divided across all your shards, so if you have one part of the key space that gets way more volume than another you end up eating up the fractional capacity of that shard way faster than you intend and you have to either overprovision or queue requests, both of which are not ideal.
At a previous job, we ended up creating a very complicated write through cache system in front of spanner that dynamically added memory/CPU capacity as needed to prevent hot shards; our application was extremely read heavy, and writes were relatively low RPS, so this ended up working OK, but we were paying tens of thousands of dollars a month for Spanner plus tens of thousands of dollars a month for all the compute sitting in front of it. I don't think we ended up doing much better than if we had bitten the bullet and run clustered Postgres because our write volume ended up being just a few hundred RPS, even though the read volume was 1000x that. Postgres behind this cache system would have handled the load just as well and cost less than half as much.
The other thing that frustrates me personally about Spanner is that Google's docs are incomplete (as usual); there are lots of performance gotchas like this that exist throughout the entire service, and they aren't clearly documented (unlike, to their credit, AWS with Dynamo, who explains this entire problem very clearly and has an [expensive] prebuilt solution for it in the form of the DynamoDB accelerator).
It's not purely a matter of cost, right? Say you want or need a highly available, high performance distributed database with externally consistent semantics. Are you going to handle the sharding of your Postgres data yourself? What replication system will you use for each shard? How will you ensure strong consistency? Will you be able to do transactions across shards? These are problems that systems like Spanner, CockroachDB, etc solve for you.
Just curious, why would distributed be design requirement? Is individual machine failure likely in AWS/GCP? The only failure I have seen in region level issues which spanner or dynamo don't help with AFAIK.
Individual machine failure is not likely, but we're hypothesizing the need for multiple shards for high performance. So now we have more machines and so the probability of failure increases. So we need to add replication, but then we need to deal with data getting out of sync, etc.... As others have mentioned though, these issues only really become important at a certain scale.
It's hard to find a use case that a plain old filesystem can't handle at small to medium scale. But there are perhaps more important considerations than just "can it handle it"
Uh let's not get carried away. It's fine with enough work maybe. But Postgres has a lot of awkwardness too. HA is a pain, major version upgrades are a pain, JS or JVM stored procs are a pain, configuring auth is a pain. There is a reason so many people are desperate to pay someone else to run Postgres for them instead of just renting a few VMs and doing it themselves.
It's the best, but it's far from perfect. Default mode is non-ACID, and going `serializable` mode makes it very slow. Spanner is always ACID... but always slow.
I know the spanner marketing blurb says you can scale down etc. But I think in practice spanner is primarily aimed at use cases where you'd struggle to fit everything in a single postgres instance.
Having said that I guess I broadly agree with your comment. It seems like a lot of people like to plan for massive scale while they have a handful of actual users.
I said this in another comment, but I have seen _two_ applications in my career that actually had a request load that might warrant something like one of these databases. One was an application with double digit million MAU and thousands of RPS on a very shardable data set, which fit Spanner's ideal access pattern and performance profile pretty well, but we paid an absolute arm and a leg for the privilege and ended up implementing a distributed cache in front of Spanner to reduce costs. The other just kept the data set in memory and flushed to disk/S3 backup periodically because in that case liveness was more important than completeness.
In the first case, the database created as many problems as it solved (which is true of any large application running at scale; your data store will _always_ be suboptimal). A fancy, expensive NoSQL database won't save you from solving hard engineering problems. At smaller scales (on the order of tens-hundreds of RPS), it's hard to go wrong with any established SQL (or open source NoSQL if that floats your boat) database, and IMO Postgres is the most stable and best bang for your engineering buck feature wise.
Postgres/mysql shouldn't have much trouble doing thousands of RPS on a laptop for basic CRUD queries (i.e. as long as you don't need to do table scans or large index range scans). It's possible to squeeze a lot more than that out of them.
My team bought the "scale down" thing and got bit.
Using Spanner is giving up a lot for the scalability, and if you ever reach the scale where a single node DB doesn't make sense anymore, I don't know if Spanner is still the answer, let alone Spanner with your old design still intact. For one, Postgres has scaling options like Citus. Or maybe you don't need a scalable DB even at scale, cause you shard at a higher layer instead.
I've only kicked the tires, but https://neon.tech is a pure hosted Postgres play. I'd be curious to hear if anyone has used them for a real projects, and how that went.
I mean sure, NoSQL gives you more opportunities to screw stuff up because it's doing less for you. But it can be a reasonable tradeoff in some scenarios anyway
Sure, You can compare Cloud SQL vs Cloud Spanner and RDS vs Dynamo, but it makes more sense to just say "Postgres" and assume that the reader can figure out that it means "Whatever managed postgres service you want to use".
The entire point is that every cloud provider has a managed postgres offering, and there's no vendor lock-in. Though, technically, Dynamo does have a docker image you could run in other cloud providers if it came down to that, you'd get no support for it.
I don't think it's really relevant to compare plain Postgres to Spanner, most folks have no need for something like this. It is made for folks who need to do millions of ACID type transactions a second/minute from all over the globe, and have a globally consistent database at all times.
There's a reason why Google installed and built their own atomic clocks and put them in their datacenters, it is to facilitate global timekeeping for this type of services. Most likely 99.9% of the time this type of database is overkill, and also likely way more expensive than you need.
> It is made for folks who need to do millions of ACID type transactions a second/minute from all over the globe, and have a globally consistent database at all times.
I think just doing some (not necessary millions) ACID transactions over the globe and have consistent DB is strong value proposition even for small users.
The dynamodb docker image you’re referring to will get you shot if you try and use it in prod. It’s an API wrapper on top of sqlite and has a ton of missing functionality
There are a couple of databases out there with ddb compatible interfaces, like scylladb
AWS and GCP (and Azure, and Oracle cloud, and bare Kubernetes via an operator, and...) support Postgres really well. Just...use Postgres.