Rendered at 11:19:48 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
mmnfrdmcx 2 hours ago [-]
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...
dikei 2 hours ago [-]
It's all about trade off.
Do you want to spend thousands of dollar upfront for that DB server with sweet sweet NVME SSDs, of which your application might not even use 1% before the app get scraped?
sdcfgy 1 hours ago [-]
Back in the old days we chucked it all on one box. Then scaled up when we needed to.
If you can’t afford to throw down enough money on a single box to bring a business up then you’re fucked anyway.
I know whole businesses that make a lot of money and never needed more than that box. (And a hot spare)
kqr 1 hours ago [-]
I would have argued against you a couple of years ago, because I've also built stuff this way and it required a developer or two to care at least a little bit about database administration, and few of the one's I've met do.
However, these days, I notice my DBA skills atrophying because LLMs have encoded most of the answers and diagnostics I need in their weights. Coupled with something like Nix, spinning up new servers including backups and everything, it's easier easier than ever to run your own production database instance.
sdcfgy 6 minutes ago [-]
It's easy doing a lot of stuff you don't understand quickly these days. But when the shit hits the fan and you have customers you need to know what you're doing.
Hiding behind the LLM, or even a cloud service like RDS makes you think you can get out of a corner, but you are nowhere near that. Practice, drills, experience are the only solution here. The illusion of that is dangerous.
I spent a couple of days recently digging one of our associated businesses out of the shit because they killed a postgres on RDS instance (AWS bug!) and couldn't google or LLM their way out of it. It hit the trade press and did a lot of damage to them.
If you're not going to do the work, don't take on customers.
Tanjreeve 25 minutes ago [-]
Don't LLMs make it easier to write all the low level/boring stuff that makes managing a database deployment directly viable without the middleman?
c0n5pir4cy 1 hours ago [-]
I built my own server for home last year, all consumer hardware (AMD Ryzen, 96GiB RAM, NVME SSD, HDDs etc). Total cost was ~£3,000 at the time - £6000 up front cost for a business seems reasonable; and you could get away with less.
However, the main cost would be colocation - it's risen quite a bit.
sdcfgy 10 minutes ago [-]
Just buy one off Hetzner or something.
I wouldn't use Ryzen on a production server myself.
sgt 53 minutes ago [-]
You also fail to realize that even managing a physical DB server is much easier now with AI, enabling people to more easily understand what their infrastructure needs are, what maintenance is needed, how things can be automated.
mlnj 41 minutes ago [-]
True. We do have to remember that this is only possible in the last few months. The costs to manage more and more infrastructure is steadily going down.
maeln 1 hours ago [-]
You can rent bare metal server for that. The real advantage of cloud solution is a cohesive offer, ease of scalability, and many other things which boils down to "we take care of things for you". It can be worth the $, i can also be a huge waste of $.
mitxela 32 minutes ago [-]
Ease of scalability - "it starts at 0.01x, but adding extra increments of 0.01x is easy, provided your app is designed for horizontal scalability"
haileys 49 minutes ago [-]
No, you can just rent a VPS with local NVMe storage if even renting bare metal is too much.
tecleandor 16 minutes ago [-]
And yet we do multi year purchase commitment contracts with cloud providers that break that "flexibility" premise.
With the multi million dollars A MONTH purchase commitment contracts I've seen in my previous employers, you could use a single month budget to buy hardware that could serve our applications for years.
mcapodici 49 minutes ago [-]
Sounds cheaper than AWS :)
mitxela 34 minutes ago [-]
So like, 0.5 extra salary months to make sure the whole company's app is fast?
dizhn 1 hours ago [-]
Why are you paying for hardware that might not even be used ? If you get an unexpected burst of demand that's not the worst problem to have. You can even jump on a cloud for a few months.
perching_aix 2 hours ago [-]
> Too many engineers don't understand how (...) VMs with chatty neighbors are compared to bare metal
sell a lie, blame the victim?
bob1029 3 hours ago [-]
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.
Havoc 2 hours ago [-]
>If my #1 requirement is lowest possible latency, I can't imagine picking anything else.
optane
globnomulous 54 minutes ago [-]
Intel P5800X user, checking in. The latency improvement when I switched from a Samsung 990 to the P5800X wasn't quite as mind-blowing or revelatory as the HDD to (SATA) SSD switch was ~16 years ago (OCZ Vertex LE -- $300 for 100 GB!) but compared to standard nvme it still feels like a dramatic improvement. It's startling to realize how much performance we're leaving on the table even with nvme ssds. Build times for what I work on dropped by much more than 50%, if I remember correctly, when I switched and everything on my system feels more responsive.
Edit: optane also shows what the SSD/nvme sequential-throughput marketing wars have cost us. Latency and random 4k iops always should have been the focus (in research, development, reviews, and benchmarks), not sequential throughput.
zozbot234 27 minutes ago [-]
There's rumors floating around that Chinese firms will soon be bringing new PCM storage solutions to the datacenter market. Not nearly as good as XPoint/Optane, but should nonetheless be lower latency than Flash and much better endurance (DWPD).
If the pricing is competitive it will easily be replacing DRAM for many lower-level uses, thanks to its support for better PCIe standards (5.0 at least, while aftermarket Optane is stuck on 4.0).
stefanfisk 15 minutes ago [-]
Where can I read more about these rumors?
sgt 52 minutes ago [-]
Have you checked out DuckDB for time series data?
arethuza 2 hours ago [-]
What approach do you take for backups?
bob1029 1 hours ago [-]
Windows server storage replica in async mode.
Kinrany 1 hours ago [-]
What happens when a new version is deployed?
CharlieDigital 16 minutes ago [-]
"Back in the day", we used to have to carefully plan the underlying storage array to eek out every ounce of performance from the database server.
I think there's a generation of devs that just takes big memory pools and SSDs for granted; there's a mental disconnect to the fact databases map the underlying storage.
It becomes obvious once I bring it up with them in discussion and I can almost see the bulb go off (happened while discussing partitioning and sharding), but they never make the connection that a query is mapping to some artifacts sitting on storage.
youngtaff 3 minutes ago [-]
Had a recent conversation with someone about something similar…
In our case the primary index on a table was a something like a sequential ID but the table stored data from multiple tenants so each page had data from multiple tenants
Took a while to convince people that the DB was always going to read 16KB chunks so we could reduce IO by changing the primary index so it was based around an ID that was unique to the tenant
iancarroll 3 hours ago [-]
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!
nasretdinov 3 hours ago [-]
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...
dikei 1 hours ago [-]
NAS/SAN still make sense when you need to share storage between multiple DBs for cost efficiency: most application DBs are tiny compared to the size of an SSD.
ngrilly 3 hours ago [-]
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.
jonatron 3 hours ago [-]
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.
vasco 3 hours ago [-]
And because those backups lifecycle is usually tied to the instance itself. Someone deletes your instance and your backups are gone as well.
icedchai 2 hours ago [-]
It’s kinda crazy that this is the case, and then they make it sooo convoluted to copy those backups elsewhere. Typical cloud.
ngrilly 48 minutes ago [-]
That's scary! And wrong...
glimshe 46 minutes ago [-]
Postgres isn't slow, your data just isn't in the cache.
mitxela 31 minutes ago [-]
When writing, it needs to fsync to ensure ACID
glimshe 26 minutes ago [-]
But no database that ensures ACID can avoid that one.
If that's the problem, we should say "Postgres isn't slow, you're just on ACID".
sausagefeet 14 minutes ago [-]
Is that entirely true? If you have a replication protocol like Viewstamped Replication you have the option to make a trade off for how many machines you the will fail concurrently vs forcing an fsync.
I think I'm a bit of an outlier here on that k never really understood why durability requires fsync. It doesn't help you if the storage dies. If durable storage is important to you, then isn't the only solution to synchronously replicate to a quorum of machines at which point an fsync isn't that relevant? What am I missing?
CodesInChaos 3 hours ago [-]
I'd like clouds to offer NVMe with value-add features like atomic snapshots and asynchronous replication on top.
nubinetwork 1 hours ago [-]
For what its worth, some databases just run like trash. I've put a database on nvme raid and still had some queries take 5 seconds.
crabbone 1 hours ago [-]
I recently interviewed for a company (for the purpose of not making this an advertisement, I will not name it) that uses FPGAs to speed up SQL queries. They claimed at least two orders of magnitude speedups for joins!
They also told me that they've started as a h/w shop and that they have competitors who are building ASICs rather than programming FPGAs to do the same (with, potentially, even more performance gains).
This completely changed the perspective for me. Before, I had this idea that if I need to optimize database performance, I'd first look at indices. Then, at how much memory is available to the database for various tasks. Only after that, I'd look at I/O performance, but, mostly, because of fsync configuration.
It wouldn't even appear to me as a possibility that the compute can become the culprit of optimization. But, apparently, it's a new hot thing.
* * *
From my experience with storage testing, which also included quite a bit of pg-bench exposure... My impression was this: the sort of query that is used for performance benchmark matters the most. It will define the optimization direction. Blindly trying to add or speed-up one resource or another is not going to be an efficient strategy. In the database world, there's this idea of an "ideal" benchmark. Without too much detail, it imagines an "ideal" business accounting back-end, the sort of queries it would have to execute. It defines an "ideal" unit that roughly encompasses one "sale", and then it measures how many such "sales" a business can perform per unit of time. This will translate into some ratio of selects / inserts / deletes / updates with some index hits and some linear searches etc. But it's all extremely vague and it's very hard to adapt this benchmark to the actual business needs of the database being tested.
There's also a question what to optimize for: the resource usage? the response speed? the concurrency? In some scenarios, I can see how storage would factor in, but there are also certainly scenarios where using a faster, in some sense, storage will not result in any meaningful gains.
justincormack 35 minutes ago [-]
There was a whole bunch of work on GPUs for databases before GPUs became hard to get, which slowed it down a bit. A lot of the columnar database performance gain is from vectorization. Now which direct pcie DMA to GPU from NVMe this is even more attractive. FPGA seems much more work than GPU.
Rapzid 35 minutes ago [-]
Why would an FPGA speed up joins by two orders of magnitude?
jdw64 2 hours ago [-]
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?
nik736 2 hours ago [-]
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.
jdw64 2 hours ago [-]
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.
icedchai 2 hours ago [-]
Look up “SAN” or “storage area network.” EBS looks like a regular, local block device, another disk in /dev, but is actually served over the network. The implementation is transparent.
throw0101a 4 minutes ago [-]
> Look up “SAN” or “storage area network.” EBS looks like a regular, local block device, another disk in /dev, but is actually served over the network.
When I think of "SAN" I think of Fibre Channel, which can be quite fast. EBS sounds more like iSCSI.
jdw64 1 hours ago [-]
Thank you for taking the time to let me know the keywords. I’m grateful for your kindness, and have a good day.
mike_hearn 1 hours ago [-]
I'm an incorrigible contrarian so this seems like a good opportunity to shill for Oracle, where I currently have a part time gig doing research in the database group and am thus highly financially conflicted; read what follows with that in mind.
Well written databases are frequently I/O bottlenecked and getting a good storage/network setup can thus grant massive performance gains. But this is hard, operationally. The difficulty of developing the right opinions about hardware design, acquiring it, properly wiring it up, tuning it and ensuring really excellent performance across the stack was recognized by Oracle decades ago, which led to the creation of ExaData.
ExaData is a bit like Oxide, if you've encountered them. It's basically a cloud database in a pre-manufactured rack. You buy an ExaData rack and it turns up at your on-prem colo facility, you plug it in, and now you have a fast database. Internally it has a dedicated high speed InfiniBand or RoCE RDMA capable backplane network and handles replicated between storage devices itself. There's an alerting system and repl-like tool that guides you through swapping out failed storage devices, you can expand it by adding more racks, and there are various cloud hosted versions of all of this if your budget doesn't stretch to your own full system.
The software stack on top of that provides fast multi-master synchronous writes by using RAM as a shared disk cache. If a node needs to read a block off disk it will prefer to read it directly out of the RAM of another node using RDMA across the dedicated backplane than go to storage, so if you buy the high capacity variant then you have a three tier storage system: HDDs for cold data, flash as a cache and DRAM as another cache on top of that, all kept in sync using cache coherency protocols.
There are lots of optimizations on top of that which a Postgres-on-EBS architecture can't match, for example, the storage nodes that handle the disks and serve blocks can have some of the query computations pushed down to them to reduce IO across the backplane and accelerate query execution. A final 'optimization' is that Oracle uses a different MVCC algorithm that doesn't require vacuuming and can be more IO efficient in general (doesn't copy whole rows); there are patch sets that switch Postgres to an Oracle-style MVCC engine but none of them were ever merged.
The whole thing acts as a single coherent database, so e.g. rolling upgrades across the rack's cluster don't cause downtime because transactions can be handed off across nodes. Clients can discover the IP addresses of the individual servers automatically and shift their traffic across nodes in response to load balancing requests from the system, etc.
I'm not sure it's a good idea to run a Postgres cluster like the one described in the article unless you have a lot of appetite for manual operations. If you can tolerate downtime or if you're renting bare metal where you can configure a proper RAID setup, then OK, but if you care a lot about query performance to begin with then a commercial database will win in a lot of ways (doesn't have to be Oracle, commercial RDBMS in general is a much more competitive space than open source).
Obligatory disclaimer: opinions above are not corporate approved.
matthorse 1 hours ago [-]
I think that cloud made people forget how fast computers actually are. Hardware specs got reduced to "vCPUs" count, which isn't a useful abstraction if you are doing anything beyond basic stuff. Similarly, network attached storage became the default, and we started treating its latency as Postgres’s latency. These abstractions made choices seem simpler, and in an era of cheap hardware that was fine, since you could just throw more resources at the problem.
safeer_malik 1 hours ago [-]
[flagged]
traceroute66 3 hours ago [-]
Postgres isn't slow, your schema and queries are.
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. ;)
s08148692 2 hours ago [-]
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
nijave 8 minutes ago [-]
Mostly agree although you should probably spend a bit more time testing on a more realistic setup.
At least run some plans on the real database with real sized data and real statistics.
Similar to how mocks/stubs are great until they aren't.
That won't magic away statistics problems, but it's still good practice to acknowledge the lower app environments tend to not reflect performance of the production one.
smolder 3 hours ago [-]
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.
traceroute66 2 hours ago [-]
> 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).
CodesInChaos 3 hours ago [-]
Which postgres specific schema/query features do you use to significantly improve performance?
smitty1110 2 hours ago [-]
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.
traceroute66 3 hours ago [-]
> 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
boxed 2 hours ago [-]
A good schema and good queries won't save you if your storage is physically far enough away :P
nijave 6 minutes ago [-]
Good storage won't save you if your schema and queries are bad enough, either.
OroPla 2 hours ago [-]
Sounds like blame shifting to me.
rfgplk 3 hours ago [-]
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?
esposm03 3 hours ago [-]
Reliability. A general-purpose DBMS probably has multiple decades of battle-testing, your vibecoded DBMS doesn't.
alessandroberna 2 hours ago [-]
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)
nijave 6 minutes ago [-]
Why stop at DB? May as well just write a purpose built OS from scratch.
solyin 2 hours ago [-]
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.
TheCapeGreek 2 hours ago [-]
For the same reason I want my car to have FSD instead of my FSD to know how to assemble a car to drive by itself.
dkdbejwi383 3 hours ago [-]
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.
cmrx64 2 hours ago [-]
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.
conradfr 2 hours ago [-]
Who is stopping you?
techpression 2 hours ago [-]
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.
dizhn 1 hours ago [-]
You just don't write some class of software unless that is the product itself. DBs are one of them. Another one is operating systems. It takes a huge level of domain knowledge, real life use and time.
xueyu 2 hours ago [-]
[dead]
amitpoonia19xyz 4 hours ago [-]
It feels jarring to see these LLM generated blog titles everywhere, cant bring myself to read the content of it even if I am interested.
dewey 4 hours ago [-]
That’s a pretty common title format for talks or blog posts long before LLMs existed.
TuningYourCode 3 hours ago [-]
I support this claim. Also the title already includes everything relevant compared to „we made Postgres 20x faster“.
On the other side if dev(ops) does not know that local storage is faster than network/cluster filesystems…
amitpoonia19xyz 3 hours ago [-]
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".
arethuza 3 hours ago [-]
I'm starting to find complaints about AI generated content more annoying than the content being complained about.
tomnipotent 2 hours ago [-]
Agreed. Almost every submission has someone claiming it's AI now, it's pretty exhausting.
joduplessis 3 hours ago [-]
That's a load-bearing statement.
kaon_2 3 hours ago [-]
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 :).
RugnirViking 3 hours ago [-]
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.
vidarh 3 hours ago [-]
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.
anonymous_sorry 59 minutes ago [-]
Perhaps LLM users who are open and happy to enable reader choice could label AI-assisted writing as such.
Do you want to spend thousands of dollar upfront for that DB server with sweet sweet NVME SSDs, of which your application might not even use 1% before the app get scraped?
If you can’t afford to throw down enough money on a single box to bring a business up then you’re fucked anyway.
I know whole businesses that make a lot of money and never needed more than that box. (And a hot spare)
However, these days, I notice my DBA skills atrophying because LLMs have encoded most of the answers and diagnostics I need in their weights. Coupled with something like Nix, spinning up new servers including backups and everything, it's easier easier than ever to run your own production database instance.
Hiding behind the LLM, or even a cloud service like RDS makes you think you can get out of a corner, but you are nowhere near that. Practice, drills, experience are the only solution here. The illusion of that is dangerous.
I spent a couple of days recently digging one of our associated businesses out of the shit because they killed a postgres on RDS instance (AWS bug!) and couldn't google or LLM their way out of it. It hit the trade press and did a lot of damage to them.
If you're not going to do the work, don't take on customers.
However, the main cost would be colocation - it's risen quite a bit.
I wouldn't use Ryzen on a production server myself.
With the multi million dollars A MONTH purchase commitment contracts I've seen in my previous employers, you could use a single month budget to buy hardware that could serve our applications for years.
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
Edit: optane also shows what the SSD/nvme sequential-throughput marketing wars have cost us. Latency and random 4k iops always should have been the focus (in research, development, reviews, and benchmarks), not sequential throughput.
If the pricing is competitive it will easily be replacing DRAM for many lower-level uses, thanks to its support for better PCIe standards (5.0 at least, while aftermarket Optane is stuck on 4.0).
I think there's a generation of devs that just takes big memory pools and SSDs for granted; there's a mental disconnect to the fact databases map the underlying storage.
It becomes obvious once I bring it up with them in discussion and I can almost see the bulb go off (happened while discussing partitioning and sharding), but they never make the connection that a query is mapping to some artifacts sitting on storage.
In our case the primary index on a table was a something like a sequential ID but the table stored data from multiple tenants so each page had data from multiple tenants
Took a while to convince people that the DB was always going to read 16KB chunks so we could reduce IO by changing the primary index so it was based around an ID that was unique to the tenant
If that's the problem, we should say "Postgres isn't slow, you're just on ACID".
I think I'm a bit of an outlier here on that k never really understood why durability requires fsync. It doesn't help you if the storage dies. If durable storage is important to you, then isn't the only solution to synchronously replicate to a quorum of machines at which point an fsync isn't that relevant? What am I missing?
They also told me that they've started as a h/w shop and that they have competitors who are building ASICs rather than programming FPGAs to do the same (with, potentially, even more performance gains).
This completely changed the perspective for me. Before, I had this idea that if I need to optimize database performance, I'd first look at indices. Then, at how much memory is available to the database for various tasks. Only after that, I'd look at I/O performance, but, mostly, because of fsync configuration.
It wouldn't even appear to me as a possibility that the compute can become the culprit of optimization. But, apparently, it's a new hot thing.
* * *
From my experience with storage testing, which also included quite a bit of pg-bench exposure... My impression was this: the sort of query that is used for performance benchmark matters the most. It will define the optimization direction. Blindly trying to add or speed-up one resource or another is not going to be an efficient strategy. In the database world, there's this idea of an "ideal" benchmark. Without too much detail, it imagines an "ideal" business accounting back-end, the sort of queries it would have to execute. It defines an "ideal" unit that roughly encompasses one "sale", and then it measures how many such "sales" a business can perform per unit of time. This will translate into some ratio of selects / inserts / deletes / updates with some index hits and some linear searches etc. But it's all extremely vague and it's very hard to adapt this benchmark to the actual business needs of the database being tested.
There's also a question what to optimize for: the resource usage? the response speed? the concurrency? In some scenarios, I can see how storage would factor in, but there are also certainly scenarios where using a faster, in some sense, storage will not result in any meaningful gains.
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.
When I think of "SAN" I think of Fibre Channel, which can be quite fast. EBS sounds more like iSCSI.
Well written databases are frequently I/O bottlenecked and getting a good storage/network setup can thus grant massive performance gains. But this is hard, operationally. The difficulty of developing the right opinions about hardware design, acquiring it, properly wiring it up, tuning it and ensuring really excellent performance across the stack was recognized by Oracle decades ago, which led to the creation of ExaData.
ExaData is a bit like Oxide, if you've encountered them. It's basically a cloud database in a pre-manufactured rack. You buy an ExaData rack and it turns up at your on-prem colo facility, you plug it in, and now you have a fast database. Internally it has a dedicated high speed InfiniBand or RoCE RDMA capable backplane network and handles replicated between storage devices itself. There's an alerting system and repl-like tool that guides you through swapping out failed storage devices, you can expand it by adding more racks, and there are various cloud hosted versions of all of this if your budget doesn't stretch to your own full system.
The software stack on top of that provides fast multi-master synchronous writes by using RAM as a shared disk cache. If a node needs to read a block off disk it will prefer to read it directly out of the RAM of another node using RDMA across the dedicated backplane than go to storage, so if you buy the high capacity variant then you have a three tier storage system: HDDs for cold data, flash as a cache and DRAM as another cache on top of that, all kept in sync using cache coherency protocols.
There are lots of optimizations on top of that which a Postgres-on-EBS architecture can't match, for example, the storage nodes that handle the disks and serve blocks can have some of the query computations pushed down to them to reduce IO across the backplane and accelerate query execution. A final 'optimization' is that Oracle uses a different MVCC algorithm that doesn't require vacuuming and can be more IO efficient in general (doesn't copy whole rows); there are patch sets that switch Postgres to an Oracle-style MVCC engine but none of them were ever merged.
The whole thing acts as a single coherent database, so e.g. rolling upgrades across the rack's cluster don't cause downtime because transactions can be handed off across nodes. Clients can discover the IP addresses of the individual servers automatically and shift their traffic across nodes in response to load balancing requests from the system, etc.
I'm not sure it's a good idea to run a Postgres cluster like the one described in the article unless you have a lot of appetite for manual operations. If you can tolerate downtime or if you're renting bare metal where you can configure a proper RAID setup, then OK, but if you care a lot about query performance to begin with then a commercial database will win in a lot of ways (doesn't have to be Oracle, commercial RDBMS in general is a much more competitive space than open source).
Obligatory disclaimer: opinions above are not corporate approved.
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
At least run some plans on the real database with real sized data and real statistics.
Similar to how mocks/stubs are great until they aren't.
That won't magic away statistics problems, but it's still good practice to acknowledge the lower app environments tend to not reflect performance of the production one.
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.