Re: Oracle vs. PostgreSQL - a comment - Mailing list pgsql-general

Paul Förster <paul.foerster@gmail.com> writes:

> Hi,
>
> I know, this list is not for this, but I just couldn't resist. Please forgive me.
>
> Being an Oracle DBA for two decades now (back then starting with Oracle 8.0.5) and only doing PostgreSQL since
version10.3, I feel compelled to share some of my experiences with both. 
>
> Quick facts:
>
> All installations were performed on the same host except for Oracle Data Guard and Patroni with etcd, which both
requireat least three nodes, or an odd number >2 respectively) to establish a democracy to determine the master/primary
andreplica/standby databases. However, all machines have the same hardware and operating system: 
>
>     OS:  openSUSE Leap 15.1 (server setup, not desktop)
>     CPU: Intel i7-7700T CPU
>     RAM: 32 GB
>     Disk Hardware: SSD
>
> Also, Oracle requires 161 additional packages to be installed, many of which are 32-bit packages, for a supposedly
64-bitonly software! This results in 150 MB additional disk space needed and swamps the system with 32-bit packages! 
>
> PostgreSQL only requires a few packages to be installed depending on the options one chooses to compile the source
with.Anyway, none of these packages require a 32-bit version! 
>
> Size of installation:
>
>     Software:
>         $ du -sh /data/postgres/12.3 /data/oracle/product/19.6
>         62M     /data/postgres/12.3
>         8.5G    /data/oracle/product/19.6
>     Databases:
>         $ du -sh /data/oradb/*
>         3.3G    /data/oradb/cdb01    # Oracle Container w/ 1 PDB
>         1.8G    /data/oradb/sdb01    # Oracle stand alone database
>         $ du -sh /data/pgdb/sdb01
>         659M    /data/pgdb/sdb01     # PostgreSQL 12.3 database cluster
>
> All databases are a clean setup, no schemas, users, tables, data, etc. Just an empty base.
>
> Installation:
>     Oracle 19c: ~2h
>         unzip to ORACLE_HOME
>         runInstaller
>         unzip newest OPatch p6880880_200000_Linux-x86-64
>         apply p30797938_190000_Linux-x86-64 (19.6.1)
>     PostgreSQL 12.3 compiled from source: ~3m30s
>         bunzip postgresql-12.3.tar.bz2 to PGHOME
>         make install-world
>
> Create database:
>     Oracle:
>         create stand alone database:          ~30m
>         create container database (cdb$root): ~47m
>         create pluggable database (pdb):      ~26s
>         Memory to run a database reasonably well:
>             at least 1 GB SGA for a stand alone database
>             at least 4 GB SGA for a container database
>     PostgreSQL:
>         initdb:          <1s
>         create database: <200ms
>         Memory:          128 MB db_buffers
>
> Start/stop database:
>     Oracle:
>         startup (standard, 1 GB SGA):             ~15s
>         shutdown immediate (standard, 1 GB SGA):  ~21s
>         startup (container, 4 GB SGA):            ~16s
>         shutdown immediate (container, 4 GB SGA): ~23s
>     PostgreSQL:
>         pg_ctl start: 0.1s
>         pg_ctl stop:  0.2s
>
> Other discoveries/experiences:
>     Oracle:
>         Set up Data Guard (2 nodes) with observer (3. node): ~4h
>         Applying a PSU or RU often requires downtime of 60m-90m.
>         Migrating a major version often requires downtime of 60m-90m.
>         Migrating a new major version requires a lot of work in advance.
>         Switching Data Guard takes ~1m.
>     PostgreSQL:
>         Set up Patroni (2 nodes) with etcd (3 nodes): ~30m
>         Applying a new minor version requires downtime of <2s.
>         Migrating a new major version requires downtime of <20s.
>         Migrating a new major version requires a few minutes work in advance.
>         Switching Patroni takes ~1s.
>
> Oracle has some good concepts. I like the conecpt of separate UNDO and TEMP tablespaces and not having to care about
vacuuming.Also, I like the idea of global container/cluster-wide views such as CDB_TABLES, etc., a thing which I
definitelyand seriously miss about PostgreSQL. 
>
> What I especially hate about Oracle (despite the license costs, of course) is that it has so many bugs, bugs and even
morebugs and one keeps on searching for patches all day, generating lot of downtime. Applying a PSU or RU is mostly not
enough.
>
> So bottom line, PostgreSQL beats Oracle by far in my opinion, at least as far as installing it and sizes are
concerned.
>
> So, guess what I think is wrong with Oracle after 20 years of working with it...
>
> Sorry for the rant. ;-)
>
> Cheers,
> Paul

I also worked with Oracle for 20 years, mainly as a developer, but some
DBA work as well. I didn't encounter the bugs you seem to have unless I
wondered off into their 'add-ons'. Sticking with the base RDBMS, I found
it to be pretty solid and reliable. However, I prefer Postgres. The main
reason is that with Oracle, you really need to choose one road or the
other - either be a developer or be a DBA. This is especially true with
the introduction of things like DAtaGuard, GoldenGAte etc. Oracle takes
a lot more administration than Postgres and there is a lot of knowledge
to stay on top of.

The oracle installation process is horrible. In addition to all the
library crap, you also commonly run into bugs in their scripts. Worse
still, some of those bugs have been there for 20 years and are just
'known' issues experienced DBAs deal with. Their documentation site is
also horrible.

As a developer, provided you stick with basic database functionality,
i.e. SQL and PL/SQL and avoid their extensions/add-ons, like their
various pub/sub, rules engine, PSP or anything they have obtained by
purchasing a company and 'integrating' it, it is pretty good. I think
they have one of the best locking models out there. The way they handle
indexes and updates is also much faster than postgres and you have to
worry less about the structure of your queries with respect to
performance.

Still, I prefer Postgres. The main reason is that although I may need to
think about how I structure queries, updates and indexes a bit more, on
the whole, it gets out of my way and does what I want - provide a
reliable data store that I can use and get the job done without having
to spend hours caught up in DBA tasks. Updates are easy and the basic
architecture is easy.

The biggest challenge when migrating from oracle to postgres is
recognising they are completely different and while they may both
provide a compliant SQL implementation, the similarities stop there. If
I'm in an environment where someone else is responsible for all the DBA
stuff, Oracle is nice to work with. However, you tend to only be in that
situation when your working in a large, usually bureaucratic,
environment, which tends to detract from the whole experience in other
ways. If your unlucky enough to also be using any of the Oracle 'value
add' extensions, development frameworks, application layers etc, it is
really horrible and mind numbing.

apart from this, Oracle licensing is an absolute nightmare. Apart from
the expense, the complexity is unbelievable and it is almost impossible
to know with any certainty what you will be paying in 12, 24 or more
months.
--
Tim Cross