Re: Differences in key database design and architecture

On 1/15/24 16:57, richard goulet wrote:
> Yudi,
>
>     It's been a couple of years since I last worked with PostgreSQL
> and I will admit that as an open source db. It's rather good and more
> like Oracle than any other, but there are differences that can be
> troublesome.
>
>     1) the root of a PostgreSQL database is a filesystem location. 
> From there your rather limited in what you can do by the size of the
> mount point.  You can create a number of "databases" which are
> analogous to tablespaces, but they cannot span mount points.  Most
> troublesome in my view is the PostGres community's  overwhelming
> demand that the database be owner by postgres and the executable's by
> root.  There are ways to get around this but as soon as you do it gets
> harder to get support from the community and those commercial vendors
> that provide support.  The argument is that with the executables under
> root it's harder for someone to mess with them.  My issue here is that
> every machine has a root account that is every hacker's target, and
> once your logged in as root you own everything on the machine.  I've
> seen where a hacker got access to a login prompt on a web server and
> tried for a long time to connect as oracle.  The problem was it did
> not exist so all he did was activate an alarm.
>
>     2) Everything in PostgreSQL is case sensitive, so MYTABLE is
> different from MYtable and mytable.  I've also seen that at the column
> level where there was a SEQ_no and a SEQ_NO column in the same table.
>
>     3) What we call REDO in Oracle is implemented very differently in
> PostGres.   Make sure you have the vacuum deamon on and scheduled at a
> reasonable interval.
>
>     4) Stored PLSQL is rather different as well.  You have to specify
> the language the function/procedure is written in with the "LANGUAGE
> PL/SQL;" identifier.
>
>
One thing that people frequently forget is that Postgres doesn't have a shared pool. There is no caching between sessions. None whatsoever. Session A compiles and re-executes a procedure 1000 times. Session B will have to do the same, not benefiting from the work done by the session A at all. Postgres variants (EDB, Yugabyte, CockroachDB, Aurora) are much cheaper than Oracle but, generally speaking, need much, much more powerful machine to achieve the same throughput. That is usually acceptable because machines are much cheaper than Oracle database. Long time ago, it was normal that software was cheaper than the machine it was running on. That is no longer true. Don't get me wrong: Oracle is still much better in terms of the code quality and performance, but we are reaching the situation when Postgres might be good enough. And that may spell trouble for your and mine favorite RDBMS. The only really important thing that Oracle can do and Postgres cannot is to create a global index on a partitioned table. That means that you cannot have a unique/primary key on a partitioned table unless the partitioning column is the 1st column of the key. And that's a big limitation. However, people are working on the global indexes and I expect to have them before long. And that will mean trouble for the Redwood Shores.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l