Re: Oracle vs. PostgreSQL - a comment

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