Thread: Oracle vs. PostgreSQL - a comment

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
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

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

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
bugsme 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
scriptto 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
youjust change the PGDATA variable after moving/copying the whole database cluster and that's it. Well, if you copy it
andwant 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
installan 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
structureof 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'susually when I play BOFH because I'm not willing to debug 10 pages which its creator hasn't even cared to take a
lookat 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
shouldsend only that line along with a (approximate) time when it occurred. If I get the full stack trace, I send it
backto them telling them they should come back when they find the line containing the ORA message. They usually don't
comeback 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
someapplications which are bound to Oracle and which we never get rid of but we move as many applications to PostgreSQL
asis 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'susually when I play BOFH because I'm not willing to debug 10 pages which its creator hasn't even cared to take a
lookat 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.
Theyshould send only that line along with a (approximate) time when it occurred. If I get the full stack trace, I send
itback to them telling them they should come back when they find the line containing the ORA message. They usually
don'tcome 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
changesbecause 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>:
>
> 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
changesbecause 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 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
databaseinside the cluster is faster. Yet, it's an inconvenience. Also, if I had to create a new database, I'd also
haveto 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.
Iknow 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
wouldbe 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
becauseit 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

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
tonsuper 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
basicallyit. Few use XML tables, Java inside the RDBMS, some (unfortunately) use Oracle Text. Many use BLOBs (instead
ofCLOBs) to mitigate the varchar2(4000) problem. Bottom line, most applications happily perform (even much better) on
notso 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
withPostgreSQL 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'sslow and in 99.9% doesn't solve the problem. I even abstain from opening service requests for years now. And my
teammatesstill opening (and not having given up) service requests never get their first answer sooner than a day or two
afterthe 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
wantthen I don't see a need for thousands of tweaking features that Android (probably) has. Same with PostgreSQL. I
don'tneed something like "alter session set events '10046 trace name context forever'" and learn that by heart. Why
shouldI? 

> 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
hoursare 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
thatonce is enough of course and then it's packaged into Ansible for distribution. It's not about the 2 hours per se,
it'sabout 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
nightmarewhich 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
setupnightmare that Oracle requires? I've been asking myself that for ages and always wondered why it couldn't be just
aseasy 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

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.

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

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).

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

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

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.

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> 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).

No, nothing does as PG doesn't support it as we have one WAL stream for
the entire cluster.

Generally speaking, I discourage having lots of databases under one PG
cluster for exactly these kinds of reasons.  PG's individual clusters
are relatively lightweight, after all.

Thanks,

Stephen

On 6/2/20 1:30 PM, Stephen Frost wrote:
> Greetings,
>
> * Ron (ronljohnsonjr@gmail.com) wrote:
>> 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).
> No, nothing does as PG doesn't support it as we have one WAL stream for
> the entire cluster.

Right.  Making WAL files specific to a database should be high on the list 
of priorities.

> Generally speaking, I discourage having lots of databases under one PG
> cluster for exactly these kinds of reasons.

It's just two... :)

>    PG's individual clusters are relatively lightweight, after all.

But require a new port, and Enterprises have Processes that must be followed.

-- 
Angular momentum makes the world go 'round.

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> On 6/2/20 1:30 PM, Stephen Frost wrote:
> >* Ron (ronljohnsonjr@gmail.com) wrote:
> >>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).
> >No, nothing does as PG doesn't support it as we have one WAL stream for
> >the entire cluster.
>
> Right.  Making WAL files specific to a database should be high on the list
> of priorities.

That's almost certainly not going to happen.  I'm not aware of anyone
pursuing that nor has there been any discussion on hackers.

In other words, I wouldn't bet on that being something that's going to
happen as there's seemingly very little interest in it among those who
are developing PG and it'd be an awful lot of work for what seems to be
very little actual gain.

> >Generally speaking, I discourage having lots of databases under one PG
> >cluster for exactly these kinds of reasons.
>
> It's just two... :)
>
> >   PG's individual clusters are relatively lightweight, after all.
>
> But require a new port, and Enterprises have Processes that must be followed.

Sure they do.  Automate them.

:)

Thanks,

Stephen

On 02/06/2020 19:43, Stephen Frost wrote:
>> But require a new port, and Enterprises have Processes that must be followed.
> Sure they do.  Automate them.
>
> :)
>
> Thanks,
>
> Stephen


+1 for automation, isoX != slow


Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852
58031687 | 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
youmust 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
ManifestVoting 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 1:56 PM, Tim Clarke wrote:
> On 02/06/2020 19:43, Stephen Frost wrote:
>>> But require a new port, and Enterprises have Processes that must be followed.
>> Sure they do.  Automate them.
>>
>> :)
>>
>> Thanks,
>>
>> Stephen
>
> +1 for automation, isoX != slow
It is when FW rules must be manually approved (and they do review them all), 
then the TASK is converted to a CHANGE and that goes before a CAB meeting.

That's all bypassed with SQL Server and Oracle, though.

-- 
Angular momentum makes the world go 'round.

>
> Generally speaking, I discourage having lots of databases under one PG
> cluster for exactly these kinds of reasons.  PG's individual clusters
> are relatively lightweight, after all.
>

Plus PG does not directly support cross database queries using 3 part name, something
sqlserver excels at.

Greetings,

* Ravi Krishna (sravikrishna@comcast.net) wrote:
> > Generally speaking, I discourage having lots of databases under one PG
> > cluster for exactly these kinds of reasons.  PG's individual clusters
> > are relatively lightweight, after all.
>
> Plus PG does not directly support cross database queries using 3 part name, something
> sqlserver excels at.

Eh, that's something that I think we should be looking at supporting, by
using FDWs, but I haven't tried to figure out how hard it'd be.

Thanks,

Stephen

> On Jun 2, 2020, at 13:30, Stephen Frost <sfrost@snowman.net> wrote:
> 
> Eh, that's something that I think we should be looking at supporting, by
> using FDWs, but I haven't tried to figure out how hard it'd be.

Being able to access a FDW that way would rock.

--
-- Christophe Pettus
   xof@thebuild.com

>
> Eh, that's something that I think we should be looking at supporting, by
> using FDWs, but I haven't tried to figure out how hard it'd be.
>

How good will that be in performance.

In db2 you can do it using dblinks and that kills performance. isn't FDW  something like dblink.

The cool part is that in SQLServer the optimizer recognizes 3 part name and caching works also.

Greetings,

* Ravi Krishna (sravikrishna@comcast.net) wrote:
> > Eh, that's something that I think we should be looking at supporting, by
> > using FDWs, but I haven't tried to figure out how hard it'd be.
>
> How good will that be in performance.
>
> In db2 you can do it using dblinks and that kills performance. isn't FDW  something like dblink.
>
> The cool part is that in SQLServer the optimizer recognizes 3 part name and caching works also.

I'm sure there's things we can do to improve the performance of the FDW.
Not sure we'll get to a point where we are actually cacheing information
from the far side... but who knows, maybe if we arrange to have a
notification sent whenever certain objects are updated...

These things could be worked on independnetly, of course, no need to
have one done before the other.

Thanks,

Stephen

Ron schrieb am 02.06.2020 um 20:38:
>
>>    PG's individual clusters are relatively lightweight, after all.
>
> But require a new port, and Enterprises have Processes that must be followed.

I am not 100% sure, but I think you can get around that by putting pgPool or pgBouncer
in front and make all connections through that (with a single port)

Greetings,

* Guyren Howe (guyren@gmail.com) wrote:
> On Jun 2, 2020, at 14:16 , Stephen Frost <sfrost@snowman.net> wrote:
> > I'm sure there's things we can do to improve the performance of the FDW.
> > Not sure we'll get to a point where we are actually cacheing information
> > from the far side... but who knows, maybe if we arrange to have a
> > notification sent whenever certain objects are updated...
> >
> > These things could be worked on independnetly, of course, no need to
> > have one done before the other.
>
> By all means, let’s improve the FDW. But is it practical to make it possible to query across databases on the same
server, in a similar manner to SQL Server, without needing FDW at all? 

If you'd like to try and figure out how to make that work, it could be
theoretically possible, but I seriously doubt it'd be at all straight
forward to do, or that it'd ultimately end up being worth it.

Thanks,

Stephen

Greetings,

* Adam Brusselback (adambrusselback@gmail.com) wrote:
> >  How good will that be in performance.
>
> In my experience, not great. It's definitely better than not having it at
> all, but it does not make for quick queries and caused serious
> connection overhead when a query referenced that foreign table. I've since
> moved to logical replication to improve the situation there.

How much of the issue there has been setup/connection time..?  That's
something I've wondered about maybe finding a way to improve on.  Also
depends on if you're using the "run explain first before running the
query" approach with the FDW or if you're actually running analyze on
the foreign tables.

Thanks,

Stephen

On Tue, Jun 02, 2020 at 11:18:52PM +0200, Thomas Kellerer wrote:
> Ron schrieb am 02.06.2020 um 20:38:
> >
> >>   PG's individual clusters are relatively lightweight, after all.
> >
> >But require a new port, and Enterprises have Processes that must be followed.
> 
> I am not 100% sure, but I think you can get around that by putting pgPool or pgBouncer
> in front and make all connections through that (with a single port)
> 

Hi,

That was going to be my suggestion and you can connect to local sockets
only.

Regards,
Ken

Ron wrote:

> On 6/2/20 1:56 PM, Tim Clarke wrote:
> > On 02/06/2020 19:43, Stephen Frost wrote:
> > > > But require a new port, and Enterprises have Processes that must be followed.
> > > Sure they do.  Automate them.
> > > 
> > > :)
> > > 
> > > Thanks,
> > > Stephen
> > 
> > +1 for automation, isoX != slow
> It is when FW rules must be manually approved (and they do review them all),
> then the TASK is converted to a CHANGE and that goes before a CAB meeting.
> 
> That's all bypassed with SQL Server and Oracle, though.

Presumably, these processes have to be followed for SQL
Server and Oracle at least once too. If someone with
the same process requirements wanted to use Postgresql
instead, and they knew that they might need multiple
ports, presumably the process could be followed once
for a (possibly pre-allocated) set of ports. It doesn't
have to be any less efficient. Same number of meetings,
just in relation to a different number of ports. The
only problem would be when the processes were followed
for a single port before it was realised that more
ports would be needed later. Then the process would
have to be followed twice, once for the first port, and
once again for all the other ports that might become
necessary.

cheers,
raf

> On 6/2/20 1:30 PM, Stephen Frost wrote:
>> No, nothing does as PG doesn't support it as we have one WAL stream for
>> the entire cluster.

On 6/2/20 11:38, Ron wrote:
> Right.  Making WAL files specific to a database should be high on the
> list of priorities.

Did Oracle change this?  Last time I looked, I don't think Oracle
supported local redo in their multitenant architecture either.

-Jeremy


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

> On 6/2/20 1:30 PM, Stephen Frost wrote:
>> No, nothing does as PG doesn't support it as we have one WAL stream for
>> the entire cluster.

On 6/2/20 11:38, Ron wrote:
> Right.  Making WAL files specific to a database should be high on the
> list of priorities.

Did Oracle change this?  Last time I looked, I don't think Oracle
supported local redo in their multitenant architecture either.

[...]

 

Regardless of what Oracle does, I agree this would be a huge step in the right direction for pg-DBAs.

I have absolutely no clue about how much work is required etc., but I think it's kind of strange that no companies have invested in making this happen.

 

I manage database clusters where the number of databases is a reason not to do logical replication based upgrades, where pg_upgrade is far preferred instead.

 

If this were to be the case, I would be very concerned that a bunch of things would have to change:

1.  Shared catalogs would have txid problems unless you stay with global txids and then how do local wal streams work there?

2.  Possibility that suddenly streaming replication has the possibility of different databases having different amounts of lag

3.  Problems with io management on WAL on high throughput systems (I have systems where a db cluster generates 10-20TB of WAL per day)

 

So I am not at all sure this would be a step in the right direction or worth the work.  

On Sun, May 31, 2020 at 11:26:07PM +1000, Tim Cross wrote:
> 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. 

In my many years as a DBA/developer, I have learned that understanding
the low-level stuff, even down to the CPU behavior, allows debugging of
problems much more efficiently, to the point where it looks like magic
when you can quickly point out the problem.  Also, the low-level stuff
rarely changes, so once you understand it, you can use it forever.  The
big problem is getting people to see the value in learning that stuff
when they don't have an immediate need --- curiosity helps  with
motivation.  :-)

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On 2/6/20 10:45 μ.μ., Ravi Krishna wrote:
>> Generally speaking, I discourage having lots of databases under one PG
>> cluster for exactly these kinds of reasons.  PG's individual clusters
>> are relatively lightweight, after all.
>>
> Plus PG does not directly support cross database queries using 3 part name, something
> sqlserver excels at.
Maybe because SQL server does not have real databases but schemas instead ?
This sucks security wise.
>
>
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

>> Plus PG does not directly support cross database queries using 3 part name, something
>> sqlserver excels at.

>Maybe because SQL server does not have real databases but schemas instead ?
>This sucks security wise.

SQLServer has real databases with its own transaction log files.  You can restore individual databases in a cluster.
They also have schemas which are not same as users (Oracle treats users and schemas same).

For security, there is grant connect to the DB and further filtering based on schema.

I find SQLServer implementation pretty strong in this.  

The only time this can be a problem is when few databases failover in a mirrored environment (streaming replication in
PGspeak).
 
Then suddenly 3 part names would fail if the remote DB is no longer primary. My experience with SQLServer is badly
dated.Last 
 
time I worked was SS2008.  I believe in later versions they solved this problem by the failover group concept which
failoversall
 
inter-related databases at one go.

BTW Mysql treats databases and schemas as same (that's what it was few years ago)

On 5/6/20 3:33 μ.μ., Ravi Krishna wrote:
>>> Plus PG does not directly support cross database queries using 3 part name, something
>>> sqlserver excels at.
>> Maybe because SQL server does not have real databases but schemas instead ?
>> This sucks security wise.
> SQLServer has real databases with its own transaction log files.  You can restore individual databases in a cluster.
> They also have schemas which are not same as users (Oracle treats users and schemas same).
Ok, I never meant SQL Server does not have real databases, I meant it handles databases as top level schemas.
>
> For security, there is grant connect to the DB and further filtering based on schema.
PostgreSQL has stronger protection at connection level, via pg_hba.conf . PostgreSQL also supports db-level GRANTs .
In MS SQL server if you need an additional DB for maintenance tasks or to act as an intermediate bridge (like in our
case)with write access on it, then automatically you write access to the main 
 
schema (ok DB in MS SQL terms). (and need further REVOKES to fix security). This (security-wise) is just poor.
So the cross db joins come with a price.
>
> I find SQLServer implementation pretty strong in this.
>
> The only time this can be a problem is when few databases failover in a mirrored environment (streaming replication
inPG speak).
 
> Then suddenly 3 part names would fail if the remote DB is no longer primary. My experience with SQLServer is badly
dated.Last
 
> time I worked was SS2008.  I believe in later versions they solved this problem by the failover group concept which
failoversall
 
> inter-related databases at one go.
>
> BTW Mysql treats databases and schemas as same (that's what it was few years ago)
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Achilleas Mantzios schrieb am 05.06.2020 um 14:05:
>> Plus PG does not directly support cross database queries using 3 part name, something
>> sqlserver excels at.
>
> Maybe because SQL server does not have real databases but schemas instead ?
> This sucks security wise.

That is wrong.

SQL Server has both: databases and schemas and it allows for standard compliant catalog.schema.table references.

I think you are confusing that with MySQL where a schema and a database are the same thing

On 5/6/20 5:19 μ.μ., Thomas Kellerer wrote:
> Achilleas Mantzios schrieb am 05.06.2020 um 14:05:
>>> Plus PG does not directly support cross database queries using 3 part name, something
>>> sqlserver excels at.
>> Maybe because SQL server does not have real databases but schemas instead ?
>> This sucks security wise.
> That is wrong.
>
> SQL Server has both: databases and schemas and it allows for standard compliant catalog.schema.table references.
>
> I think you are confusing that with MySQL where a schema and a database are the same thing
I think that you are confusing what you think I am confusing. I am talking about our MS SQL installation here at work.
Notdone by me anyways I am the pgsql guy, but still do most of the query stuff 
 
in ms sql as well.
I wrote already that my comment was far fetched in a subsequent email.
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 6/5/20 10:02 AM, Achilleas Mantzios wrote:
> On 5/6/20 5:19 μ.μ., Thomas Kellerer wrote:
>> Achilleas Mantzios schrieb am 05.06.2020 um 14:05:
>>>> Plus PG does not directly support cross database queries using 3 part 
>>>> name, something
>>>> sqlserver excels at.
>>> Maybe because SQL server does not have real databases but schemas instead ?
>>> This sucks security wise.
>> That is wrong.
>>
>> SQL Server has both: databases and schemas and it allows for standard 
>> compliant catalog.schema.table references.
>>
>> I think you are confusing that with MySQL where a schema and a database 
>> are the same thing
> I think that you are confusing what you think I am confusing. I am talking 
> about our MS SQL installation here at work. Not done by me anyways I am 
> the pgsql guy, but still do most of the query stuff in ms sql as well.
> I wrote already that my comment was far fetched in a subsequent email.

I manage SQL Server instances with multiple databases, each with multiple 
schemas.

-- 
Angular momentum makes the world go 'round.

On Wed, Jun  3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote:
> I agree these are all technical issues, but nevertheless - "implementation
> details", which DBAs don't care about. What's important from a DBA's
> perspective is not whether WAL is cluster-wide or database-wide, but whether
> it's possible to manage backups/PITR/restores of individual databases in a more
> convenient matter, which other RDBMS-vendors seem to provide.
>  
> I love PG, have been using it professionally since 6.5, and our company depends
> on it, but there are things other RDBMS-vendors do better...

The bigger issue is that while we _could_ do this, it would add more
problems and complexity, and ultimately, I think would make the
software less usable overall and would be a net-negative.  We know of no
way to do it without a ton of negatives.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On Tue, Jun  2, 2020 at 03:45:08PM -0400, Ravi Krishna wrote:
> 
> > 
> > Generally speaking, I discourage having lots of databases under one PG
> > cluster for exactly these kinds of reasons.  PG's individual clusters
> > are relatively lightweight, after all.
> > 
> 
> Plus PG does not directly support cross database queries using 3 part name, something
> sqlserver excels at.

We consider the lack of this ability to be a security benefit. 
Cross-container queries can be done using schemas.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On 6/13/20 1:46 PM, Bruce Momjian wrote:
> On Wed, Jun  3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote:
>> I agree these are all technical issues, but nevertheless - "implementation
>> details", which DBAs don't care about. What's important from a DBA's
>> perspective is not whether WAL is cluster-wide or database-wide, but whether
>> it's possible to manage backups/PITR/restores of individual databases in a more
>> convenient matter, which other RDBMS-vendors seem to provide.
>>   
>> I love PG, have been using it professionally since 6.5, and our company depends
>> on it, but there are things other RDBMS-vendors do better...
> The bigger issue is that while we _could_ do this, it would add more
> problems and complexity, and ultimately, I think would make the
> software less usable overall and would be a net-negative.  We know of no
> way to do it without a ton of negatives.

How do other RDBMSs do it with ease?  (I know it's an architectural issue, 
but what's the architectural issue?)

-- 
Angular momentum makes the world go 'round.

On Sat, Jun 13, 2020 at 05:06:37PM -0500, Ron wrote:
> On 6/13/20 1:46 PM, Bruce Momjian wrote:
> > On Wed, Jun  3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote:
> > > I agree these are all technical issues, but nevertheless - "implementation
> > > details", which DBAs don't care about. What's important from a DBA's
> > > perspective is not whether WAL is cluster-wide or database-wide, but whether
> > > it's possible to manage backups/PITR/restores of individual databases in a more
> > > convenient matter, which other RDBMS-vendors seem to provide.
> > > I love PG, have been using it professionally since 6.5, and our company depends
> > > on it, but there are things other RDBMS-vendors do better...
> > The bigger issue is that while we _could_ do this, it would add more
> > problems and complexity, and ultimately, I think would make the
> > software less usable overall and would be a net-negative.  We know of no
> > way to do it without a ton of negatives.
> 
> How do other RDBMSs do it with ease?  (I know it's an architectural issue,
> but what's the architectural issue?)

I don't know.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On Sat, Jun 13, 2020 at 09:27:25PM -0400, Bruce Momjian wrote:
> On Sat, Jun 13, 2020 at 05:06:37PM -0500, Ron wrote:
> > On 6/13/20 1:46 PM, Bruce Momjian wrote:
> > > On Wed, Jun  3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote:
> > > > I agree these are all technical issues, but nevertheless - "implementation
> > > > details", which DBAs don't care about. What's important from a DBA's
> > > > perspective is not whether WAL is cluster-wide or database-wide, but whether
> > > > it's possible to manage backups/PITR/restores of individual databases in a more
> > > > convenient matter, which other RDBMS-vendors seem to provide.
> > > > I love PG, have been using it professionally since 6.5, and our company depends
> > > > on it, but there are things other RDBMS-vendors do better...
> > > The bigger issue is that while we _could_ do this, it would add more
> > > problems and complexity, and ultimately, I think would make the
> > > software less usable overall and would be a net-negative.  We know of no
> > > way to do it without a ton of negatives.
> > 
> > How do other RDBMSs do it with ease?  (I know it's an architectural issue,
> > but what's the architectural issue?)
> 
> I don't know.

I don't know the details, but I do know the general issues.  Other
vendors must have sacrificed architectural simplicity, features,
reliability, or performance to allow these things.  For example, it
wouldn't be hard to just make databases another level of container above
schemas to allow for simple cross-database queries, but we would lose
the security isolation of databases (connection control. private system
tables and extensions) to do that.  Having per-database WAL causes loss
of performance, reliability issues, and architectural complexity.  Those
problems might be solvable, but you will need to take a hit in one of
these areas.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On Sat, Jun 13, 2020 at 09:27:25PM -0400, Bruce Momjian wrote:

On Sat, Jun 13, 2020 at 05:06:37PM -0500, Ron wrote:

On 6/13/20 1:46 PM, Bruce Momjian wrote:

On Wed, Jun  3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote:

I agree these are all technical issues, but nevertheless - "implementation

details", which DBAs don't care about. What's important from a DBA's

perspective is not whether WAL is cluster-wide or database-wide, but whether

it's possible to manage backups/PITR/restores of individual databases in a more

convenient matter, which other RDBMS-vendors seem to provide.

I love PG, have been using it professionally since 6.5, and our company depends

on it, but there are things other RDBMS-vendors do better...

The bigger issue is that while we _could_ do this, it would add more

problems and complexity, and ultimately, I think would make the

software less usable overall and would be a net-negative.  We know of no

way to do it without a ton of negatives.

How do other RDBMSs do it with ease?  (I know it's an architectural issue,

but what's the architectural issue?)

I don't know.

I don't know the details, but I do know the general issues.  Other

vendors must have sacrificed architectural simplicity, features,

reliability, or performance to allow these things.  For example, it

wouldn't be hard to just make databases another level of container above

schemas to allow for simple cross-database queries, but we would lose

the security isolation of databases (connection control. private system

tables and extensions) to do that.  Having per-database WAL causes loss

of performance, reliability issues, and architectural complexity.  Those

problems might be solvable, but you will need to take a hit in one of

these areas.

-- 

>> Did Oracle change this?  Last time I looked, I don't think Oracle supported local redo in their multitenant architecture either.

Hi Jeremy, they are moving in this direction (project seems to be called DGPDB internally). And what is interesting for this discussion is that they initially had redo (and even undo) at instance level, but moved this to PDB following what users were asking for: pdb level flashback, pitr, switchover,...But it is hard to compare those needs with PostgreSQL. Multiple db clusters in PG is lightweight and is the right place to isolate (users, cgroups,...). Oracle CDB is too heavy to have multiple on one host. And many isolation features is made at PDB level (lockdown profiles, resource manager)

-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

Hi Ludovico,

> Sorry for this reply, but I feel it is necessary to make it clear what is reality and what is FUD against Oracle from
Paul'se-mails in this thread... 

nothing of it was a FUD. It was a comparison done on a single machine. Then, I drew my conclusions from that and added
mypersonal view. You don't necessarily havet to agree to my opinion nor did I ask you to agree. But it's definitely not
FUD!

> (Note: I work for Oracle now, but I've had 20 years experience as multi-platform database consultant)

I work *with* Oracle databases too and have been for 20+ years. But I do not work *for* Oracle and I don't feel
inclinedto spread their advertising. 

> That is... not a problem. Is it, for real?

technically no. Still, a) it makes no sense at all to advertise a 64 bit product that still needs 32 bit support (one
couldeven call that an advertising lie!) and b) it may (or may not?) cost performance. 

> Although I completely agree that the Oracle installation process is much longer and more complex than PostgreSQL, I
disagreewith the rest. 

to create a CDB, you still have to provide paths which are then hard-coded into the control-file! Oracle software takes
tonsof space and the installation takes longer. 

> The CREATE PLUGGABLE DATABASE is also a single line SQL command... The scripts to create a PDB or a PostgreSQL
databasedepend a lot on what do you want to achieve (empty database? specific users or permissions? sanity checks?
pre-emptivebackup? add to cmdb?) 

yes, create pluggable database. Takes 30+ secs to run, while on PostgreSQL, it takes a few milliseconds. But we require
acertain structure in the filesystem which makes the thing much more complex. 

> For a new PostgreSQL architecture in the past I have written 230 lines of code to automate the database creation in
anexisting PostgreSQL cluster. That included setting up application users, hardening the default permissions on the
publicschema, registering in the CMDB, etc. It is not much code in my opinion and it is done once for all. 

again, a simple initdb, or create database would do. For all to be done in PostgreSQL, my script is some 30 lines and
includesdefault user creation, revoking some stuff, etc., nothing compared to what I need for Oracle. 

> This is bashing FUD against Oracle or lack of basic Oracle knowledge. Oracle online move, reorganization and patching
capabilitiesare far ahead from PostgreSQL. 

nonsense!

> Online Datafile Movement has existed since 12cR1. 8 years!
https://oracle-base.com/articles/12c/online-move-datafile-12cr1

yes, I know. But did you try to move SYSTEM, UNDO or TEMP tablespace or online redo log files? Did you try to move the
*whole*database? You can move all data/index tablespace files with that (one by one which is tiresome with many files),
butyou can't move the essential tablespace files! Well, you can move the online reado log files by creating new ones
anddropping the old ones but that's about it. You still can't move the essential tablespace files. I admit that I
didn'ttry that with 19.x but it wasn't possible up to now. 

> Prior to that, for many years, it was possible to offline, move, rename and online datafiles, either grouped or
singularly,without stopping the instance. Online logs can be rotated to a new location online. The only exception are
thecontrolfiles that require an ALTER SYSTEM, shutdown, move, startup. 

I know all that but it still requires far to much work! And it still doesn't move the while database!

> PostgreSQL must be stopped in order to move the database to a new path, and if it is to a new filesystem, you need
thetime for a full copy of the data, unless you do it via backup and recovery to reduce the downtime. 

that's not true. pg_basebackup it while running to a new destination. Set up primary_conn_info and replication and
startup the copy. Once it's in sync and you have a physical copy, change the port in postgresql.conf of the copy, stop
bothand then only launch the copy. Promote it then. The switch takes 2-3 secs of downtime. 

If downtime doesn't matter but space does, stop the database cluster, move the whole PGDATA to a new location and start
itthere. It only requires as much downtime as the copy process takes plus a few seconds for shutdown and startup. 

> Again no, you don't need to recreate the controlfile for moving the datafiles , and no: altering binary controlfiles
with`sed` is nothing a production DBA would ever do... 

again no, you can't move SYSTEM, UNDO and TEMP! Also, what I mentioned was a quick and dirty hack and is not
recommended.

> The laziness or lack of knowledge of your developers  is not a problem with Oracle technology. Still, you can get a
"querywhich is generated and, if printed out in a 11pt. sized font, can fill a billboard on a street", give it to
Oracleand get the optimal execution plan 99.9% of the times. And if the execution is not optimal, Statistics Feedback
kicksin and tries to produce a better one next time. And if it still fails, you can use hints or produce a trace 10053
andpin-point the reason for the CBO choice and get better statistics (or physical structures) for it. 

yes, I know all this too. The problem is not setting an event, asking the tuning advisor of an OEM for help or
providinga hint to the query (something I find absolutely disturbing per se!). The problem is the lazy application
developerasking a DBA to do the work for him! 

> Seriously, I agree to a part of this. I would never buy something as expensive as Oracle for a basic CRUD
application,PostgreSQL does the job very well. 

but that makes 99.9% of all applications, no matter how big databases become during their lifetime. It's like the
typicalM$ Office argument: "everyone has it, so it's used in this company too!" Ok, this is whataboutism, but it
applieshere. 

> With Oracle you buy additional features that go beyond that and it is up to you to evaluate what you will be missing
whenusing PostgreSQL instead (you might say "not much for the price", I would not agree),  and if you will still be
ableto respond to your business requirements. 

with PostgreSQL you can install any extension you want or need, or even write them yourself. Your point being? Throwing
moremillions at Oracle? It can't even authenticate LDAP or Windows AD users without buying a very expensive directory
service(OID, OUD, or whatever) from them. 

And any Oracle software has bugs, bugs and even more bugs. I have never seen such a bug-ridden software as Oracle.
Aftera patch is before the next patch. Many patches even don't work with each other, etc. This is far worse than even
Windowshas been in its darkest days. 

> Because with Oracle you don't buy a good DBA experience (or a good developer experience, even if I think that the
developerexperience with Oracle is actually pretty good), you buy an enabler for specific business critical
requirements:availability, performance, concurrency... 

nonsense! You sound like a marketing brochure. :-P

> Trying to minimize the value of Oracle instrumentation is as FUD as it can be...

again, nonsense! If Oracle software tells me to look at some log file and all I find is a hex dump, then it's worthless
tome, both as a user and as a DBA. 

> Oracle not only gives you the instruments to understand what's happening, but also what's happened in the past.
[..]
> PostgreSQL gives you the source code that is obviously better, but it gives different answers for different problems.

there's the brochure again. Look at PostgreSQL's single most important log file and you have it all, depending on what
loggingyou have enabled or disabled. 

> I used to have procedures to create 10046 traces and analyze them. Again, instrumentation is knowledge.

again, why need that at all?

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

wow!

> But with RAC you have online patching, and with ADG you have rolling upgrades with almost no downtime. Not for the
instance,but for the business. (I know, more money, but still it is possible). 

yes, more and more money. In fact, a huge and expensive car per database per year. But since stopping and starting an
instancetakes about a minute or so, while it takes 2-3 seconds with PostgreSQL, I don't see why anyone should again
throwmoney at Oracle. 

Oh, and there's still the LONG datatype, which Oracle can't seem to get rid of in its own database, because it's so
bloatedbut recommends people to not use it for almost 20 years now. SCNR. 

And with Oracle, you still can't rename users, a feature that DBAs have been asking for for decades. And there's more
stuff,but I'm tired now. 

Botton line: You don't work *at* Oracle, you don't work *with* Oracle, you work *for* Oracle as an evangelist. That's
whythis is my final mail in this thread. I don't discuss religion. 

Cheers,
Paul