
Oracle vs. PostgreSQL: First Glance - takacsroland
https://rolkotech.blogspot.com/2020/05/oracle-vs-postgresql.html
======
jcadam
I've been on a project where we were forced to migrate the opposite direction:
From PostgreSQL to Oracle, because the client was already paying for Oracle
licenses and really, _really_ , wanted us to use Oracle to justify the
expense.

It was actually a pretty big setback. We were using PostGIS to support spatial
queries (a key requirement), and Oracle Spatial was just not at the same level
(both in performance and features). The development experience with Oracle was
also awful. The licensing for Oracle was highly granular, down to the feature
level. More than once I'd identify a feature that provided a solution to an
issue through online research only to be prevented from using it due to the
customer not having the requisite license for it.

And the support was useless. Oracle was so complex (by design) we resorted to
contacting support a couple of times - they would send out an "engineer" who
could turn any technical troubleshooting session into a sales presentation for
some Oracle product or feature that would "solve" whatever the issue was.

I will never work on a project involving Oracle again (barring obscene amounts
of money to assuage my frustration, of course).

~~~
dx034
I really don't like Oracle from a DBA perspective but it's still often far
ahead of PostgreSQL when it comes to query performance. In Postgres, the query
structure can make huge differences in terms of performance and it can take a
lot of tuning to find the right query to optimize performance (especially when
subqueries are involved). Oracle (and SQLServer) are usually pretty good at
optimizing the query exactly the right way, reducing development time by quite
a bit.

~~~
rpedela
PG community has put a lot effort into performance the last few years,
including JIT compilation in PG12. Is that criticism still true today?

~~~
dtheodor
It's very true today. The problem discussed here is performance on complex
queries (e.g. subqueries), and the query planner plays a huge role in that.
The Postgres query planner has various issues. Here are two recent posts
talking about planner issues:

[https://medium.com/@rbranson/10-things-i-hate-about-
postgres...](https://medium.com/@rbranson/10-things-i-hate-about-
postgresql-20dbab8c2791)

[https://www.cybertec-postgresql.com/en/things-could-be-
impro...](https://www.cybertec-postgresql.com/en/things-could-be-improved-
postgresql/)

Also JIT compilation, while very nice and a step in the right direction, is
very barebones at the moment, hardly achieving its potential. Here's a long
todo list of what and how to make efficient use of JIT in postgres, by the
main author of the feature.
[https://twitter.com/AndresFreundTec/status/10025899696161996...](https://twitter.com/AndresFreundTec/status/1002589969616199680)
Postgres release 12 did not add any JIT related improvements, and as far as I
know no work has been done on it on 13 either.

~~~
ksec
For someone not following closely, What are the reasons behind the lack of
work on JIT since then?

~~~
ris
The traditional view that the bottleneck for query performance is
overwhelmingly disk throughput, which has become slightly less true with the
advent of SSDs.

------
revel
Oracle used to be by far and away the best database out there. Now I wouldn’t
use it even if you paid me. It’s shocking how little Oracle invested in
developing their products and services over the years. They are a distant
second, if not merely an “also ran”, for everything that they do. The company
largely exists as an experiment in just how far you can go with a vendor lock-
in strategy. Sadly that experiment is proving to be a remarkably successful
one

~~~
da_chicken
They're a victim of their own success. They became a monopoly and the quality
of their product stopped mattering. They're an example of Steve Job's comments
on Xerox's failure[0]. It happened at Oracle, IBM, Cisco, and Microsoft. It's
happening now at Apple, Intel and Google.

[0]: [https://youtu.be/NlBjNmXvqIM](https://youtu.be/NlBjNmXvqIM)

~~~
toyg
I agree on the overall theory (dominance in a sector tends to shift internal
incentives in such a way that the result is an ossified development
structure), but I think Jobs' terminology is imprecise. Larry Ellison is not
really a product guy first and foremost, he's the definition of a tough
salesman. The "bad guys" are a more generic variety of "corporate type" who
can materialize in any department, really. Typically they power themselves up
the ladder with "cost efficiencies". The most recent Oracle CEOs (Hurd and
Catz) fit that profile to a T.

En passant: another big name that suffered from this phenomenon was Nokia.

------
sbuttgereit
The article seems to misunderstand what table inheritance is in PostgreSQL.

CREATE TABLE new_table AS TABLE existing_table;

Doesn't create any PostgreSQL inheritance relationship between the parent and
child tables. It merely makes a new non-inherited table with a copy of the
data whereas with true table inheritance you're working with the same data
(there's some visibility rules to consider between parent and child, but
that's different than a copy).

I'm also uncomfortable with too simply stating that you should think of this
like OOP inheritance; while I agree that in some respects there's passing
similarity, it is its own beast and needs to be understood outside of the OOP
paradigm to be useful. Many of the Object Relational aspects of PostgreSQL are
very powerful, but can not be understood in OOP terms.

For inheritance, it's better to read about this from the documentation:
[https://www.postgresql.org/docs/12/tutorial-
inheritance.html](https://www.postgresql.org/docs/12/tutorial-
inheritance.html)

Also, another part of the article talks about the ramifications of not having
Oracle "packages". So while it's not completely the same concept and there are
different sets of trade-offs, one option includes using PostgreSQL schema for
this sort of logical namespace organization. Both Oracle and PostgreSQL have
the concept of different schemas, but Oracle has a much more rigid idea about
schema usage (related to database users) and PostgreSQL has a much more fluid
idea about usage. As a former Oracle guy, I can see how that organizational
tool might not be front of mind when coming to PostgreSQL, but I've used
PostgreSQL schema for this sort of organizational purpose with good success.

~~~
takacsroland
Thanks, I will check out your input on table inheritance and update it.

------
bjpirt
I'm a happy Postgres user and recently did some work with a government agency
using Oracle - the thing that shocked me most about Oracle was the lack of
transactional DDL operations which was something I'd just taken for granted in
the Postgres world.

~~~
munk-a
Coming from MySQL to Postgres a few years back the transactional DDL
statements were a joy to work with - I've had to claw a legacy into the modern
era and utilizing them has allowed me to execute live migrations from legacy
into shims and then from shims into modern.

I also really appreciate the transactional TRUNCATE - I pretty much never use
it but at least in Postgres I never have to worry about someone else trying to
run one and wiping state unexpectedly.

~~~
takeda
> I also really appreciate the transactional TRUNCATE - I pretty much never
> use it but at least in Postgres I never have to worry about someone else
> trying to run one and wiping state unexpectedly.

as long as auto commit is not enabled.

These goodies are possible, because of PostgreSQL's MVCC which requires
running vacuum. Nothing is for free unfortunately.

~~~
jsmith45
Interestingly even though MSSQL server uses an extremely different
implementation of MVCC, it internally has a vacuum equivalent. (Which is
required even when all MVCC support is disabled! It is used to enable
efficient implementation of deletes, without having to use absurdly coarse
locks).

MSSQL just handles doing that cleanup silently in the background while
exposing basically no no configuration except a trace flag that can turn it
off.

------
markab21
Why anyone would use Oracle for anything other than supporting legacy systems
is beyond me.

~~~
F_J_H
I have not been an Oracle fan in the past, especially because of their
complicated (and expensive) licensing, but late last year we moved to their
hosted autonomous database. The on demand pricing model makes it quite
economical, and the performance is amazing.

However, the killer feature for me is that it has application Express (or
APEX) included, which is a complete web application development framework, as
well as Oracle restful data services (ORDS). With built-in application
development and deployment, it is the only complete, full-stack data
management platform I am aware of (enterprise level).

YRMV, but it has been incredible for us, both to support our data science
initiatives, and for rapidly deploying applications. I couldn't imagine going
back to anything else.

~~~
cryptonector
PostgreSQL + PostgREST == blinding light.

You get a RESTful interface to PG. All you need to add is a static page w/
some JS, for which you can use react-admin or similar.

Presto: web apps written in PG.

~~~
edmundsauto
Can you point me to a good resource on this? I work 100% in the data layer
right now, but the idea of being able to create an easy frontend (assuming I
learn react) would be wonderful.

~~~
cryptonector
PostgREST:
[https://github.com/PostgREST/postgrest](https://github.com/PostgREST/postgrest)
and [http://postgrest.org/en/v6.0/](http://postgrest.org/en/v6.0/)

Sample PostgREST apps:
[http://postgrest.org/en/v6.0/ecosystem.html](http://postgrest.org/en/v6.0/ecosystem.html)

React-admin: [https://marmelab.com/react-admin/](https://marmelab.com/react-
admin/) and [https://github.com/marmelab/react-
admin](https://github.com/marmelab/react-admin)

React.js: [https://reactjs.org/](https://reactjs.org/)

Combinations:

\- [https://github.com/tsingson/ra-postgrest-
client](https://github.com/tsingson/ra-postgrest-client) \-
[https://github.com/raphiniert-com/ra-data-
postgrest](https://github.com/raphiniert-com/ra-data-postgrest) \-
[https://reactjsexample.com/a-react-web-application-to-
query-...](https://reactjsexample.com/a-react-web-application-to-query-and-
share-any-postgresql-database/) \- [https://github.com/tomberek/aor-postgrest-
client](https://github.com/tomberek/aor-postgrest-client) \-
[https://github.com/priyank-purohit/PostGUI](https://github.com/priyank-
purohit/PostGUI) \- [https://awesomeopensource.com/project/priyank-
purohit/PostGU...](https://awesomeopensource.com/project/priyank-
purohit/PostGUI?categoryPage=9) \-
[https://www.reddit.com/r/learnpython/comments/bzrr1c/how_to_...](https://www.reddit.com/r/learnpython/comments/bzrr1c/how_to_create_dynamic_dashboard_on_top_of/)

More links:

\- [https://github.com/topics/postgrest](https://github.com/topics/postgrest)
\-
[https://duckduckgo.com/?q=%22postgrest%22+%22react%22&t=ffab...](https://duckduckgo.com/?q=%22postgrest%22+%22react%22&t=ffab&ia=software)
\- [https://duckduckgo.com/?q=%22postgrest%22+%22react-
admin%22&...](https://duckduckgo.com/?q=%22postgrest%22+%22react-
admin%22&t=ffab&ia=software) \- [https://react-
admin.com/docs/en/ecosystem.html](https://react-
admin.com/docs/en/ecosystem.html)

I'm sure you can find more!

Yeah, there's not _one_ solution. There are _many_. Many are open source.
PostgREST is amazing. The rest is up to you, but there's tons of tools out
there.

------
dx034
> \-- By EXCLUDED.email we could refer to the "old" email value that we are
> updating.

Small nitpick: The excluded table contains the values proposed for insertion,
not the values already present in the table (as described in [1]).

[1] [https://www.postgresql.org/docs/current/sql-
insert.html](https://www.postgresql.org/docs/current/sql-insert.html)

~~~
takacsroland
You are right, I already corrected this. Thanks.

------
masklinn
> IF EXISTS for DDL operations

It's a super convenient operation but it has one big drawback which might be
unexpected: `if exists` first acquires the relevant lock then checks.

This means a "ALTER TABLE table_name DROP COLUMN IF EXISTS column_name" will
first acquire an ACCESS EXCLUSIVE lock, then check if the column exist.

Since DDL is transactional the lock will not be released until the transaction
is committed or rollbacked, therefore even if the column doesn't exist it will
prevent all concurrent operations on the table.

------
krakatau1
I don't have a lot of experience with Oracle but I can tell you that Postgres
optimizer is shit compared to Db2 zOS or Db2 LUW.

When I worked in a large bank we tried to migrate core system from Db2 zOS to
Postgres and it went nowhere. I was a in-house developer working with Postgres
consultants and they were amazed by db2 performance in OLTP scenarios.

So if your organization is already spending cash on Oracle, Db2 or MSSQL, use
them for superior performance. Migration off them is costly and risky process.

If your working at a startup there is absolutely no reason to choose anything
but Postgres if you need relational.

~~~
takacsroland
In fact, my company wants to spare the expenses on Oracle. This is the main
reason for migrating. We'll see how it works out. You are not the first one to
point out postgre's optimizer. Is it really that bad?

~~~
outworlder
YMMV. That depends on your workload, how your data is structured and a million
other things. I know a few shops with heavy usage that couldn't be happier.

It may require handholding if you are not happy with the plans it is
generating. Also the quality of the query planner results will depend a lot on
how up to date the statistics are.

~~~
takacsroland
I see, thanks!

------
iracic
Some good points in article. There are some things that may need more
attention. 1) Update from another table is not safe as it should be (in case
of multiple values, final value will be sort of random) 2) Schemas as
namespace separators (grouping tables inside database) 3) Extern join syntax
in Oracle is actually more vulnerable (in case of error in multicolumn syntax
it fallbacks to normal join). So, it's not better or easier - it is just
created before standard JOIN existed. 4) Crucial difference how buffer-vs-
filesystem cache works 5) Miss of plan stability - no solution out of the box
in standard installation 6) Batch operation (in)efficiency 7) Pros/cons in
undo/rollback handling [likely some more that can't think of right now]

------
drdec
> I am confident that anyone who works with Oracle often uses the (+) inside a
> query to simply force an outer join.

For the love of all you hold sacred, please don't do this.

~~~
miahi
For some reason I find a query that uses (+) way easier to read than verbose
outer joins. Probably because it's near the field and you see immediately
"hey, this can be null".

Yes, it makes the query harder to migrate to other DMBS and to collaborate
with non-Oracle persons.

~~~
ibejoeb
To make it terser, omit `outer` because it is redundant. Now it's up to
whatever you find easier to type. Definitely `left` or `right` for me. (+) is
a really awkward sequence on QWERTY, at least.

------
philliphaydon
I haven't touched Oracle in like 12 years so I can't comment on that. But some
of the examples are a bit strange or atleast lacking for PostgreSQL.

For example, in the partitioning, he states:

> SELECT * FROM sales_p_america;

But doesn't mention that if you select based on a region, it will use only the
partition table.

> SELECT * FROM sales WHERE sales_region IN ('USA','CANADA');

While I believe if you do the equiv in Oracle it wont use the partition table?

\---

The section on table inheritance isn't right either.

[https://www.postgresql.org/docs/12/tutorial-
inheritance.html](https://www.postgresql.org/docs/12/tutorial-
inheritance.html)

What he demonstrated was just a way of making additional tables based on
existing ones. While inheritance works sort of like partitioning except the
child tables can contain additional data. Selecting from the parent will
display all data from the child.

~~~
miahi
Oracle will also use partitioning optimizations in that case. See partition
pruning[1].

[1] [https://docs.oracle.com/en/database/oracle/oracle-
database/1...](https://docs.oracle.com/en/database/oracle/oracle-
database/12.2/vldbg/partition-pruning.html)

~~~
philliphaydon
Awesome. Thanks for the info. I wasn’t sure if it was supported or not.

------
ajuc
Biggest difference for me is DDLs are transactional in Postgres, but not on
Oracle.

That means migration scripts for software on Postgress can just have all DDLs
(alter, create, drop, grant etc.) and DMLs (inserty, update, etc.) mixed in
whatever order they need to be, and if any particular line of the migration
script fails - the whole thing is rolled back as if nothing happened. And then
you fix the problem and run migration again. Easy.

In comparison writing migration scripts on Oracle is a nightmare - DDLs aren't
transactional (THEY COMMIT ON EACH LINE...), so you have to separate them from
DMLs and ensure that only the scripts that haven't passed yet are re-run
later. I've worked in 3 different companies that used oracle, and there were 3
different approaches to that problem, and all 3 of them sucked :)

In one company we had several big customers each with 1 production db, and
software was written on separate branches for each customer, and helpdesk
staff was dealing with migrations - programmers just asked helpdesk to add a
column and worked on the test db for that customer. It was a lot of
unnecessary work to port changes and bugfixes between branches, but at least
we knew exactly what is on each db and could fix problems by ourselves. There
was no migration to speak of, just manual changes on dbs and documenting them
in svn (it was before git was popular).

In another company there was one development branch and several customers, and
there were migration scripts written by all developers when they made changes,
which were merged into development branch for db by 1 guy whose whole job was
to merge these scripts and check if migration works. It slowed down
development (because when you finished your task on local db you had to make a
migration script(s) and send them to be verified. And even "that guy"
sometimes made mistakes and then if you fetched db scripts in the morning you
couldn't work until stuff was fixed (or you had to recreate oracle db from
scratch which took several hours).

That was before docker BTW, now they probably use docker so that can be less
of a problem.

In the third company we had one customer but with hundreds of installations,
and we had one development branch with frequent releases. Developers
maintained migration scripts between release, major and minor versions. There
was no "that guy" \- we had smoke tests instead, and it sometimes took more
time to write that migration script(s) than to change the code.

So you want to add 3 columns to 3 tables and fill them? And it has to be done
in order because of dependencies? Write no less than 6 migration scripts
(alter table 1, update table 1, alter table 2, ...). Add them with proper
names and some boilerplate to the migration scripts for minor versions (3.4.5
-> 3.4.6). But that's not all! We also have migration scripts for major
versions (3.4.0 -> 3.5.0), so you also need to add them there. You have to
check the migration separately because these scripts often use shortcuts to
run faster. So your scripts might break despite working for minor version
migration.

Then there's the scripts for release version migration (3.0.0->4.0.0). Add
your scripts there as well, and test once again.

Oh, and testing these scripts on test data doesn't mean they will work - each
installation of db changes slightly over time - people add stuff from ui.
There are rules what they can change and what they cannot, but if you don't
think about it you might break something with your migration scripts on
production despite it working on test data.

When that happens you have to write migration fixes which need to detect that
problem and fix it on data you don't have direct access to :)

It was a nightmare.

Meanwhile Postgress is just doing the right thing, write 1 migration script
with everything in it, if it works it works, if not - it rollbacks. Nobody
thinks twice about it.

~~~
hans_castorp
> Biggest difference for me is DDLs are transactional in Postgres, but not on
> Oracle.

The answer I received more than once from Oracle evangelists regarding
transactional DDL: it's useless and if you need it, you are not testing your
scripts properly

~~~
ajuc
> Oracle evangelists

There is such a thing?

> it's useless and if you need it, you are not testing your scripts properly

Is that also their response for static typing and constraints on database :) ?

~~~
nordsieck
>> Oracle evangelists

> There is such a thing?

Tech evangelist is a common job title.

~~~
ajuc
I thought X evangelism is when you aren't paid, like with open source. If you
are paid you're just a salesman.

~~~
NickNameNick
I've seen 'Developer evangelist' as a job title.

------
MrHamdulay
Why are the table and field names in the examples between Oracle and
PostgreSQL different? It makes it harder to compare the two.

~~~
zozbot234
IIRC, Oracle licensing forbids publishing direct comparisons with competing
products. I guess they had to find a workaround.

~~~
defnotashton2
This kind of model speaks to who actually buys it, I would never pay for a
product with such limitations out of principle.

~~~
paulmd
Microsoft SQL Server has the same clause in their license, unfortunately.

[https://www.brentozar.com/archive/2018/05/the-dewitt-
clause-...](https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-
you-rarely-see-database-benchmarks/)

So, you wouldn't be considering any commercial SQL offering, basically.

------
ksec
Whenever I see comment making comparison between Oracle and Postgre, I cant
help bug wonder why isn't it compared to Enterprise DB, which is sort of like
the unofficially official Postgre for Enterprise products.

~~~
pasha_golub
Postgres! You're not saying Orac, aren't you? :)

------
jonahbenton
Hmm, looks like EnterpriseDB is still a thing:

[https://www.enterprisedb.com/](https://www.enterprisedb.com/)

------
simonebrunozzi
I would bet the next "Oracle" (as in, large IT company) of the decade
2020-2030 will be based on PostgreSQL. Can't see an obvious candidate, yet.
Perhaps someone has seen some interesting companies heading in this direction?

------
davio
I've been at 3 separate companies where each respective CIO had "get rid of
Oracle" as a strategic initiative.

~~~
aserafini
Amazon even made a promotional video when they shut down their last Oracle
database
[https://m.youtube.com/watch?v=9yBP5gnnZi4](https://m.youtube.com/watch?v=9yBP5gnnZi4)

Perhaps it was retaliation for Larry’s comments about Amazon in this interview
[https://m.youtube.com/watch?v=xrzMYL901AQ](https://m.youtube.com/watch?v=xrzMYL901AQ)

------
devit
How come nobody has implemented an Oracle compatibility mode for PostgreSQL?

Or in general, why don't databases support each others SQL dialect? It can't
be that much work, at least if one is content with only supporting the
majority of applications, and seems pretty essential for popularizing a
specific database.

Looking at the article, supporting Oracle syntax seems trivial in all cases
except for adding full MERGE support.

~~~
sbuttgereit
You mean like...

[https://www.enterprisedb.com/enterprise-postgres/database-
co...](https://www.enterprisedb.com/enterprise-postgres/database-
compatibility-oracle)

It's been around for years. Earlier on PostgreSQL did make some efforts of
being recognizable to Oracle users... look at Oracle PL/SQL and PostgreSQL
PL/pgSQL... very similar and I recall that similarity being intentional.

Also, there is the SQL standard. Rather than supporting all vendors' syntax
and features, which can change on the whim of some competitor that probably
doesn't have your best interests at heart, it's better to adhere to the
standard if you want the broadest applicability. PostgreSQL does exactly that
with few deviations from the standard, relative to the industry as a whole. At
the end of the day it's really about goals and not every RDBMS has the same
goals; with PostgreSQL standards compliance is a goal.

------
CodeSheikh
As a dev you would use Oracle only if your execs have cut a sweet licensing
deal with the Oracle.

~~~
takacsroland
:)

------
stuff4ben
Great article if I ever get back into DB-based development again...

------
trollied
One RDBMS that I don't see mentioned much is Tibero
[https://www.tmaxsoft.com/products/tibero/](https://www.tmaxsoft.com/products/tibero/)

It's a clone of Oracle & I'm surprised that Oracle legal have never tried to
splat it!

Interesting blog post discussing it here:
[https://www.tmaxsoft.com/products/tibero/](https://www.tmaxsoft.com/products/tibero/)

~~~
fl0wenol
That's not a blog post, that's just marketing material.

~~~
trollied
Damn, posted the wrong link by accident. Wondered why I was getting downvoted.
The link I meant to post was [https://blog.dbi-
services.com/tibero-i/](https://blog.dbi-services.com/tibero-i/)

