Oracle vs. PostgreSQL: First Glance • Hacker News Board

Re: Oracle vs. PostgreSQL: First Glance

#171

Post by m_mueller » Tue, May 05, 2020, 2:04 AM UTC

ants_a wrote: post #140

sjwright wrote: post #64

Earlier quoted context omitted.

I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush. (Most recent version I've used was SQL Server 2008.)

All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model a…

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale horizontally. Granted, it's hard to design the data structures in the design space of normalization (many joins) vs. read performance (few joins) - but at least it's all laid bare to reason about.

Re: Oracle vs. PostgreSQL: First Glance

#172

Post by ken » Tue, May 05, 2020, 2:35 AM UTC

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.

I don't know anyone who compiles their own Postgres at all.

AWS is a pretty common "distro" (of sorts). Postgres 12.2 was released 2020-02-13, and AWS RDS supported it on 2020-03-31. You don't have to wait very long to use this.

Re: Oracle vs. PostgreSQL: First Glance

#173

Post by sjwright » Tue, May 05, 2020, 3:41 AM UTC

m_mueller wrote: post #171

ants_a wrote: post #140

Earlier quoted context omitted.

All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model a…

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale ho…

Sorry, but hard disagree. For all the failings of SQL systems, they work staggeringly well 99% of the time as long as the operator understands how they work. No-SQL systems do have their place, but all too often their choice stems from a failure to understand SQL-based databases. (And the hard truth: unless you’re doing something especially novel there’s a lot less difference between map/reduce and a bog standard table index than no-SQL proponents would have you believe.)

In my estimation, if you're not already intimately familiar with the intricacies of performant SQL, chances are you're not playing in a space where a no-SQL architecture is an appropriate fit anyway. And you're certainly not in a position to make an informed decision between SQL and no-SQL. It's a much better use of this hypothetical person's time to set up Mariadb or Sqlite and do a deep dive into the fundamentals query performance.

Re: Oracle vs. PostgreSQL: First Glance

#174

Post by sjwright » Tue, May 05, 2020, 4:40 AM UTC

ants_a wrote: post #140

sjwright wrote: post #64

Earlier quoted context omitted.

I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush. (Most recent version I've used was SQL Server 2008.)

All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model a…

> All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't.

Every single time the SQL Server query optimiser did something obviously wrong, rebuilding statistics fixed it. The problem I had with SQL Server wasn't its reliance of statistics—it's not like they could work any other way—it's that it failed to maintain its statistics correctly. Any time that statistics stop being correct is a bug. It should be able to maintain them itself and trigger rebuilds whenever there's any doubt about them.

Re: Oracle vs. PostgreSQL: First Glance

#175

Post by ashtonkem » Tue, May 05, 2020, 4:43 AM UTC

AmericanChopper wrote: post #170

ashtonkem wrote: post #9

Earlier quoted context omitted.

This is actually my experience with Oracle; I had a DBA to take the management pain from us, and we had the budget for the correct licenses and hardware. The query performance was phenomenal considering the absolutely crazy amount of data we threw at that thing. That being said, I would not recommend Oracle without all of the above factors already in place. For most use cases, Oracle is more pain than it’s worth.

As somebody who does a fair amount of DBA work, Oracle is my favorite RDBMS to operate. Low level administration is easy to manage, the optimizer is very performant, and the plan management features make tuning easy, and statistics management much less risky (and it can be very risky). I wouldn't use it on any of my own projects though, but only because I wouldn't want to pay for it, and because I have enough faith i…

To be fair, I have no idea how hard it was to manage Oracle vs. how much my DBA just liked to gripe. But I largely agree with you, except I would lean towards a managed service because I’m no DBA.

Re: Oracle vs. PostgreSQL: First Glance

#176

Post by m_mueller » Tue, May 05, 2020, 5:48 AM UTC

sjwright wrote: post #173

m_mueller wrote: post #171

Earlier quoted context omitted.

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale ho…

Sorry, but hard disagree. For all the failings of SQL systems, they work staggeringly well 99% of the time as long as the operator understands how they work. No-SQL systems do have their place, but all too often their choice stems from a failure to understand SQL-based databases. (And the hard truth: unless you’re doing something especially novel there’s a lot less difference between map/reduce and a bog standard tab…

Just to clarify: I'm not saying there is a significant difference between map views and indices, I'm saying I prefer the directness of programming with them over trusting the query planner and table statistics. In couch, to do a table scan, I need to program it as such, otherwise I'm forced to use a view - things like forgotten indices for some edge cases are impossible. Sure takes longer to do things, but when performance issues arise it's easy to reason about while with SQL based DBs I've seen a lot of time being spent at that stage. They work well 99%, and then that remaining 1% takes 99% of your time.

Re: Oracle vs. PostgreSQL: First Glance

#177

Post by sebazzz » Tue, May 05, 2020, 6:31 AM UTC

cryptonector wrote: post #139

krakatau1 wrote: post #92

Earlier quoted context omitted.

To be fair I think only Postgres has that feature.

Also SQLite3.

And MSSQL.

Re: Oracle vs. PostgreSQL: First Glance

#178

Post by NickNameNick » Tue, May 05, 2020, 9:56 AM UTC

ajuc wrote: post #77

nordsieck wrote: post #75

Earlier quoted context omitted.

>> Oracle evangelists > There is such a thing? Tech evangelist is a common job title.

I thought X evangelism is when you aren't paid, like with open source. If you are paid you're just a salesman.

I've seen 'Developer evangelist' as a job title.

Re: Oracle vs. PostgreSQL: First Glance

#179

Post by ants_a » Tue, May 05, 2020, 10:06 AM UTC

m_mueller wrote: post #171

ants_a wrote: post #140

Earlier quoted context omitted.

All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model a…

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale ho…

I somewhat agree. Not with the sweeping that declarative query languages can't work, for every case where faulty statistics cause a bad plan there are likely to be many cases where a statistic based plan choice correctly switched plans due to data distribution changes.

But I do agree that PostgreSQL has way too little tools to nail down the performance even though they have downsides. Tools like pinning execution plans would be nice, as it has less severe worst case behaviors. As would be the ability to just pass the execution plan directly, although that would have severe cross-version compatibility implications and security will also be hard to nail down after the fact because of all the "can't happen" assumptions sprinkled around in executor code. And even just plain plan hints would be great to have, be it the heavy handed "join in this order", "use this index", or the more graceful "this clause is way less selective than you think", "this clause is functionally dependent on that one" or "assume there is correlation between ordering and predicates".

Re: Oracle vs. PostgreSQL: First Glance

#180

Post by m_mueller » Tue, May 05, 2020, 10:30 AM UTC

ants_a wrote: post #179

m_mueller wrote: post #171

Earlier quoted context omitted.

Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale ho…

I somewhat agree. Not with the sweeping that declarative query languages can't work, for every case where faulty statistics cause a bad plan there are likely to be many cases where a statistic based plan choice correctly switched plans due to data distribution changes. But I do agree that PostgreSQL has way too little tools to nail down the performance even though they have downsides. Tools like pinning execution pla…

I'd put it this way: It could be made to work, but IMO all current RDBMS have the same problems with terrible worst case guarantees. Query planners have not enough degrees of freedom - maybe with a new concept like automated indices it could be made to work with acceptable average and worst case guarantees.