ARIN migrated from Oracle to PostgreSQL | Hacker News Reader

ARIN migrated from Oracle to PostgreSQL

lists.arin.net

116 pointswireprotocol31 comments

Open articleView on HN

It should also be noted that Afilias, which manages .org, .info, .mobi (and more), uses PostgreSQL.

So PostgreSQL is pretty heavily involved in internet infrastructure right now.

As it has been. Is this an under-documented fact?

What version, configuration, hardware, do they use replication and how?

Me to having worked for a registry ICANS requirements for up time and availability are "challenging"

Would be very interesting to see how they setup PostgreSQL to support HA

I just remember reading that the .org TLD was running on Postgres ten plus years ago. Yahoo knows a thing or two about scaling up on Postgres as well, as I recall. When these decisions were made, it was generally expected that one would have quite a lot of work to do, regardless.

Makes me wonder how much hacking of postgres source code it required.

I especially appreciate PostgreSQL's documentation. I find it easy to locate, search, and understand. I certainly have not had that experience with Oracle docs.

Why one would need docs if there are plenty of 100-200$/hours oracle consultants :)

It means you need a $300/hr consultant :)

I've always found Oracle's database documentation to be reasonably good -- PostgreSQL's is better, but I'd rather be using Oracle's than Microsoft's SQL Server docs.

To be fair, Microsoft's reference material is pretty good - it's standardized, comprehensive and available for every version. You just need to know what to google to find the right page..

Yeah, that's why its bad: the key to the utility of reference material is organization.

Oracle's documentation is excellent and is WAY more comprehensive than PostgreSQL. For example, most of PostgreSQL's SQL functions have a single line in a table to describe them, whereas each one in Oracle has it's own section with a full description, examples, etc.

I agree that the relative small size of PostgreSQL documentation and the fact that it fits in a single "book" compared to the many books that comprise the Oracle documentation can make it easier to find something. But can't say I've ever seen a product with as much quality documentation as Oracle.

You can find the documentation for Oracle 12c here: http://www.oracle.com/pls/db121/homepage

As a user, you want to look in "SQL Language Reference" first, followed by "Data Warehousing Guide" (found in a separate folder on the left side). As an administrator, "Concepts" is required reading followed by "Administrator's Guide".

Nice try, Larry.

> Oracle's documentation is excellent and is WAY more comprehensive than PostgreSQL.

I'd generally agree that Oracle's documentation is more extensive than PostgreSQL's in many respects, but Oracle's is also substantially less well organized than PostgreSQL's.

> For example, most of PostgreSQL's SQL functions have a single line in a table to describe them

Reference?

This is probably a good discussion point

http://www.postgresql.org/docs/9.3/static/functions-aggregat...

Not only as an example of "single line in a table" but also as an example where a huge volume would not add much value.

I'm struggling at this time to figure out how to produce multiple pages of boilerplate explaining "min()" that would be any more useful than the table.

On the other hand the explanation about nulls really sucks. Its important enough to put in the single line. For those too lazy to click and read, when a noob thinks he wants "SUM(bunchastuff)" what he probably wants is along the lines of using COALESCE or a homemade function that smells like COALESCE. There is a meta discussion that whenever NULLs are possible, and its more than just table definitions for example an overactive WHERE clause, then the NULLs will be a PITA. Defensive SQL coding can be tedious.

It can also be viewed directly in the 'psql' shell with '\df'.

But it is so annoying to filter out all that branding, marketing slogans and self praise.)

Unfortunately, Oracle's documentation document Oracle's product. That's reason enough to prefer PostgreSQL's documentation.

That was a bit tongue-in-cheek... Oracle makes an exceptionally solid RDBMS and deserves credit for that. It's just that it hurts every time I write VARCHAR2.

PostgreSQL has some of the best documentation i've come across to. Very well organized and comprehensive enough.

I wonder if this was as much work as I suspect it was.

Depends on the complexity of their schema, and how many and of what complexity any stored procedures they relied on were.

In many ways PostgreSQL is fairly similar to Oracle and it also supports its own version of PL/SQL. There are tools to automate migration of both schema and data. I reckon the real difficulty of such a transition is updating application code (and legacy code) to work with PostgreSQL.

This is by design because EnterpriseDB (based on Postgres) is basically intended as a replacment for Oracle. Anything commercial that uses fancy Oracle features would want to look at this because it would be easier to support.

This is the real story

"Migrating from Oracle to PSQL" means almost nothing (from a technical point of view)

But what were the issues? How did their schema look like then and now? Any stored procedures? Any Oracle-only features that had to be migrated? Any PSQL-only features now being used?

Having worked on this team (QA side) for 3 years, I'm "just guessing" this was a big effort. Very cool Mark Kosters and Andy Newton gave the dev team the bandwidth to do this, their plates are almost always full of new features and whatnot.

There's an extension for PG that adds many of Oracle's functions:

http://pgxn.org/dist/orafce/

"This module allows use a well known Oracle's functions and packages inside PostgreSQL"

Does anyone here know how big the relevant database actually is?

We might try publicly brow-beating Mark Kosters for an answer, that would totally work. Or maybe: asking nicely.

most probable email addr: markk at arin dot net

https://www.arin.net/announcements/2013/20131214.html

You can brow-beat, I asked nicely.

Reply on news.ycombinator.com