Comment by thiht
11 days ago
> The production database will be different than the "testing Postgres instance", leading to bugs.
It never happened to me to be honest. This reads an argument for "if you can’t do perfect, just do it badly" but it’s nonsense. Running tests against a local Postgres instance with the same major.minor version and same extensions as your prod instance WILL work.
And testing your storage layer against the database is probably the most reliable safety net you can add to an app.
> Running tests against a local Postgres instance with the same major.minor version and same extensions as your prod instance WILL work.
A team I worked with recently said the same thing. But, as I predicted, they ran into bugs because the CloudSQL Postgres was different than their Dockerized Postgres, even though it was the same core version.
There will always be testing problems you can't anticipate. Especially with systems that are not your own code. Just be ready to adapt your testing when it doesn't work as expected, and don't invest too much in the testing if it's not worth it.
Which bugs?
I’ve been ready for 5 years as it’s the duration I’ve been testing my storage layers with 90% integration tests (the 10% being the hard to reproduce error cases, these tests have low value but are easy to test with mocks so I still test them). The only issue I’ve encountered was with time zones (shocking), and it made me ensure I got full control of the time zones in my app, in my deployment, in my local Postgres and in my prod Postgres, so net benefit in the end.
I don't remember them all off the top of my head, but I do remember:
- Access to different parts of the database are limited in CloudSQL since it's a managed database. This makes some features of automated tooling (like migrations) not work on CloudSQL (i'm not saying migrations don't work, i'm saying some features do work, some don't). Sometimes elevated permissions can fix it, but some aspects are just walled off.
- There are schema differences from the stock Postgres (I don't remember specifics). No support for custom tablespaces.
- Import/export can often lock up a CloudSQL instance, whereas it might work fine on a local instance.
- You only get Read Committed transaction isolation.
- Operation is different (load, logging, replication, nodes, etc) and this affects performance, and performance can lead to bugs if there's an expectation of a certain run time or performance that doesn't match up with the development experience. Often times some job will have run faster on a laptop than in the cloud, and that leads to weird corner cases where production has an issue due to weird performance and it has to be triaged in the cloud and a fix applied to avoid it. Performance issues don't sound like a bug, but if you have to change your code to make it work in prod, it's a bug.
- To access CloudSQL they want you to use a proxy app, and that can have inconsistent results compared to a dev connecting directly to a Postgres instance. Even something as simple as handling reconnects is often not considered when all you have is a local instance that never needs reconnecting.
- There's a limited selection of Postgres extensions supported. And I could be wrong but I think the version of the extensions is pinned for each version of Postgres core used in CloudSQL.
To all of this you might reply "well none of that affects me", and that's fine... but it does affect other people, and that's important to note when you're telling people on the internet there will be no problems.
1 reply →