Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Oracle vs. PostgreSQL: First Glance

rolkotech.blogspot.com

Post reply on HN
Print view View original

51–60 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#51

Post by carlosf »

Pxtl wrote: post #8
dx034 wrote: post #4

Earlier quoted context omitted.

I really don't like Oracle from a DBA perspective but it's still often far ahead of PostgreSQL when it comes to query performance. In Postgres, the query structure can make huge differences in terms of performance and it can take a lot of tuning to find the right query to optimize performance (especially when subqueries are involved). Oracle (and SQLServer) are usually pretty good at optimizing the query exactly the…

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

You might even call it a Data Lake and get away with it.

Re: Oracle vs. PostgreSQL: First Glance

#52

Post by zozbot234 »

simonebrunozzi wrote: post #28
forinti wrote: post #15

Earlier quoted context omitted.

You could connect Oracle to Postgresql using HA and from Postgresql to Oracle using FDW. If your client was paying Oracle already, he should take steps to move away from it, not setup himself up for paying licences forever. I cringe at having to call Oracle Support. It takes forever and they make you send a ton of files before someone even looks at it.

> he should take steps to move away from it Unless - as it often happens - someone in high places need to keep justifying a business decision taken 1-2-3 years before.

That's what we call throwing good money after bad. Besides, PostgreSQL has actually come a long way since 3 years ago or so. It's not a slow-moving project, especially for this space.

Re: Oracle vs. PostgreSQL: First Glance

#53

Post by da_chicken »

doublesCs wrote: post #26
ibejoeb wrote: post #23

Earlier quoted context omitted.

One of the big changes in 12 allows the optimizer to work properly with CTEs, which was a major barrier to using the more expressive language features. There a good writeup here: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

Thanks for that link. > Historically we've always materialized the full output of a CTE query Do you know if this means that CTEs are written to disk? I didn't think this was the case. (In the case of materialized views that qualifier means the view is written to disk).

Here "materialization" just means that the query planner treats it as an optimization fence. That means the system doesn't do any optimization between the CTE and the query referencing it. It would prepare the output of the CTE as a completely separate entity essentially as if you had dumped it to a temp table. It may not be written to disk if there was sufficient memory, but either way you're sacrificing any optimization. I believe that it would even materialize the CTE multiple times if it was referenced multiple times, but don't quote me on that.

On the other hand, if the same query were written with subqueries instead of with CTEs, then the query optimizer would not treat it as an optimization fence. If it could utilize indexes or rewrite the query to be relationally equivalent, it would do so.

Note that sometimes that optimization fence is beneficial. There are situations where it's better to create temp tables and run smaller simpler queries instead of running an extremely complex monolithic query because the query planner isn't perfect even with hints. You can still enable that optimization fence functionality in PostgreSQL if you need to, but it's generally pretty rare that it happens like this. Still, you'll see stored procedures for reports still using temp tables and even cursors sometimes because they can be made to perform better in certain situations.

Re: Oracle vs. PostgreSQL: First Glance

#54

Post by F_J_H »

jedieaston wrote: post #45
F_J_H wrote: post #24

Earlier quoted context omitted.

I have not been an Oracle fan in the past, especially because of their complicated (and expensive) licensing, but late last year we moved to their hosted autonomous database. The on demand pricing model makes it quite economical, and the performance is amazing. However, the killer feature for me is that it has application Express (or APEX) included, which is a complete web application development framework, as well a…

>application express Is it smart to have all of your web app code running in the database, making it impossible to run without the Oracle Database?

That is somewhat of a concern, but less so now that they support web services. So, you would still need a basic oracle DB set up in order to run APEX, but you could just use OracleXE, which is a quite capable free version, and then connect to whatever you want.

Also, you see this argument a lot - "what if I want to switch databases"? I've seen more than my share of overly-complicated and highly non-performant code bases, "just in case we want to change our database at some point" (see the ORM messes out there.) Its a problem in theory, and in my experience, not in practice. Never in 25 years of IT work have I switched databases, so it's often a classic case of "prevention worse than the disease".

One case where this use to be an issue was for software vendors that used to sell applications requiring a database, and they had to be ready to work with whatever the customer had. In our day of cloud based apps, this is increasingly becoming less of an issue.

Re: Oracle vs. PostgreSQL: First Glance

#55

Post by revel »

Oracle used to be by far and away the best database out there. Now I wouldn’t use it even if you paid me. It’s shocking how little Oracle invested in developing their products and services over the years. They are a distant second, if not merely an “also ran”, for everything that they do. The company largely exists as an experiment in just how far you can go with a vendor lock-in strategy. Sadly that experiment is proving to be a remarkably successful one

Re: Oracle vs. PostgreSQL: First Glance

#56

Post by hathawsh »

hans_castorp wrote: post #43
ajuc wrote: post #20

Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. That means migration scripts for software on Postgress can just have all DDLs (alter, create, drop, grant etc.) and DMLs (inserty, update, etc.) mixed in whatever order they need to be, and if any particular line of the migration script fails - the whole thing is rolled back as if nothing happened. And then you fix the problem and run…

> Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. The answer I received more than once from Oracle evangelists regarding transactional DDL: it's useless and if you need it, you are not testing your scripts properly

Transactional DDL is, IMHO, the best way to write database tests! I've been really happy with database testing since I built and started using a library that runs DDL and uses savepoints without ever committing a transaction.

https://docs.rs/diesel_pg_tester/0.5.1/diesel_pg_tester/

Re: Oracle vs. PostgreSQL: First Glance

#57

Post by catblast »

markab21 wrote: post #14

Why anyone would use Oracle for anything other than supporting legacy systems is beyond me.

For one, it does things that the competition is not capable of or just inferior -- and despite the bias on this site, the major consumers of RDBMSes are not price sensitive scrappy startups. There is no comparison between the HA offerings in Oracle and something like Postgres, which are comparatively toys. Replication doesn't equate to HA and the "nobody got fired for buying xxx.." actually has some justification. Why would I risk my reputation on a flimsy solution just to save a few bucks in a large corporation?

Re: Oracle vs. PostgreSQL: First Glance

#58

Post by miahi »

philliphaydon wrote: post #18

I haven't touched Oracle in like 12 years so I can't comment on that. But some of the examples are a bit strange or atleast lacking for PostgreSQL. For example, in the partitioning, he states: > SELECT * FROM sales_p_america; But doesn't mention that if you select based on a region, it will use only the partition table. > SELECT * FROM sales WHERE sales_region IN ('USA','CANADA'); While I believe if you do the equiv…

Oracle will also use partitioning optimizations in that case. See partition pruning[1].

[1] https://docs.oracle.com/en/database/oracle/oracle-database/1...

Re: Oracle vs. PostgreSQL: First Glance

#59

Post by ksec »

dtheodor wrote: post #38
rpedela wrote: post #5

Earlier quoted context omitted.

PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?

It's very true today. The problem discussed here is performance on complex queries (e.g. subqueries), and the query planner plays a huge role in that. The Postgres query planner has various issues. Here are two recent posts talking about planner issues: https://medium.com/@rbranson/10-things-i-hate-about-postgres... https://www.cybertec-postgresql.com/en/things-could-be-impro... Also JIT compilation, while very nice…

For someone not following closely, What are the reasons behind the lack of work on JIT since then?

Re: Oracle vs. PostgreSQL: First Glance

#60

Post by da_chicken »

Pxtl wrote: post #8
dx034 wrote: post #4

Earlier quoted context omitted.

I really don't like Oracle from a DBA perspective but it's still often far ahead of PostgreSQL when it comes to query performance. In Postgres, the query structure can make huge differences in terms of performance and it can take a lot of tuning to find the right query to optimize performance (especially when subqueries are involved). Oracle (and SQLServer) are usually pretty good at optimizing the query exactly the…

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement.

The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always.

The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.

Post reply on HN