Oracle vs. PostgreSQL - a comment

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 version 10.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 require at least three nodes, or an odd number >2 respectively) to establish a democracy to determine the master/primary and replica/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-bit only 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 definitely and 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 more bugs 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

Hi Tim,

On 30. May, 2020, at 23:14, Tim Cross <theophilusx@gmail.com> wrote:
I didn't encounter the bugs you seem to have unless I wondered off into their 'add-ons'.

use Oracle Text for example and you'll sooner or later run into severe bugs. My current favorite is ORA-20084 which bugs me for almost a year now. Text index corruption is really painful and Oracle does not feel inclined to fix it.

Sticking with the base RDBMS, I found it to be pretty solid and reliable.

yes, but who does that? We have loads of third party applications which do all the stuff we (DBAs) don't want them to do.

The oracle installation process is horrible.

that's why I scripted the whole create database thing, including PDBs, and their parameters, file paths, etc. For example, my script to create a container database is 782 lines long, whereas PostgreSQL just needs an "initdb". And my script to create a PDB still has 277 lines whereas in PostgreSQL, you can do it with a simple "create database" line.

Even moving a database to another path is a nightmare as you'd have to create new controlfiles, etc. With PostgreSQL you just change the PGDATA variable after moving/copying the whole database cluster and that's it. Well, if you copy it and want to run both at the same time, you still have to change the port in postgresql.conf of course.

Installing database software and creating a database is pretty easy compared to other Oracle stuff. Did you ever install an Oracle Enterprise Manager or Oracle Universal Directory? Don't! That's when the pain really starts.

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.

sic! :-)

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.

and then, some day, a developer approaches a DBA with a query which is generated and, if printed out in a 11pt. sized font, can fill a billboard on a street, to optimize it or search for what's wrong with it, or why it performs so slow... That's usually when I play BOFH because I'm not willing to debug 10 pages which its creator hasn't even cared to take a look at first. :-P :-)

Same goes for the app guys sending me 10 MB of Java stack trace by email containing one single line of ORA-xxxxx. They should send only that line along with a (approximate) time when it occurred. If I get the full stack trace, I send it back to them telling them they should come back when they find the line containing the ORA message. They usually don't come back because they don't know how to grep. :-) Some do, though, and those are the ones I try to help.

If I'm in an environment where someone else is responsible for all the DBA stuff, Oracle is nice to work with.

yes, leave the cardiac arrest to us DBAs. :-P

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.

that's why we are migrating more and more to PostgreSQL and why I came in touch with PostgreSQL at all. There will be some applications which are bound to Oracle and which we never get rid of but we move as many applications to PostgreSQL as is possible to reduce license costs. And it already has payed out!

Cheers,
Paul

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

and then, some day, a developer approaches a DBA with a query which is generated and, if printed out in a 11pt. sized font, can fill a billboard on a street, to optimize it or search for what's wrong with it, or why it performs so slow... That's usually when I play BOFH because I'm not willing to debug 10 pages which its creator hasn't even cared to take a look at first. :-P :-)

Same goes for the app guys sending me 10 MB of Java stack trace by email containing one single line of ORA-xxxxx. They should send only that line along with a (approximate) time when it occurred. If I get the full stack trace, I send it back to them telling them they should come back when they find the line containing the ORA message. They usually don't come back because they don't know how to grep. :-) Some do, though, and those are the ones I try to help.

If I'm in an environment where someone else is responsible for all the DBA stuff, Oracle is nice to work with.

yes, leave the cardiac arrest to us DBAs. :-P

Yes, even after longer time doing Oracle, I still never felt as
comfortable or across things as much as I do with PG. Started with
Oracle 7 and stayed until 11g and each year, it got worse rather than better.

After working as a DBA, I know exactly what you mean. Sometimes, DBA has
to equal "Don't Bother Asking".

As a developer, I have to admit being somewhat embarrassed by the
frequently poor understanding amongst many developers regarding the
technology they are using. I've never understood this. I come across
developers all the time who are completely clueless once outside their
IDE or editor. Too often, they have little understanding of the hosting
environment, the base protocols they are using, the RDBMS or even basic
SQL. I don't understand how you can develop anything of quality if you
don't have a thorough understanding of all the technology involved.

I'm probably just a dinosaur - I also prefer VI and Emacs as my primary
development environments and will use psql and sqlplus before Taod,
pgAdmin, sqlDeveloper etc.

Tim

P.S. for moving Oracle databases, we use to just use sed and change the
paths in the control file. Worked remarkably well. Often used this
technique to 'refresh' our dev or testing systems to current prod data.

--
Tim Cross

Hi Tim,

On 31. May, 2020, at 15:26, Tim Cross <theophilusx@gmail.com> wrote:
P.S. for moving Oracle databases, we use to just use sed and change the
paths in the control file. Worked remarkably well. Often used this
technique to 'refresh' our dev or testing systems to current prod data.

it works well if the length of path+filename does not change. I had bad experiences with this technique if the length changes because controlfiles are binary files unless you alter database backup controlfile to trace as '...'. So, as I said, you need to recreate the controlfile.

But this is Oracle and not PostgreSQL... let's leave it at that here.

Cheers,
Paul

Hi Paul

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

Also, I like the idea of global container/cluster-wide views such as CDB_TABLES, etc.,
a thing which I definitely and seriously miss about PostgreSQL.

Can you specify little more: What's the use case for this (assuming
you know dblink and postgres_fdw)?

:Stefan

Am So., 31. Mai 2020 um 17:09 Uhr schrieb Paul Förster
<paul.foerster@gmail.com>:

Okay I'll bite.

Comparing Postgres with Oracle is a bit like comparing a rubber duck you
might buy your three year old, with a 300000 ton super tanker.

Do they both float? Yeah, but that's about the only similarity.

The rubber duck barely tells you how and why it floats, but the super
tanker is packed with instrumentation, statistics, events and trace
functionality down to every last bit of activity.

Of course, that comes at a cost.

Oracle is also the single most feature-rich database out there - the
feature set of Postgres isn't even 1% of what Oracle has.

It's not a fair comparison.

Postgres has its place, it's free, it works well.

But you can't compare it to an RDBMS like Oracle. Not in terms of size, nor
the time it takes to install (and your 2 hours are definitely on the high
end - it shouldn't take much more than half an hour).

In fact, you likely want to limit the feature set you are installing with
Oracle - both to keep it as lean as possible, to reduce bugs (yes it
contains many millions of lines of code more than Postgres, and it's
written by humans, it will obviously have more bugs), and also to reduce
the time it takes to install, upgrade and patch it. There are ways to do
that.

That's my THB 0.02

On Sat, May 30, 2020 at 7:21 PM Paul Förster <paul.foerster@gmail.com>
wrote:

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 version 10.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 require at least three nodes, or an
odd number >2 respectively) to establish a democracy to determine the
master/primary and replica/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-bit only 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 definitely and 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 more bugs 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

--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/

Hi Stefan,

On 01. Jun, 2020, at 00:35, Stefan Keller <sfkeller@gmail.com> wrote:
Paul Förster <paul.foerster@gmail.com> wrote:

Also, I like the idea of global container/cluster-wide views such as CDB_TABLES, etc.,
a thing which I definitely and seriously miss about PostgreSQL.

Can you specify little more: What's the use case for this (assuming
you know dblink and postgres_fdw)?

you don't expect me to create a dblink to each and every database inside each database cluster? Reconnecting to another database inside the cluster is faster. Yet, it's an inconvenience. Also, if I had to create a new database, I'd also have to setup a dblink to it. Why?

I don't know much about FDW. Our developers (increasingly) use Flyway to distribute their data models across platforms. I know that FDWs are used in some cases but I don't know much about them (yet).

The use case would be to locate whatever a user/developer is referring to, something like this (assuing such a view would be named pg_global_tables):

postgres# select dbname, schema, owner, tablename from pg_global_tables;

postgres=# select * from pg_global_tables;
dbname | schema | owner | tablename
--------+----------------+---------+-----------
db01 | schema_test | test | testtab
db02 | schema_test_13 | test_13 | testtab
(2 rows)

Many times, a user calls and is in his context and you don't even know what database he's on. So you can do a quick search. Interviewing him on the phone and making him tell you from the start a) takes time and b) drives him nuts because it throws him out of his context. But I still need to know which database he's on and which schema he uses.

Same goes for all other object types, such as views, etc.

It's all there in pg_tables and information_schema (what a name...) but it's only inside each database and not globally.

Cheers,
Paul

On Sat, May 30, 2020 at 5:51 PM Paul Förster <paul.foerster@gmail.com>
wrote:

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 version 10.3, I feel compelled to
share some of my experiences with both.

The Stack Overflow survey results are interesting - try the top 2 in Most

Dreaded databases.
https://insights.stackoverflow.com/survey/2019#technology-_-most-loved-dreaded-and-wanted-databases
<https://insights.stackoverflow.com/survey/2019#technology-_-most-loved-dreaded-and-wanted-databases&gt;

Regards,
Jayadevan

Hi Stefan,

On 01. Jun, 2020, at 07:36, Stefan Knecht <knecht.stefan@gmail.com> wrote:

Okay I'll bite.

Comparing Postgres with Oracle is a bit like comparing a rubber duck you might buy your three year old, with a 300000 ton super tanker.

yes, and no. You are right about Oracle having gazillions of features but your comparison is way too drastic.

But be honest: How many features do you actually need? Most people use create table, view, sequence, index and that's basically it. Few use XML tables, Java inside the RDBMS, some (unfortunately) use Oracle Text. Many use BLOBs (instead of CLOBs) to mitigate the varchar2(4000) problem. Bottom line, most applications happily perform (even much better) on not so huge monsters.

The rubber duck barely tells you how and why it floats, but the super tanker is packed with instrumentation, statistics, events and trace functionality down to every last bit of activity.

yes, but why do I need a huge hex block section in some trace file? Only Oracle can read that anyway. I don't have that with PostgreSQL because I don't need it.

And I am never sure if I deliver data to Oracle if I upload a trace file to them. Oracle support (sorry) sucks anyway. It's slow and in 99.9% doesn't solve the problem. I even abstain from opening service requests for years now. And my teammates still opening (and not having given up) service requests never get their first answer sooner than a day or two after the question even though the license says otherwise.

Of course, that comes at a cost.

... excessive, that is...

It's not a fair comparison.

I think it is because the user experience counts. It's like the iOS vs. Android religion. If iOS does exactly what I want then I don't see a need for thousands of tweaking features that Android (probably) has. Same with PostgreSQL. I don't need something like "alter session set events '10046 trace name context forever'" and learn that by heart. Why should I?

Postgres has its place, it's free, it works well.

most definitely yes.

But you can't compare it to an RDBMS like Oracle. Not in terms of size, nor the time it takes to install (and your 2 hours are definitely on the high end - it shouldn't take much more than half an hour).

I see that differently. As for the two hours: that is manual work just as is when installing PostgreSQL. Having done that once is enough of course and then it's packaged into Ansible for distribution. It's not about the 2 hours per se, it's about the big "much more" one has to do in any respect.

In fact, you likely want to limit the feature set you are installing with Oracle

yes, I know chopt. Still...

also to reduce the time it takes to install, upgrade and patch it. There are ways to do that.

yes, I know, which sometimes involves additional databases, storage and VMs, network, firewall rules and the whole nightmare which takes 4-8 weeks to implement because there are 4-5 departments involved.

Why not just limit the downtime as drastic as can *easily* be done with PostgreSQL in the first place without the whole setup nightmare that Oracle requires? I've been asking myself that for ages and always wondered why it couldn't be just as easy as it is with PostgreSQL.

Cheers,
Paul

Stefan Knecht schrieb am 01.06.2020 um 07:36:

Oracle is also the single most feature-rich database out there - the
feature set of Postgres isn't even 1% of what Oracle has.

I try to stay out of discussions like this, but the above is simply
not true.

Oracle indeed has more features but 1% is by far not correct.
Monitoring and analyzing performance problems (using AWR, ASH) are one point,
rolling upgrade without downtime are another one.

I'd say it's more in the vicinity of 80% or 90% depending on which features
you find more important would be more realistic.

But then Postgres has features that Oracle has not, like
transactional DDL, a much richer set of data types (Oracle still
has no proper DATE or BOOLEAN type) and I think the extension system
is something that Oracle lacks as well (at least I am not aware
of any API that would let self-written code e.g. influence the
query optimizer). Postgres also has a more flexible indexing infrastructure
and it's full text search is much more stable and reliable.

So bottom line is - as far as I see it: you can't really come up with a percentage.

From a DBA point of view, the percentage is probably lower than 80%, from
a developer's point of view, Oracle lacks a lot of things and the percentage
would be greater than 100%.

My €0.02

First I have to state that I have used Oracle mostly from 8.x to 10.x
and I have little experience with 11 and 12 and none with current
versions. So I'm comparing Oracle from 10 years ago with current
PostgreSQL, which isn't fair.

On 2020-06-01 12:36:14 +0700, Stefan Knecht wrote:

Comparing Postgres with Oracle is a bit like comparing a rubber duck you might
buy your three year old, with a 300000 ton super tanker.

If it's a 300000 ton rubber duck, that might be correct :-).

Do they both float? Yeah, but that's about the only similarity. 

The rubber duck barely tells you how and why it floats, but the super tanker is
packed with instrumentation, statistics, events and trace functionality down to
every last bit of activity.

That may be, but all that functionality is very hard to use. One of the
main reasons why I prefer PostgreSQL is that it is much easier to
extract the information I need than with Oracle. There is usually an
option to write it to a logfile (in a readable format) or a view to pull
it from (sometimes you need an extension like auto_explain or
pg_stat_statements). With Oracle that was always complicated, needed a
trawl through Metalink (their "support" website) or specialized tools.

Yes, Enterprise Manager was quite nice, but it required an Enterprise
license and we couldn't afford that (I'm guessing that there is now a
similar tool which can be used with Standard Edition). And being a
GUI/Web tool it wasn't that flexible either.

Oracle is also the single most feature-rich database out there - the feature
set of Postgres isn't even 1% of what Oracle has.

As a developer (and part time DBA) I have a hard time thinking of any Oracle
feature that I'm missing in PostgreSQL. OTOH, every time I have to deal
with one of our legacy Oracle databases I notice quite a few things that
PostgreSQL has and Oracle doesn't. But of course that's also not fair.
Over the last 6 years I've become quite familiar with PostgreSQL and
have forgotten much about Oracle. And those databases are old.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

Oracle is losing market share consistently and irreversibly for the last 4-5 yrs. It is not due to migration to open source
RDBMS, but also due to the fact that now there are many alternatives to RDBMS for data storage. Until about 10-15 yrs back,
if the application has to store data, then RDBMS was the only choice. I have seen Oracle used to store news text and for
nothing else in that app. For searches inside the news text, 3rd party app was used. Now such applications use Solar
Lucene or Mongodb type of app. Cassandra is used for high write intensive apps.

If someone publishes cases of Oracle being used for new projects, I bet it will be shockingly low. Same is true for DB2
and even SQLServer.

In general RDBMS is no longer used for every application where they are not the right fit.

On Mon, Jun 01, 2020 at 12:36:14PM +0700, Stefan Knecht wrote:

The rubber duck barely tells you how and why it floats

It sure doesn't spoonfeed but it certainly does tell us
*exactly* how and why it floats.

https://www.postgresql.org/docs/devel/install-getsource.html

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On 6/1/20 4:58 AM, Peter J. Holzer wrote:
[snip]

As a developer (and part time DBA) I have a hard time thinking of any Oracle
feature that I'm missing in PostgreSQL.

The inability to do a point-in-time restoration of a *single* database in a
multi-db cluster is a serious -- and fundamental -- missing feature (never
to be implemented because of the fundamental design).

In SQL Server, it's trivial to restore -- including differentials and WAL
files -- an old copy of a prod database *to a different name* so that you
now have databases FOO and FOO_OLD in the same instance.

In Postgres, though, you've got to create a new cluster using a new port
number (which in our case means sending a firewall request through channels
and waiting two weeks while the RISK team approves opening the port -- and
they might decline it because it's non-standard -- and then the Network team
creates a /change order/ and then implements it).

Bottom line: something I can do in an afternoon with SQL Server takes two
weeks for Postgres.

This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.

On 02/06/2020 09:22, Ron wrote:
The inability to do a point-in-time restoration of a single database in a multi-db cluster is a serious -- and fundamental -- missing feature (never to be implemented because of the fundamental design).

In SQL Server, it's trivial to restore -- including differentials and WAL files -- an old copy of a prod database to a different name so that you now have databases FOO and FOO_OLD in the same instance.

In Postgres, though, you've got to create a new cluster using a new port number (which in our case means sending a firewall request through channels and waiting two weeks while the RISK team approves opening the port -- and they might decline it because it's non-standard -- and then the Network team creates a change order and then implements it).

Bottom line: something I can do in an afternoon with SQL Server takes two weeks for Postgres.

This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.

But that's nothing to do with Postgres; it takes two weeks because you have broken procedures imho

Tim Clarke

Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.

On 6/2/20 11:22 AM, Ron wrote:

The inability to do a point-in-time restoration of a *single* database
in a multi-db cluster is a serious -- and fundamental -- missing
feature (never to be implemented because of the fundamental design).

It is possible via 3rd party tools like pg_probackup and pgbackrest.

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

I spent about 10 years as an Oracle DBA (back around Oracle 7 and 8) and
the last 20 or so years doing PostgreSQL.

My initial impressions were that Oracle did a better job providing tools
and options that users and DBAs need and PostgreSQL was pretty much
roll-your-own.

Things like being able to copy tables from one database to another or to
restore the dump of a table to another table name in the same database are
things that would make a DBA's life a lot easier.

I worked on a general-purpose web-based tool that could read the PostgreSQL
table structures and provide a form for querying and updating most field
types in a table, but never really took it beyond the persona use basis.
Now that I'm retired, maybe I"ll work on this again.
--
Mike Nolan

On 6/2/20 3:27 AM, Tim Clarke wrote:

On 02/06/2020 09:22, Ron wrote:

The inability to do a point-in-time restoration of a *single* database in
a multi-db cluster is a serious -- and fundamental -- missing feature
(never to be implemented because of the fundamental design).

In SQL Server, it's trivial to restore -- including differentials and WAL
files -- an old copy of a prod database *to a different name* so that you
now have databases FOO and FOO_OLD in the same instance.

In Postgres, though, you've got to create a new cluster using a new port
number (which in our case means sending a firewall request through
channels and waiting two weeks while the RISK team approves opening the
port -- and they might decline it because it's non-standard -- and then
the Network team creates a /change order/ and then implements it).

Bottom line: something I can do in an afternoon with SQL Server takes two
weeks for Postgres.

This has given Postgres a big, fat black eye with our end users.

--
Angular momentum makes the world go 'round.

But that's nothing to do with Postgres; it takes two weeks because you
have broken procedures imho

Following ISO 20000 process (which is a pain) doesn't impact SQL Server like
it does Postgres.

--
Angular momentum makes the world go 'round.

On 6/2/20 4:59 AM, Grigory Smolkin wrote:

On 6/2/20 11:22 AM, Ron wrote:

The inability to do a point-in-time restoration of a *single* database in
a multi-db cluster is a serious -- and fundamental -- missing feature
(never to be implemented because of the fundamental design).

It is possible via 3rd party tools like pg_probackup and pgbackrest.

pgbackrest does *not* support PITR recovery of individual databases into
*new* database names in the same cluster (so that the end user can have both
the current database and an old version at the same time).

--
Angular momentum makes the world go 'round.