It’s not everyday you’re welcomed to a new job by someone asking if having a new 15″ MacBook Pro would meet your standards. I can only speak for myself when I say that I have never gotten a welcome email to a job before, and getting that email in my inbox triggered a legit happy dance! The question came from Will, who does devops at Cozy, two weeks prior to my arrival at Cozy for my summer internship.
I was granted this opportunity through the Emerging Leaders Internship (ELI) Program. ELI’s mission is to bring more underrepresented groups into management and leadership positions in Portland. They operate under the assumption that if underrepresented persons gain leadership skills during internships, within a few years, Portland’s leaders will be more diverse.
I’m pursuing my Bachelor’s Degree in Computer Science from Portland State University. A school advisor urged me to apply for an internship through ELI. Even though the chances of being picked for the limited number of internships seemed slim, I applied anyway. After a series of group interviews with ELI, then individual interviews and technical interviews at Portland companies affiliated with ELI, I landed at Cozy; one of Portland’s top tech companies, according to ELI.
“What do you want to do for your internship?” asked Rob, Cozy’s CTO and my manager for the summer. I was surprised by his question. I expected to be told what I’d be doing, which has been standard at other jobs and in school.
We came to an agreement that I would work on backend software development with a focus on optimizing Cozy’s PostgreSQL queries. This work aims to speed up Cozy’s website. I chose this task because regardless of the different types of SQL databases, the language doesn’t change too much, and I can count on every established system to have slow/problem queries.
The work I did
Let me break this process down for you. Whenever you’re on Cozy’s website, clicking around, those clicks generate queries (precise requests for information) from a server, and often times a database as well. That is called “querying.” Depending on how the code is written, the query could return information relatively fast or slow. My job was to find where those slow queries were being generated from in the code and fix them.
A slow query is kind of like asking a roundabout question, and getting a long, drawn out, roundabout answer; it will take a long time to extrapolate important information from the response. A fast query is like if you’d asked a simple, precise question, and receive the answer to your question without all the rigmarole.
Without going into too much detail, here are the steps I took to determine these are the steps I took to determine whether the queries in question were fixable (which unfortunately, not all of them were).
Step 1: Find a tool to determine slow queries. At first I used pgBadger, which pulls from the postgres.log. The log is generated by the Postgres database. However, if you have a Postgres database, I encourage you to set up the
pg_stat_statements table in your server, and use my app, pgPerformance which I developed at the end of my internship. More about that later.
Step 2: Look for slow queries. We looked for individual queries that take more than a second to execute, but if there were duplicate queries that took longer than a millisecond to complete, we also analyzed those too.
Step 3: Run
explain analyze on the query. In
psql (do not use pgcli, the spacing will mess up the next step), enter
explain analyze <your query here>; and grab the explain that prints out. This explain will give you a breakdown of where your database is spending its time.
Step 4: Paste the explain in https://explain.depesz.com/ and analyze results. You will want to pay close attention to the exclusive column. When there’s a high number in this column, you know that portion of the query needs speeding up. Sometimes, there isn’t a specific portion that has a high exclusive number, and in that case it will be important that you understand what types of searches are slow (i.e. sequential search, sub-selects) and how to speed those up.
Step 5: Fix the problem query. This could mean something as simple as adding an index, but you should be prepared to rewrite the SQL query.
Step 6: Compare the speed of your new query to the speed of your original query using the same process as Step 3. If you’ve added an index, you will just re-run your original query. Be sure to run the query twice. The first run is usually slow, and we want to know the cached speed.
Note: In the unfortunate event that the speed hasn’t improved, you may be stuck with what you got from the original query. This may mean that the architecture or general structure of the code will need to be rebuilt. If you see significant increase in speed, proceed to Step 7.
Step 7: Translate the query from SQL into code relative to your system. Cozy uses the Ruby programming language, and the Sequel gem (library of Ruby code) to talk to the Postgres server from Ruby.
What was the hardest query I solved? The queries that couldn’t be solved. I would work, and work, and work, only to find out that some queries just weren’t solvable. Once I figured that out, I wanted to work on something new. And that new, shiny thing was pgPerformance.
I built pgPerformance, which is an open-source app developed responsively for desktops and mobile devices. It gives you tables of your slowest Postgres queries by mean time, total time, most frequent, active queries, and long running queries (longer than one second). Once installed, it gives you live results every time you spin up the app. You can find it on my GitHub: https://github.com/aleenawatson/pg_performance.
At Cozy, pgPerformance was used to make sure we could see the bottlenecks and things we could fix in Postgres, which ultimately makes the Cozy web application faster.
Through creating pgPerformance, I learned HTML, CSS, and jQuery, and throughout my internship I learned SQL, Postgres, and even some Ruby—all things I knew nothing about before my internship.
Respected as an equal
During one of my first days at Cozy, Joshua, a software engineer, said to me, “Know that you can have this. You can be respected and listened to at work. And if you aren’t getting that, know that there’s better out there. You don’t have to settle for anything less than what you deserve, which is really just common respect.”
He made this comment right after our first Cozy all-hands meeting, a weekly gathering where we talk about the week’s projects and upcoming events as a company. Just attending the meeting made me feel a huge rush of appreciation and respect that I’d never felt before from so many people. I knew in that moment that everyone there cared about me, even though they barely knew me.
At Cozy, I was respected as another developer, regardless of my experience. Not once did I feel like “just the intern.” I think this level of respect, and the fact that I worked with so many uplifting, talented people, helped me learn and accomplish so much during such a small window of time.
About a third of the people of Cozy are women, which honestly surprised me. In school, I’m one of just a few women in every computer science cohort; women in my classes usually make up 10% or less of the people in the room. And the whole reason I pursued an internship with ELI was the appeal of bringing to light to the fact that we need more women and underrepresented groups in tech, which is something that resonates with me.
Being at Cozy made me feel like I gained a tribe of women in tech that I now look to for inspiration. What Joshua said changed my perspective and helped me ask more questions. I plan to carry this impression of respect and being listened to throughout my career as a woman in computer science and tech.
One of my most important goals for the summer was to figure out if the tech industry was where I felt like I belong. Before the conclusion of my first week at Cozy, I realized without doubt, tech is where I belong. The passion I felt in school for computer science was not a fluke, but something that prevailed in the workplace.
I’m excited for my future professional career, and to a large degree, I have Cozy and ELI to thank for that perspective. A huge thank you and shout out to the Cozy fam for the opportunity to find this excitement, and also for the assurance and confidence to continue on my path to the professional world of tech.