6 ms·

Making indexes smaller is nice even when you have a ton of storage, as then more can fit into the hot set. However as someone who runs TB of databases, "just pr

by aftbit 3y ago

Making indexes smaller is nice even when you have a ton of storage, as then more can fit into the hot set. However as someone who runs TB of databases, "just provision more storage" is always a valid option. Especially if you are outside the cloud. If you have your own hardware, new enterprise NVMe SSDs are about $80/TB, and 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.

krembo 3y ago
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.
rockostrich 3y ago
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).
_a_a_a_ 3y ago
> new enterprise NVMe SSDs are about $80/TB, and DDR4 RAM is around $1.20/GB sounds incredibly cheap
dchftcs 3y ago
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.
vikingerik 3y ago
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.
- Dylan16807 3y ago
  Are you putting indexes into your backups?
  - krab 3y ago
    pg_basebackup is, like any physical replication of Postgres.
  - deleted 3y ago
    [deleted]
  - nine_k 3y ago
    Backing up indexes makes sense if you care about restoring the DB quickly.
- danielheath 3y ago
  Weekly full backups are quite large vs eg ZFS snapshots, right?
- ilyt 3y ago
  > a year of weekly backups makes your 1 TB into 50, even if it's offline storage. 20TB (~18GB usable) drive is $370 + whatever power it will use 6 of them in RAID6 will give you 72TB backup storage, add extra copy and say extra drive as spare and that's 13*370 = $4810 USD for ~5 years + whatever power it will use, ~50-55W if idling most of the time and just writing backup once a day I think people got completely disconnected from how it actually costs to run something because of vastly higher resell value of storage and bandwidth in the cloud. > 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. ...but that's definitely a bigger part of that issue, cloud or not. Backup is slow, restore is slow, anything that involves any of the two is also slow
paulddraper 3y ago
Neither one will save you fro slower writes due to unnecessary indices.
pgwhalen 3y ago
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.
- ilyt 3y ago
  No wonder. SSD gives you raw storage. You're losing some (at worst could be even 50%) for redundancy just to start. SAN Flash controllers are hideously expensive because they need to push tens/hundreds worth of SSDs to tens of servers. If you can solve redundancy other way (say via pair of replicated databases), a pair of servers with local storage will be far more $ effective
  - pgwhalen 3y ago
    True, but it's not as though the 10X expense is for nothing. A SAN enables a lot more than local storage. I didn't mean to suggest that there wasn't a way to achieve lower costs on storage hardware directly, just that in practice, for a variety of reasons, that quoted cost is not representative.
    - ilyt 3y ago
      And in case of database it doesn't give you much. You still need 2 nodes for redundancy, if you don't need a rack's worth of storage for that database, using SAN for that is waste of money. I know the corporate idea of "everything on SAN, nothing outside of SAN" very well, it's just plainly solving political problems, not techncial ones.
      - pgwhalen 3y ago
        Your understanding of SAN usage is narrow, and incorrect in the case of my organization. One major benefit that SAN-backed database storage provides us is, when combined with VMs, enables us to spin up another database instance against already existing data in the SAN (i.e. a staging DB that looks at prod data).
crabbone 3y ago
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.
- ilyt 3y ago
  I mean I'm also proponent of "doing it right" most of the time, but throwing hardware at the problem is mighty fine solution in sub-TB areas > 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. 10NVMe SSDs fit into 1U. The cloud always comes out terrible on hardware. The cloud is not selling hardware, it's terrible to buy any hardware in cloud, it sells spike capacity, and it sells software running on it. There are many services in cloud that would be a lot of work to create (even if you need just small part of functionality) on-site but if you don't use it and just need to throw raw hardware at the problem consistently, a rack with hardware will be far cheaper solution even after incurring the engineering cost to deploy and automate it. We have 7 racks and time to manage hardware is some minuscule fraction, and time to manage automation on it is still small part of our time, and we're just ops team of 3.
codetrotter 3y ago
> Four hours of engineering time (very roughly $1000) Why $1000 for four hours? Does it cost your company $150,000 per month per engineer?
- sokoloff 3y ago
  How do you get from $250/hr to $150K for ~160 hours?
  - codetrotter 3y ago
    Because I just woke up from sleeping so I made a mistake in the calculation. My bad heh
sokoloff 3y ago
> 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.
- whoiscroberts 3y ago
  You mean you can’t buy ram off Newegg sorted by cheapest and just plop it in your server?
CoolCold 3y ago
> 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.
- aftbit 3y ago
  I typically calculate the cost of engineering hours as about 2x their salary costs. This captures the additional employer-paid taxes, benefits, etc, as well as ensuring that I don't under-value the cost, which is typically the bigger threat when providing estimates. I know good SREs (fancy sysadmin plus some devops, but with more class) on the scale from around $120k/yr up to about $300k/yr. This is in the USA. Everyone I know started in SF but since the COVID diaspora, they are all around the US now. $250/hr estimate -> $125/hr salary -> roughly $250k/yr My estimate might have been a big high, but really what I'm trying to drive home is that people are almost always more expensive than hardware.

by aftbit 3y ago

krembo 3y ago

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.

rockostrich 3y ago

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

_a_a_a_ 3y ago

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

dchftcs 3y ago

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.

vikingerik 3y ago

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.

Dylan16807 3y ago
Are you putting indexes into your backups?
- krab 3y ago
  pg_basebackup is, like any physical replication of Postgres.
- deleted 3y ago
  [deleted]
- nine_k 3y ago
  Backing up indexes makes sense if you care about restoring the DB quickly.
danielheath 3y ago
Weekly full backups are quite large vs eg ZFS snapshots, right?
ilyt 3y ago
> a year of weekly backups makes your 1 TB into 50, even if it's offline storage. 20TB (~18GB usable) drive is $370 + whatever power it will use 6 of them in RAID6 will give you 72TB backup storage, add extra copy and say extra drive as spare and that's 13*370 = $4810 USD for ~5 years + whatever power it will use, ~50-55W if idling most of the time and just writing backup once a day I think people got completely disconnected from how it actually costs to run something because of vastly higher resell value of storage and bandwidth in the cloud. > 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. ...but that's definitely a bigger part of that issue, cloud or not. Backup is slow, restore is slow, anything that involves any of the two is also slow

paulddraper 3y ago

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

pgwhalen 3y ago

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.

ilyt 3y ago
No wonder. SSD gives you raw storage. You're losing some (at worst could be even 50%) for redundancy just to start. SAN Flash controllers are hideously expensive because they need to push tens/hundreds worth of SSDs to tens of servers. If you can solve redundancy other way (say via pair of replicated databases), a pair of servers with local storage will be far more $ effective
- pgwhalen 3y ago
  True, but it's not as though the 10X expense is for nothing. A SAN enables a lot more than local storage. I didn't mean to suggest that there wasn't a way to achieve lower costs on storage hardware directly, just that in practice, for a variety of reasons, that quoted cost is not representative.
  - ilyt 3y ago
    And in case of database it doesn't give you much. You still need 2 nodes for redundancy, if you don't need a rack's worth of storage for that database, using SAN for that is waste of money. I know the corporate idea of "everything on SAN, nothing outside of SAN" very well, it's just plainly solving political problems, not techncial ones.
    - pgwhalen 3y ago
      Your understanding of SAN usage is narrow, and incorrect in the case of my organization. One major benefit that SAN-backed database storage provides us is, when combined with VMs, enables us to spin up another database instance against already existing data in the SAN (i.e. a staging DB that looks at prod data).

crabbone 3y ago

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.

ilyt 3y ago
I mean I'm also proponent of "doing it right" most of the time, but throwing hardware at the problem is mighty fine solution in sub-TB areas > 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. 10NVMe SSDs fit into 1U. The cloud always comes out terrible on hardware. The cloud is not selling hardware, it's terrible to buy any hardware in cloud, it sells spike capacity, and it sells software running on it. There are many services in cloud that would be a lot of work to create (even if you need just small part of functionality) on-site but if you don't use it and just need to throw raw hardware at the problem consistently, a rack with hardware will be far cheaper solution even after incurring the engineering cost to deploy and automate it. We have 7 racks and time to manage hardware is some minuscule fraction, and time to manage automation on it is still small part of our time, and we're just ops team of 3.

codetrotter 3y ago

> Four hours of engineering time (very roughly $1000) Why $1000 for four hours? Does it cost your company $150,000 per month per engineer?

sokoloff 3y ago
How do you get from $250/hr to $150K for ~160 hours?
- codetrotter 3y ago
  Because I just woke up from sleeping so I made a mistake in the calculation. My bad heh

sokoloff 3y ago

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

whoiscroberts 3y ago
You mean you can’t buy ram off Newegg sorted by cheapest and just plop it in your server?

CoolCold 3y ago

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

aftbit 3y ago
I typically calculate the cost of engineering hours as about 2x their salary costs. This captures the additional employer-paid taxes, benefits, etc, as well as ensuring that I don't under-value the cost, which is typically the bigger threat when providing estimates. I know good SREs (fancy sysadmin plus some devops, but with more class) on the scale from around $120k/yr up to about $300k/yr. This is in the USA. Everyone I know started in SF but since the COVID diaspora, they are all around the US now. $250/hr estimate -> $125/hr salary -> roughly $250k/yr My estimate might have been a big high, but really what I'm trying to drive home is that people are almost always more expensive than hardware.