Postgres Performance

handlers.go

// listingDetailHander retrieves a single Listing from the database using the "id" in the URL params.
func listingDetailHandler(c web.C, w http.ResponseWriter, r *http.Request) (int, error) {
	id := c.URLParams["id"]
	title := c.URLParams["title"]
	listing := models.Listing{
		Id: id,
	}

	exists, err := listing.Get()
	if err != nil {
		return 500, err
	}

	if !exists {
		return 404, ErrRecordNotFound
	}

	// Re-direct to the correct URL if the slug does not match the id.
	if listing.Slug != title {
		http.Redirect(w, r, listing.GetAbsoluteURL(), http.StatusMovedPermanently)
	}

	return 200, renderTemplate(w, "listing_detail.tmpl", M{
		"listing": listing,
	})
}

// listingIndexHandler shows a sub-set (by desc. expiry_date) of listings at the app. root.
func listingIndexHandler(c web.C, w http.ResponseWriter, r *http.Request) (int, error) {
	listings := models.Listings{}

	var err error
	// Fetch the Listings from the database (LIMIT defined in application config)
	// If not results are returned, still render the index page.
	_, err = listings.GetLatest(conf.DB.PerPage)
	if err != nil {
		return 500, err
	}

	return 200, renderTemplate(w, "index.tmpl", M{
		"listing": listings,
	})
}

// The handler that calls GetAll is identical to GetLatest, bar the different method call.

queries.go

type Listing struct {
	// 21 fields
}

type Listings []Listing // So we can define methods on the slice

// Get retrieves a single Listing from the database by its ID provided it has not expired.
// It will return (false, nil) if the record does not exist.
func (l *Listing) Get() (bool, error) {
	// Only retrieve the listing if it has not expired
	err := db.Get(l, "SELECT * FROM listings WHERE id = $1 AND expiry_date > current_date", l.Id)
	if err != nil {
		// Delineate between a driver error and no result
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}

	return true, nil
}

// GetLatest returns a slice of listings from the database.
func (l *Listings) GetLatest(limit int) (bool, error) {
	err := db.Select(l, "SELECT id, title, slug, company, location, commute, term, expiry_date FROM listings WHERE expiry_date > current_date ORDER BY expiry_date DESC LIMIT $1", limit)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}

	return true, nil
}

// GetAll returns a all listings from the database.
func (l *Listings) GetAll() (bool, error) {
	err := db.Select(l, "SELECT id, title, slug, company, location, commute, term, expiry_date FROM listings WHERE expiry_date > current_date ORDER BY expiry_date DESC")
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}

	return true, nil
}