An unexpected find that freed 20GB of unused index space (2021)

Even on RDS 20GB's price is neglectible for most companies and doesn't worth the added efforts and engineers salaries of looking into that. From DBA perspective, that's a cool find. Thanks for sharing.

Just provision more storage is usually the answer on the cloud as well unless someone has a concrete idea as to how to use less storage. Although the math is a bit trickier since it's a monthly price rather than a one time cost (although if your database is growing at a constant rate then that one time provisioning is really just a monthly price on-prem as well).

> new enterprise NVMe SSDs are about $80/TB, and DDR4 RAM is around $1.20/GB

sounds incredibly cheap

Depending on the scale and complexity, if you make no effort to control resource usage, costs grow exponentially, sometimes even without growth in business because the requirements just become more complex. For a certain optimization, you may save 1TB today, but end up saving 2TB some years down the road; just a few of these decisons you could be looking at a difference of a magnitude or more, sometimes even at larger scale. Overall there's always a balance to be struck.

Well, there's a multiplier between the nominal capacity and the capacity you actually need to purchase for your whole system. You're not buying just that 1 TB. You probably want at least two live failover servers at least, maybe more. Plus however many layers of backups and disaster recovery; a year of weekly backups makes your 1 TB into 50, even if it's offline storage.

My own company struggles with that - throwing more storage on the live db is easy, so we've kept doing that for years, but pushing around multi-terabyte backups is getting cumbersome and we're going to have to slim down the data in prod even at the cost of engineer effort.

Neither one will save you fro slower writes due to unnecessary indices.

I'm not super in the know about it, but I believe the total cost of SAN storage at my employer (on prem) is ~10X that.

This is not at all a good way to compare.

Engineering hours: there's a good chance you pay for that once, and that solves the problem.

10 SSDs: will require rack space, electricity, PCIe slots, timely replacement, management software... most of these expenses will be recurring expenses. If done once, sometimes the existing infrastructure can amortize these expenses (i.e. you might have already had empty space in a rack, you might have already had spare PCIe slots, etc.), but amortization will only work in small numbers.

Another aspect of this trade-off: systems inevitably lose performance per unit of equipment as they grow due to management expenses and increased latency. So, if you keep solving problems by growing the system, overall, the system will become more and more "sluggish" until it becomes unserviceable.

On the other hand, solutions which minimize the number of system resources necessary to accomplish a task increase overall performance per unit. In other words, create a higher-quality system, which is an asset in its own right.

> Four hours of engineering time (very roughly $1000)

Why $1000 for four hours?

Does it cost your company $150,000 per month per engineer?

> DDR4 RAM is around $1.20/GB. Four hours of engineering time (very roughly $1000) buys either 800 GB of RAM or 12 TB of storage.

$1000 doesn't buy you 800GB more of the RAM that your server likely takes, which is likely to run $3-6/GB ($3 for uncertified, $6 for motherboard manufacturer certified RAM), plus $0.50-$1/GB for the DIMM slots over and above your baseline RAM config.

It can still sometimes be smart to "throw hardware at it", but I don't think you can go from 128 GB of RAM today to 1 TB of RAM tomorrow for $1K in most cases.

> Four hours of engineering time (very roughly $1000)

In my proximity I don't know anyone who is doing any sort of Engineering work, be it dev/sysadmin/ML/datascience/whatever. I'm so astonished by the numbers of 250$/hour of fulltime job (consulting likely a different story).

I'm very much curious, what kind of Engineering role that could be, in which industry/country/company, if you can name a few and/or share some other info.

In previous discussion of this article, an HN user did the math: pg12 is 16 bytes per NULL and pg13 is 6.32 bytes per NULL. https://news.ycombinator.com/item?id=25989467 So definitely some pretty significant savings there.

> if you work at a startup or early stage company, it is nearly always the better decision to throw more disk space at a storage problem like this than worry about optimizing for size. Developers are expensive, disk space is cheap

This is great advice. In general at the beginning it is better to keep things as simple as possible.

At one fast growing startup I worked at, one of the founders insisted we kept upgrading just one machine (plus redundancy and backups). It was a great strategy! Kept the architecture super simple, easy to manage, easy to debug and recover. For the first 5 years of the company, the whole thing ran on one server, while growing exponentially and serving millions of users globally.

After seeing that, it’s clear to me you should only upgrade when needed, and in the simplest, most straightforward way possible.

There’s a lot of emphasis here about just adding storage, but that’s missing the fact that unnecessary indexing affects writes and reads performance also (and potentially quite significantly).

Using a partial index where it’s obviously matches the use case (like when a majority of values are null) is just correct modeling and should not be considered a premature optimization or waste or developer time.

The fact that this is news to you makes me feel better about not considering it myself. I suspect we have a few large indexes that could be given the same treatment.

Double a very small number. If that's seriously an issue use a faster disk. So many people trying to run a DB on EBS with less IOPS than my PC from 2015.

I manage plenty of DBs with hundreds of millions of records and 40+ indexes per table/collection...

Insertion should still be extremely fast for such a small index, right?

Doing binary search over a b-tree page is <100 cycles. b-tree traversal over 100M records should still be measured in microseconds and binary search over that should be in microseconds too if not in 100s of nanoseconds.

Only if you used null to represent the common value, which generally isn’t a good idea.

An index is most efficient when it represents a unique set of values. It's still very useful to have an index for grouping, but if the groups represent a severe minority then you will end up wasting a lot of space and cycles searching through the index.

Couldn’t you create separate indexes for the 0 and non-0 cases?

Yes, Postgres keeps track of the most common values. If it knows 0 is common and "where val = 0" will keep 90% of rows it might choose to use a table scan instead of the index.