Re: Oracle vs. PostgreSQL - a comment

[Ludovico Caldara]

Il giorno gio 29 apr 2021 alle ore 19:13 Paul Förster <
paul.foers...@gmail.com> ha scritto:

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

Features are not an opinion. I am not trying to convincing you that Oracle
is better than PostgreSQL (postgresql official mailing lists are not a good
place for that ^^)
But I can't stand when people advocate against Oracle (or FWIW, whatever
technology) using, among understandable  arguments, also false claims.
Oracle is "heavy" (but fast in application performance), it takes time to
install it. Stopping and starting the instances takes time. Patching can be
painful if you have encountered too many bugs in the past and need to merge
the patches. It is the most expensive database in the world (at least,
looking at what you pay and not what you get). It is complex for the DBAs
and the learning curve gets steeper and steeper with more and more features
added at every release. All these points are true.

Now, let's keep this momentum and continue with more incontestable truth:

> 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), but you can't move the essential tablespace
> files! Well, you can move the online reado log files by creating new ones
> and dropping the old ones but that's about it. You still can't move the
> essential tablespace files. I admit that I didn't try that with 19.x but it
> wasn't possible up to now.
>
>
Tried? I blogged about it 8 years ago:
http://www.ludovicocaldara.net/dba/oracle-database-12c-move-datafile-online/
And I use this feature extensively like tons of DBAs out there.

Some more examples:

--- UNDO, move online back and forth
SQL>  alter database move datafile
'+DATA/_MGMTDB/DATAFILE/undotbs1.279.1071175797' to '/tmp/undotbs1.dbf';

Database altered.

--- SYSTEM, move online back and forth
SQL> ALTER DATABASE MOVE DATAFILE
'/u02/app/oracle/oradata/cdb1/system01.dbf' TO '+DATA';

Database altered.

SQL> alter database move datafile '/tmp/system01.dbf' to '+DATA';

Database altered.

-- TEMPFILE: add a new one and drop the old one
SQL> alter tablespace temp add tempfile '/tmp/temp01.dbf' size 50M;

Tablespace altered.

SQL> alter database tempfile '+DATA/_MGMTDB/TEMPFILE/temp.284.1070901055'
offline;

Database altered.

SQL> alter database tempfile '+DATA/_MGMTDB/TEMPFILE/temp.284.1070901055'
drop including datafiles;

Database altered.

 > Well, you can move the online reado log files by creating new ones and
dropping the old ones but that's about it.

what do you mean... "but that's about it"? redo logs are not datafiles, new
ones are created when the old ones are full, just like WAL files. You
decide where to put them and just archive the old ones.

And if I want to move an entire database... with ASM, 100% with Oracle
technology, you can switch from a storage to another one without instance
downtime or brownouts. Or you can do some junior-level scripting and do
the online datafile movement automatically between different filesystems.
Control files (and only them) are the only thing that you cannot move
without bouncing. Truth. (it's in my blog as well). But with ASM you can
change disks online.


> > PostgreSQL must be stopped in order to move the database to a new path,
> and if it is to a new filesystem, you need the time 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 start up the copy. Once it's in
> sync and you have a physical copy, change the port in postgresql.conf of
> the copy, stop both and then only launch the copy. Promote it then. The
> switch takes 2-3 secs of downtime.
>

I did say the truth and I quote myself again in case you have skipped my
sentence: "unless you do it via backup and recovery to reduce the
downtime." That's what a replica is. A backup that you keep recovering
until you switch to it.
This applies for Oracle as well, BTW, in case you want to relocate to
another server. It might be longer to relocate, but there are technologies
that make it transparent to the application (complex to implement, yes, but
still they exist).


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

Read above. Read the doc. Try it yourself. Ask your friends. Do something
but stop telling that it's not possible.

but that makes 99.9% of all applications, no 

Re: Oracle vs. PostgreSQL - a comment

[ERR ORR]

I may be off-topic as I've only worked occasionally with ORA but still know
it good enough.
What I miss most of the Oracle DB in PostgreSQL is the elaborate system of
object security and granting permissions which exists in Oracle DB.
What I like most about the Postgres DB is that lots of plugins/extensions
exist which implement features which do not exist in the basic feature set,
and it is comparatively easy to program extensions which you need.
There are lots more things which make me prefer PostgreSQL.
I think that is PostgreSQL included a security system comparable to Oracle,
that would be a firm Plus in the market.

On Thu, Apr 29, 2021, 19:13 Paul Förster  wrote:

> 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's e-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 my personal 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 inclined to 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 could even 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 disagree with the rest.
>
> to create a CDB, you still have to provide paths which are then hard-coded
> into the control-file! Oracle software takes tons of 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 database depend a lot on what do
> you want to achieve (empty database? specific users or permissions? sanity
> checks? pre-emptive backup? add to cmdb?)
>
> yes, create pluggable database. Takes 30+ secs to run, while on
> PostgreSQL, it takes a few milliseconds. But we require a certain 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 an existing PostgreSQL
> cluster. That included setting up application users, hardening the default
> permissions on the public schema, 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 includes default 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 capabilities are 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), but you can't move the essential tablespace
> files! Well, you can move the online reado log files by creating new ones
> and dropping the old ones but that's about it. You still can't move the
> essential tablespace files. I admit that I didn't try 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 the controlfiles 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 the time 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 start up the copy. Once it's in
> sync and you have a physical copy, change the port in postgresql.conf of
> the copy, stop both and 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 

Re: Oracle vs. PostgreSQL - a comment

[Paul Förster]

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's e-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 my personal 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 inclined to 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 could even 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 disagree with the rest.

to create a CDB, you still have to provide paths which are then hard-coded into 
the control-file! Oracle software takes tons of 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 database depend a lot on what do you 
> want to achieve (empty database? specific users or permissions? sanity 
> checks? pre-emptive backup? add to cmdb?)

yes, create pluggable database. Takes 30+ secs to run, while on PostgreSQL, it 
takes a few milliseconds. But we require a certain 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 an existing PostgreSQL cluster. 
> That included setting up application users, hardening the default permissions 
> on the public schema, 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 includes default 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 capabilities are 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), but you can't move the essential tablespace files! Well, you can move 
the online reado log files by creating new ones and dropping the old ones but 
that's about it. You still can't move the essential tablespace files. I admit 
that I didn't try 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 the controlfiles 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 the time 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 start up the copy. Once it's in sync and 
you have a physical copy, change the port in postgresql.conf of the copy, stop 
both and 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 it there. 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 "query which is generated and, if 
> printed out in a 11pt. sized font, can fill a billboard on a street", give it 
> to Oracle and get the optimal execution plan 99.9% of the times. And if 

Re: Oracle vs. PostgreSQL - a comment

[Ludovico Caldara]

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's e-mails in this thread...
 (Note: I work for Oracle now, but I've had 20 years experience as
multi-platform database consultant)

Paul Förster  wrote:

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

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

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

Although I completely agree that the Oracle installation process is much
longer and more complex than PostgreSQL, I disagree with the rest.
The CREATE PLUGGABLE DATABASE is also a single line SQL command... The
scripts to create a PDB or a PostgreSQL database depend a lot on what do
you want to achieve (empty database? specific users or permissions? sanity
checks? pre-emptive backup? add to cmdb?)
For a new PostgreSQL architecture in the past I have written 230 lines of
code to automate the database creation in an existing PostgreSQL cluster.
That included setting up application users, hardening the default
permissions on the public schema, registering in the CMDB, etc. It is not
much code in my opinion and it is done once for all.
For a similar project with Oracle Multitenant, the create_pdb.sh was 177
lines of code, including dealing with TDE wallets and CMU authentication.
Again, not that much IMO.



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

This is bashing FUD against Oracle or lack of basic Oracle knowledge.
Oracle online move, reorganization and patching capabilities are far ahead
from PostgreSQL.
Online Datafile Movement has existed since 12cR1. 8 years!
https://oracle-base.com/articles/12c/online-move-datafile-12cr1
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 the controlfiles that require an ALTER SYSTEM, shutdown,
move, startup. PostgreSQL must be stopped in order to move the database to
a new path, and if it is to a new filesystem, you need the time for a full
copy of the data, unless you do it via backup and recovery to reduce the
downtime.

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

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

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

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

> > Comparing Postgres with Oracle is a bit like comparing a rubber duck
you might buy your three year old, with a 30 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, 

Re: Oracle vs. PostgreSQL - a comment

[Franck Pachot]

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

Re: Oracle vs. PostgreSQL - a comment

[Laurenz Albe]

On Sun, 2020-06-14 at 09:17 -0400, Bruce Momjian wrote:
> 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.

One example for what may be difficult:

If you GRANT a permission on a table to a user, you may get an entry in
"pg_catalog.pg_shdepend", which is a global table (it is shared by all
databases).

Now if you want to recover a single database, and you get a WAL entry
for that table, you'd have to "logically decode" that entry to figure
out if it should be applied or not (because it references a certain
database or not).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Oracle vs. PostgreSQL - a comment

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Oracle vs. PostgreSQL - a comment

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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.

Re: Oracle vs. PostgreSQL - a comment

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Oracle vs. PostgreSQL - a comment

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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.

Re: Oracle vs. PostgreSQL - a comment

[Achilleas Mantzios]

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.






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

Re: Oracle vs. PostgreSQL - a comment

[Thomas Kellerer]

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

Re: Oracle vs. PostgreSQL - a comment

[Achilleas Mantzios]

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 in PG 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 failovers all
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

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

>> 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 PG 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 failovers all
inter-related databases at one go.

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

Re: Oracle vs. PostgreSQL - a comment

[Achilleas Mantzios]

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

Re: Oracle vs. PostgreSQL - a comment on Mysql

[Alvaro Herrera]

On 2020-Jun-03, Martin Mueller wrote:

> On the topic of what other databases do better: I much prefer Postgres to 
> Mysql because it has better string functions and better as well as very 
> courteous error messages. But MySQL has one feature that sometimes makes me 
> want to return it: it stores the most important metadata about tables in a 
> Mysql table that can be queried as if it were just another table.  That is a 
> really feature. I makes it very easy to look for a table that you edited most 
> recently, including a lot of other things.
> 
> Why doesn’t Postgres have that feature? Or is there a different and equally 
> easy way of getting at these things that I am just missing?

Every little schema detail in Postgres is in a catalog table that you
can query.  See pg_class for a list of relations; pg_attribute for
attributes; and so on.  You can learn a lot about them just by running
"psql -E" and executing \d -- look at the queries that appear above the
resultset.  We even have a whole section in our docs about the layout of
the system catalogs.  Also, there are views that make the whole thing
easier.  See https://www.postgresql.org/docs/devel/catalogs.html

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Oracle vs. PostgreSQL - a comment on Mysql

[Michael Nolan]

On Wed, Jun 3, 2020 at 5:21 PM Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> On the topic of what other databases do better: I much prefer Postgres to
> Mysql because it has better string functions and better as well as very
> courteous error messages.
>

Martin, I definitely sympathize.  The company I used to work for before I
retired (and still advise) is moving from the in-house membership system I
built for them over the last 20 years to CIVI-CRM, which is MySQL based.
We used MySQL on our Joomla-based website and I've been doing some work in
WordPress, so I've got some familiarity with MySQL(more than I used to
have), but it still drives me nuts at times!
--
Mike Nolan

Re: Oracle vs. PostgreSQL - a comment

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Oracle vs. PostgreSQL - a comment on Mysql

[Adrian Klaver]

On 6/3/20 3:21 PM, Martin Mueller wrote:
On the topic of what other databases do better: I much prefer Postgres 
to Mysql because it has better string functions and better as well as 
very courteous error messages. But MySQL has one feature that sometimes 
makes me want to return it: it stores the most important metadata about 
tables in a Mysql table that can be queried as if it were just another 
table.  That is a really feature. I makes it very easy to look for a 
table that you edited most recently, including a lot of other things.


With a lot of caveats:

https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

" Update_time

When the data file was last updated. For some storage engines, this 
value is NULL. For example, InnoDB stores multiple tables in its system 
tablespace and the data file timestamp does not apply. Even with 
file-per-table mode with each InnoDB table in a separate .ibd file, 
change buffering can delay the write to the data file, so the file 
modification time is different from the time of the last insert, update, 
or delete. For MyISAM, the data file timestamp is used; however, on 
Windows the timestamp is not updated by updates, so the value is inaccurate.


Update_time displays a timestamp value for the last UPDATE, INSERT, or 
DELETE performed on InnoDB tables that are not partitioned. For MVCC, 
the timestamp value reflects the COMMIT time, which is considered the 
last update time. Timestamps are not persisted when the server is 
restarted or when the table is evicted from the InnoDB data dictionary 
cache. "


What are the lot of other things?

My guess is they can be found in information_schema.*.





Why doesn’t Postgres have that feature? Or is there a different and 
equally easy way of getting at these things that I am just missing?


*From: *Andreas Joseph Krogh 
*Date: *Wednesday, June 3, 2020 at 12:54 PM
*To: *Chris Travers 
*Cc: *"pgsql-generallists.postgresql.org" 


*Subject: *Re: Oracle vs. PostgreSQL - a comment

På onsdag 03. juni 2020 kl. 20:07:24, skrev Chris Travers 
mailto:chris.trav...@gmail.com>>:


[...]

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.

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


--
Andreas Joseph Krogh




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Oracle vs. PostgreSQL - a comment on Mysql

[Martin Mueller]

On the topic of what other databases do better: I much prefer Postgres to Mysql 
because it has better string functions and better as well as very courteous 
error messages. But MySQL has one feature that sometimes makes me want to 
return it: it stores the most important metadata about tables in a Mysql table 
that can be queried as if it were just another table.  That is a really 
feature. I makes it very easy to look for a table that you edited most 
recently, including a lot of other things.

Why doesn’t Postgres have that feature? Or is there a different and equally 
easy way of getting at these things that I am just missing?

From: Andreas Joseph Krogh 
Date: Wednesday, June 3, 2020 at 12:54 PM
To: Chris Travers 
Cc: "pgsql-generallists.postgresql.org" 
Subject: Re: Oracle vs. PostgreSQL - a comment

På onsdag 03. juni 2020 kl. 20:07:24, skrev Chris Travers 
mailto:chris.trav...@gmail.com>>:
[...]

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.

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

--
Andreas Joseph Krogh

RE: Oracle vs. PostgreSQL - a comment

[Basques, Bob (CI-StPaul)]

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.

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

All,

Since we’re not limited by how many instances of Postgres we run, we actually 
have a setup where we do live backups over a multi-node configuration.  More 
than one Postgres instance, and syncing between the databases as a scripted 
process across database instances.  This allows us to do some interesting 
things like replicate to mobile hardware for Postgres in the field, etc.

The difference in how the DBs accomplish things are more related to taking 
advantage of the capabilities in each software package vs comparing on a 
function by function basis.

bobb

Re: Oracle vs. PostgreSQL - a comment

[Andreas Joseph Krogh]

På onsdag 03. juni 2020 kl. 20:07:24, skrev Chris Travers <
chris.trav...@gmail.com >: 


[...] 

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. 

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


--
 Andreas Joseph Krogh 

Re: Oracle vs. PostgreSQL - a comment

[Chris Travers]

On Wed, Jun 3, 2020 at 7:45 PM Andreas Joseph Krogh 
wrote:

> På onsdag 03. juni 2020 kl. 18:50:12, skrev Jeremy Schneider <
> schnj...@amazon.com>:
>
> > 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.

>
> --
> Andreas Joseph Krogh
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Oracle vs. PostgreSQL - a comment

[Andreas Joseph Krogh]

På onsdag 03. juni 2020 kl. 18:50:12, skrev Jeremy Schneider <
schnj...@amazon.com >: 
> 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. 


--
 Andreas Joseph Krogh 

Re: Oracle vs. PostgreSQL - a comment

[raf]

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

Re: Oracle vs. PostgreSQL - a comment

[Kenneth Marshall]

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

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Adam Brusselback (adambrusselb...@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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Guyren Howe (guy...@gmail.com) wrote:
> On Jun 2, 2020, at 14:16 , Stephen Frost  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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Adam Brusselback]

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

In SQL Server I had never noticed the difference when I had to do cross-db
queries. There may or may not be a difference, but it was never something
that I had to think about or look up, so I am not sure.

It's something I do still miss though, as it sure was convenient.

Just my $0.02.
-Adam

Re: Oracle vs. PostgreSQL - a comment

[Guyren Howe]

On Jun 2, 2020, at 14:16 , Stephen Frost  wrote:
> 
> Greetings,

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

Re: Oracle vs. PostgreSQL - a comment

[Thomas Kellerer]

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)

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ravi Krishna (sravikris...@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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

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

Re: Oracle vs. PostgreSQL - a comment

[Christophe Pettus]

> On Jun 2, 2020, at 13:30, Stephen Frost  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
   x...@thebuild.com

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ravi Krishna (sravikris...@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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Guyren Howe]

On Jun 2, 2020, at 12: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.

Gotta say, not generally a fan of SQL Server, but this is very nice. If I’m 
dealing with a database with 50 small databases on it, it’s an utter pain to 
have to set up fdw connections between every set of databases I want to use 
together.

Any chance of getting this in Postgres? Seems like since the databases are in 
the same program, this ought to be simpler.

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

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

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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.

Re: Oracle vs. PostgreSQL - a comment

[Tim Clarke]

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 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 
ad...@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.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 6/2/20 1:30 PM, Stephen Frost wrote:
> >* Ron (ronljohnso...@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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Ron]

On 6/2/20 1:30 PM, Stephen Frost wrote:

Greetings,

* Ron (ronljohnso...@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.

Re: Oracle vs. PostgreSQL - a comment

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@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


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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.

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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 2 process (which is a pain) doesn't impact SQL Server like 
it does Postgres.


--
Angular momentum makes the world go 'round.

Re: Oracle vs. PostgreSQL - a comment

[Michael Nolan]

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


>

Re: Oracle vs. PostgreSQL - a comment

[Tim Clarke]

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 
ad...@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.

Re: Oracle vs. PostgreSQL - a comment

[Grigory Smolkin]

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

Re: Oracle vs. PostgreSQL - a comment

[Ron]

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.

Re: Oracle vs. PostgreSQL - a comment

[Karsten Hilbert]

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

Re: Oracle vs. PostgreSQL - a comment

[Ravi Krishna]

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.

Re: Oracle vs. PostgreSQL - a comment

[Peter J. Holzer]

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 30 ton super tanker.

If it's a 30 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.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Oracle vs. PostgreSQL - a comment

[Thomas Kellerer]

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

Re: Oracle vs. PostgreSQL - a comment

[Paul Förster]

Hi Stefan,

> On 01. Jun, 2020, at 07:36, Stefan Knecht  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 30 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

Re: Oracle vs. PostgreSQL - a comment

[Jayadevan M]

On Sat, May 30, 2020 at 5:51 PM Paul Förster 
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


Regards,
Jayadevan

Re: Oracle vs. PostgreSQL - a comment

[Paul Förster]

Hi Stefan,

> On 01. Jun, 2020, at 00:35, Stefan Keller  wrote:
> Paul Förster  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

Re: Oracle vs. PostgreSQL - a comment

[Stefan Knecht]

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 30 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 
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_20_Linux-x86-64
> apply p30797938_19_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 

Re: Oracle vs. PostgreSQL - a comment

[Stefan Keller]

Hi Paul

Paul Förster  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
:
>
> Hi Tim,
>
> > On 31. May, 2020, at 15:26, Tim Cross  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
>

Re: Oracle vs. PostgreSQL - a comment

[Paul Förster]

Hi Tim,

> On 31. May, 2020, at 15:26, Tim Cross  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

Re: Oracle vs. PostgreSQL - a comment

[Tim Cross]

Paul Förster  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-x. 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

Re: Oracle vs. PostgreSQL - a comment

[Paul Förster]

Hi Tim,

> On 30. May, 2020, at 23:14, Tim Cross  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-x. 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

Re: Oracle vs. PostgreSQL - a comment

[Tim Cross]

Paul Förster  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_20_Linux-x86-64
> apply p30797938_19_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, 

Oracle vs. PostgreSQL - a comment

[Paul Förster]

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_20_Linux-x86-64
apply p30797938_19_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