3 ms·

Wow, thanks for this great writeup! I thought this was really useful not just from the point of the "partial index 'find'" that is the focus of the post (but th

Wow, thanks for this great writeup! I thought this was really useful not just from the point of the "partial index 'find'" that is the focus of the post (but that was a great point and thing to be aware of), but from the general overview of good techniques and things to be aware of in postgres if you're worried about using space inefficiently. Saving this one for reference! One minor "word to the wise", cause I thought this was a great post but also has the potential to be misused: 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.

nico 3y ago
> 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.
jtc331 3y ago
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.
- lmm 3y ago
  > 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). Again, for a startup or early stage company, spending developer time on read and write performance is almost certainly a waste. (Obviously if it's bad enough to be a blocker then fix it, but if it's bad enough to be a blocker then you've noticed it already).

nico 3y ago

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

jtc331 3y ago

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.

lmm 3y ago
> 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). Again, for a startup or early stage company, spending developer time on read and write performance is almost certainly a waste. (Obviously if it's bad enough to be a blocker then fix it, but if it's bad enough to be a blocker then you've noticed it already).