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...

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.

CloudNativePG supports Kubernetes Local Storage as one of its main features [0]

  Persistent volume management, including:
    Support for Local Persistent Volumes with PVC templates.
    Reuse of Persistent Volumes storage in Pods.
    Separate volumes for WAL files and tablespaces.

> While the standard approach ensures service reliability and leverages Kubernetes' self-healing capabilities, there are scenarios where operating with a temporarily degraded cluster may be acceptable. This is particularly relevant for PostgreSQL clusters relying on node-local storage, where the storage is local to the Kubernetes worker node running the PostgreSQL database. Node-local storage, or simply local storage, is employed to enhance performance.

[0] https://cloudnative-pg.io/docs/devel/#main-features [1] https://cloudnative-pg.io/docs/devel/kubernetes_upgrade/#tem...

"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.

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!

This is the fundamental issue with networked storage: all apps on top assume small io is fast an local, so network latency doesn't amortize.

With application-level replication, you can amortize it to a few steps (e.g. on commit, do 2PC).

Obviously there's nuance, esp if you have to read from another node, but it's why moving replication as far up the stack as possible generally performs better with DBs -- the DB knows when to touch the network better than the disk.

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...

I was using postgres for embarrassingly long time before I've learned the difference between "index scans" and "index only scans".

Including relevant columns in the index can massively improve the performance, because postgres does not need to read the randomly sorted tuple in the original table.

I'd like clouds to offer NVMe with value-add features like atomic snapshots and asynchronous replication on top.

io2 has lower latency than gp3, and better durability. We switched workloads off gp3, didn’t even need to provision crazy iops. It had a cascading effect of calming down.

Postgres isn't slow, your data just isn't in the cache.

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.

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.

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?

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.

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.

Sounds like blame shifting to me.

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?

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

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.