https://carlineng.com/?postid=sql-critique#blog

Carlin Eng

Blog PBP 2019 [?] About [site-image]

Former Data Engineer and Engineering Manager at Strava; then spent
two years as a Sales Engineer and Data Scientist at Snowflake.
Currently Head of Data Engineering at Eppo. Avid cyclist, and proud
member of the Dolphin Club in San Francisco, California.

Made with [?] by Carlin Eng
   

About

This page is based off of the John Doe template, a single-file
website written only in HTML and CSS, to which I've added small
Javascript snippets for toy functionality. Source code for this
website can be found at Github. -[?]

A Critique of SQL, 40 Years Later 08.11.2022 [ ]

The SQL language made its first appearance in 1974, as part of IBM's
System R database. It is now over 50 years later, and SQL is the de
facto language for operating the majority of industrial grade
databases. Its usage has bifurcated into two domains - application
programming and data analysis. The majority of my 12 year career
(data engineer and data scientist) has been concerned with the
latter, and SQL is by far the language that I have used the most. I
love SQL for the productivity it has afforded me, but over time I've
also become aware of its many flaws and idiosyncrasies. My
perspective is primarily from a practitioner's standpoint, and I have
always been curious if those "real world" issues have more
fundamental or theoretical underpinnings. This brought me to A
Critique of the SQL Database Language, by mathematician and computer
scientist CJ Date. Date was a former IBM employee, a well known
database researcher, and friend of EF Codd. The SQL standard has
received many major updates since this critique was first published,
but which of those critiques are still valid today?

A Critique of the SQL Database Language was first published in
November 1984 in The ACM SIGMOD Record. It examines the dialect of
SQL implemented by several IBM systems (SQL/DS, DB2, and QMF) which
provided the basis for the initial SQL standard. Having no direct
experience with any of these systems, reading the SQL examples from
the paper is a bit like trying to read 17th century English - it has
a stilting, unfamiliar cadence that requires an extra bit of effort
to understand. In the examples below, I'll use the terms SQL[1983]
and SQL[2022] to distinguish between the older dialect, and what is
available today. Use of the unqualified term "SQL" means my comment
could apply to both.

The paper consists of eight sections, each one describing a different
category of criticism: lack of orthogonality in expressions, lack of
orthogonality in functions, miscellaneous lack of orthogonality,
formal definition, mismatch with host language, missing functions,
mistakes, and missing aspects of the relational model. In the rest of
this post, I'll go through each of those sections, describe the
critique in informal terms, and give my interpretation on whether the
critique is still relevant.

Lack of Orthogonality: Expressions

Orthogonality with respect to programming languages means roughly
that the constructs of the language are like Lego blocks - a small
number of basic pieces can be recombined in simple and intuitive
ways. Lack of orthogonality (again, informally speaking) means the
language has lots of special cases and exceptions in how the
components can be put together, which make it complex to learn and
unintuitive to use.

This section begins with a definition of table-expression,
column-expression, row-expression, and scalar-expression.
Respectively, these are expressions in SQL that return a table,
column, row, and scalar value. In SQL[1983], the FROM clause of a
SELECT statement was restricted to only specifying table or view
names, and not general table-expressions, i.e., subqueries or
common-table expressions (CTE). This made constructing nested
expressions, one of the key features of Relational Algebra, nearly
impossible. Modern SQL provides the capability to reference a CTE or
subquery in a FROM clause, so this concern is mostly irrelevant
today; however, the idea that a table-expression can take the form of
"tablename" in some contexts, but must be SELECT * FROM tablename in
others is interesting.

For example, why not allow the following expression as a legal
statement:

tablename;

which would return identical results to:

SELECT * FROM tablename;

Both are table-expressions (statements that return a table), and thus
should be allowed anywhere that accepts a table-expression, e.g., the
FROM clause of a SELECT statement, or a statement itself.

While SELECT statements in SQL[1983] are not allowed in the FROM
clause, they are required as an argument to an EXISTS clause.
Furthermore, the SELECT statement here is required to be a
column-expression (selecting only a single column) - a statement that
returns a table, a row, or a scalar will not work. When is a SELECT
statement a table-expression, a column-expression, a row-expression
or a scalar-expression? The language itself provides no guidance
here, and it is wholly dependent on the query itself; e.g.:

SELECT a FROM tablename;

is a column-expression, but

SELECT a,b FROM tablename;

is a table-expression. This bit of arbitrariness still exists in SQL
[2022].

Lack of Orthogonality: Functions

While some of the concerns in this section are mitigated by the
introduction of subqueries and CTEs, a lot of them still hold true
today. Column functions in SQL take a column of scalars as input, and
return either a column of scalar values (e.g., the MD5 function or a
type-casting function), or a single scalar (e.g., aggregate functions
like SUM). The author argues here that since column functions take a
column of scalar values as input, any valid column-expression should
be allowed. An example where this is not the case is as follows:

SELECT SUM(val) FROM tbl

is allowed, but

SELECT SUM( SELECT val FROM tbl )

is not, even though "SELECT val FROM tbl" is a valid
column-expression - it returns a single column, val, from table tbl.

The key problem here is that the input to the SUM function in the
first example is a column name, but that column name alone does not
define the column-expression. Instead, we must look at the context
(i.e., the full query) to understand that the "val" column comes from
"table". Said another way, in SQL, F(X) is not dependent only on X,
but on contextual information surrounding F:

SELECT SUM(amount) FROM purchases;

and

SELECT SUM(amount) FROM purchases WHERE 1 = 0;

Are two very different queries, even though the column-function
invocation SUM(amount) is identical.

This also makes it difficult to nest aggregations. Consider the
following example: we have a database of purchases for an ecommerce
website, and want to retrieve (1) the total amount spent for each
customer, and (2) the average spend across all customers. SQL[1983]
could not solve this in a single statement. SQL[2022] can solve it
with the use of CTEs:

  WITH spend_per_customer AS (
    SELECT
      SUM(amount) AS customer_total
    FROM purchases
    GROUP BY customer
  )
  SELECT AVG(customer_total) FROM spend_per_customer

However, the following (arguably more natural) statement is not
allowed:

  SELECT
    AVG(
        SELECT SUM(amount) FROM purchases GROUP BY customer
    )

In the above query, the inner SELECT is a column-expression (SELECT
statement returning a single column), and AVG is a function that
takes a single column; however, the above statement does not work in
most databases. In Snowflake, the above query responds with the error
message "Single-row subquery returns more than one row", which I find
confusing, since the AVG function clearly expects input of more than
one row.

Another interesting consequence here is the necessity of the HAVING
clause. The HAVING clause is a favorite "gotcha" of SQL interviewers
everywhere. Why and how is it different from a WHERE clause? The
answer is not immediately obvious to someone looking at SQL for the
first time. Specialized knowledge like this certainly serves a
purpose as an indicator for experience, but it can just as easily be
seen as a deficiency of the SQL language. The HAVING clause provides
a scoping hint to a column-function to indicate that the function
input must make use of the GROUP BY clause. The author does not mince
words here: "The HAVING clause and the GROUP BY clause are needed in
SQL only as a consequence of the column-function argument scoping
rules."

The author also describes table-functions (functions that take a
table as input, rather than just a column), and laments several
instances of arbitrary and non-orthogonal syntax. First, the EXISTS
function (takes a table-expression, returns a scalar) can only be
used in a WHERE clause, whereas orthogonality would dictate that it
should be allowed anywhere that the language accepts a scalar.
Second, the UNION function is represented by an in-fix operator, and
since SQL[1983] did not allow arbitrary table-expressions in FROM
clauses, it was impossible to compute a column-function over a UNION
of two tables. This problem is solved in SQL[2022], as the following
syntax is now legal:

  SELECT
    SUM(val)
  FROM (
    SELECT val FROM instore_purchases
    UNION ALL
    SELECT val FROM online_purchases
  )

Lack of Orthogonality: Miscellaneous Items

This section contains a grab-bag of items related to functionality
and implementation details of the underlying systems - host/indicator
variables, cursors, "long" fields (e.g., character fields with length
greater than 254). Some of the limitations are indeed very
frightening (a "long" field could not be referenced in a WHERE or
GROUP BY clause!), but modern database systems are no longer subject
to these restrictions. Other items in this section have been
addressed by updates to the SQL standard. In no particular order, the
following limitations are no longer applicable:

  * Only simple expressions (column names) allowed in GROUP BY
  * NULL literal could not be used in places where a scalar constant
    was expected
  * No concept of UNION ALL
  * Only possible to aggregate at one level with the GROUP BY
    construct

While much of the discussion here is no longer relevant, the
discussion of NULL values remains as scary today as it ever was.
Inconsistency in NULL handling gives rise to some truly unexpected
and frightening results, most notably in aggregate functions.
Aggregate functions ignore NULL values, leading to the unfortunate
fact that for a column X with values x1, x2, ..., xn,

x1 + x2 + ... + xn != SUM(X)

and

(X1 + X2) != SUM(X1) + SUM(X2)

See the following example in Postgres:

  WITH v AS (
    SELECT * FROM (
        VALUES
          (1, 5),
          (null, 10)
      ) AS t (column1, column2)
  )
  SELECT
    SUM(column1 + column2) AS sum_of_addition
    , SUM(column1) + SUM(column2) AS addition_of_sum
  FROM v;

which outputs

    sum_of_addition | addition_of_sum
   -----------------+-----------------
                  6 |              16
   (1 row)


Formal Definition, Mismatch with Host Language, and Missing Functions

These three sections are taken together, as I found none of them to
be of particular relevance to modern databases, modern SQL, or
analytical query processing.

Formal Definition: This section highlights areas where the developing
SQL[1983] standard either disagreed with the IBM implementation, or
was not precise enough - cursor positioning, lock statements, alias
scoping rules, and more. I understand this section more to be a
critique of the standard, as opposed to the language itself.
Furthermore, many of these issues (cursors, locks) are not as
relevant to analytical processing, and are thus not as interesting to
me personally.

Mismatch with Host Language: Similar to the previous section, I found
this one mostly irrelevant. The author points out many differences
between SQL and the host language (e.g., IBM PL/I) that cause
friction for the programmer. Today, there are so many potential host
languages (Python, Ruby, Javascript. Java just to name a few), each
with their own idiosyncrasies, that it would be impossible for SQL to
conform to all of them. Technologies like LINQ aim to address some of
these concerns, but as with above, these primarily target application
programming use cases.

Missing Functions: Most of the bullet points here are related to
cursors and locking, which I view as implementation-specific details
related to underlying systems.

Mistakes

This section describes several things that the author views as simply
a mistake in the language design. Here again, NULL is the prime
example:

    In my opinion the null value concept is far more trouble than it
    is worth... The system should never produce a (spuriously) precise
    answer to a query when the data involved in that query is itself
    imprecise. At least the system should offer the user the explicit
    option either to ignore nulls or to treat their presence as an
    exception

It is interesting to note that this was far from the consensus view,
even amongst the original developers of the relational model. EF Codd
himself sanctioned the use of NULL in his 12 Rules (rule no. 3).

Other "mistakes" included are:

  * Primary Key is specified as part of an Index as opposed to at
    table creation time.
      + The reasoning here is that a Primary Key is really a logical
        property of a table, and should not be intermingled with an
        index, which deals primarily with the physical access path of
        that data. Today, most databases allow a CREATE TABLE
        statement to include a Primary Key, so this concern has
        largely been rectified.
  * SELECT * is undoubtedly convenient for interactive querying, but
    extremely prone to errors when used in programs.
      + Date argues that SELECT * should only be allowed in
        interactive sessions. I largely agree with this sentiment,
        but defining "interactive session" is by no means a trivial
        problem.

Aspects of the Relational Model Not Supported

This section is another list of miscellaneous items, unified by the
fact that each of them prevented SQL[1983] from truly being
"relational".

Primary Keys and Foreign Keys: Primary Keys could easily be ignored
by SQL[1983] and Foreign Keys did not even exist. While SQL[2022]
does allow for Foreign Keys, and many databases enforce referential
integrity, SQL[2022] still does not fully understand the semantics of
Primary Keys and Foreign Keys. Two examples:

  * When performing a GROUP BY on the Primary Key of a table, and
    including other columns from that table, because the Primary Key
    guarantees uniqueness, it is guaranteed that those other columns
    will also be unique; however, SQL requires that those columns
    also be included in the GROUP BY.
  * A join between a Foreign Key and its corresponding Primary Key
    could easily be implicit, but SQL still requires the join
    condition to be explicitly written out.

Domains: Domain is another word for "type". Type systems in SQL[1983]
only permitted primitive types (int, char, float, etc.). Today,
Postgres provides support for user-defined types of arbitrary
complexity, as well as check-constraints that allow users to restrict
primitive types to acceptable values. Unfortunately, most OLAP data
warehouses don't support user-defined types, and SQL itself doesn't
have much to say on the topic.

To take a simple example of how this can be dangerous, many databases
in the wild have tables with integer Primary Key ID columns. Clearly
not all of the operations that are legal for integers should be
allowed on Primary Key columns - what does it mean to add, multiply,
or divide two PK IDs? SQL, and most databases, will happily let you
perform these operations.

Relation Assignment: The critique here is a single sentence -

    A limited form of relation assignment is supported via INSERT ...
    SELECT, but that operation does not overwrite the previous
    content of the target table, and the source of the assignment
    cannot be an arbitrary algebraic expression (or SELECT
    equivalent).

This is no longer true. Relation assignment can be done via CREATE OR
REPLACE TABLE AS. With subqueries and CTEs, the source can be any
arbitrary algebraic expression.

Explicit JOIN, INTERSECT, and DIFFERENCE operators: SQL[1983] did not
support these. SQL[2022] does. JOIN was added to the SQL92 standard.
INTERSECT and MINUS are supported by most databases, and even if they
aren't, the operators have semantically identical equivalents using
JOIN.

Summary

While many of the critiques of SQL have been fixed by updates to the
ANSI standard, many are still present. Lack of orthogonality in many
places still exists, which makes SQL clunky to learn and use;
however, I suspect the learning curve here is not actually all that
high, judging by the number of people out there who can write SQL. By
contrast, missing components of the relational model and issues
arising from NULL values are likely the cause of many queries that
look correct but provide wrong answers, especially by folks who are
confident in their ability to write queries, but unfamiliar with some
of the nastier traps.

Despite the improvements listed above, in a 2014 interview, CJ Date
said "we didn't realize how truly awful SQL was or would turn out to
be (note that it's much worse now than it was then, though it was
pretty bad right from the outset)." This quote leaves me wondering -
if Date himself were to write an updated critique, what would it look
like? My best guess is most of his criticism would revolve around
further departures of SQL from the relational model, but specific
examples escape me.

SQL's market dominance means every DBMS vendor is strongly
incentivized to implement a SQL interface, and every aspiring
programmer must learn it. So does this mean that despite all its
problems, we're stuck with SQL for good? I think SQL will continue to
live on in some form for a very long time, probably even as the
dominant query language; however, I strongly believe there's still
room for the development of new query languages that have learned the
lessons of the past. Furthermore, I think the time is now better than
ever for such a language to succeed. My reasons for believing so are
beyond the scope of this essay, perhaps a good topic for the next
one.

My Time on the Job Market as a Data Engineer 04.17.2019 [ ]

In December 2018, I had an amazing job at a company I loved, doing
work I was immensely proud of. This made my decision to quit
extraordinarily difficult. This decision could itself be the topic of
a long and rambling blog post, but that's a story for another day. In
this post, I'll talk through my experience being unemployed, my
approach to the job search, and how I ultimately made the decision
for my next career move.

Unemployment

I left my prior job without a new one lined up. When my friends heard
I was entering a phase of funemployment, they all assumed I would be
taking off to backpack around the world for 6 months. I take that to
mean that I do a pretty good job of hiding my true workaholic anxious
nature. By the time the New Year rolled around, I was already
irrationally worrying about my employability and felt like I was
behind schedule with everything -- interview prep, networking, and
everything in-between. Soliciting advice from a few friends who had
taken extended time off, I was able to assuage the rising feeling of
panic and settle into a loose routine for my time off. I would get a
full night's sleep every day, spend mornings catching up on the
latest tech news and analysis (I read a LOT of Stratechery), and take
at least one weekday each week to do absolutely nothing job related
(this usually meant riding my bike all day). The rest of the time
would be split between catching up with old friends and former
colleagues, gathering information about companies I was interested
in, and practicing my programming and technical whiteboarding skills.

The Job Search

From both my experience as a hiring manager as well as preliminary
conversations with a few peers, I understood very quickly that it was
a buyer's market, and I could easily make job hunting a full time
effort. I also wanted to give myself the opportunity to truly
evaluate a broad spectrum of companies of all shapes and sizes,
across many different industries. In total, I spoke to 26 different
companies, had tech screens with 11 (withdrew my application from the
rest), did 9 onsite interviews, and ended up with 7 offers, the
majority of which were for individual contributor roles as a data
engineer.

I thought this process would be exhausting, but it turned out to be
far more fun and exciting than I anticipated. It was fascinating to
learn about all the different organizations and businesses, and the
unique challenges faced by each of them. As I talked to more and more
people, I started to develop a better sense of how to extract real
signal from my conversations. During interviews, most folks are
either in evaluation mode or sales mode. In both cases, they're
likely to stick to an HR-approved script. My goal in every interview
was to build enough rapport with the interviewer that I could
successfully navigate the conversation away from the typical cliches.
I also did my best to ask very similar questions to each interviewer.
By listening closely to their individual answers, I could then
evaluate them each in the context of the whole, which would often
paint a much more telling picture of the organization than any
individual answer. Do individual contributors understand the vision
set forth by their managers? Are the pains of the ICs being heard by
management? Are different business units aligned on the company
mission?

Every company I talked to had extremely aggressive hiring goals. Most
were looking to double their engineering headcount by the end of the
year, and more than double the size of their data engineering teams.
More often than not, when I asked engineering leaders about their
biggest challenges, hiring was #1 on the list. I began to evaluate
prospective companies through this lens, asking "how will this
company differentiate from all the others when competing for talent?"
Every company had a different angle for this, some leveraging recent
fundraising events or a high profile consumer brand, others leaning
heavily on their social-impact oriented mission. I tried to
understand not only how their answers appealed to me, but how they
might appeal to the broader segment of job-seekers.

Key Takeaways

I learned a lot during my interviews. Rather than try and tie them
all together into a neat narrative, I'll just list a few things that
stood out to me as noteworthy:

  * The technical bar for data engineering is reasonable. I did quite
    a bit of prep using the standard books and websites like Cracking
    the Coding Interview and leetcode. Never was I asked anything I
    felt was overly difficult or unfair.
  * I don't consider myself a great programmer, but do think I have
    better than average soft skills for an engineer. Based on my
    success during the interview process, I suspect this combination
    is more valuable than the inverse.
  * A 5 hour onsite interview is simply not enough time to
    effectively evaluate a workplace. During 5 hours of interviewing,
    a candidate has at most 1 hour available for asking questions
    about the company. How can someone possibly learn enough about a
    company during that time to make an informed decision? Doing
    pre-interview prep and intelligence gathering is absolutely
    critical, as is being efficient with your time.
  * I really enjoyed all of my conversations with companies, except
    declining offers. It's emotionally draining to let someone down
    immediately after they've congratulated you and told you how
    excited everyone is about the possibility of you joining. It also
    forced me to confront the reality that the choice to go through
    one door meant closing many others.

The Decision

I count myself as extraordinarily fortunate to have had my pick of
some of the best technology companies in San Francisco. I was looking
for a company with aggressive growth, a great product, and awesome
leadership, and while many of the companies I talked to met these
criteria, Snowflake was a clear cut above. When I first started using
Snowflake as a customer at my previous job, I was totally blown away
by their product. The Snowflake data warehouse was critical to my job
as a data engineer, and it was obvious to me how revolutionary a
technology they had developed.

The job at Snowflake was in sales engineering, a big change from my
prior role as an in-house data engineer. As a sales engineer, the
responsibilities are primarily around evaluating the data
architecture of potential customers, helping prove out the value of
Snowflake within that architecture, and scoping and executing on a
proof-of-concept. The chance to get a glimpse of data teams of all
shapes and sizes across the San Francisco tech scene and beyond
seemed like a unique opportunity.

From a team perspective, I knew Snowflake's sales and sales
engineering org fairly well from my time as a customer. Both groups
were great to work with -- their sales engineering lead was enormously
valuable in helping us with our initial implementation, and the
regional sales director struck me as an ambitious, driven individual
who would likely push me to realize more of my potential. This gave
me a high degree of confidence in the general quality of the team
over at Snowflake, which was confirmed yet again during my interview
process.

As I alluded to earlier, I spent a fair amount of my funemployment
reading through the back catalogue of Ben Thompson's Stratechery
blog. Stratechery focuses primarily on consumer technology, with
decidedly fewer articles on enterprise software, especially a product
as technical as Snowflake. Even so, many of the themes he emphasizes
over and over when discussing consumer tech apply just as well to
enterprise. In this light, many of Snowflake's initiatives made sense
as part of a broader strategy. I didn't see any other players in the
space operating at the same level, and this combination of superior
product and thought leadership made it an extremely compelling
opportunity.

I'm only a few weeks into my new role as a sales engineer at
Snowflake, and so far it has not disappointed. The energy around what
we're building, both in terms of the product and the business is
absolutely incredible. Funemployment is finally over, but now the
real fun begins!

Fort Bragg 600k Ride Report 05.13.2017 [ ]

The crown jewel of the San Francisco Randonneurs brevet series is the
Fort Bragg 600k. I'd never ridden a 600k before, and this would be my
longest ride by a good margin. I was confident my legs were strong
enough, but a 600k is ridden on the strength of a rider's stomach,
not his legs. The real challenge would be keeping myself adequately
fueled while preventing my stomach from revolting.

To that end, I enlisted the help of Hammer Perpetuem, a powdered
energy drink mix. One scoop of Perpetuem, 70 grams, delivers 135
calories -- 87% simple carbohydrates in the form of maltodextrin
(simple carbohydrate energy source), 10% soy protein (to prevent
cannibalization of lean muscle tissue), and 3% fat. It's a bland
substance, without the ultra-sweet kick of energy gels, and a
consistency somewhat reminiscent of sidewalk chalk, but it keeps the
engine running and burns relatively clean. I brought along 10 scoops,
and would keep a water bottle filled with a 2-scoop mix at all times.
Theoretically enough Perpetuem for 10 hours of saddle time.

Pt. Reyes Station is the first stop along the ride, and most other
riders made the standard rush for a pastry at Bovine Bakery. As they
heaped praises on their scones and danishes, I sipped my
Strawberry-Vanilla Perpetuem in silence. By Petaluma, I'd nearly
finished my second bottle, but was starting to crave salty foods.
Unwilling to stray too far from my liquid diet, I grabbed some string
cheese to satisfy my craving, and diligently mixed up my third bottle
of powdered fuel. As we rode to Healdsburg, the thought of fried
chicken tenders from Safeway lodged itself stubbornly in my
imagination. By the time I'd reached my fourth bottle of Perpetuem,
each successive sip was becoming more and more laborious. When eating
on the bike becomes a chore, it's a sure sign that bad times are
ahead. My fifth and final Perpetuem shake was meant to last all the
way out to Fort Bragg at mile 182, but halfway through the bottle,
the thought of drinking more induced mild nausea. Unable to eat take
in calories on my preferred schedule, the 40 miles from Fort Bragg to
the Indian Creek campground were an absolute slog. My legs began to
run out of energy, but my stomach remained closed off, shutting out
the possibility of refueling.

I arrived at Indian Creek at around 10 PM in a ragged state, but was
immediately welcomed by lots of familiar volunteer faces. I collapsed
into a camp chair around a blazing fire, and was handed a variety of
hot foods including homemade potato vegetable soup and crispy
quesadillas. I had planned to sleep for around four hours at the
campground, but really wanted to eat a full meal before going to bed
to allow my body to digest and recover. I stared blankly into the
distance, waiting for my appetite to recover. It never fully did, but
I was able to force down the soup and one of the quesadillas before
passing out in my tent.

I closed my eyes for what felt like five seconds, but my 3:30 AM
alarm rang loudly. Despite going to bed feeling pretty awful, I woke
up in much better spirits. I wolfed down an egg and cheese sandwich,
and was back on my bike, ready to go by 4:15. Riding through Anderson
Valley just before dawn was undoubtedly the highlight of my ride. Rte
128 is beautiful, but plagued by an endless stream of cars shuttling
back and forth from the coast. Combined with a small shoulder, it's
not a pleasant daytime ride. Before sunrise, I had the entire road
all to myself, with a gibbous moon lighting the valley.

Once I hit Healdsburg, I was essentially on autopilot. I deliberately
chose to ride solo on the second day to relieve the pressure of
trying to keep up with a group or pull through at an appropriate
pace. With a more relaxed pace, I could also afford to be less
draconian with my diet, and as an added bonus, I was completely out
of Perpetuem. A few Clif bars and Larabars held me over until
Freestone where I stopped in at Wild Flour bakery. Coffee with two
scones -- cheddar/bacon/onion and meyer lemon/cherry/almond -- did
wonders for my mood. From Wild Flour, I'm a skip, hop and a jump away
from San Francisco on familiar roads. I developed some pain in my
right knee right after Pt Reyes Station (mile 330), but at that
point, I felt so close to home that spirits were high, even limping
along at a severely diminished pace.

I rolled into the finish at Crissy Field just before 4 PM, greeted by
a small crew of volunteers holding down the fort on a windy day at
the shore. Too tired to socialize much, I grabbed a quick bite to
eat, remounted my bike, and rode the final few miles back to my
apartment.

Paris-Brest-Paris 2019 Ride Report

Paris-Brest-Paris has a credible claim to being the greatest cycling
event in the world. If this is true, it's not because the route is
the most beautiful or the most challenging (though it has its fair
share of both), but because of the sheer number of people
participating. Over 6,000 riders attempt the 1200 km course, and the
drama, triumph, and despair of each individual ride is on full
display to onlookers.

No ride report can accurately recreate the experience of the ride, so
rather than a play-by-play account, I'll instead tell a series of
vignettes about my sensory experiences -- the sights, sounds, smells,
tastes, and aches of my 88 hours on the bike. It's a terribly
incomplete picture, but telling the full story is an impossible task.
Paris-Brest-Paris is something you have to ride to truly understand.
Scroll down to continue reading [?]

[?][?][?]

Lights

Riding PBP means riding at all times of day, including the dead of
night. My wave rolled at 7:30pm, with the sun hanging inches above
the horizon. The chaos and the rush of the start made it tough to
appreciate the sunset, but night fell around us and my jitters
calmed. We began to catch riders in prior start waves, and the
infinite trail of red tail lights ahead looked like glowing coals
marking the way to Brest. Every now and then I'd be tempted to look
back, but it was always a mistake. Modern headlights are viciously
bright. Even a brief glance would make my eyes wince in pain and
leave spots in my vision. I had to glean whatever information about
the situation behind me by watching the shadows dance. When the lines
of my shadow sharpened, it meant someone was approaching from behind.
As they faded, it meant we were separating.

Three hours into the ride, I stopped on the side of the road to
relieve myself. As I stretched my back and neck, I looked up at the
sky and for the first time noticed the half-moon and the stars of the
Milky Way above. I took a few extra seconds to soak in the beauty of
the moment -- red lights marching on ahead, white lights slowly
approaching from behind, and the cosmos above, twinkling softly like
any other night. I remounted my bike and rejoined the endless stream
of red and white making its way west.

[?][?][?]

Smells

At our first stop in the town of Mortagne-au-Perche, my first remark
was "this whole town smells like brie." Our evening start meant that
for the first part of the ride, the French countryside was completely
cloaked in darkness. Without visual stimulation aside from the lights
of the other riders, my primary sensory experience was that of smell.

Rain from the previous day had moistened the ground, and every
forest, farm, and town that we sped through was an explosion of
odors. Four primary smells left a major impression on me, and all but
one I think was best described by a different type of cheese: brie,
feta, and chevre. The fourth was manure with some sour notes. Though
none were unpleasant by any means, 770 miles of riding incurred
fatigue in every part of my body, nose included. The occasional whiff
of wild lavender was always a welcome reprieve, and early morning
towns with boulangeries baking fresh croissants and baguettes were
absolutely divine.

Many US brevets travel on roads with moderate to heavy auto traffic,
so I'm mostly accustomed to the smells of gasoline and diesel exhaust
while I ride. I will take the smells of livestock, wildflowers, and
pastries any day.

[?][?][?]

Scenery

The PBP course rolls up and down through the tiny villages of
Normandy and Brittany. The scenery, while at times quite lovely, can
also become tiresome. The following pattern repeats endlessly: ride
through a flat stretch lined with either cow pastures or corn fields.
Turn into a small climb through a deciduous forest, and descend until
you reach the outskirts of town. The road pitches up again, and
cobblestone houses begin to appear, getting denser and denser until
you reach the top of the hill, invariably marked by a large church.
Begin the descent through the center of town over cobbled roads, past
the boulangerie and the pharmacy, and before you know it, you're back
in the corn fields. Quedillac, Medreac, Merleac, Reteac, Loudeac.
Rinse and repeat.

Sunflowers on the return leg, near Louvigny Sunflowers on the return
leg, near Louvigny [?][?][?]

Sounds

What does it sound like when 6000+ riders from every country on earth
attempt to ride 1200 km?

  * The steady whirring of my bike's drivetrain
  * The creaks, squeaks, and rattles of the bikes owned by 6000
    people who all have wildly varying bike maintenance standards
  * The low rumble of an approaching peloton moving twice my speed. I
    nervously anticipate the rush of wind as they blow past
  * Heavy labored breathing as we grind up the endless rolling hills
    on our way to Brest
  * The sound of Ian's voice up ahead as he chatters happily away
    with one of our many friends on the ride
  * Cheers of "Bonne route!" and "Bon courage!" from spectating
    French villagers
  * Crinkling of space blankets as riders toss and turn, trying to
    steal a few precious minutes of sleep on the floor of a control
    point
  * Constant chirping from Garmins, Wahoos, or other bike computers,
    alerting their owners to who knows what
  * The clopping of bike cleats on asphalt and tile floors
  * A cacophony of every language on Earth. French, German, English,
    Italian, Hindi, Korean, Japanese, Portuguese, Spanish, Chinese
    and more

[?][?][?]

Discomforts

 [?][?]

Sunday 7:30 PM: Feeling great. Start wave goes. Body feels amazing.

Sunday 10 PM: riding in same position for awhile in a fast group.
Feel the typical aches associated with riding bikes. Some soreness in
the saddle and a bit of numbness in toes, but nothing unusual. Knees
holding up great. Finally catch up to Irving, Ian and Ben who started
30 minutes before me. Grateful for the chance to sit up and ride with
them and stave off any potential muscle soreness from hammering with
this group.

Monday 4 AM: rolling out of the control point at Villaine-ah-Juhel.
We made the 200k mark in under 8 hours and in celebration I over-ate.
Bolognese topped with gruyere. Stomach not happy. Burps taste like
gruyere.

Monday 11 PM: a few hours of sleep at the hotel in Loudeac. Getting
up and I am stiff in the legs, but a few minutes on the bike and I'm
feeling good again. 450 km in, knee is holding up.

Tuesday 6 AM: descending in freezing fog down into the town of Sizun.
Cold air rushing over exposed skin is painful, but my time swimming
in the SF bay with the Dolphin Club has made me far more tolerant of
cold. Espresso and croissant in Sizun, and it's off to Brest.

Tuesday 10 AM: we reach Brest and I am ecstatic. 600 km in, and no
physical problems, just fatigue and hunger.

Tuesday 1 PM: left knee starts acting up. Haven't had issues with
this one before, so am getting worried. I stop, stretch out my legs,
and attempt to manage the pain by easing back on the pace and trying
different pedaling positions. Eventually start riding much slower,
and with more emphasis on the right leg.

Tuesday 6 PM: left knee pain persists, but isn't getting too much
worse from stop to stop. As expected, pedaling asymmetrically also
starts to cause problems for my right knee. IT band begins to tighten
up and generate soreness on my right side. Start limping at controls
and having trouble walking down stairs.

Tuesday 9 PM: Right knee decently swollen and left knee still feeling
moderate pain. Riding slowly has taken a huge toll on me. Without the
rush of physical exertion, staying focused on the road takes an
enormous amount of mental energy, leading to boredom and frustration.
I swear up and down that I am never doing this, ever again. To stay
awake and alert I fall back on caffeinated gels, which make me
nervous and jittery. In a bout of frustration, just as we are hitting
Loudeac for the second time, I turn up the gas. For a solid 15
minutes, I'm flying. Lots of pent up energy from the gels and
caffeine. Passing dozens of riders like they are standing still, and
momentarily the joy of riding returns. I stop briefly at a town
corner and notice that even though I am not feeling tired or
breathing heavily, my heart is absolutely racing. I stop, take thirty
deep breaths, and try to down-regulate both my heart rate and my
exhilaration. Oddly enough, both knees felt fine. Take a mental note
of that, but fully expect this foolishness to put my knees further
into a hole.

Wednesday 2 AM: wake up in the hotel, and everything is stiff as
hell. Open my mouth to yawn and immediately yelp in pain due to
chapped lips. Tongue and mouth are raw and abraded from constantly
chewing crusty baguettes. Legs feel moderately better, but can tell
that knees still won't be happy back on the bike. 450 km to go. How
will I manage?

Wednesday 10 AM: the heat of the day is driving me crazy, and still
bothersome knee pain means I'm moving super slowly. After 3 hours of
crawling, I look back and a huge group of cyclists is coming up on us
fast. I can't resist the temptation, and jump on the train and start
hammering to keep up with them. Japanese power couple pulls us all
the way to Villaine. Wait a second, knee suddenly feels great...

Wednesday 4 PM: Decide my knee only feels good when I'm hammering.
The last two days of riding slowly means my legs have a good deal of
snap left. Passing a continuous stream of riders, and begin
collecting a decent sized train of riders in my slipstream. Get swept
up by a group of twelve or so serious 84-hour riders and tuck in.
This is some of the most fun I've ever had on a bike.

Wednesday 11 PM: arrive in Mortagne-au-Perche. Legs start to ache
from the effort, but knees feel decent, especially after completing a
regular regimen of hip and hamstring stretches. My renewed focus on
riding hard means I'm paying less attention to my hands, and at this
control I start to notice some of my fingers going numb, a classic
symptom of ulner nerve compression. I'll take that any day over knee
pain.

Wednesday 3 AM: leaving the Mortagne Airbnb, I start developing a
pulsing headache, which I suspect is from not drinking enough water
before going to sleep. Both knees still ache slightly, but somehow in
a way that doesn't concern me.

Wednesday 11 AM: the finish. Nothing is terribly painful, but nothing
feels great either. Contact points (butt, hands, feet), joints
(knees, ankles), supporting tissues and small muscles (neck,
Achilles) are all straining from nearly 90 hours of continuous
effort. We are elated to have finished successfully, and all ignore
the aches and pains in the glow of the finish.

Thursday 8 AM: the aftermath. The worst after-effects are chapped
lips and sores/abrasions in my mouth and tongue, which make it hard
to eat. Of course legs and butt are sore. Not about to jump on the
bike again today, but after 1200 km of riding, I'm pleasantly
surprised at how good I feel. I can sense the randonnesia setting in,
and thoughts begin to form in my head -- what might a 2023 ride look
like?

[?][?][?]

This page is not referenced in the menu, yet it exists.

- back

[profile]