Comment by cess11
11 days ago
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?