PostgreSQL DBE Position at Etsy.com

From: "Haim Schoppik" <haim(at)etsy(dot)com>
To: pgsql-jobs(at)postgresql(dot)org
Subject: PostgreSQL DBE Position at Etsy.com
Date: 2008-05-22 06:18:45
Message-ID: 55225.24.114.252.233.1211437125.squirrel@webmail.tuffmail.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jobs

Etsy is the number one site on the internet for buying and selling
handcrafted items. We have been a PostgreSQL shop since our creation in
2005 and are currently looking for additional full time PG talent to join
our team. If you are a seasoned DBA with extensive PostgreSQL experience
we would like to talk to you about opportunities at Etsy. Our offices are
currently located in Brooklyn, NY however remote workers are welcome if
they are up to the challenge.

Here is what we are looking for:

-- Minimum five years experience working with production database systems
for e-commerce sites, with at least two years working with PostgreSQL.

-- Experience with all aspects of engineering a PostgreSQL database,
including:
-- Schema design
-- Deep experience writing stored procedures in PL/PGSQL (other embedded
query languages a plus)
-- Excellent knowledge of the various types of indexes that PG provides
and their practical applications
-- Excellent facility with writing efficient queries that span multiple
tables
-- Practical knowledge of PG backup and replication solutions
-- Experience with some of the modules that have become defacto in PG
8.3 such as tsearch2
-- Familiarity with concepts such as sharding/horizontal partitioning

If you are interested in speaking with us please send us your resume to
work(at)etsy(dot)com with the topic "PostgreSQL DBA". Note: If you happen to be
at PGcon 2008 (www.pgcon.org/2008) most of our team will be around from
Wed 5.21.08 - Sat 5.24.08 if you'd like to meet and discuss the position.
When emailing your resume if you'd really like to get our attention please
include answers to the following questions:

1. Given the following table DDL:

CREATE TABLE user (
id SERIAL PRIMARY KEY,
first_name,
last_name
);

We want to create a table that contains "items" and associates them with a
specific user. At a minimum the table should list the item name,
description, creation date, and the last modified time. Write the DDL for
such a table and create indexes to make user associations run quickly.
What would be your strategy to make sure the last modified time is kept up
to date?

2. Let's say you had a table 'items' with many records that has a
varchar(32) field called 'status'. A small subset of the records have the
status 'active' and these records happen to be hit the most. Craft an
index that would improve the performance of searching such records.

3. At Etsy we use views extensively. The performance of select queries
against views can be dramatically improved by use of 'materialized views.'
What are they? Under what conditions are they a good choice for
improving select performance and under what conditions are they not?

4. Assume a view called user_items contains user_id (integer), item_id
(integer), and item_date_created (timestamp w/ timezone). Write a stored
procedure in PL/PGSQL that retrieves a result set of records from the view
ordered by item_date_created and which takes in a user_id, offset and
limit.

5. What are 'functional indexes'? How would you craft a functional index
over a column that is of type tsvector?

Bonus Points: Are you familiar with inheritance in PostgreSQL? What is
it commonly used for and what vital performance aspects are *not*
inherited by child tables from the parent?

Extra Bonus Points: Describe a method of database sharding that is not
built on dates but rather on active users. How could such a system scale
and what are the clear dangers?

Browse pgsql-jobs by date

  From Date Subject
Next Message Todd B Davis 2008-05-28 17:35:06 Job Posting Request
Previous Message Richard Broersma 2008-05-15 05:53:48 2008-05-14 Postgres Database Administrator