AWS and GCP (and Azure, and Oracle cloud, and bare Kubernetes via an operator, and...) support Postgres really well. Just...use Postgres.
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.
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.
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.
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.
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".
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.'
example=> CREATE TABLE tab(k int PRIMARY KEY, data jsonb NOT NULL);
CREATE TABLE
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
example=> CREATE INDEX idx ON tab((data->'mod'));
CREATE INDEX
example=> SELECT k, data FROM tab WHERE data->'mod' = '7';
k | data
------+---------------------------
7 | {"v7": true, "mod": 7}
1007 | {"mod": 7, "v1007": true}
2007 | {"mod": 7, "v2007": true}
3007 | {"mod": 7, "v3007": true}
4007 | {"mod": 7, "v4007": true}
5007 | {"mod": 7, "v5007": true}
6007 | {"mod": 7, "v6007": true}
7007 | {"mod": 7, "v7007": true}
8007 | {"mod": 7, "v8007": true}
9007 | {"mod": 7, "v9007": true}
(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
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
See docs for expression indices: https://www.postgresql.org/docs/16/indexes-expressional.html
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.
I think the truth is that you should use the simplest, most effective tech possible until you are absolutely certain you need something more niche.
Once you have millions of messages, maybe consider moving the data intensive parts out if postgres, if necessary.
The criticism is often that people look for big data solutions, before they have big data.
If you scale out of postgres, you probably have enough users and money that you can fix it :)
But moving to a NoSQL before you have to, might just slow down development velocity -- also you haven't yet learned what patterns users have.
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.
I'm using SenseDeep's OneTable which was pretty interesting to learn https://doc.onetable.io/ in case others reading this are curious.
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.
EC2-backed ECS has a great use case for things that you can run ephemerally in a container but require a persistent data store.
Seems like a leaner setup than using ECS/Fargate + LBs to me. Have I overlooked something?
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.
CDK for everything else.
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).
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.
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.
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.
Also https://www.postgresql.org/support/professional_hosting/
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.
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.
https://cloud.google.com/spanner/docs/true-time-external-con...
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.
There are a couple of databases out there with ddb compatible interfaces, like scylladb
I think they're directly comparable with this context.