Re: Migration to Postgres training

On 8/8/19 1:21 PM, Mladen Gogala wrote:
>
> On 8/8/19 2:44 PM, Joshua D. Drake wrote:
>>
>>
>> If you are looking for a single in box solution to solve all your
>> needs, it is true that Postgres won't necessarily service your needs.
>> However, to address the two specific points:
>>
>> No "in-memory": PostgreSQL has unlogged tables. It also works
>> wonderfully with any number of in-memory solutions such as redis or
>> memcache
>
> It's not the same: the in-memory solution developed by IBM and Oracle
> maintains both the classic block/row storage and columnar storage in
> memory. So called in-memory technique is not just a bigger cache, it's
> a data warehouse technology. Unlogged tables or memcached are simply
> buffering techniques.

No, unlogged tables are not buffering techniques at all. They are essentially non-acid compliant tables. They don't write to the xlog so you get a huge performance increase at the expense of not being able to replicate them nor are they crash safe.

>>
>> Partitioning: This gets better every release. Version 10 was o.k., 11
>> was much better, and 12 is even better. Pretty much the only thing
>> missing as of 12 is the global indexes. However, you can have Primary
>> Keys per partition with 12.
>
>
> I'll have to check it up. I have played with Pg11 but not with Pg12.
> However, the lack of global indexes is a crucial feature.
>
I agree. We need them internally as well. If we had cross table indexes, our foreign key lookups would be much faster.

>
>
>>
>> It all depends on your specific needs but PostgreSQL can usually
>> service 100% of 95% of Oracle users needs.
>>
>> JD
>
> That's a very brave statement. How did you come up with those numbers?
> Does PgSQL have its own version of TDE? How about fine-grained
> auditing? Do you have a tool like EM Express built into the database?
> How do compare your backups to rman backups?

You are making assumptions like TDE or EM express are needed. As someone who consults on Oracle Migrations for a living, I can tell you that by far the #1 thing that we have to work around is packages, the #2 is weird stuff Oracle does (like a completely broken number and NULL implementation). Again this is for homegrown apps against Oracle not something out of the box.

Yes we have fine grain auditing via pgAudit and we have more backup options than we can count including to the txid, differential and incremental.

>
>
> Don't get me wrong, Postgres is a nice OSS database but is not as
> feature rich as Oracle. Not even remotely so. I have been doing
> projects where people expressed the desire to get off  Oracle and
> start using something else. Postgres was in play with 2 companies, but
> I've had the most of success with DB2 which can match Oracle on
> feature by feature basis and is significantly cheaper. I like
> Postgres, but so called migration projects should be done vewy, vewy
> cawefully.

Oh I get it and I agree with you. You shouldn't "just migrate". Postgres is different but we have a lot of features Oracle doesn't too (Transaction DDL anyone?) and if reviewed correctly we do very, very well. There is also no question that there are parts that Oracle is just better at, that's what happens when you have billions of dollars to build features :D

Let me put this another way, if you are building a custom application and you want a relational database, your first evaluation should be PostgreSQL. If PostgreSQL can't do it, then consider something else (Oracle or DB2) but for custom apps, PostgreSQL really is the best, first choice at this point.

JD

-- 
Command Prompt, Inc. || http://the.postgres.company/ || _at_cmdpromptinc
Postgres centered full stack support, consulting and development.
Advocate: _at_amplifypostgres || Get help: https://commandprompt.com/
*****     Unless otherwise stated, opinions are my own.   *****

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