141–150 of 159 posts
I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39/month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs. For security, I implemented TailScale which adds only ~5ms of latency while completely eliminating public network exposure - a worthwhile tradeoff for the significant s…
Earlier quoted context omitted.
Samsung 9910 has a 1:1 TB:GB cache size of LPDDR4X memory. I won't pretend to understand the magic NVMe drives possess, but if you got a 4TB or 8TB 9910, could you not in theory pull in all of the data you require to cache? I would assume, and it might be a poor assumption, that NVMe controllers don't pull in files, but rather blocks, so even if you had a database that exceeded cache size, in theory if the active blo…
The DRAM on a SSD like that isn't for caching user data, it's for caching the drive's metadata about which logical blocks (as seen by the OS) correspond to which physical locations in the flash memory.
Very nicely written post! I'd love to start running these in production on NVMe and hope its something major cloud providers start to offer ASAP. The performance gains are _extremely_ attractive
Does anyone know when the update allowing more concurrent connections is dropping, so we can stop using pgbouncer?
Earlier quoted context omitted.
Indexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrel…
On commercial databases from Oracle and Microsoft, you can cluster the DB on any index. Really would love for Postresql to do the same.
CLUSTER table_name USING index_name;
Earlier quoted context omitted.
For a long time ago there have been APIs to do asynchronous file I/O on the books for Linux but they weren't worth using because they didn't really speed anything up.
IIRC they literally just did the sync I/O on a worker thread.
Earlier quoted context omitted.
Apples to apples, Postgres might lose, but that'd be tying both hands behind its back first. Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust. Imagine writing a benchmark comparing programming languages, but the benchmark only includes idiom…
Yes, but I wonder how many apps using ORMs actually use all features from postgres. So, in the practical use case of typical Rails, Laravel, Django or expressJS, what would the performance look like?
Earlier quoted context omitted.
I noticed this with bandwidth. AWS price for bandwidth: $90.00/TB after 0.1TB/month. Price everywhere else (low cost VPSes): $1.50/TB after 1-5TB/month. Price some places (dedicated servers): $0.00/TB up to ~100TB/month, $1.50/TB after. You pay 60 times the price for the privilege of being on AWS. Bandwidth is just their most egregious price difference. The servers are more expensive too. The storage is more expensiv…
Which is why most folks use CloudFront like AWS recommends, which is typically free for services to route through. Prices for CloudFront egress are competitive with what you described for other vendors. I don't know anyone paying $90/TB out to internet on AWS. https://aws.amazon.com/cloudfront/pricing/?nc=sn&loc=3 Were you trying to route S3 directly out to the internet?
Earlier quoted context omitted.
FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the of…
Ah yeah, getting good kernel userspace oneshot memcpy performance for large files is surprisingly hard. mmap has setup/teardown overhead that's significant for oneshot transfers, regular read/write calls suffer from page cache/per page overhead. Hopefully all the large folio work in the kernel will help with that.
You might also just keep around the file mapping until memory/address space pressure requires, and at that point MAP_FIXED over it.