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

151–160 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#151

Post by takacsroland »

krakatau1 wrote: post #96

I don't have a lot of experience with Oracle but I can tell you that Postgres optimizer is shit compared to Db2 zOS or Db2 LUW. When I worked in a large bank we tried to migrate core system from Db2 zOS to Postgres and it went nowhere. I was a in-house developer working with Postgres consultants and they were amazed by db2 performance in OLTP scenarios. So if your organization is already spending cash on Oracle, Db2…

In fact, my company wants to spare the expenses on Oracle. This is the main reason for migrating. We'll see how it works out. You are not the first one to point out postgre's optimizer. Is it really that bad?

Re: Oracle vs. PostgreSQL: First Glance

#152

Post by [deleted] »

jsmith45 wrote: post #143
takeda wrote: post #114

Earlier quoted context omitted.

> I also really appreciate the transactional TRUNCATE - I pretty much never use it but at least in Postgres I never have to worry about someone else trying to run one and wiping state unexpectedly. as long as auto commit is not enabled. These goodies are possible, because of PostgreSQL's MVCC which requires running vacuum. Nothing is for free unfortunately.

Interestingly even though MSSQL server uses an extremely different implementation of MVCC, it internally has a vacuum equivalent. (Which is required even when all MVCC support is disabled! It is used to enable efficient implementation of deletes, without having to use absurdly coarse locks). MSSQL just handles doing that cleanup silently in the background while exposing basically no no configuration except a trace fl…

[deleted]

Re: Oracle vs. PostgreSQL: First Glance

#153

Post by outworlder »

user5994461 wrote: post #106
zozbot234 wrote: post #52

Earlier quoted context omitted.

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.

How often do developers recompile postgresql? How often do developers go through the hassle of upgrading databases versions? Whatever postgresql may have done recently, it won't be used and available in the common distro until a while later. Bear in mind that minor versions in postgresql are breaking changes. It does not follow semver.

> How often do developers recompile postgresql?

Uh, never? I mean, there may be cases where you might need to do that, but I don't want to use a PG built by JoeRandoMacbook somewhere. It's better to use PGDG.

> How often do developers go through the hassle of upgrading databases versions?

As often as they have to. Most places don't upgrade all that often.

Re: Oracle vs. PostgreSQL: First Glance

#154

Post by takacsroland »

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…

Thanks for your inputs. Yes, you are totally right about the DDLs, you cannot mix it with DML. Seeing all the comments, it looks like a good decision to migrate. :)

Re: Oracle vs. PostgreSQL: First Glance

#155

Post by [deleted] »

pjmlp wrote: post #101
augusto-moura wrote: post #78

Earlier quoted context omitted.

Sure it works, and is well tested and mature, and so are most of the jdbc drivers, never had any problem with any jdbc driver, aside of digging to code to understand some not documented behaviours. I don't see why put the Java driver as a benefit point of Oracle, most of the developers problems and issues don't occur at the JDBC level. It might be true for .NET but I'm really supicious if the Oracle support is that b…

Well, to know which obscure and internal hacks are those one has to read the source code. Also Oracle drivers also run in other JVMs, so which JVM are they abusing then? As for why speaking about the drivers, I have had my share of driver issues during the last couple of decades, when going enterprise scale.

[deleted]

Re: Oracle vs. PostgreSQL: First Glance

#158

Post by takacsroland »

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 the partitions. Regarding your other input, thanks, I will have a look.

Re: Oracle vs. PostgreSQL: First Glance

#159

Post by tomnipotent »

thda wrote: post #124
modoc wrote: post #107

Earlier quoted context omitted.

I STRONGLY dislike Oracle the company, and the Oracle DB is quite complex, however Oracle RAC (HA) beats the pants off anything else out there as far as performant, reliable HA. Oracle DB also has been the leader performance wise for complex queries and large datasets. All of that is very important in my niche (enterprise grade eCommerce).

> Oracle DB also has been the leader performance wise for complex queries and large datasets SQL server has a column store type of storage, and major innovations like 'froid'. Oracle is not such a strong leader there. Also, on a whole lot of workloads clickhouse is much superior.

> whole lot of workloads clickhouse is much superior.

ClickHouse is a read-only analytics database and overlaps with Oracle in only those areas, otherwise Oracle blows it out of the water.

Re: Oracle vs. PostgreSQL: First Glance

#160

Post by outworlder »

takacsroland wrote: post #151
krakatau1 wrote: post #96

I don't have a lot of experience with Oracle but I can tell you that Postgres optimizer is shit compared to Db2 zOS or Db2 LUW. When I worked in a large bank we tried to migrate core system from Db2 zOS to Postgres and it went nowhere. I was a in-house developer working with Postgres consultants and they were amazed by db2 performance in OLTP scenarios. So if your organization is already spending cash on Oracle, Db2…

In fact, my company wants to spare the expenses on Oracle. This is the main reason for migrating. We'll see how it works out. You are not the first one to point out postgre's optimizer. Is it really that bad?

YMMV. That depends on your workload, how your data is structured and a million other things. I know a few shops with heavy usage that couldn't be happier.

It may require handholding if you are not happy with the plans it is generating. Also the quality of the query planner results will depend a lot on how up to date the statistics are.

Post reply on HN