Hacker News Reader

There's a reason Oracle can charge an arse-load of money, and it's not because they're "evil": it's because their DB does some frigging incredible things when you know how to use it. I've worked with a 4 billion row star schema, partitioned by day then sub-partitioned for query optimization. It was OK to be "stale", so we inserted each day's data into an indexless table then swapped it in as a new partition over night.

Billions of rows in performance critical apps is pretty edge case. I think a lot of people paying for Oracle could pretty easily migrate away.

As much as I hate Oracle you're right, they are very much in the Cisco "makes the easy things hard and the hard things possible" zone.

Nearly every situation I've used Oracle under would have worked fine in any free DB (including SQLite3 in some silly instances) but I don't doubt there are situations out there where it excels.

> our data which is partitioned by week then organized according to a hierarchical triangular mesh with bitmapped indexes

Sounds like you're getting your money's worth out of Oracle - which is a good thing. For many others (especially those in the .NET world) they use very few of the high end SQL Server stuff.

Yeah, there's a lot of other things we use it for. We have a lot of hierarchical data that uses the CONNECT BY statement, and I hate it because it has issues with scaling and bad execution plans, so I'm working on migrating all of that to a closure table instead.

Does Postgres have an equivalent Connect By feature? Last I checked it didn't. If you don't need to scale your hierarchical data much, its pretty handy. I'd prefer to go to Postgres too.

PostgreSQL 8.4+ has recursive common table expressions. I don't know if they do everything that CONNECT BY does, but I know that they do everything that I've ever used CONNECT BY for.

Since way back when the tablefunc contrib module had a connectby() function, but it has some limitations being a C function.

The better way is to use WITH RECURSIVE CTE's. See http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.... for how we use them in LedgerSMB for hierarchical data.

WITH RECURSIVE is the SQL standard version of what CONNECT BY provides with oracle. Postgresql and SQL Server both have it.

If you'd like to give Postgres a serious shot, I'd recommend taking a look at Citus: http://citusdata.com/. It's a distributed database forked from Postgres, and can easily handle billions of rows in realtime for certain use-cases. We use it at MixRank to store and run analytics on for ads we crawl in the internet (a respectable amount of data), and it handles extremely well. (Full disclosure: I know the Citus team well).

I've also pushed Postgres to its limits and suspect you'd be able to make it work with that much data, though I wouldn't consider it a trivial exercise executing that transition on a production system. If you're seriously considering it though, feel free to reach out via the email in my profile.

There could be a good reason for Oracle to choose a hash join. Do you have a large table joining to a small table? Multiple processors? Setting the PGA might also be influencing optimiser...

The biggest issue with Oracle is that even experts don't really know how the optimiser does something. Tuning queries in Oracle s a bit of a black art. It really shouldn't be!

In PostgreSQL tuning queries is generally quite easy once you have learned to read the EXPLAIN output. The EXPLAIN output makes it obvious most of the time why the planner chose the plan it did. This is also an area which has received improvements in every recent release. Both the explain output and the cost based planner.

While your partitioning scheme should be implementable in PostgreSQL using table inheritance I do not doubt it is simpler to manage in Oracle. The PostgreSQL partitioning is lacking in usability and also a bit in features.