4 ms·

I'm doing professional services of optimizing on-prem and cloud Postgres databases. It always surprises me how much companies tend to take cloud databases as au

by krembo 5y ago

I'm doing professional services of optimizing on-prem and cloud Postgres databases. It always surprises me how much companies tend to take cloud databases as automated and rely on them to do auto-tuning for everything. Well they don't, and as soon as the product goes live and the amount of data slightly grows, the performance degrades exponentially. * Never count on cloud databases to replace a DBA work. * Don't take ORM optimizations for granted. They all suck at one point or another. * If you can't afford an FTE, take short professional gigs from time to time to fix and boost performance.

wpietri 5y ago
Yeah, for me this is a classic "easy vs simple" example. Cloud databases are a very easy option. But Postgres is 1.3 million lines of code, and on top of that you add the complexity of the cloud vendor's environment, choices, and custom code. It may be easy, but it's definitely not simple.
spicybright 5y ago
> Don't take ORM optimizations for granted. I've been bitten many a time by this. I'd even go as far to say if you don't know how to write SQL that does the same thing your ORM framework is doing, you're setting yourself up for a lot of pain later when you do something non-trivial.
- berkes 5y ago
  Worse, is that ORMS tend to creep all through your code. Introducing not only all sorts of coupling- and responsibility issues down the road, it most of all leads to unexpected issues. Sure, its nice being able to say `ThisPost.authors.sort(DESC, 'postCount').where('active = true').select('name', 'avatarUrl', 'postCount')` anywhere in your codebase, but you know have spread knowledge of how to sort, filter; what attributes there are, what their relations are and so on, throughout your codebase. Or, more on-topic: you know have spread knowledge about what can be fetched optimized, and what will cause performance pain, throughout your code. Suddenly some view-template, or completely unrelated validation, can now cause your database to go down. Rails' activerecord is especially cumbersome in this: it offers "sharp knives"[0] but hardly any tooling to protect yourself (or that new intern) from leaving all those sharp knives lying around. A "has_and_belongs_to :users" and a ".where(users: { status: 'active', comment_count: '>10' }" is just two lines. But they will haunt you forever and probably will bring your database down if you have any load. [0] https://rubyonrails.org/doctrine/#provide-sharp-knives https://rubyonrails.org/doctrine/#provide-sharp-knives
- beaconstudios 5y ago
  this is why nowadays I prefer to use query builders (like knex - knexjs.org) instead of ORMs. You can optimise your queries without having huge blobs of raw SQL in the middle of model code, and you can build models (or service objects, as I prefer) manually that are completely transparent.
selfhoster11 5y ago
> It always surprises me how much companies tend to take cloud databases as automated and rely on them to do auto-tuning for everything. Automation and auto-tuning is kind of the whole value proposition of managed services, isn't it?
- paulryanrogers 5y ago
  IME, there is a spectrum from barely hosted (rented bare metal) to white-glove service (Heroku)

by krembo 5y ago

wpietri 5y ago

Yeah, for me this is a classic "easy vs simple" example. Cloud databases are a very easy option. But Postgres is 1.3 million lines of code, and on top of that you add the complexity of the cloud vendor's environment, choices, and custom code. It may be easy, but it's definitely not simple.

spicybright 5y ago

> Don't take ORM optimizations for granted. I've been bitten many a time by this. I'd even go as far to say if you don't know how to write SQL that does the same thing your ORM framework is doing, you're setting yourself up for a lot of pain later when you do something non-trivial.

berkes 5y ago
Worse, is that ORMS tend to creep all through your code. Introducing not only all sorts of coupling- and responsibility issues down the road, it most of all leads to unexpected issues. Sure, its nice being able to say `ThisPost.authors.sort(DESC, 'postCount').where('active = true').select('name', 'avatarUrl', 'postCount')` anywhere in your codebase, but you know have spread knowledge of how to sort, filter; what attributes there are, what their relations are and so on, throughout your codebase. Or, more on-topic: you know have spread knowledge about what can be fetched optimized, and what will cause performance pain, throughout your code. Suddenly some view-template, or completely unrelated validation, can now cause your database to go down. Rails' activerecord is especially cumbersome in this: it offers "sharp knives"[0] but hardly any tooling to protect yourself (or that new intern) from leaving all those sharp knives lying around. A "has_and_belongs_to :users" and a ".where(users: { status: 'active', comment_count: '>10' }" is just two lines. But they will haunt you forever and probably will bring your database down if you have any load. [0] https://rubyonrails.org/doctrine/#provide-sharp-knives https://rubyonrails.org/doctrine/#provide-sharp-knives
beaconstudios 5y ago
this is why nowadays I prefer to use query builders (like knex - knexjs.org) instead of ORMs. You can optimise your queries without having huge blobs of raw SQL in the middle of model code, and you can build models (or service objects, as I prefer) manually that are completely transparent.

selfhoster11 5y ago

> It always surprises me how much companies tend to take cloud databases as automated and rely on them to do auto-tuning for everything. Automation and auto-tuning is kind of the whole value proposition of managed services, isn't it?

paulryanrogers 5y ago
IME, there is a spectrum from barely hosted (rented bare metal) to white-glove service (Heroku)