12 ms·
Why Postgres RDS didn't work for us
- avereveard 3y ago> [we were] storing a large time-series table saved you a click
- starttoaster 3y agoPeople are willing to put in way too much work just to avoid using Prometheus or InfluxDB, aren't they?
- avereveard 3y agoTheir solution is zfs on the write master can't wait for the next blog post on how they found their data corrupted
- philkrylov 3y agoPostgreSQL does not use SEEK_DATA/SEEK_HOLE so they're ok
- bfung 3y agoOr how after they do a writer failover, they start seeing duplicate data.
- ikiris 3y agoOk, I'm out of the loop, whats the problem with zfs here?
- avereveard 3y agohttps://hn.algolia.com/?dateRange=all&page=0&prefix=false&query=Zfs%20corruption&sort=byDate&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu... the mean time between corruption article on zfs is two years
- philkrylov 3y agoLooking at your search results, there's just one recent ZFS corruption case with SEEK_DATA/SEEK_HOLE (in several HN reflections), a 2-year old Ubuntu-only buggy patch story, and some 2008 [Open]Solaris corruption.
- ikiris 3y agoMost of those links are about bad memory. If you blame bad memory for filesystem issues I don't really know what to tell you. Ignoring the poor state of the native encryption code, ZFS has had 1 corruption bug in like 10 years. Thats one of the best records for modern filesystems. I still wouldn't trust my data to btrfs by comparison.
- WJW 3y agoPosts with the basic messgage "Use nothing but postgres for everything from pubsub to background job queues, it's the best thing since sliced bread and will solve all your problems" have been a HN staple for at least a decade. It's no surprise that sooner or later people would start believing it.
- Ozzie_osman 3y agoIn all fairness, postgres still works for their workload even if RDS didn't. You can get a lot of workloads out of postgres with the right hardware, the right replication, and the right extensions (eg Citus has a columnar extension that probably would have been a pretty good fit for that).
- eximius 3y agoEh, it's a reaction against people making or reaching for the wrong tools or the right tools but at the wrong scale. 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.
- tracker1 3y agoTo be fair, we're in an age with servers that can handle hundreds of simultaneous threads on a single system with terabytes of RAM and storage faster than RAM a few generations back. 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.
- zilti 3y agoOr just TimescaleDB
- wenc 3y agoUnless the time-series was only for simple monitoring or querying, I would stay away from key-value databases like Prometheus or InfluxDB which have limited joins and limited analytics capabilities. 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.
- Spivak 3y agoWell yeah, of course. I can't understand why people reach for a bunch of bespoke databases where you need a whole other ecosystem of tooling and libraries to use and monitor, can't have transactions across them, another single point of failure, your ORMs don't mix if you use one of those. 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).
- golergka 3y agoIt's always a good idea to use a jack of all trades like PostgreSQL that you know well for a first version and migrate parts of your service to a specialised tool that you have to research later, after you're sure that you have a good product.
- hobobaggins 3y agoIt's not that pgsql wasn't appropriate, it's that the neutered AWS RDS managed instance was inappropriate. Whether more appropriate non-pgsql solutions existed seems to have been outside the scope of the article.
- anonzzzies 3y agoBut 20m rows as example. Come on: we were running that and more for ad networks begin ‘00s on a rack with Postgres and it ran fine for analytics and everything else we needed; how can it be an issue now?
- nostrebored 3y agoAs an aside to anyone who has a question like this for AWS — support is the wrong way to ask. 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.
- arjvik 3y agoHow does one get to this account team, especially if you're a very small company and using essentially a personal AWS account? (Genuinely asking because there have been times where talking to an account rep would have been incredibly helpful, but all I thought I could do was reach support.)
- nostrebored 3y agoYou can ask the support rep to find your account manager (hit or miss) or get in touch with an SDR (https://aws.amazon.com/contact-us/sales-support/ https://aws.amazon.com/contact-us/sales-support/)
- worik 3y ago> especially if you're a very small company Probably better not on AWS
- mannyv 3y agoPeople think it's hard to get to AWS people. It isn't. Ask your rep and they'll try and get you to an architect. You might have to ask support who your rep is.
- itsthecourier 3y agoAurora gave us more performance, but starting charging us for IOPS, where plain RDS wasn't. We are moving out. Also self host db and backup is inmensively more cheaper/faster
- starttoaster 3y ago> 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.
- HatchedLake721 3y ago> but starting charging us for IOPS 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-aurora-i-o-optimized/ 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
- drdaeman 3y ago> 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.
- worik 3y ago> moving away from clouds to bare metal 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.
- b2bsaas00 3y agoAgree. I run all using virtual machine and using Cloud66 for managed backups and UI. I am also considering switching to Hetzner for bare metal.
- Marazan 3y ago> When you’re storing a large time-series table (say 20 million rows) Stares at 4.4 billion row Aurora Postgres table and thinks.
- osigurdson 3y agoAgree. 20 million rows is nothing for even the most basic Postgres setup. However, at some point ClickHouse (or perhaps more dedicated ts databases) starts to make sense as the 23 byte per row overhead in Postgres weighs. Usually covering indexes are needed as well so it eventually becomes a little too much. 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.
- mfreed 3y agoCheck out how TimescaleDB adds columnar compression to PostgreSQL, typically saving 95% of storage overhead: https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.timescale.com/blog/building-columnar-compression...
- tbragin 3y agoHowever if you really want to optimize data currently residing in Postgres for analytical workloads, as the original comment suggests - consider moving to a dedicated OLAP DB like ClickHouse. See results from Gitlab benchmarking ClickHouse vs TimescaleDB: https://gitlab.com/gitlab-org/incubation-engineering/apm/apm/-/issues/4#results 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
- mfreed 3y agoThat PoC benchmark didn't turn on Timescale's columnar compression, which every real deployment uses. So misleading at best. (Timescaler)
- bakugo 3y agoIs it just standard practice to put an ugly, completely unrelated AI image at the top of every medium article now?
- eikenberry 3y agoNo mention of HA and last I checked Postgres still had no good solution for that... so I'm guessing they don't need HA and can tolerate downtime while they restore the DB cluster?
- dijit 3y agoThe issue with “no good solution” is that sometimes things are inherently hard and certain technologies don’t permit lying. 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*).
- candiddevmike 3y agoPatroni or pg_auto_failover work well enough.
- viraptor 3y agoThey mentioned Aurora. That's AWS's solution to database clusters and it can do fancy HA.
- Marazan 3y agoThat said the EBS bandwidth credits complaint is very very valid. 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
- coredog64 3y agoISTR 8xlarge is the threshold for banishing “up to” for most instance limits.
- dalyons 3y agoHmm? It’s been for the most part faster than RDS when I’ve used both the Postgres and MySQL versions. Less random slowdowns that’s for sure - the log storage system is a lot more predictable than traditional/ebs ones
- wpeterson 3y agoIf they’re optimizing full table scans of 20M+ rows, they probably want an optimized column oriented DB or a data warehousing option like Snowflake.
- imheretolearn 3y agoCame here to say this. If you use a hammer to fasten a screw, it's probably not going to work
- hobobaggins 3y agoPerhaps cockroachdb or titaniumdb would be a better choice.
- dalyons 3y agoI can’t tell if you’re trolling or not, as those are even more terrible options for analytics workloads . You must be
- spamizbad 3y agoAnd even if you want to stay in the Postgres ecosystem there's options for you there.
- gregw2 3y agoFor analytics, use a columnar database. There are even other AWS Postgres-oriented options (check the pricing first): ZeroETL from Aurora Postgres to (postgres-compatible) Redshift (Serverless?)
- Moto7451 3y agoYup. Even gross abuses of Redshift run fine with appropriate roll ups and caching. At a past job we did it “wrong enough” that it took a while for a more state of the art solution to catch up. This is not to say the abuse of Redshift should have been done, but AWS has been abused a lot and the engineers there have found a lot of optimizations for interesting workloads. 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.
- doctor_eval 3y agoWould love to see a comparison with some of the many other vendors out there. Vultr, Supabase, Tembo, …
- jprafael 3y agoI don't get the "its hard to measure throughput" line. I'm using RDS at work. At some point we had 20TB data, with daily 500GB (batch) writes into indexed tables. Same order of magnitude cost, sure. But the combination of RDS instance monitor, Performance Insights, PGadmin dashboard means you have: visual query plan with optional profilling (pgadmin), live tracking of SQL invocations with # invokes per second, avg number of rows per invocation, and sampling based bottleneck analysis (disk reads, locks, cpu, throttling, network reads, sending data to client, etc), you have per disk read/write throughput (MBps), IOPS being used, network throughput, etc. At most times what i felt lacking was the ability to understand why PG was using so much CPU/disk troughput(e.g. inserts into indexed tables) but the disk throughput the instance was under was always very visible. The article also doesnt mention anything about using provisioned IO instances. Nor any mention of which architectures have the highest PIOPs ceiling.
- hobobaggins 3y agoI think the article is saying that EBS comes with no throughput guarantees (or even estimates of what to expect)
- dekhn 3y agoIOPS times blocksize is bandwidth in my experience (on modern storage). 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.
- pritambarhate 3y ago>> 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?
- mannyv 3y agoFunny they split read and write instances out late. That should be done by default.
- mannyv 3y agoAnd 20m rows isn't a lot. Maybe they forgot to put in an index?
- hipadev23 3y agoclickhouse, influxdb, timescale, rockset, etc are all viable solutions here. a single ec2 box for ~$100/mo would give them ample room to grow. no need to split read/write. This is anything but a hard problem. Literally the defaults on the above DBs and they'd be fine. 20M rows is so tiny it's laughable.
- wutwutwat 3y agoSure, but I think most people use RDS specifically to not have to deal with the ops of maintaining a highly available, fault tolerant, snapshotted, clonable, backed by infinite block storage service. A single ec2 instance won’t fly for any company that wants to exist when an availability zone takes a nap or a developer fat fingers the wrong command in a prod psql session.
- hipadev23 3y agoManaged solutions for all of the above exist for materially lower TCO than cited in the article. It’s more about using the right tool.
- wutwutwat 3y agoThe comment I’m replying to said ec2 instance and that’s what I’m responding to. I’m aware managed services exist for these things, but that’s not what the parent comment said.
- tonymet 3y agoRDS is very expensive. 3000 iops is included on EBS (EC2) , but costs ~$300/mo on RDS. Additional iops is 20x 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.
- hobobaggins 3y agoAnd it can be far worse than that, too. Comparing to on-premise DB on real iron, that cost differential could easily be closer to 30x. 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.
- Zanfa 3y ago> RDS is very expensive. 3000 iops is included on EBS (EC2) , but costs ~$300/mo on RDS. Additional iops is 20x 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=0d612854fb94107dcb144414c7fa0bb92f89f3e9 https://calculator.aws/#/estimate?id=0d612854fb94107dcb14441...
- tonymet 3y agoIt varies by reservation status and engine
- menschmanfred 3y agoLarge? With 20 million? 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.
- dboreham 3y agoArticle has been submitted four times by the same user, so perhaps some astroturfing being done?
- frugalmail 3y agoArticle should accurately be titled "Consequences of bad technical leadership"
- ReflectedImage 3y agoI would say "AWS unsuitable for real world business applications". 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.
- Scubabear68 3y agoWas using Postgres Aurora RDS at a startup client last year, and the Aurora costs were through the roof. We had some moderately inefficient union queries that burned through credits like no tomorrow. Just a handful of users lightly using the system cost about $3,000 a month. I loved everything else about it. Edit: it was real estate market data, so only about 15 million rows.
- elteto 3y agoYou could probably keep 15 million rows in a csv file and still get great performance. Without even mentioning sqlite. Any reason to use such an overkill solution for that problem? Honest question, not passing judgement.
- Scubabear68 3y agoI inherited it from the prior CTO and team. 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…
- bomewish 3y agoThis is just so incredibly incompetent. What gives? I would expect this in government but in a company incentives are meant to be aligned.
- Scubabear68 3y agoThe prior CTO came from much larger, highly regulated startups. His only experience was very large scale systems with complex requirements. He took the approach that worked there to this much smaller, mostly unregulated startup in a very different industry. 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.
- 3y ago
- JaggerFoo 3y agoI did't get the article at first. Was the solution self-managed PostgreSQL on EC2 and EBS, it's not stated explicitly, but implied with the WAL-G reference? Why not use K-V if your looking for performance?
- ReflectedImage 3y agoPostgreSQL gives the performance if you are not running it on AWS
- web3-is-a-scam 3y agoThis article matches my experience with RDS. Performance is just absolutey atrocious when even a docker container on my MacBook performs the same queries on the same dataset at 100x the speed. M1 with 16gb of memory vs 16 vcpu Xeon with 128g, my laptop absolutey trounces it.
- deleted 3y ago[deleted]
- PrimeMcFly 3y agoThat doesn't sound right at all.
- ReflectedImage 3y agoSounds right to me. Postgres on AWS doesn't work because relational databases don't work on networked storage.
- zbentley 3y agoNot really. EBS isn't really network storage in the traditional sense. It's closer to iSCSI-attached NVMe over a dedicated low-congestion storage backbone network.
- kossae 3y agoLol I get what you’re saying but it’s funny your description is essentially “storage that is networked”.
- ReflectedImage 3y agoThis is very simple: 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.
- declan_roberts 3y agoIt’s a good article, but rolling your own is usually not advised. Who is going to manage and monitor it? Who will be in charge of upgrading it now? These often come with a sysadmin cost that is offloaded to some software engineer.
- Glyptodon 3y agoI can't comment overall on the article, and my experience is a a few years out of date, but my experience with Amazon managed DB services was that the DB iops bandwidth and limitations were so bad that you'd be better off colocating your work laptop with a DB for even moderate size projects. (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.)
- anonzzzies 3y agoThis is more rds than Postgres; if you run Postgres yourself, you can install plugins (columnar store for this example) that fix the issue.
- eezing 3y agoAlloyDB on GCP could be a good fit here.
- mt42or 3y agoThe main issue here is Postgres which cost lot of more I/O than MySQL
- eightnoteight 3y agoI don't think it would have reduced the bill much, but generally RDS is 2x costly than EC2, but I'm guessing most of the improvement the article speaks about came from ephemeral disk i.e nvme storage 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
- Attummm 3y agoPerhaps it's a clickbait title, but while reading the article, it struck me that the obvious point is that the default choice tool is not the best in all situations; engineering is about tradeoffs. It's akin to saying why a machete didn't work for us when cutting bread.