Comment by fulafel
11 days ago
It's possible it has redeeming features but seems more common to be just legacy. Multiple apps accessing the same DB leading to a gridlock from migration POV. (Plus career oracle DBAs etc in the org).
As Oracle is so expensive it skews the architecture decisions towards multiple apps accessing the same DB.
Even worse. It skews the architecture decisions towards few large physical database servers instead of many small VMs, because licensing cost is per core in the whole VM cluster, so totally unaffordable. So you get reduced availability, higher risk, reduced separation, reduced security, higher datacenter cost, and they bill you an arm and a leg on top...
This isn't always a bad thing because microservice architecture isn't always the best solution.
This often isn't related to a monolith vs microservice comparison. Large enterprises and institutions tend to run a lot of completely separate applications, which then end up sharing database infrastructure unnecessarily. Think of universities, for example.
Oracle extends the problem to the opposite end of microservices, by encouraging monolith DB consolidation, with unrelated monolith applications on the same db cluster for purely budgetary reasons.
2 replies →
If you handle large amounts of geographical data you'll need to invest quite a bit to move to Postgres. It's possible but you're going to need to touch a lot of existing code and figure out new performance characteristics and so on. A lot of it will be hard for an average organisation, not because it's very sophisticated and complex but because it will be large amounts of boring rote work that many developers don't see how they could do programmatically.
Rumour has it the same holds for some other types of data as well but I lack immediate experience in other areas.
With Oracle you also have a rather robust, exhaustive documentation of error messages and even obscure stuff is likely to be figured out in some forum thread by someone and an indian guy. Postgres isn't exactly bad in this area but you can run into things where you need to go deep in debugging yourself and figure out minutiae of your specific version.
Containers also remove most of the issues with running several instances in development and CI environments.
I still don't recommend anyone to pick Oracle for greenfield stuff, instead you should work around shortcomings in other database engines, but for a large organisation with certain demands that already has buyin it makes sense.
Postgres is commonly used in GIS, via PostGIS (https://postgis.net). That said it is Oracles model to try to lock customers in.
PostGIS seems leaps better to me (like the PG DX in other aspects). Eg in Oracle you don't have 2d points. Adding a geo index can fail in the middle and leave the table in a unusable state that requires DBA magic to untangle. Etc.
This is just on top of the general technical inferiority (eg there are no transactional schema changes, so you don't get the safe go/no-go in those when applying those as part of app deploys with a migration tool)
SDO_POINT(x, y, 0) or SDO_POINT(x, y, NULL) ought to do what you want. Index corruption can be a nasty problem on Postgres too.
You need to decide if and how to perform a rollback, similar to how you would define a down() procedure in migration files. A schema change might imply changes to data, and in that case you might turn off client writes, copy the table, change it, validate, do rename dance, turn on client writes again. If it doesn't it might be much cheaper to operate on a single copy. How does Postgres decide on such strategies automatically?