Why Postgres RDS didn't work for us
medium.com
medium.com
The article also doesnt mention anything about using provisioned IO instances. Nor any mention of which architectures have the highest PIOPs ceiling.
I've built block devices using the highest IOPS (fulfilling all the necessary requirements) at well as extremely large block devices (64TB) using EBS. When maxxed out and tuned to the gills, it's fast and big.
Genuine question: How is it cost wise, compared to other solutions you have experience with?
The transition from on-premise DB to cloud DB can be jarring. With on-premise hardware your cpu usage and query throughput are correlated. With RDS your queries will suddenly hang without indication from traditional resource metrics.
Be meticulous about your iops & cpu needs, and assess whether snapshots & replication config is worth paying 3x for.
that old saying "but it's opex, not capex" will only take you so far -- especially if you see the pricing for amazing but ten-year-old hardware at your local dedicated server leasing and then you've still got opex instead of capex.
I’m not trying to argue that RDS isn’t expensive, but an on-demand multi-az 4 CPU / 16GB instance with 12,000 IOPS / 500MiBps bandwidth is ~$600 / month on RDS.
https://calculator.aws/#/estimate?id=0d612854fb94107dcb14441...
That said, over time PostgreSQL has wildly expanded the range for which it's suitable and if you can and want to bet on the future, it's often a better bet than niche systems.
It's also important to remember that PostgreSQL is decades ahead of other systems in data virtualization and providing backward-compatibility to applications after changes; pushing down computation near the data and avoiding moving billions of rows into middleware, including a world class query optimizer; concurrent data access; data safety and recovery; and data management and reorganization, including transactional DDL. Leaving this behind feels like returning to the stone age.
I'm not sure what you mean by ultra low latency but unfortunate to have to rethink what a tool is good for because of RDS / EBS.
There are even other AWS Postgres-oriented options (check the pricing first):
ZeroETL from Aurora Postgres to (postgres-compatible) Redshift (Serverless?)
But to pick the wrong DB tool in the first place and bemoan it as “not scalable” is a bit like complaining that S3 made for a poor CDN without looking at how you’re supposed to use it with Cloudfront.
(I would like to know, where their ZeroETL originated from, usually AWS picks up ideas somewhere and makes it work for their offerings to cash in. A universal replication tool.)
After that is when I'll start migrate to duckdb or clickhouse (or citus if I don't want to move out completely from Postgres)
I'm lost on the article. Sounds to me they had someone doing this without any DB experience at all.
I would not have written a blog post about an obvious choice of having some cheap nvme based 'warehouse' server.
But I do wanna see there explain statement tbh and how they store the data in their columns.
M1 with 16gb of memory vs 16 vcpu Xeon with 128g, my laptop absolutey trounces it.
EBS has a latency of around 2 ms.
SSD has a latency of around of around 0.25 ms.
A relational database will have around 10x the performance on an SSD compared to EBS because relational databases need to ensure data has been fully written to disk.
A separate set of behavior exists for RDS Aurora, which isn't using EBS underneath, so it might be worth looking into the Aurora latency characteristics (vs. cost, of course; Aurora ain't cheap) if you're concerned about the performance impact of EBS-vs-local-disk.
Million dollar database bills do not happen outside of the cloud world.
Laughs in Oracle per-CPU licensing terms
We used on-prem DB server and fat clients. After a long debugging session I hadn't found anything. Gradping at straws I asked what kind of network they ran.
Turned out the clients all ran on laptops connected through Wi-Fi... so yeah 10x latency turned into 10x longer job.
It's the difference between using SSD and EBS for your disk storage.
Stares at 4.4 billion row Aurora Postgres table and thinks.
We were doing ok with about ~10B rows in Postgres before deciding to switch however. Even that might be fine for some workloads but not ours.
https://www.timescale.com/blog/building-columnar-compression...
See results from Gitlab benchmarking ClickHouse vs TimescaleDB: https://gitlab.com/gitlab-org/incubation-engineering/apm/apm...
Key findings:
* ClickHouse has a much smaller data volume footprint in all cases by almost a factor of 10.
* There are very few ClickHouse queries that have >1s latency at q95. TimescaleDB has multiple >1s latencies, including a few in the range of 15-25s.
Disclaimer: I work at ClickHouse
(Timescaler)
https://www.timescale.com/blog/what-is-clickhouse-how-does-i...
This is an open source benchmark - we'd love contributions from Timescale enthusiasts if we missed something: https://github.com/ClickHouse/ClickBench/
I think perhaps because ClickHouse is a little more general purpose, it was easier to map our use case to it. Also, one thing I appreciate about ClickHouse is it doesn't feel like a black box - once you understand the data model it is very easy to reason about what will work and what will not.
I burst out laughing, thinking about our main table which was over 450 columns at that time.
Staring at a >trillion rows in a TimescaleDB hypertable on PostgreSQL.
You have an account team whether you know it or not. The account team has an SA who will be able to help out or request help from a specialist.
You might have to ask support who your rep is.
Probably better not on AWS
saved you a click
You can scale up a lot with a general purpose RDBMS like postgres on a single server, and a read replica today.
It's not perfect, or even ideal for many workloads or even all environments... But it probably can be good enough for most application needs.
It's knowing when it isn't, why it isn't, and what to use instead that counts in those instances. But I hold no blame for starting with what is probably one of the better known and understood solutions to start with.
Postgres is very very good. The vast majority of use cases work with it with very minor effort. People would, in general, be better off investing in thoroughly understanding a general tool like postgres (or similar dbs, just pick one to learn, but there are reasons why you would pick postgres over, say, oracle).
There are still reasons to use more specialized DBs. But the push for postgres is because very often the people reaching for those specialized DBs do so in error.
20M rows is practically an in-memory dataset, for example.
A fully relational time-series database like Timescale (a plugin db built on Postgres) gives you full SQL analytics, including aggregations and full relational joins with other data, which is where a lot of the value-add usually is. This also opens up the field to building multivariate machine learning models.
The amount of work you need to do to make it not worth it is quite high assuming it can be done which they seem to have accomplished pretty easily by DIYing their own provisioned iops rds (not sure why they didn't try that).
(It's been a minute, but I think we ended up using an NVMe SSD volume on k8s w/ a PG container to run a stack of low volume services for fractions of what RDS would cost. Next job was married to RDS and we constantly had issues with lack of iops for whatever we were doing, though my memory is that is wasn't anything wild. Just bursty.)
I loved everything else about it.
Edit: it was real estate market data, so only about 15 million rows.
This was one of the better solutions they had. They also had Talend for ETL and Snowflake for “analysis”. You don’t want to know what Snowflake cost.
For the same roughly 15 millions rows…
It happened because he was the only semi technical person who was an FTE. Everyone else was from a consulting firm that the private equity owners “suggested” they use.
Anything beneath a db.r6g.4xlarge get "up to" bandwidth which means credits. The RDS docs are not explicit about this as we didn't get bit by it but we could of if we had been less cautious. And I notice for the r7g instances you need to hit an 8xlarge before you get guaranteed EBS bandwith.
I'd never be moving to Aurora for performance though, you do it for the magical (but expensive) replication
Every business application uses a database and AWS charges $3000 per month for the same database that could run on your MacBook, it's beyond ridicious.
Also self host db and backup is inmensively more cheaper/faster
Always has been... the whole point of the AWS managed services is to get them to do a lot of the lifecycle management for updates/backups/restores. It's always been understood to cost more money though.
if high I/O is an issue, AWS announced I/O Optimized just last year https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-au...
> Also self host db and backup is inmensively more cheaper/faster
So is running a server in a colocation centre or your own closet. But there's a reason people opt for that less and less these days
Do they? Could be my bubble, but I’m hearing stories how moving away from clouds to bare metal dramatically lowered costs, even accounting for having to hire some sysadmins who know how to deal with this stuff.
And those who aren’t that brave are still fed up with cloud nonsense and are rebuilding cloud stuff themselves (like setting up replacements for insanely overpriced AWS NAT Gateway.)
Clouds were definitely the way to go just a few years ago - and still are, but I believe folks are more and more wary of their drawbacks. And they understand they’re not Google so they don’t really need all those insanely complex but highly scalable solutions for their stuff.
Could be just my bubble, though. I’m most definitely biased here.
Moving away from proprietary AWS services
The "bare metal" can be a VPS.
The "proprietary AWS services" are pushed hard to get lock in.
The are trade offs.
But I've heard about moving away from virtual servers specifically, or only using those for the highly elastic parts when the load fluctuates a lot. Bare metal is simply so much cheaper in the long run, if you know how to work with it. Yes, it's rigid in terms of provisioning, but it's the baseline (all clouds and VPSes have bare metal underneath) so it gives the best bang for the buck.
its a recent feature but I think RDS Optimized Reads should achieve similar improvement in performance and get the cost from 11K to 4.2K
Why not use K-V if your looking for performance?
Good example is async in Rust; people don’t like it but mostly because async is hard.
Postgres has excellent HA options, if you know what you’re trying to do with your data. CitusDB for data-warehousing storage, TimescaleDB for time-series data, and the traditional replication system for having HA (with a single write-primary) - which is the same method that Elasticsearch and ETCd are doing under the hood. Though in Elasticsearches case they do it by aggressively sharding their data set and splitting write masters across multiple nodes which has huge latency tradeoffs.
Other multi-master HA systems have trade offs (or, lie about not having tradeoffs *cough* mongo *cough*).
20M rows is so tiny it's laughable.