4 ms·

> What killer core features does Oracle have that Postgres doesn't? While I don't really like Oracle as a company, there are some features that Oracle has and

by glogla 11y ago

> What killer core features does Oracle have that Postgres doesn't? While I don't really like Oracle as a company, there are some features that Oracle has and postgres don't, that can be important for production deployment: - flashback - partitioning that's much more mature - merge /*+ parallel - materialized views that refresh on commit - and oracle is quite a bit quicker - especially with badly written queries But those (except for optimizer) are all Oracle Enterprise features which means you're paying ridiculous money per-core for enterprise edition itself, and then you're paying ridiculous money for the features one by one. There's mostly no reason to use Oracle Standard edition instead of postgres. And I have seen Oracle XE in producion and that's just sad. EDIT: oh and I forgot - postgres has that stupid "we don't want to include optimization hints in the language so we use CTEs as optimization boundary" policy that the developers stuck on and refuse to change, which means you have to decide between readable code and performant code. Meh.

pmontra 11y ago
I didn't know about the CTE optimization fence. I was starting to use CTEs but I'll have to work around them. Thank you.
sgt 11y ago
Flashback is indeed a killer feature. I'd like to see something like Flashback in Postgres. PITR is possible with Postgres but it takes very long to replay log files to a certain point, if you need to restore data. I know this because I've had to do this quite a few times.
- je42 11y ago
  Flashback is useless if there are schema changes.
jeltz 11y ago
There has been talk about removing the optimization fence but people are afraid of breaking people's applications.
- AlisdairO 11y ago
  It is pretty frustrating. At this point I wouldn't even mind if there was just some extra syntax to say 'guys, seriously, no optimisation fence'
feld 11y ago
Never use hints in production!
- brianwawok 11y ago
  Client or server side? (I guess maybe the oracle side "hints" have another name, but my Oracle-Foo is not strong enough to remember the name query plan?). I have seen some really big shops that the only way they could keep their app running in production was a fair bit of server side hints. Which then really sucked because someone fixed the casing on a table name (say "Orders" to "orders") and made it fall out of the query plan. Whereas if they were done as hints, they would have kept working.
  - jerven 11y ago
    The new query plan stabilization features in 12cEE really help with this. Hints are a pain when upgrading Oracle major versions as they can affect query correctness, as my boss found out painfully last week ;(
    - AlisdairO 11y ago
      I too think plan stabilisation is the important thing. What people particularly want is the ability to know that their production performance is not just going to suddenly crash and burn because of a change in plan.

by glogla 11y ago

pmontra 11y ago

I didn't know about the CTE optimization fence. I was starting to use CTEs but I'll have to work around them. Thank you.

sgt 11y ago

Flashback is indeed a killer feature. I'd like to see something like Flashback in Postgres. PITR is possible with Postgres but it takes very long to replay log files to a certain point, if you need to restore data. I know this because I've had to do this quite a few times.

je42 11y ago
Flashback is useless if there are schema changes.

jeltz 11y ago

There has been talk about removing the optimization fence but people are afraid of breaking people's applications.

AlisdairO 11y ago
It is pretty frustrating. At this point I wouldn't even mind if there was just some extra syntax to say 'guys, seriously, no optimisation fence'

feld 11y ago

Never use hints in production!

brianwawok 11y ago
Client or server side? (I guess maybe the oracle side "hints" have another name, but my Oracle-Foo is not strong enough to remember the name query plan?). I have seen some really big shops that the only way they could keep their app running in production was a fair bit of server side hints. Which then really sucked because someone fixed the casing on a table name (say "Orders" to "orders") and made it fall out of the query plan. Whereas if they were done as hints, they would have kept working.
- jerven 11y ago
  The new query plan stabilization features in 12cEE really help with this. Hints are a pain when upgrading Oracle major versions as they can affect query correctness, as my boss found out painfully last week ;(
  - AlisdairO 11y ago
    I too think plan stabilisation is the important thing. What people particularly want is the ability to know that their production performance is not just going to suddenly crash and burn because of a change in plan.