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.
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. ;)
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?
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.
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.
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
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. ;)
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.