PostgreSQL DBE Position at Etsy.com - Mailing list pgsql-jobs
From | Haim Schoppik |
---|---|
Subject | PostgreSQL DBE Position at Etsy.com |
Date | |
Msg-id | 55225.24.114.252.233.1211437125.squirrel@webmail.tuffmail.net Whole thread Raw |
List | 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@etsy.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?
pgsql-jobs by date: