4 ms·

To be fair, we're using Aurora Serverless, so I'll have to dig deeper if we can use these kinds of hints. But thanks for the tip, this could be really useful!

mst 5y ago
Given Aurora talks about itself as a replacement for pg/mysql that provides compatible skims I'm honestly wondering whether you're dealing with the pg optimiser here in any meaningful way. (exactly how far along the scale from 'tweaked version' to 'basically just a skin on a completely new database' aurora is is ... opaque to me ... there's probably something out there that answers that but I haven't yet managed to find it) Edited to add: zorgmonkey downthread points out they have support for some extensions like pg_hint_plan - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Maintenance.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... - which suggests at least a decent amount of pg code but I'm still curious exactly where the changes start.
- cube2222 5y ago
  I obviously don't know its exact internal architecture, but based on my digging and trying out different PostgreSQL-native features, it looks like it's PostgreSQL instances as query executors with a custom (high-availability) storage layer, fronted by PgBouncer.
  - mst 5y ago
    That sounds like a pretty reasonable educated guess to me (that's meant positively, I'm just ops enough that I consider lots of things I hope I know to be in that category). But it -does- still leave the question of how well the storage layer reports table statistics and how that interacts with the planner. If I sound suspicious, it's because I ran into a situation recently where a SELECT inside a transaction on mysql Aurora somehow (as far as I can tell, I was helping out at a couple removes so if you're reading this and it's relevant to you please don't believe me without testing yourself) took a whole table lock in a situation where I am pretty damn sure that for all its 'fun' actual InnoDB would've locked a page at most. So ops goes.

mst 5y ago

Given Aurora talks about itself as a replacement for pg/mysql that provides compatible skims I'm honestly wondering whether you're dealing with the pg optimiser here in any meaningful way. (exactly how far along the scale from 'tweaked version' to 'basically just a skin on a completely new database' aurora is is ... opaque to me ... there's probably something out there that answers that but I haven't yet managed to find it) Edited to add: zorgmonkey downthread points out they have support for some extensions like pg_hint_plan - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Maintenance.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... - which suggests at least a decent amount of pg code but I'm still curious exactly where the changes start.

cube2222 5y ago
I obviously don't know its exact internal architecture, but based on my digging and trying out different PostgreSQL-native features, it looks like it's PostgreSQL instances as query executors with a custom (high-availability) storage layer, fronted by PgBouncer.
- mst 5y ago
  That sounds like a pretty reasonable educated guess to me (that's meant positively, I'm just ops enough that I consider lots of things I hope I know to be in that category). But it -does- still leave the question of how well the storage layer reports table statistics and how that interacts with the planner. If I sound suspicious, it's because I ran into a situation recently where a SELECT inside a transaction on mysql Aurora somehow (as far as I can tell, I was helping out at a couple removes so if you're reading this and it's relevant to you please don't believe me without testing yourself) took a whole table lock in a situation where I am pretty damn sure that for all its 'fun' actual InnoDB would've locked a page at most. So ops goes.