Category Archives: Databases

Backing up your database from Heroku

I’m writing a quick post on this, because every time I do it I fck around for a while and eventually get it – so this time I am writing my steps!

And yes, I know there is documentation on Heroku, but it never quite works for me. So!

  1. Make sure you have a database created and migrated into which you are going to put your backup
  2. Capture a backup with $heroku pg:backups capture --app rails-girls-events
  3. It will give you a backup id, which you can use in the next command to get the public url of that backup $heroku pg:backups public-url b006 --app rails-girls-events
  4. When you visit that url, it will download to your machine
  5. Use the path to that download to upload it $pg_restore --verbose --clean --no-acl --no-owner -h localhost -U tracymusung -d your-data-base-name path-to-your-download

 

Leave a reply

Database Normalisation

In March I have been concentrating on learning more about Databases. I got a couple of books (7 Databases in 7 weeks by Pragmatic Programming, and SQL and Relational Theory by CJ Date), I got the Lynda program I spoke of previously. I’ve also been doing some coding tutorials online like Learn SQL the Hard Way and SQL Zoo.

As many of my programming friends, colleagues and mentors predicted – the textbooks were my least favourite approach. In 7 Databases, I could only get through the intro and the chapters on Postgres before my eyes glazed over. The Relational Theory one I hope to read more of in the future, but I couldn’t get into it right now – I’m feeling like learning more practical skills for now, rather than theory. I wanted to know how to understand more about the database I’m using every day at work.

Continue reading

Leave a reply

Database Indexes – The Best Explanation in the World

Every time I think I understand indexes, I later discover I don’t. Now I feel like I REALLY do (take with a grain of salt), and this is how I understand them to be…

Indexes are all about speed of access. While a primary key lets us uniquely identify a row, it doesn’t help us find the row really fast – that’s what an index is for. An index helps you jump directly to the relevant record.

A clustered index is the one column in a database that the data is ordered by. You choose one column to order all the data by down on the disk level where it is saved.

Most often, the primary key on any table is the clustered index. Whichever column is the clustered index, the database will order the data in the table based on that column.

Each table can have only one clustered index, but you can have others which aren’t clustered.

If you query on the clustered index column – the query will be very fast. However, if you query on a column without an index, the Database management system has no idea where that particular record might turn up, so it would require it to read through every row of the database looking for it. this is also known as a Full Table Scan, which is massively inefficient. The more rows, the worse it gets. If you get to 100000s, it is highly inefficient.

So you might do a non-clustered index, or secondary index, on another column. This is like an index in the back of the book.
Continue reading

Leave a reply

Avoid SQL Nightmares

It’s a simple, simple tip:

Instead of being all like

DELETE * FROM users
WHERE id = 23

How about first you go

SELECT * FROM users
WHERE id =23

and once you’ve seen the results and are sure you’ve got it exactly right, then and only then, change SELECT * to DELETE

Leave a reply

PostgreSQL Join Tables

For our ‘Winter of Code’ we’ve chosen PostgreSQL as the database to be using while working on Spree. I’ve been reading a lot about it, but still get a bit confused on a few things. However, I’ve found this excellent resource, which has interactive exercises plus really great explanations.

I don’t want to forget them, so I’m making a list of them here, and in case he ever removes them, I’m also lifting the content (with links for attribution).

Continue reading

Leave a reply