Comp 305-001/488-305: Database Administration

Spring 2020: Mondays, 5:30-8:00

Week 11

Apr 6

PostgreSQL is the world's best database! Yay! This was the title of a blog post by Kirk Roybal three weeks ago.

I'm using #1 on Roybal's list, "transparent security", as an example of what not to do for open-source security, in my open-source class. #2, on MVCC, is important, but MySQL and Oracle also use MVCC.

Three weeks after Roybal's post, Rick Branson posted 10 Things I Hate About PostgreSQL. For the record, Branson is a hard-core Postgres deployer. He's got a list of 10 things that are terrible. Let me draw particular attention t o#4: MVCC garbage [collection] frequently painful.

Who is right? Maybe both! You can be the world's best and still have some rough edges.

But I will say this: if you even have a vague understanding of most of Branson's 10 Things, you have a lot of insight into Postgres. Ditto if you can make out Branson's third paragraph for his #4:

In contrast, MySQL and Oracle use redo and undo logs. They don’t need a similar background garbage collection process. The trade-off they make is mostly additional latency for transactional commit and rollback operations.

We'll get to MVCC soon.

Very Slow Query: What happens if you don't have an index to do an index join?

CTEs and recursion

    Do the bigcompany recursive queries.

    How would you write a query to determine if the University prerequisites table had a circularity?

Window functions