Cloud is insanely expensive and inefficient. Too many engineers don't understand how slow I/O back and forth is on network storage, or VMs with chatty neighbors are compared to bare metal...
Recently moved our RDS instances from r8g. to r8gd., which adds local NVMe, and I think the average I/O latency went from 1.0ms to 0.1ms. Adds up in big queries!
SQLite + NVMe feels like cheating sometimes. If my #1 requirement is lowest possible latency, I can't imagine picking anything else.
Kestrel (AspNetCore) + SQLite + NVMe all on the same machine is where things start to get really spicy in my experience. Imagine handling an entire form submission that mutates database state in tens to low-hundreds of microseconds. Total wall clock time required to service any HTTP request can be well under a single millisecond. If your average cpu time per request is 30uS, you could approach half a million per second on a 16-32 core machine.
Honestly the network-attached storage only ever made sense with HDDs, it's ridiculous to have to pay 10x penalty for both iops and latency just for some "operational flexibility". You get way simpler and much more robust architecture if you just stick to local NVMe and replication -- not that you can forgo one in the (also replicated) network block storage solution anyway...
Mostly agree, but then you have to take care of replication, failover, and backup. By using a replicated block storage, you can mostly externalize that problem.
I can't be the only one that always has to set up a separate backup system in addition to the RDS automatic backups, because it uses a weird format and is slow.
But why was EBS chosen instead of NVMe, which is faster? Since I only know how to use cloud programming and don’t understand the internal implementation, I don’t really get why it works out this way.
Is it because of ease of backup? Or data movement?
When the host dies with local storage, the VM is dead. With EBS it's not, since it simply can be booted up on another host. EBS is also replicated accross multiple hosts.
Thank you for your kind answer. I understand EBS as, in other words, virtualized storage, so then somewhere the logical block addresses have to be connected to actual physical storage devices, right? Then where and how is that mapping implemented? If you just give me the keywords, I’ll search for them myself.
Since I’m a freelance programmer, I mainly work on small-to-medium-sized projects, so I don’t know much about this kind of infrastructure programming.
Too many times I see people making the same avoidable mistakes time and time again.
They get stuck in the dream of "portable" schemas and don't use their brain to design a database-specific schema.
They have no clue how to write SQL queries (or again, have stupid dreams of "portable" queries). As a result performance suffers.
Then they come to your desk complaining the databse is slow, and insisting that they absolutely MUST have a Redis caching layer.
I'm sick and tired of people using databases as blackbox dumping grounds. If slow storage was always the problem, my life would be far less stressful. Since the prescription would be easier for the doctor to write. ;)
You can spend a week crafting the most optimal query possible, making sure the plans are perfect and all the best index types are used
And then you push it to production and get completely different results
Statistics change everything. Queries which produced beautiful plans in local and staging environments breakdown when given production statistics, some users get the same plans you did, others get nasty nested loops, indexes don't get used, and debugging and fixing it requires poking around in prod statistics which is never a good place to be. Don't even get me started on how RLS impacts query optimisation
Postgres is great but the authors refusal to add plan hints is a decision that comes to bite me at least a few times a year
Yeah, a lot of people can't figure out how to structure databases or what indices to add to them, but no, that is not the only problem just because it's the one you deal with.
> that is not the only problem just because it's the one you deal with
Sure. But to be clear: I am not denying storage is a genuine problem for some people.
I am just saying in the majority of cases it's probably not "the storage". For most people "the storage" will be quite far down the list of likely causes of a "slow" database.
Throw all the Enterprise SSDs or other storage solutions you like at a shitty schema and it will still struggle under load (or sometimes just struggle full stop).
Sit down, think about the queries you are using, and think about how the tables are updated. Now take that and rethink your indexes. Use the right index type for the right situation, don’t just slap a basic index on a serial id field and call it a day. And while you’re at it, think about the ID fields you use, sometimes better options exist. UUIDv7 is now built in, use it.
Lots of things in PGSQL benefit from thinking about how your use case is manipulating the underlying B-tree, if you work in performance sensitive stuff the devil is really in the details. And having to rebuild indexes that have decayed over time is painful.
Optimizing column layout has been enough for me to leave OLAP workloads in Postgres a few times when I otherwise would've had to spin up something dedicated for them.
> Which postgres specific schema/query features do you use to significantly improve performance?
Erm. Isn't this kind of a schema specific question ?
I mean, sometimes I see schemas that are so goddam awful that the answer to your question would be "anything, literally anything".
Other times its the old story of failing to use or misconfiguring indexes.
And then, yes you end up in the realms of Postgres specific stuff. What you do there clearly depends on what you're looking at.
But the point is, I could probably count on one hand the number of times the problem has been "the storage" or "the database". Most of the time its "the developer". :D
Postgres (and similar DBs) are a byproduct of a pre-2023 era. Give me one reason why I shouldn't get my agents to write a use-case specific DB that is almost certainly going to be faster than a general-purpose DB?
Opportunity cost. If getting rid of Postgres is going to make you more money, go right ahead. More likely though, you have other problems to be dealing with that are more directly in line with your work.
Imagine not reinventing the wheel for no reason.
But don't worry, I imagine that modern prompting techniques will be used such as "do not make mistakes" so everything will be fine (/s)
There's a balance to be had between a highly specialised custom tool that does exactly what you need now, versus a generalist tool that does what you do today and more but is less optimised for your specific use-case today. That, and standing on the shoulders of giants, knowing why those fences are there, and so on.
1. You don’t have those speed requirements, and if you do, you already know the answer to your question.
2. Data tends to be somewhat important to a lot of companies. A vibe-coded database increases your risk of losing that data.
3. Your database might have clients using different languages. Now, your vibe-coded drivers become another layer of potential problems.
4. People has been doing this since way before 2023 btw, most probably agree Postgres would be the sane choice.
try it and see :) check out that Goose language. you’ll quickly realize your agents are idiots and need reams of PDFs that YOU don’t know how to select and filter down to even have good ideas, and then will drift forever failing to “seam” the contracts between the storage, query planning, and compilation layers.
I feel its hard to prove that either way, but even the content of the article itself is full of LLM generated text, for e.g. here is one line from the conclusion "The bottom line is deliberately narrower than the title".
Yes. My experience: "Oh interesting title". "Oh god... slop galore. Disappointment, sadness, dejection, a feeling of being dismissed and not taken seriously, alienation from the world", "OK close the link".
Happy to still be here among my fellow humans. Hackernews the last bastion it feels like.
Can we somehow filter out slop from the front page? That would be great :).
some kinda system running the linked page through pangram and then putting the result in a small font next to the submission time would be perfect I think. A small font to keep the idea that such things may potentially hold value while satisfying those who aren't interested. Nobody has to argue about it themselves then because it's unambiguous, and everyone can filter them out if you choose.
I'm generally very pro-AI, including for writing, as long as the "producer" (I wouldn't want to call the human responsible the author) has vetted it and will judge based on whether something reads well rather than on whether it feels AI-written.
But using detectors to label so people can choose seems entirely reasonable to me even with a risk of false positives, and I think a lot of people who are generally pro-AI would feel that's fine.
sell a lie, blame the victim?
Kestrel (AspNetCore) + SQLite + NVMe all on the same machine is where things start to get really spicy in my experience. Imagine handling an entire form submission that mutates database state in tens to low-hundreds of microseconds. Total wall clock time required to service any HTTP request can be well under a single millisecond. If your average cpu time per request is 30uS, you could approach half a million per second on a 16-32 core machine.
optane
Since I’m a freelance programmer, I mainly work on small-to-medium-sized projects, so I don’t know much about this kind of infrastructure programming.
There. Fixed it for you.
Too many times I see people making the same avoidable mistakes time and time again.
They get stuck in the dream of "portable" schemas and don't use their brain to design a database-specific schema.
They have no clue how to write SQL queries (or again, have stupid dreams of "portable" queries). As a result performance suffers.
Then they come to your desk complaining the databse is slow, and insisting that they absolutely MUST have a Redis caching layer.
I'm sick and tired of people using databases as blackbox dumping grounds. If slow storage was always the problem, my life would be far less stressful. Since the prescription would be easier for the doctor to write. ;)
And then you push it to production and get completely different results
Statistics change everything. Queries which produced beautiful plans in local and staging environments breakdown when given production statistics, some users get the same plans you did, others get nasty nested loops, indexes don't get used, and debugging and fixing it requires poking around in prod statistics which is never a good place to be. Don't even get me started on how RLS impacts query optimisation
Postgres is great but the authors refusal to add plan hints is a decision that comes to bite me at least a few times a year
Sure. But to be clear: I am not denying storage is a genuine problem for some people.
I am just saying in the majority of cases it's probably not "the storage". For most people "the storage" will be quite far down the list of likely causes of a "slow" database.
Throw all the Enterprise SSDs or other storage solutions you like at a shitty schema and it will still struggle under load (or sometimes just struggle full stop).
Lots of things in PGSQL benefit from thinking about how your use case is manipulating the underlying B-tree, if you work in performance sensitive stuff the devil is really in the details. And having to rebuild indexes that have decayed over time is painful.
Optimizing column layout has been enough for me to leave OLAP workloads in Postgres a few times when I otherwise would've had to spin up something dedicated for them.
Erm. Isn't this kind of a schema specific question ?
I mean, sometimes I see schemas that are so goddam awful that the answer to your question would be "anything, literally anything".
Other times its the old story of failing to use or misconfiguring indexes.
And then, yes you end up in the realms of Postgres specific stuff. What you do there clearly depends on what you're looking at.
But the point is, I could probably count on one hand the number of times the problem has been "the storage" or "the database". Most of the time its "the developer". :D
On the other side if dev(ops) does not know that local storage is faster than network/cluster filesystems…
Happy to still be here among my fellow humans. Hackernews the last bastion it feels like.
Can we somehow filter out slop from the front page? That would be great :).
But using detectors to label so people can choose seems entirely reasonable to me even with a risk of false positives, and I think a lot of people who are generally pro-AI would feel that's fine.