From Blackboard to Green Screen

To help keep the COVID-19 pandemic at bay, during the summer semester of 2020 all lectures at the University of Tübingen were held in an online-only format. This also applied to the two courses “Implementation of Database Systems” (referred to as \(\textit{DB2}\) in the following) and “Advanced SQL” which I read during that term. Foregoing the established in-lecture-hall teaching and going fully digital affected all aspects of these courses. Throwing the existing course material overboard simply was no option due to limited preparation time. Instead, I opted to strike a middle ground based on existing slide sets and code examples: delivering the material in terms of YouTube videos required a shift of focus and reshuffling but did not turn the courses on their heads. The end result were two variants of \(\textit{DB2}\) and Advanced SQL which I consider the best I have yet delivered–depth was gained (not lost) and an extensive archive of video and code material was created that I am happy to share with everyone.

The present article shines a light on what it meant to read the courses in front of a green screen instead of a blackboard (Sect. 2) and zooms in on details that made \(\textit{DB2}\) (Sect. 3) as well as Advanced SQL (Sect. 4) work on YouTube.

The backbones of both courses have always been two extensive slide decks, deliberately designed to be heavy on illustrations and light on text. Since around 2016, I author all slide decks via (1) Markdeep [13], a Markdown variant that renders quite intricate drawings directly from textual descriptions that are placed inline with all other slide text, and use (2) PragmataPro, a font whose 9000+ characters support to lay out code, tables, trees, graphs, and other regular structures (see Fig. 1). Markdeep-based slides render instantly in any Web browser. The purely text-based input facilitates slide versioning and collaborative authoring (e.g., via git), encourages hot fixes, and allows to keep slide text as well as associated code samples together. We will see that the latter was crucial. These slide decks were largely kept unchanged, yet bugs were fixed and adaptations to the then current versions of the database systems (PostgreSQL v12 and MonetDB 5) in use were applied.

Resting on the foundation of these slide sets provided a good start. Still, an online course entirely based on these decks, essentially consisting of hour after hour of narrated slides in a series of YouTube videos, was a dire prospect for both, students and myself. A closer look at the core contents of both courses turned things around:Both courses naturally lend themselves to a lecture format that uses code extensively (predominantly SQL, but also C, the systems’ implementation language) to develop and express the central ideas, formulate examples, and devise experiments. A focus on code and the delivery via YouTube led to a course redesign that revolved around live coding. There is a vibrant YouTube subculture on live coding in many forms, and I entirely banked on its success from the first to the last lecture video. The approach held the promise that it would be far more engaging than the slide-only lectures. The evaluation of both courses by my students–most of which were a experienced live coding audience, it turned out–showed that that promise was kept (see Sect. 5). Truly live coding also kept me on my toes during the semester, in the best possible sense.

Canned video, live coding. Given the nature of the courses, live coding happened in the interactive read-eval-print-loops (or REPLs) psql and mclient of PostgreSQL and MonetDB, respectively. Less often, the unix shell was used to compile and run C code or initiate benchmarking runs. To save time and not bore viewers with the authoring and mistyping of larger code fragments, I used a text editor with a split view: code was rearranged and completed in the split’s top half, marked for evaluation, and (on key press) submitted to the bottom half which hosted the current REPL (see Fig. 2 showing a SQL query on the top and the [tail of] its EXPLAIN output in the bottom psql shell). In the Markdeep source, these code fragments live right next to the slide text they relate to. This helps to keep both in sync and provides welcome context for a piece of code that may have been written months or years ago. Before shooting the video, I cut&pasted the relevant fragments out of the Markdeep source into a blank file such that (1) viewers could focus on the code alone and (2) a code-only file could be distributed to the students: everyone was able to replay the code experiments on their own machines without the need to copy verbatim code from slides (or, let alone, YouTube streams). Call-outs in the videos explicitly named the current code file in use, making it immediate for viewers exactly which piece of the distributed material currently was in focus.

During the video shoot itself, I constantly switched from slides to code and back. It was absolutely instrumental that this switch was instant and seamless. Video recording and streaming software supports such multi-scenario setups; we come back to these at the end of this section. To tie slide contents and code samples tighter together, I used the PragmataPro font to typeset and edit both, leading to identifiers and symbols on the slides that were instantly recognizable in the live code. PragmataPro is highly legible yet narrow which ensured that code fragments remained readable even if the YouTube streams were watched at lower resolutions. At the same time, this allowed slides as well as editor windows to show reasonably-sized portions of code despite my deliberate use of large fonts throughout.

While videos were recorded in advance and then uploaded to YouTube (see below), the coding during the shot itself was truly live: I edited and ran code, applying modifications and extensions to it when they crossed my mind during the shoot. Such ad-hoc code changes led to some of the most insightful experiments during the semester but, inevitably, also conjured syntactic or logic errors here and there. I fixed these during the shoots while commenting on my interpretation of error messages. Quite quickly student feedback made it clear that this was seen as insightful rather than a nuisance. In consequence, I relied on pre-recorded coding footage only once during the semester, when an experiment based on CPU caching and branch prediction behavior turned out to be so time-sensitive, that the simultaneously running video recording process would ruin all measurements.

As intimidating and daunting the live coding sections felt at first, the more I enjoyed them towards the end of the semester. Knowing that true boo-boos could be removed through post-shoot video editing helped but was seldomly necessary. The frequent switches from slide to code and back led to changes of pace, structured the video’s narrative, and allowed for interactive bits of content that slides could never contain. Sects. 3 and 4, respectively, discuss how the \(\textit{DB2}\) and Advanced SQL courses were laid out to emphasize the live coding parts.

Push and pull: weekly assignments. The prevalence of code and the time spent to develop, modify, and discuss fragments of SQL and C during the videos, also set the tone for the weekly assignments that accompanied the entire semester: students outright expected to read and write significant chunks of code during these assignments. With essential support by my assistants, we made sure that the coding style (e.g., code formatting and choice of identifiers) in the assignments matched those in the lecture videos. After a while we found that many students submitted code that mimicked the lectures’ style, leading to well-structured submissions that were easy to grade by assistants and tutors.

For the \(\textit{DB2}\) course, the availability of the source code for both, PostgreSQL and MonetDB, facilitated new kinds of assignments. Once a lecture had identified an algorithm or data structure as a central database kernel concept, we asked the students to locate its implementation in the systems’ source. Invariably, this turned up variations or simplifications of the original textbook concept, which helped to appreciate the complexity of a true and tested DBMS.

Just like the lecture slides and associated code, we published the assignments on GitHub. Students pulled the assignment text along with code skeletons and completed the latter to build their solutions in teams of two, using GitHub’s collaboration features (e.g., pull requests). Tutors then annotated the solutions, pushing the graded submissions back to GitHub from which the students pulled and reviewed the tutors’ comments. The tutors’ ability to place annotations right next to or inline with the students’ code led to very specific and actionable feedback, sometimes in the form of actual code snippets that would improve or correct the students’ original solution. GitHub’s push-pull model turned out to be a very fitting two-way communication channel that helped to establish a connection between students and their tutors, making up at least partly for the lack of face-to-face meetings.

Throughout the entire semester and beyond, we operated a Discourse-based forum that served as a hub for discussion of the video lectures and the weekly assignments. Any new posting in that forum would trigger a notification such that my research assistants and myself were able to respond immediately, typically within less than 30 minutes. This led to a lively atmosphere of back-and-forth in the forum with several hundreds of postings during the overall semester. While even a bustling forum is hardly a replacement for live interaction, polls among the students showed that, taken together, (1) a forum with quick turnaround, (2) the around-the-clock availability of YouTube, and (3) the ability to pause/rewind lecture videos as needed, formed an acceptable replacement for in-class tutorial sessions.

Think like a YouTuber. Staying alert and attentive throughout a 90-minute in-hall lecture is challenging. To expect the same from viewers of a video stream at home–where distraction is abundant–is straight out wishful thinking. From the get go, I thus aimed for brief lecture videos that averaged about 25 to 30 minutes in length (looking back, I now regard this as the absolute maximum). While this payed respect to limited attention spans and helped to blend with the “YouTube culture,” there also were ample practical benefits:Further, limited video length enforced a bite-sized presentation of the course contents: it helped to entirely focus on the \(\text{B}^{+}\)tree leaf level now, take a breath, and zoom into inner nodes only later instead of tackling a monolithic all-embracing \(\text{B}^{+}\)tree discussion. Student feedback was unanimously positive.

For \(\textit{DB2}\), the entire course contents was split up into 83 videos; for Advanced SQL, 58 videos were shot. YouTube’s play lists turned out to be ideal to organize these collections: viewers let their video players walk the list from front to back to take in the entire contents. In addition, I was disciplined in numbering all videos, gave descriptive titles, and added keywords which facilitated to hop and search through the play lists at will. Weekly assignments then referred to these video numbers (“Relevant videos for this assignment: #\(n\) to #\(m\)”). Students used these hints to adjust the rate at which they consumed the play lists.

Not least important, a video length of about half an hour helped me to keep my concentration throughout the shoot. The intricacies of the upcoming slides (way less than 10 in one video, typically) and code fragments to be discussed in that time frame, were easily remembered. As a consequence, almost all videos were shot in one take from start to finish, a process that would not last much longer than the 30-minute target. Still, close attention and care was required. After about four to five video shoots, I started to feel exhausted and adjusted my daily pensum accordingly.

Video post-processing was kept to be very light and typically required less than 15 minutes per video. Occassionally, one of the above-mentioned boo-boos needed cutting–the ability to insert fluid frame-to-frame transitions made most of these cuts next to invisible. Whenever that appeared helpful, I overlayed call-outs onto a sequence of frames (see Fig. 3). These call-outs pointed out details I forgot to mention during the shoot, contained corrections, or directed viewer attention to a particular portion of the frame. Used sparingly, these can be effective tools to touch up an already canned recording. Call-outs also worked great to sprinkle the videos with the occasional joking remark.

The video recording itself was performed with OBS¹, a piece of free (in the beer as well as the GNU sense) software that I cannot recommend highly enough. To set the stage, I defined multiple screen layouts (scenes) between which OBS could switch on a key press: intro as well as outro screens, slides, and editor + REPL. I additionally set up a scene for an overhead camera that would film my hand while I was scribbling on a blank DIN A4 sheet (Fig. 3). Now and then I used this additional camera to capture quick sketches or examples that would benefit from a stepwise development in front of the viewers.

Most of the scenes were configured to insert a camera picture of my head and upper waist into the frame’s lower-right corner as shown in Figures 1 and 2. Since such an insertion unavoidably obscures parts of the slide or REPL contents, I tried to minimize its impact and removed the background behind me using a vanilla green screen. I am fully aware that the value of talking heads is up for debate. Over the course of the semester, I found myself to make more and more use of the feature, though: it was essential that my arms and hands were captured which allowed me to pinpoint caveats ( ), rate a specific behavior of the database system ( / ), or use similar gestures. The talking head definitely helped to keep myself engaged and student feedback suggests a similar effect on the viewers’ side.

(  Play list: http://​tiny.​cc/​DB2-summer-2020.)

As its name already suggests, \(\textit{DB2}\) is the second installment in a tandem of courses on relational database technology. \(\textit{DB1}\) introduces students to a variety of data models with the relational model being front and center. These data models are never discussed without their associated languages. Indeed, the lion share of \(\textit{DB1}\) revolves around SQL. While the language itself is a subject of core interest, SQL is also used to explore other fundamental concepts of the relational model–functional dependencies, say–in terms of queries.

\(\textit{DB2}\), then, is meant to look behind the scenes and explore the internals of systems that implement the relational data model. With an audience in mind that has heard \(\textit{DB1}\), the design of \(\textit{DB2}\) builds on a working knowledge of SQL: the SQL language is used to formulate a series of queries and updates that are crafted to exercise the individual components of a database system. The system’s built-in or tacked-on monitoring facilities are then used to observe the database kernel’s response. This naturally leads to a study of the kernel’s algorithms and data structures involved in generating the result of our queries.

This series of SQL probe queries replaces the commonplace walks through architectural “boxes-and-arrows” diagrams that determine the structure of most \(\textit{DB2}\)-like courses. Instead, we bank on the natural curiosity felt by novice and not-so-novice users of SQL: “How did that query execute in only 5 milliseconds?”, or “Table size only doubled but the query now used significantly more buffer space–why?” If the series of probe queries is arranged carefully, students will encounter database components in a gradual fashion. Importantly, the presence and function of the components is motivated by the probe query itself. Juggling with probe queries further provides essential training for database practice beyond the course: posing the right inquisitive queries can disclose lots about any RDBMS’s internals. The overarching rationale here is that the immediate application of existing knowledge (SQL) is more engaging and satisfying than the confrontation with the overwhelmingly complex architecture of database components, the operation of which is (initially, at least) opaque.

The \(\textit{DB2}\) material has been derived from a variety of scientific papers, few textbook excerpts, developer blogs and mailing list postings, Stack Exchange Q&As, SQL references and standards, RDBMS documentation and kernel source code, as well as experience and best practices. A dedicated chapter on the choice of indexes that fit a given set of queries was inspired by Markus Winand’s book SQL Performance Explained [20]. This book is remarkably to-the-point, uses clever notation to illustrate the value distribution inside index pages, and is ripe with practical advice on index definition and usage in contemporary SQL. A free edition is available on the Web [21].

( Play list: http://​tiny.​cc/​AdvSQL-summer-2020.)

“Can you express \(\langle\textit{computational problem }P\rangle\) in SQL?” For arbitrary \(P\) and for more than 20 years now, since the advent of SQL:1999 [16], the answer to this question is a definite “yes”: the introduction of recursive common table expressions (CTEs) has turned SQL into a Turing-complete language. The question remains whether \(P\) indeed should be tackled using SQL. The knee-jerk answer goes along the following lines:

The course Advanced SQL has been designed to challenge this far too common point of view. We set out to use SQL in earnest and consider a substantially larger subset of problems \(P\) to be in its reach. Advanced SQL does not advocate to replace general purpose programming languages, but treats SQL as a true programming language with very specific strengths (and, not the least, terseness and style). The course is ripe with scenarios and use cases that would not be considered “query-like” by most, but still have elegant and efficient SQL solutions. A variety of these problem scenarios is listed below. We pursue the goal to boost the students’ ability to think and write in SQL, certainly way beyond the SELECT-FROM-WHERE class of problems.

A division of complex computation between the database system (where the data lives) and an external programming language (where the processing takes place) is bound to suffer from the infamous DB/PL bottleneck [5]. In the face of ever more workloads that are data-intensive and computationally complex–think database-supported machine learning [2]–I do think that Advanced SQL is a timely course offering: move your computation close to the data [15] has never been a more important credo.

Straight in line with the overarching theme of this article, Advanced SQL promotes to read and write lots of SQL code. Since some of the computational problems we tackle are algorithmically complex, the associated SQL text may indeed be substantial in size. At points, Advanced SQL devotes entire series of slides to develop a single query. The course thus discusses techniques to author large queries, predominantly based on the use of common table expressions (WITH) in which, helpfully, the reading and evaluation order of SQL aligns. Much like for \(\textit{DB2}\), this code-centric approach in the lectures also determines the style for the accompanying assignments: students author many-line SQL statements and the lectures slides and videos offer methods to organize the resulting code.

The focus of Advanced SQL deliberately is on the syntax, semantics, pragmatics, and–first of all–practical use of SQL. Aspects of the language’s implementation or optimization are not on the table: this is the realm of \(\textit{DB2}\). We aim for efficient formulations but, for once, this takes the back seat behind expressiveness, readability, and good style. By the end of the course, some may argue that we have crossed the borders of what should be expressed in SQL–this may apply to the use of non-linear recursion which is perfectly expressible and usable in PostgreSQL, for example. Here, our rationale is to bend the language until it breaks: knowing SQL’s limits helps to assess its applicability.

Advanced SQL uses PostgreSQL’s dialect of SQL. The system offers a stable, well-documented, and complete implementation of SQL:2003 [17] that is constantly being modernized. In particular, recursive CTEs in PostgreSQL come with less restrictions than their counterparts in Oracle or Microsoft SQL Server. I used PostgreSQL v12.1 in the videos of the Summer 2020 course, but any version 11 or beyond would have been workable.

SQL beyond SELECT-FROM-WHERE. True to its denomination, the course only spends the first few lectures to remind students of the basics of the relational model and the SELECT-FROM-WHERE backbone of SQL. The course then dives deep into advanced ideas in SQL, among these:

Complex computation close to the data. Coverage of the above SQL concepts, however, is not what primarily determines the flavor of Advanced SQL. Rather, the course focuses on algorithmic challenges and then identifies the SQL constructs required for their solution. To frame these challenges, extra effort was invested to devise scenarios off the beaten paths–not the least, this facilitated the production of slides and varied assignments. I made a point to never use scenarios like the well-trodden employees-departments-projects: these are overused, uninspired, and hardly suited to engage students in deep thinking and query authoring.

It is foreseeable that the majority of students will never be required to build parsers or simulate cellular automata with SQL. With the above computational tasks successfully implemented, however, students built confidence in both, the potential of SQL and their own ability to express these problems in a declarative, set-oriented fashion. I am positive that most students who completed the course will understand RDBMSs as capable data processors rather than mere keepers of piles of tabular data.

As I write this in October 2020, the videos in the \(\textit{DB2}\) and Advanced SQL play lists have a cumulative view count of 26 200+ (more than 3 300 hours of watch time). Viewers predominantly are from the Tübingen area, but YouTube analytics data and e‑mail feedback indicate that the videos have a global reach.

With the summer semester of 2020 drawing to a close in late June, the Computer Science department at University of Tübingen contacted its students to conduct the regular teaching evaluation. Survey forms were sent to the \(80+\) and \(90+\) students of Advanced SQL and \(\textit{DB2}\), respectively. A sample of their responses (in German) has been reproduced below. Based on the students’ evaluation, among the 37 courses that were evaluated by the department in that semester, \(\textit{DB2}\) and Advanced SQL ranked in the two top spots.

Die Qualtität der Videos ist wunderbar, gerade das Unterteilen der Themen in kleinere Videosegmente ist eine sehr gute Idee. Der Mix aus Theorie (Folien) und Praxis (Shell/Queries) ist genau das was ich brauch um ein Thema gut zu durchblicken.

Die Veranstaltung wurde optimal digital umgesetzt. Die Videos von Herrn Grust haben eine extrem hohe Qualität.

Die Videos haben eine angemessene Länge und mit den großen Folien und dem kleinen Greenscreen-Grust ein sehr vorlesungsnahes Feeling - und es ist auch schön, dass sie auf YouTube im Prinzip für Alle, die es interessiert, verfügbar sind. … Wie immer wird gut erklärt und es gibt viele Beispiele, Folien mit stylischen ASCII-Grafiken (Hut ab!), …

Auch das Aufteilen einer Vorlesung in mehrere kleine Videos finde ich sehr gut. Im Vergleich zu anderen Vorlesungen, die ihre Inhalte immer am Stück hochladen ist es in dieser Vorlesung einfacher, den einzelnen Gedankengängen zu folgen und spezielle Themen zu wiederholen. Auch dass die Übungsblätter angeben bis zu welchem Video sie den Stoff abfragen, macht die Leistungsanforderung sehr transparent und man weiß genau welche Teile der Vorlesung man wiederholen sollte, ….

Dass die Vorlesung aufgezeichnet wird, statt live stattzufinden finde ich sehr erfreulich, da die Videos zu persönlich gut passenden Zeiten geschaut werden können, auch mitten in der Nacht. Außerdem kann man die Vorlesung pausieren um nachzudenken oder einer anderen Tätigkeit nachzugehen, die Wiedergabegeschwindigkeit bei Bedarf verändern und sich Inhalte wiederholt ansehen. Die Videos speziell zu dieser Veranstaltung haben außerdem eine sehr gute Länge, sodass sie sich gut einteilen lassen und kurze Erholungspausen einfach ermöglichen.

[Positiv war] Aufbau und Struktur der Vorlesung, Übersichtlichkeit der Videos in der Playlist ….

Prof. Grust erklärt in seinen Videos wirklich sehr gut und man merkt, dass er dieses Thema sehr gerne lehrt! Das motiviert natürlich nochmal ein bisschen mehr. Er ist einer der einzigen Dozenten, die sehr schnell die Umstellung auf digitale Medien (toll) hinbekommen hat,

On Demand Videos in Top Qualität und aufwendig erstellt. Gut erklärte Sachverhalte. Anschauliche Beispiele.

Ich finde die Veranstaltung sehr gut und setze mich gerne mit dem Stoff auseinander. Mir gefällt die Lehre mittels asynchroner Videos. Gerne immer so!

Auch wenn es wieder Präsenzlehre gibt, sollten die Videos für zukünftige Generationen auf youtube erhalten bleiben.

Massives Lob auch dass Übungen über Github laufen, falls Fehler/Unklarheiten in Aufgabenstellungen sind, werden Changes gepusht, das ist super! Falls dann etwas unklar wäre, [sind] die Reaktionszeiten und Antworten im Forum sehr gut.