A Brief History of Haiti's Cherished Soup Joumou

Soup joumou is a savory, orange-tinted soup that typically consists of calabaza squash, beef, noodles, carrots, cabbage, various other vegetables and fresh herbs and spices. Courtesy of Nadege Fleurimond

Growing up as a Haitian American, January 1 has always been more than just the start of the new year for me. As a child, waking up on New Year’s Day, I practically floated through the air to the aromas of soup joumou, or Haitian pumpkin soup, resting in one of the decorative bowls my mother reserved for the annual tradition.

Every New Year’s Day, Haitians around the world consume soup joumou as a way to commemorate Haitian Independence Day. On January 1, 1804, Haitians declared independence from French colonial rule following the Haitian Revolution that began in 1791. Soup joumou is a savory, orange-tinted soup that typically consists of calabaza squash—a pumpkin-like squash native to the Caribbean—that’s cooked and blended as the soup’s base. To that base, cooks add beef, carrots, cabbage, noodles, potatoes and other fresh vegetables, herbs and spices.

“[Soup joumou] is freedom in every bowl,” says Fred Raphael, a Haitian chef and co-owner of two Haitian restaurants in the New York City metropolitan area. “[Haitians] fought for unity, just the same way we get all these different ingredients that come together and create this taste.”

Haiti, then called Saint-Domingue, formally came under French occupation in 1697 after Spain ceded the western portion of the Caribbean island of Hispaniola to France, according to Bertin Louis Jr., a cultural anthropologist at the University of Kentucky. To sustain the island’s colonial economy—which predominantly centered on the production and export of crops like sugar, coffee and tobacco—French plantation owners captured and enslaved nearly 800,000 Africans as a labor source on the island. Saint-Domingue, by the early to mid-18th century, grew to be the most profitable colony in the world.

During the colonial era throughout the 17th and 18th centuries, enslaved Africans cultivated squash, the crop at the center of soup joumou. According to Louis, enslaved Africans in Saint-Domingue were prohibited from eating soup joumou, despite being the ones who prepared, cooked and served it to white French enslavers and colonizers. The soup was a symbol of status, and by banning enslaved Africans from consuming it, the French were able to assert their superiority, challenge the humanity of Black Africans, uphold white supremacy and exert colonial violence, Louis says.

Haitian revolutionary Toussaint L’Ouverture holding a printed copy of the Haitian Constitution of 1801. Library of Congress Prints and Photographs Division

Following the nearly 13-year struggle of the Haitian Revolution led by revolutionaries like Toussaint L’Ouverture, Jean-Jacques Dessalines and Henri Christophe, Haitians declared independence from French control and celebrated their liberation by eating soup joumou. It is said that on January 1, 1804, Marie-Claire Heureuse Félicité Bonheur Dessalines, the first empress of Haiti and Jean-Jacques Dessalines’ wife, distributed the soup to freed Haitians. Over two centuries later, the tradition of eating the decadent soup continues in celebration of a large-scale slave revolt that created the world’s first free Black republic.

“The soup represents the claiming and reconfiguration of a colonial dish into an anti-colonial symbol of resistance and also Black freedom, specifically Haitian freedom,” Louis says.

For chef and restaurateur Raphael, it was a no-brainer to include soup joumou on the menus at both of the restaurants he co-owns—First Republic Lounge and Restaurant in Elizabeth, New Jersey, and ​​Rebèl Restaurant and Bar in New York’s Lower East Side. “You don’t have a Haitian restaurant if you don’t make soup joumou,” he says. In the near-decade since First Republic Lounge and Restaurant opened—Rebèl Restaurant and Bar opened several years later, in 2020—Raphael has made it a tradition to create a space for Haitian and non-Haitian community members to come together and enjoy the dish.

“We make it a practice in both restaurants to give [soup joumou] away for free the night of December 31 as we bring in the new year,” Raphael says. “We usually make a pot of vegetarian soup with no meat, and then there’s another pot with meat, and anyone can come in and enjoy that.”

Combined, the restaurants usually see a turnout of about 300 to 500 guests. Raphael and his colleagues spend days preparing for the event, which begins in the evening of New Year’s Eve and goes until the early morning hours of New Year’s Day. For Raphael, the soup presents an opportunity to educate people and bridge cultural gaps, whether that is non-Haitians trying the soup and learning the history behind it or Haitian Americans who have never been to Haiti or haven’t been back in years feeling more connected to the island. “We make sure that [soup joumou] is served properly, because we want you to see the pride in making this,” Raphael says. “It’s sweat and tears, and a lot of folks have died to ensure that we have the freedom we have today.”

Chef and restauranter Fred Raphael has distributed vegetarian and nonvegetarian bowls of soup joumou for free every year on New Year’s Eve for nearly a decade. Courtesy of Fred Raphael

Nadege Fleurimond, a Haitian chef, restaurateur and cookbook author, says soup joumou reminds her of the importance of cultural heritage, family and community. She recalls growing up in Brooklyn, New York, with her dad and helping him prepare the soup, but also visiting the homes of other family members and friends and trying their versions. “There’s a soup that’s made in your house, there’s also people bringing you soup because they’re visiting you, or you may be visiting someone,” says Fleurimond, who owns the Haitian restaurant BunNan in Brooklyn. “Soups vary from family to family, from household to household, but the thing I remember is how this was the day that everyone came together because you wanted to celebrate, you wanted to unite in this legacy of our ancestors.”

When it comes to preparation, Fleurimond says she used to think making soup joumou took days when she was a child. She remembers family members first chopping up vegetables and getting ingredients together. In hindsight, she realizes the process took so long because her family members were making large pots of soup, but also because the communal aspect of the dish went beyond just eating it together. She recalls her aunts in the kitchen talking about everything and nothing as they prepared the soup and cut vegetables. “It was an opportunity to congregate, so things took a little longer,” Fleurimond says. The actual cooking of the soup takes around two hours.

Once the soup is ready, it’s typically eaten as breakfast, lunch and dinner on New Year’s Day and consumed into the next day as part of Ancestors’ Day—a holiday honoring Haitian revolutionaries—on January 2. In the 219 years since Haiti declared independence, soup joumou has gone through various changes, with different families in different regions in Haiti and around the world putting their own twists on the dish. For example, modern versions of the soup typically include pasta like rigatoni or penne, but it’s unlikely that was a core part of the soup back in the 1800s, Raphael says.

With the rise of social media, Fleurimond says people can easily share recipes and pictures of their versions of soup joumou, and she’s seen many online debates about the “right way” to make it. Some people say soup joumou is only soup joumou if it has meat in it—though it’s said the original version was vegetarian. People also argue about the correct pasta and vegetables to put in the soup, but Fleurimond sees the debates as comical and ultimately arbitrary. “Yes, there’s a base, but at the end of the day, [soup joumou] does vary from region to region and household to household, because people use what ingredients they have access to,” she says. For both Fleurimond and Raphael (and myself), the soup is usually paired with a side of Haitian bread, a hard dough bread with a chewy texture, at family gatherings.

Soup joumou is traditionally eaten as breakfast, lunch and dinner on January 1. Richard Pierrin/AFP via Getty Images

Amid ongoing social and political crises in Haiti, Raphael says the message and history behind soup joumou matter more than ever. Historians and scholars have connected many of Haiti’s plights in the decades following its liberation, as well as difficulties faced in recent years, to the fact that foreign powers went on to exploit and drain the island’s wealth for generations after the country overthrew slavery and declared independence in 1804. After Haiti broke free from French rule, its government was forced to pay former French slaveholders and their descendants about $560 million in today’s dollars over the course of 64 years—money that would have amounted to $21 billion if it remained in Haiti’s economy.

Today, Haiti struggles with gang violence, a cholera outbreak, poverty, acute hunger and continued foreign intervention attempts opposed by many Haitians. As a result of these issues, many have immigrated, moved out of and fled the country in recent decades and years, with Haitians now spanning the globe. Fleurimond says soup joumou allows Haitians around the world to come together and celebrate a common history.

“We need a revolution in Haiti right now because of what’s going on in terms of political and social turmoil,” Fleurimond says. “Whenever January 1 comes around, [Haitians] have this one anchor point of the soup. It reminds us of what we’ve done, what’s still possible if we do come together and also that sense of community that’s authentically felt. It’s more than just the household that’s making the soup—it also spreads to everyone in your community making the soup, and every Haitian in the world that is making this soup. This just shows we really are one.”

Nadege Fleurimond’s Soup Joumou Recipe

Serving size: 10-12

Ingredients

For the epis seasoning

• 1/4 medium onion
• 1/2 green bell pepper, chopped
• 1/2 red bell pepper, chopped
• 1/2 yellow bell pepper, chopped
• 10 scallions, chopped
• 12 garlic cloves, chopped
• 1 cup parsley, chopped
• 2 sprigs of thyme (just the leaves)
• 1/4 cup olive or canola oil

For the soup

• 1 cup distilled white vinegar
• 1 whole lime, cut in half
• 2 pounds of beef (mixture of chuck, beef stew, soup bones)
• 2 tablespoons olive oil
• 1 cup epis seasoning
• 3 tablespoons fresh lime juice, divided
• 1 tablespoon seasoned salt
• 1 medium calabaza squash (about 2 pounds), peeled and cubed, or 2 pounds defrosted frozen cubed calabaza squash, or 1 butternut squash (about 2 pounds), peeled and cut into 2-inch chunks
• 2 large Idaho potatoes, cut into cubes
• 2 carrots (about 1 pound), sliced
• 1/4 small green cabbage (about 1/2 pound), very thinly sliced
• 1 medium onion, sliced
• 1 celery stalk, coarsely chopped
• 1 leek, white and pale-green parts only, finely chopped
• 2 small turnips, finely chopped
• 1 green Scotch bonnet or habanero
• 6 whole cloves
• 1 teaspoon garlic powder
• 1 teaspoon onion powder
• 2 teaspoons kosher salt
• 1/2 teaspoon freshly ground black pepper
• A pinch of cayenne pepper
• 1 parsley sprig
• 1 thyme sprig
• 1 cup rigatoni
• 1 tablespoon unsalted butter

Instructions

1. Start with making the Haitian epis seasoning by placing all ingredients in a blender or food processor and blending.
2. Pour 1 cup vinegar into a large bowl. Swish beef in vinegar. Squeeze lime halves and clean meat the Caribbean way by rubbing the meat with the insides of the lime pieces. Rinse with cold water, strain and set meat aside in large soup pot.
3. Add oil, 1 cup of epis seasoning, 2 tablespoons lime juice and seasoned salt to pot with meat. Toss to coat, then let marinade at least 30 minutes, preferably overnight.
4. After marinading, bring meat pot to medium heat and add in 2 cups of water. Cover and let simmer for 40 minutes, continuously adding water, until meat is soft.
5. In a separate pot, add squash with 4 cups of water. Cook on medium heat until squash is fork tender, about 25 minutes.
6. Once cooked, purée squash in a blender or food processor. Add to pot of cooked meat.
7. With the soup pot still on medium heat, add an additional 5 cups of water. Add potatoes, carrots, cabbage, onion, celery, leek, turnips, the green Scotch bonnet or habanero pepper, cloves, garlic powder, onion powder, salt, pepper, cayenne, parsley and thyme. Add water as necessary to ensure liquid is at least 2 inches above vegetables and meat.
8. Simmer, uncovered, continuing to add water as necessary until vegetables are tender, about 25-30 minutes.
9. Add in rigatoni, butter and remaining 1 tablespoon of lime juice. Simmer on low until pasta is cooked, about 10-12 minutes.
10. Taste and adjust seasonings as needed.

from Hacker News https://ift.tt/UWRhwjX

Writing an Interpreter in Go

NEW: Buy the eBook bundle and get two books!

This book now has a sequel in which we take the next step in Monkey's evolution. You can buy both books together to get:

• Writing An Interpreter In Go and Writing A Compiler In Go in one package for a reduced bundle price!
• Both books in ePub (iBook), Mobi (Kindle), PDF and HTML.
• The complete code presented in both books, including the Monkey interpreter from Writing An Interpreter In Go and the Monkey bytecode compiler and virtual machine from Writing A Compiler In Go.

Buy this book to learn:

• How to build an interpreter for a C-like programming language from scratch
• What a lexer, a parser and an Abstract Syntax Tree (AST) are and how to build your own
• What closures are and how and why they work
• What the Pratt parsing technique and a recursive descent parser is
• What others talk about when they talk about built-in data structures
• What REPL stands for and how to build one

Get a taste!

Why this book?

This is the book I wanted to have a year ago. This is the book I couldn't find. I wrote this book for you and me.

So why should you buy it? What's different about it, compared to other interpreter or compiler literature?

• Working code is the focus. Code is not just found in the appendix. Code is the main focus of this book.
• It's small! It has around 200 pages of which a great deal is readable, syntax-highlighted, working code.
• The code presented in the book is easy to understand, easy to extend, easy to maintain.
• No 3rd party libraries! You're not left wondering: "But how does tool X do that?" We won't use a tool X. We only use the Go standard library and write everything ourselves.
• Tests! The interpreter we build in the book is fully tested! Sometimes in TDD style, sometimes with the tests written after. You can easily run the tests to experiment with the interpreter and make changes.

"If you don’t know how compilers work, then you don’t know how computers work. If you’re not 100% sure whether you know how compilers work, then you don’t know how they work."

"Start by writing an interpreter with me!"

Thorsten Ball, author of the book you're looking at

This book is for you if you…

• learn by building and love to look under the hood
• love programming and to program for the sake of learning and joy!
• are interested in how your favorite, interpreted programming language works
• never took a compiler course in college
• want to get started with interpreters or compilers…
• … but don't want to work through a theory-heavy, 800 pages, 4 pounds compiler book as a beginner
• kept screaming "show me the code!" when reading about interpreters and compilers
• always wanted to say: "Holy shit, I built a programming language!"

The Monkey Programming Language

The official Monkey logo

In this book we'll create an interpreter for the Monkey programming language. Monkey is a language especially designed for this book. We will bring it to life by implementing its interpreter.

Monkey looks like this:

// Bind values to names with let-statements
let version = 1;
let name = "Monkey programming language";
let myArray = [1, 2, 3, 4, 5];
let coolBooleanLiteral = true;

// Use expressions to produce values
let awesomeValue = (10 / 2) * 5 + 30;
let arrayWithValues = [1 + 1, 2 * 2, 3];

Monkey also supports function literals and we can use them to bind a function to a name:

// Define a `fibonacci` function
let fibonacci = fn(x) {
  if (x == 0) {
    0                // Monkey supports implicit returning of values
  } else {
    if (x == 1) {
      return 1;      // ... and explicit return statements
    } else {
      fibonacci(x - 1) + fibonacci(x - 2); // Recursion! Yay!
    }
  }
};

The data types we're going to support in this book are booleans, strings, hashes, integers and arrays. We can combine them!

// Here is an array containing two hashes, that use strings as keys and integers
// and strings as values
let people = [{"name": "Anna", "age": 24}, {"name": "Bob", "age": 99}];

// Getting elements out of the data types is also supported.
// Here is how we can access array elements by using index expressions:
fibonacci(myArray[4]);
// => 5

// We can also access hash elements with index expressions:
let getName = fn(person) { person["name"]; };

// And here we access array elements and call a function with the element as
// argument:
getName(people[0]); // => "Anna"
getName(people[1]); // => "Bob"

That's not all though. Monkey has a few tricks up its sleeve. In Monkey functions are first-class citizens, they are treated like any other value. Thus we can use higher-order functions and pass functions around as values:

// Define the higher-order function `map`, that calls the given function `f`
// on each element in `arr` and returns an array of the produced values.
let map = fn(arr, f) {
  let iter = fn(arr, accumulated) {
    if (len(arr) == 0) {
      accumulated
    } else {
      iter(rest(arr), push(accumulated, f(first(arr))));
    }
  };

  iter(arr, []);
};

// Now let's take the `people` array and the `getName` function from above and
// use them with `map`.
map(people, getName); // => ["Anna", "Bob"]

And, of course, Monkey also supports closures:

// newGreeter returns a new function, that greets a `name` with the given
// `greeting`.
let newGreeter = fn(greeting) {
  // `puts` is a built-in function we add to the interpreter
  return fn(name) { puts(greeting + " " + name); }
};

// `hello` is a greeter function that says "Hello"
let hello = newGreeter("Hello");

// Calling it outputs the greeting:
hello("dear, future Reader!"); // => Hello dear, future Reader!

Yes! All of this works with the interpreter we build in the book!

So, to summarize: Monkey has a C-like syntax, supports variable bindings, prefix and infix operators, has first-class and higher-order functions, can handle closures with ease and has integers, booleans, arrays and hashes built-in.

Readers are saying...

"Compilers was the most surprisingly useful university course I ever took. Learning to write a parser and runtime for a toy language helps take away a lot of "magic" in various parts of computer science. I recommend any engineer who isn't familiar with lexers, parsers, and evaluators to read Thorsten's book."

Mitchell Hashimoto (@mitchellh)
Founder of HashiCorp

"Thorsten has a wonderful gift — as you read his books, you'll feel as though you've discovered compilers, all on your own! As a self-taught engineer without a CS degree, I found myself led on a journey of learning, driven by the nuggets of 'why' that Thorsten shares alongside code.

I've recommended these books to many, and I won't hesitate to continue. Compilers, interpreters and programming languages may seem esoteric, but if you look closely, they're everywhere. The Monkey language is my go-to project whenever I learn a new programming language. There's no better way to take a new language through its paces!"

Lauren Tan (@sugarpirate_)
Software Engineer

"Amazing book! Besides satisfying my curiosity with clear writing and code examples, the book inspired me to apply those skills to a new http testing tool I’m working on."

Felix Geisendörfer (@felixge)
Prolific Open Source Contributor, Creator of GoDrone, Node.js Core Alumni

"Great book. I loved it because everything is built by hand, so you get to think about all the details, and it does so in a gradual way, which is didactic. The implementation itself is also nice and simple 🙌"

Xavier Noria (@fxn)
Everlasting student, Rails Core Team, Ruby Hero, Freelance, Live lover

"I really enjoyed the modern, practical approach of this book. Diving into the world of interpreters, by getting your hands dirty right from the beginning."

Christian Bäuerlein (@fabrik42)
Developer, Organizer & Curator of MECHANICON

"This book demystifies and makes the topic of interpreters approachable and fun. Don't be surprised if you become a better Go programmer after working your way through it."

Johnny Boursiquot (@jboursiquot)
Go Programmer, @BaltimoreGolang Organizer, @GolangBridge Core Member

"Great writing and explanations. The practical focus of the book kept me coding for a week straight. Definitely the best book to get into the magical world of compilers and interpreters."

Arthur Tabatchnic (LinkedIn)
Senior Cloud Solutions Developer

"We use parsers and interpreters on a daily basis, just think of JavaScript and JSON. This book not only helped me to better understand how they work but will come in handy the next time I have to implement a parser for an obscure data format."

Robin Mehner (@rmehner)
Developer, Organizer of BerlinJS, Reject.JS & NodeCopter.

"This book clearly, and elegantly explains the different pieces needed to make a language. From lexing and parsing to actually executing the code, this book does a great job explaining to the reader the purpose of each element and how they interact."

Lee Keitel (lfkeitel)
Systems Programmer & Network Technician

"I loved this book and it remains one of my favorite #golang books to this day."

Brian Downs (@bdowns328)
Software Engineer & Organizer of Golang Phoenix

"I only wish this book was available ten years ago! At the time I was using Appel's Java book and trying wade through the dragon book too. It's so refreshing to have a TDD-based tutorial to learn the concepts in a language you might reasonably use to build an interpreter."

Robert Gravina (@robertgravina)
Programmer

"It has been one of the funnest experiences in my programming career. I recommended your books to all my friends in the industry. I recently finished 'Writing An Interpreter In Go' and yesterday I purchased 'Writing A Compiler In Go'. Once again thank you! I was blind and now I can see, thanks to you!"

Rodrigo Lessa

"This book is not only educational, but the code quality is incredible, which allows the reader to move seamlessly from chapter to chapter without the need to scratch their head over what the code means or how it works. It is cleanly separated, well optimized, highly readable, and very precise in its functionality. Because of this, it provides an excellent example for both novices and veterans of the Go programming language, and will serve readers beyond a purely intellectual understanding of programming language design and functionality; the code used in the book will also provide a solid foundation in Go programming that can be practically applied right away. This balance is tough to achieve, and made the book a joy to read."

Aaron Hnatiw (@TheHackerDev)
Hacker, educator, software developer

"I was completely hooked by your book on writing an interpreter and read it in 3 days. It might be the best book on programming I've ever read, and I read a lot of them. I love how all of the concepts are explained simply through very readable code and I love how the product turned out so real and useful. I wish more books were written in this style and I look forward to diving into the sequel!"

Ludvig Gislason (@ludviggislason)
Software Engineer

"Thorsten took a topic that is usually very dry and CS heavy and made it accessible and easy to understand. After reading this book I felt confident enough to write Plush, the templating language I’ve always wanted in Go! If you have yet to read Thorsten's book, I can't recommend it enough. Please go and buy it!"

Mark Bates (@markbates)
Creator of gobuffalo.io

"The best thing to do on a maternity leave when the baby is sleeping? Write an interpreter based on Thorsten Ball's book Writing An Interpreter In Go. I had so much fun! Now off to writing a compiler!"

Alena Varkockova (@alenkacz)
Distributed systems enthusiast, working at Mesosphere

"Thorsten's writing style is fun and easy to understand with detailed explanations and even better code. Even if you've written an interpreter before, this book is a great refresher. I picked it up as a project to learn Rust, translating the example Go code into Rust as I read through. Lexers, parsers, and interpreters are such a fundamental part of CS, these skills are valuable to more than just programmers implementing programming languages. As a project for picking up a new language, this book is perfect because it only requires the standard library and has extensive test driven development, which means you get quick feedback as you go along. I highly recommend it for programmers wanting to learn more about lexers, parsers, and interpreters or Go programmers picking up a new language looking for a project to learn through."

Paul Dix (@pauldix)
CTO of InfluxDB

"This is a very polished pair of books that together give a steady path to follow for learning some of the real techniques that are used to implement programming languages. They're both well above average for their depth, technical clarity, and accessibility. I've been recommending them to everyone I work with who wants to get involved in compilers."

Chris Seaton (@ChrisGSeaton)
Researcher at Shopify, PhD in Ruby, Founder of Truffle Ruby

"It's been the most fun I've had in years."

Danny van Kooten

Buy the eBook and you will get:

• The complete book in ePub (iBook), Mobi (Kindle), PDF and HTML.
• The complete, working interpreter for the Monkey programming language!
• All the code presented in the book, easily usable, organized by chapters, MIT licensed and including the complete test suite.
• Free updates: Once you buy the book you will get free updates for the lifetime of that edition of the book.
• Money-Back-Guarantee: I want you to enjoy this book. If you, for any reason, are not happy with it just send me an email. You'll keep what you bought and your money back.

Buy the paperback and you will get:

• The physical 260 pages paperback book
• The complete, working interpreter for the Monkey programming language!
• All the code presented in the book, easily usable, organized by chapters, MIT licensed and including the complete test suite.
• Amazon Support: the book is distributed through Amazon and you get to benefit by all the money-back-guarantees and shipping Amazon offers.

NEW: Buy the eBook bundle and get two books!

This book now has a sequel in which we take the next step in Monkey's evolution. You can buy both books together to get:

• Writing An Interpreter In Go and Writing A Compiler In Go in one package for a reduced bundle price!
• Both books in ePub (iBook), Mobi (Kindle), PDF and HTML.
• The complete code presented in both books, including the Monkey interpreter from Writing An Interpreter In Go and the Monkey bytecode compiler and virtual machine from Writing A Compiler In Go.

FAQ

• Do I need previous experience with interpreters or compilers?

  Absolutely not! On the contrary, this book was written for you!

• Can I read the book even though I'm not a Go programmer?

  Yes! I wrote the book with the aim to keep the code as easy to understand as possible. If you are experienced in other programming languages you should be able to understand it. Take a look at the free excerpt - that's as advanced as the Go code gets.

• Can I buy a bundle of the eBook and the paperback?

  I'm sorry to say it, but no, I cannot bundle eBooks with paperbacks. It's not that I don't want to (I do!) but I can't. The eBooks are sold and distributed through Gumroad, where I have a lot of influence on the process, but the paperback editions are being printed, sold and shipped by Amazon and I don't have many options there. I can't tell Amazon to bundle digital files with the paperback. Sorry!

• I found a typo/mistake/error in the book. What now?

  Take a look at the changelog to see whether I've already fixed it. If I haven't or you're not sure I have, please send me an email to me @ thorstenball.com — I really appreciate it!

• Why isn't the book called "Writing An Interpreter In Golang"? Wouldn't that be better for SEO?

  Well, I always thought I could use the "Golang" somewhere on the landingpage, maybe in the FAQ or something.

• The books are too expensive for me. Can you help me out?

  Sure, just send me a picture! I'm always fascinated by new places and love seeing where people live, so here's my proposal.

  You go outside, take a picture of where you live and send it to me to me @ thorstenball.com. Tell me what you feel comfortable paying for the book(s) and we'll make that happen.

The Lost Chapter: A Macro System For Monkey

More than half a year after publishing Writing An Interpreter In Go I decided to write another chapter. An additional chapter that's available to everyone: free to read online or to download as an eBook.

It's called The Lost Chapter: A Macro System For Monkey and can be thought of as the fifth chapter for Writing An Interpreter In Go. It builds directly upon the previous four and extends the Monkey interpreter as it stands at the end of the book.

In the chapter we add a fully-working Lisp-style macro system to Monkey, that's close to the way Elixir's macro system works. It looks like this:

let unless = macro(condition, consequence, alternative) {
  quote(if (!(unquote(condition))) {
    unquote(consequence);
  } else {
    unquote(alternative);
  });
};

unless(10 > 5, puts("not greater"), puts("greater"));
// outputs only: "greater"

Building your own programming language is likely not something you do in your day job. But adding a fully working macro system? Well, that's not just unlikely, but outright bizarre and, oh, so much fun! Macros are code that writes code. Can you imagine how much fun it is to write code that allows us to write code that writes code? Exactly!

About the author

Hi, my name is Thorsten Ball. I'm a programmer living in Germany. My whole professional life as a software developer I've been working with web technologies and have deployed Ruby, JavaScript, Go and even C code to production systems.

Maybe you've read one of my blog posts. Some of them are pretty popular. There's the one about the Ruby Garbage Collector. And the one about the fork system call. If you haven't read one of them, then maybe the one about forking processes in a multi-threaded environment.

I also give talks about Unix software and other topics. And I turned one talk into a blog post which got super popular and remains my favorite.

Writing an interpreter from scratch in Go has been one of the most enjoyable and fun things I ever did as a programmer. So I hope you enjoy this book as much as I enjoyed writing it.

If you want to know more about me, you can also visit my blog and website, check out my GitHub profile or even better: follow @thorstenball on Twitter.

Any questions?

If you have any questions, send me an email. I promise, you'll make my day. I love getting email from you: me @ thorstenball.com

from Hacker News https://ift.tt/T9WfB1p

Dirty USB-C Tricks: One Port for the Price of Two

[RichardG] has noticed a weird discrepancy – his Ryzen mainboard ought to have had fourteen USB3 ports, but somehow, only exposed thirteen of them. Unlike other mainboards in this lineup, it also happens to have a USB-C port among these thirteen ports. These two things wouldn’t be related in any way, would they? Turns out, they are, and [RichardG] shows us a dirty USB-C trick that manufacturers pull on us for an unknown reason.

On a USB-C port using USB3, the USB3 TX and RX signals have to be routed to two different pin groups, depending on the plugged-in cable orientation. In a proper design, you would have a multiplexer chip detecting cable orientation, and routing the pins to one or the other. Turns out, quite a few manufacturers are choosing to wire up two separate ports to the USB-C connector instead.

In the extensive writeup on this problem, [Richard] explains how the USB-C port ought to be wired, how it’s wired instead, shows telltale signs of such a trick, and how to check if a USB-C port on your PC is miswired in the same way. He also ponders on whether this is compliant with the USB-C specification, but can’t quite find an answer. There’s a surprising amount of products and adapters doing this exact thing, too, all of them desktop PC accessories – perhaps, you bought a device with such a USB-C port and don’t know it.

As a conclusion, he debates making an adapter to break the stolen USB3 port out. This wouldn’t be the first time we’re cheated when it comes to USB ports – the USB2 devices with blue connectors come to mind.

from Hacker News https://ift.tt/AFxnphs

I made a home security system, powered by a Raspberry Pi 3

In November last year — I started building a DIY security alarm system, using a Raspberry Pi as the controller. My plan was to make a self-sustained system, using proper alarm hardware — like PIR sensors and sirens.

Integration with Home Assistant would be an add-on, not a requirement. I wanted the system to be as redundant and fault-tolerant as I could make it.

This is a pretty long story, with some twists and turns — let’s get into it 👇

Introduction

This is one blog post I’ve been putting of for a while — there is just so much to tell, and the system is still a work-in-progress. I will be splitting it up into multiple posts; starting at the beginning 🙂

Why?

I’ve wanted a security alarm system for a while now, but I loathe the companies providing it here in Norway. Yes Verisure and Sector Alarm — I’m talking about you guys.

They charge an absurdly high monthly fee, and seems dedicated to get as much money from you as they possibly can. While offering no integrations with things like Home Assistant — they want you vendor locked to their system.

To add insult to injury; they both got fined last year for cooperation that restricts competition 😞 Combined they got fined 1.233 million NOK.

Making my own alarm system seemed like a cool challenge, that would eventually result in something useful 🙂

The plan

Like I wrote; my plan changed multiple times. But the overall goal stayed the same, let’s go through that now.

I wanted to use a controller I could SSH into and easily make changes and adjustments. Enter the Raspberry Pi — it has a generous amount of GPIO pins, and libraries to make them easily accessible from a Python script.

I started with a Raspberry Pi Zero W, but later changed to a Raspberry Pi 3+. I needed something a bit more powerful, and with wired Ethernet — I didn’t want to rely on Wi-Fi, especially inside a grounded metal cabinet 😛

Raspberry Pi Zero W and self-built interface circuit board

Next I wanted the ability to use any kind of sensor and hardware — wired and wireless. Wired things would connect to the Raspberry Pi GPIO pins, and wireless through Zigbee using Zigbee2MQTT as I described in the previous post in this series.

I’m a firm believer that everything that can be hardwired — should be hardwired, but for some things, like door sensors, it’s just not practical.

Zigbee CC2652P coordinator v4 USB stick

For the enclosure I started with a 200×120×75mm plastic box, but in time I realized that it wasn’t going to be big enough. Then I ordered a 400×300×150 mm metal cabinet — but soon after; I decided that the system needed a backup battery, and I needed an ever bigger enclosure.

Small plastic enclosure — with DIN rail, terminal blocks, and cable glands

So I ordered another metal cabinet, measuring 500×300×210 mm — and this is the one I ended up using 🙂

Big metal cabinet, ELDON MAS0503021R5, with DIN rails

Later on in the project I started worrying about the Python script dying while the sirens were turned on — which would have been a disaster. So I decided to include an Arduino microcontroller as a monitoring and fail-safe solution, but I think that story is better suited for a later blog post in this series.

Arduino Nano on self-built interface circuit board

Raspberry Pi

As the main controller; I’m using a Raspberry Pi 3+ — it has a decent CPU and 1 GB RAM, together with an Ethernet and USB ports.

Raspberry Pi 3+ and self-built interface circuit board

On the Raspberry Pi I:

• Added a new user, and deleted the default user pi
• Installed all updates and set the time zone
• Install Supervisor, Vim, and Git
• Installed Python libraries for MQTT and serial communication
• Added my user to the gpio and dialout groups
• Made a configuration file for Supervisor to make sure the alarm script was always running

$ sudo userdel -r pi
$ sudo apt update && sudo apt upgrade
$ sudo dpkg-reconfigure tzdata
$ sudo apt install supervisor vim git

$ sudo apt install python3-paho-mqtt python3-serial
$ sudo adduser hebron gpio dialout

$ sudo cp supervisor/alarm.conf /etc/supervisor/conf.d/
$ sudo systemctl restart supervisor.service

My Supervisor program definition looks like this:

[program:alarm]
command=python3 alarm.py
directory=/home/hebron/rpi-alarm
autostart=true
autorestart=unexpected
user=hebron
stderr_logfile=syslog
stdout_logfile=syslog

Electronics

I made a circuit board to step down 12 volts to 5 — for the Raspberry Pi, and handle all the inputs and outputs. 7 outputs and 8 inputs, which I later expanded to 8 outputs and 13 inputs 🙂

Raspberry Pi Zero W and self-built interface circuit board

The board has red and green LEDs, showing the status of the system:

• Slow flashing green: everything OK, unarmed
• Fast flashing green: everything OK, armed
• Flashing red: there is a problem
• Steady or no light: dead/script not running 💀

Pull up resistors and status LEDs on interface circuit board

On the interface board is:

• Darlington-driver for outputs
• Pull up resistors for inputs (330 Ω and 10 kΩ)
• Voltage step-down buck converter, with a diode for reverse polarity protection

The buzzer didn’t play nice with the Darlington-driver I’m using, it caused leak current on other outputs. I used a NPN transistor for it instead, which solved that problem.

The two boards are connected with Dupoint jumper wires. I had to use 18 AWG wire for the power — to prevent the voltage dropping below 5 V. Both cards are mounted to a styrene plastic sheet using brass stand-offs. On the backside is a DIN rail mounting bracket.

Dupoint jumper wires between Raspberry Pi and interface board

Here are the parts I used for the Raspberry Pi, and power and interface board:

• 1 × Darlington-driver, 7 step, ULN2003A, DIL16, In: 2.7K/5V
• 1 × DIL socket, 16-pin, 7.62mm
• 1 × Diode, rectifier, 1 A, 400V, 1N4004
• 20 × Jump/dupont wire, female-female, 20cm, 2.54mm
• 1 × LED 3mm, Green, 1.9-2.1V, 20mA, 10000-12000mcd
• 1 × LED 3mm, Red, 1.9-2.1V, 20mA, 3000-4000mcd
• 1 × microSDHC card, Samsung EVO, 32GB, UHS-1, 100MB/s
• 2 × Mounting bracket, DIN rail, Plastic
• 1 × PCB, stripboard prototyping, 94x53mm, 50cm2
• 1 × Raspberry Pi 3 Model B, 1.2GHz Quad 64bit, 1GB RAM, BT, WLAN
• 15 × Resistor, carbon film, 0.25W, 330 Ω, 5%
• 1 × Resistor, carbon film, 0.25W, 4.7 kΩ, 5%
• 13 × Resistor, carbon film, 0.25W, 10 kΩ, 5%
• 8 × Stand-off PCB, M3, male-female, 10+6 mm, brass
• 12 × Straight pin header, female, Single row, 2.54mm
• 1 × Straight pin header, male, Dual row, 2.54mm
• 55 × Straight pin header, male, Single row, 2.54mm
• 125 cm2 Styrene plastic sheet, 3.2 mm
• 1 × Transistor, NPN, 100 mA, 45V, 0.5W, BC547B
• 1 × Voltage step-down buck converter, In: 7-28V, Out: 5V/3A
• 0.3 m Wire, stranded, 2-cores, 0.75mm2, Red/Black

Below is an early test of the Python script on the Raspberry Pi Zero W, and Home Assistant integration:

Enclosure

I’m delighted that I decided to get a large enclosure. There are more space efficient ways of doing it — but I like to have some room to work 🙂

The terminal blocks take up a fair bit of DIN-rail length, but having the wire connections clear and easy is definitely worth it.

I mounted three rows of DIN-rail:

1. Sensor terminal blocks, Raspberry Pi, and interface board
2. Output terminal blocks, auxiliary I/O, supporting controllers, and relays
3. 230V, fuses, and power distribution

One strip-board on the 2nd row has been left unpopulated — the initial plan was to use an AVR microcontroller board for all the tamper circuits. But I’ve put that off for now.

Components on mounting plate for metal cabinet

The cabinet has both a mounting and gland plate. A mounting plate is the removable plate that everything is mounted to — this is a huge advantage when doing the initial install.

The gland plate is a removable plate to drill holes for cable glands — being removable makes it a lot easier, as you can use a drill press. I’ve mounted my cabinet with the gland plate on top, since that’s where the cables will enter the cabinet.

Inside metal cabinet, ELDON MAS0503021R5

The 230 V power outlet is for a CTEK battery charger that will charge the backup battery. I’ve left enough space available to fit one, or two, 7.2Ah lead-acid batteries. I’ll write more about that later in the blog post series.

Excuse the current rat’s nest of cables, I’m still in the installation phase — or so I tell myself.

Operation

There is just too much going on to explain it all in one sitting, so I’ll cover more of the functionality in future blog posts. For now; I’ll briefly explain the core security alarm functions — armed home, and away — and a few supporting functions.

Some glorious blinkenlights

Features

There is a rough overview of the currently implemented features:

• Supports both hardwired and MQTT sensors and outputs
• Support for multiple MQTT alarm panels, with set states †
• Home Assistant integration with auto discovery
• Push messages using Pushover
• Heartbeat monitoring using Healthchecks
• Fail-safe and monitoring with an Arduino †
• Personal PIN-codes
• Configurable zones with delay and direct trigger
• Home and away arm mode, with configurable zones
• Water sensor support †
• Panic and emergency alarms
• Zone timers, to use as triggers in Home Assistant automations
• Front door open warning
• Several system checks
• Lead-acid battery backup, with charger †

† : Will be further explained in future blog posts.

Arming and disarming

The system supports multiple MQTT alarm panels — as well as the Home Assistant MQTT alarm control panel.

The panels have no knowledge of PIN codes — they only send what has been entered, along with the chosen action. The system checks if the received PIN code belongs to a user — if so, the action is valid.

If the wrong code is entered multiple times, a system fault is set — notifying the system owner.

Armed home

When the system is armed home; only the peripheral zones, such as doors and windows, are armed. The alarm will trigger immediately if any zones are opened, bypassing the entry delay.

Armed away

Both entry and exit delays are used when the system is armed away. All zones are monitored, but some have delay enabled.

If a zone with delay is activated the system will enter pending mode, aka entry delay, giving the user time to input the PIN code and disarm the system.

If a zone without delay is activated — the alarm will directly trigger, even when in pending mode.

Triggered mode

Triggered mode means the alarm has been triggered — the indoor siren start immediately, while the outdoor siren starts when ⅓ (one third) of the configured trigger time has passed.

So if the trigger time is 60 seconds, the outdoor siren will be delayed by 20 seconds.

Panic and emergency

If supported by the keypad; the system can also enter triggered mode on panic or emergency.

Panic will trigger the regular alarm, with sirens — while emergency only gives a short signal to confirm, but alerts by push message as usual.

Zone timers

Zone timers can be configured with one, or many zones, and a timeout value in seconds. They simplify the process of setting up automations in Home Assistant based on multiple zones, such as motion activated lights — based on several motion sensors.

A zone timer can be cancelled manually.

Door open notification

If the front door is left open — a short buzzer signal will sound, as a reminder to close the door. The buzzer signal will gradually increase in intensity if the door is left open.

System checks

The system keeps track of several statuses, and a notification is sent if a problem is detected.

The following conditions are monitored:

• Backup battery voltage
• Cabinet temperature
• Number of failed PIN code attempts
• System and zone tamper
• Heartbeat ping
• Mains power present
• MQTT connected
• Sensors are alive
• Sensor link quality and battery
• Siren relay works
• Zigbee bridge available

Healthchecks is used to externally monitor the system, and will notify if it stops receiving heartbeat pings.

Sensors, sirens, and keypad

I consider all wireless communication to be varying degrees of unreliable — wired is always better than wireless. The wired sensors and devices are very reliable, have a battery backup, and no external dependencies.

While the wireless Zigbee sensors can have communication problems — and relies on a Docker container running Zigbee2MQTT, a MQTT broker, and network communication between the mentioned hosts and the Raspberry Pi. Backup power is provided by a UPS, but the run-time is limited.

I’ve tried to design the system in such a way that the core functionality will be operational — despite all external systems being offline. Even operate for days on battery backup.

Wireless hardware

I’m currently using a Climax KP-23EL-ZBS-ACE keypad, located in the entryway, to arm and disarm the system. It works — but doesn’t support set states. Meaning that the panel doesn’t give any feedback of the current alarm state.

The user isn’t left completely in the dark though, as the buzzer is used to signal alarm state changes. But I’m planning to explore other alarm panels in the future 🙂

Climax KP-23EL-ZBS-ACE keypad

For door and window sensors; I’m using Aqara MCCGQ11LM. It’s small, the battery lasts a long time, and they seem very reliable.

Ideally I’d like wired door sensors, but I don’t see that happening… It’s just not practical to retrofit those with the wires hidden.

Aqara MCCGQ11LM door/window sensor

I also have a couple of Aqara RTCGQ11LM motion sensors — but those I use mainly as temporary sensors until I can get proper wired PIR sensors installed 😎

Aqara RTCGQ11LM motion sensor

I already had a Philips Hue indoor motion sensor in the entryway — to control the lights, so I figured I might as well include it in the alarm system.

Since my Hue devices are on a separate Zigbee network — I’m relying on the Home Assistant MQTT statestream to forward messages.

Philips Hue indoor motion sensor

Wired hardware

Indoor and outdoor sirens are wired, although I haven’t installed the outdoor siren yet — that’s a job for warmer weather 🙂

Testing wired sirens — Vanderbilt SP203 and SIR1992-V-LP

I’ve installed a buzzer in the entryway — utilizing an empty wall box. It’s used to signal entry and exit delay, alarm state changes, door open warnings, and more.

Entryway buzzer wired up

The buzzer is mounted to a wall box blanking plate — making it flush mounted buzzer, and almost invisible 🙂

Entryway buzzer, flush mounted

A 10-cores 0.34mm² (22 AWG) signal cable in a conduit — going into a junction box close to some wired devices. The junction box has a tamper switch — detecting if the lid is removed.

Junction box OBO T60, with custom added tamper switch

Indoor siren SP203 from Vanderbilt, and Bosch Blue Line Gen2 PIR motion sensor in the 1st floor hallway. I don’t think cables should be visible, so I go to great lengths to hide them. Hidden cables also means they are harder to tamper with 🙂

Vanderbilt SP203 indoor siren and Bosch ISC-BPR2-W12 PIR motion sensor

Software

The alarm application/script is written in Python — I like Python, and there are lots of libraries available for the Raspberry Pi GPIO pins, MQTT, serial port, and more.

Currently — the code is very poorly documented… It may, or may not, improve in the future — use at your own risk 😛

Python script

Configuration

Simple things like MQTT hostname, API tokens, user PIN codes, times, etc — are defined in config.ini. The alarm state is saved when changed, meaning that the system will enter the same state if the application restarts.

More complex configuration is defined as Python objects directly in the application — things like inputs, outputs, sensors, Home Assistant entities, zone timers, and alarm panels.

Inputs are digital GPIO pins, while sensors come from MQTT messages — combined they are zones.

Some things are just plain hard-coded 😛

MQTT payload

The alarm system sends a single state object — which contains all states. It’s sent when any of the values changes, but at least once every 10 seconds.

{
    "arm_not_ready": false,
    "battery_chrg": false,
    "battery_level": 100,
    "battery_low": false,
    "battery_voltage": 12.74,
    "clear": true,
    "code_attempts": 0,
    "config": {
        "walk_test": false
    },
    "fault": false,
    "mains_power_ok": true,
    "state": "disarmed",
    "tamper": false,
    "temperature": 21.1,
    "triggered": {
        "timestamp": null,
        "zone": null
    },
    "zigbee_bridge": true,
    "zone_timers": {
        "hallway_motion": true,
        "kitchen_motion": true
    },
    "zones": {
        "door1": false,
        "door2": false,
        "door3": false,
        "emergency": false,
        "ext_tamper": false,
        "motion1": false,
        "motion2": false,
        "motion3": false,
        "panel_tamper": false,
        "panic": false,
        "water_leak1": false,
        "zone01": false
    }
}

MQTT broker

The Raspberry Pi runs it’s own Mosquitto MQTT broker, which the Python application connects to. This is to minimize the dependence on external services.

My main MQTT broker connects to the Raspberry Pi as a bridge — receiving all messages, and passing on what is relevant for the alarm system.

connection rpi-alarm
address 192.168.1.190:1883

topic # in 0
topic zigbee2mqtt/# out 0
topic home/alarm_test/# out 0
topic homelab/src_status out 0

Home Assistant

No configuration is required in Home Assistant; the alarm system will automatically be created as a device, using MQTT discovery.

All zones, zones timers, and alarm panels are automatically published — but any property included in the state object can be published as well, by defining it as an entity.

MQTT device in Home Assistant

This is my security dashboard — it gives an overview of all sensors and features, as well as some system diagnostics.

Security dashboard in Home Assistant

Ending thoughts

I’m delighted to finally have this blog post done — it has felt insurmountable. The project has been going on for more than a year, and I’m still adding features and making adjustments.

There’s a lot happening and much of the logic is fairly complex and difficult to explain in detail — I feel like I have barely scratched the surface in this post.

I will be writing more in-depth blog posts in the future, as well as keep you updated as the project progresses 👷‍♂️

This project is incredibly rewarding, and provides useful features in our daily lives 🙂 I’m currently planning a big feature upgrade for water sensors and actions related to that. More on that later.

🖖

from Hacker News https://ift.tt/5NyRbAV

Materialized View: SQL Queries on Steroids

I have been working with a client with close to 600k images on their home page. The photos are tagged with multiple categories. The index API returns paginated pictures based on various sets of filters on classes.

Recently, they normalized their data, and every image was mapped to 4 different categories through join tables. So to load the home page, the app server used to join 8 tables on runtime and return the results.

Below is the query that filters the images tagged ABSTRACT for a page and limit results to 10.

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT DISTINCT ON (images.id) external_uuid FROM images JOIN images_chapters qc ON images.id = qc.image_id JOIN chapters c ON c.id = qc.chapter_id JOIN images_sections qs ON images.id = qs.image_id JOIN sections s ON s.id = qs.section_id JOIN images_subjects qs2 ON images.id = qs2.image_id JOIN subjects s2 ON s2.id = qs2.subject_id JOIN images_topics qt ON images.id = qt.image_id JOIN topics t ON t.id = qt.topic_id WHERE s.name = 'ABSTRACT' ORDER BY images.id OFFSET <offset_page> LIMIT 10

The count on the actual category tables is meagre <5k rows per table. The join tables have mostly 1(images):1(categories) mapping. Meaning every image has at least been tagged into 4 categories. If our filter predicate results in 100k images, we are essentially joining 5 tables ( images + 4 classes) of 100k each.

Let’s break down the EXPLAIN statement

Below is the result of EXPLAIN ANALYZE that filters the images tagged ABSTRACT for a specific page, sorts by images.id, and returns the first 10 results.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

Limit (cost=2256.12..2256.17 rows=10 width=45) (actual time=939.317..939.329 rows=10 loops=1) -> Unique (cost=2255.87..2257.11 rows=248 width=45) (actual time=939.292..939.324 rows=60 loops=1) -> Sort (cost=2255.87..2256.49 rows=248 width=45) (actual time=939.291..939.308 rows=64 loops=1) Sort Key: images.id Sort Method: external merge Disk: 3648kB -> Hash Join (cost=181.85..2246.01 rows=248 width=45) (actual time=3.048..905.152 rows=64082 loops=1) -> Nested Loop (cost=136.55..2200.06 rows=248 width=53) (actual time=2.730..887.310 rows=64082 loops=1) -> Nested Loop (cost=136.13..2084.93 rows=236 width=69) (actual time=2.713..704.197 rows=59960 loops=1) -> Nested Loop (cost=135.98..2045.35 rows=236 width=77) (actual time=2.701..606.176 rows=59960 loops=1) -> Nested Loop (cost=135.56..1930.55 rows=236 width=61) (actual time=2.683..432.067 rows=59960 loops=1) -> Hash Join (cost=135.14..1762.99 rows=237 width=16) (actual time=2.666..218.527 rows=59960 loops=1) -> Nested Loop (cost=129.53..1756.48 rows=334 width=24) (actual time=2.609..202.749 rows=59960 loops=1) -> Nested Loop (cost=129.12..1595.68 rows=329 width=8) (actual time=2.589..25.415 rows=59336 loops=1) -> Index Scan using index_sections_on_name on sections s (cost=0.15..8.17 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) Index Cond: ((name)::text = 'ABSTRACT'::text) -> Bitmap Heap Scan on images_sections qs (cost=128.97..1519.71 rows=6780 width=16) (actual time=2.571..16.893 rows=59336 loops=1) Recheck Cond: (section_id = s.id) Heap Blocks: exact=1300 -> Bitmap Index Scan on index_images_sections_on_section_id (cost=0.00..127.27 rows=6780 width=0) (actual time=2.442..2.442 rows=59568 loops=1) Index Cond: (section_id = s.id) -> Index Scan using index_images_chapters_on_image_id on images_chapters qc (cost=0.42..0.48 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=59336) Index Cond: (image_id = qs.image_id) -> Hash (cost=4.16..4.16 rows=116 width=8) (actual time=0.050..0.050 rows=171 loops=1) -> Seq Scan on chapters c (cost=0.00..4.16 rows=116 width=8) (actual time=0.005..0.025 rows=171 loops=1) -> Index Scan using images_pkey on images images (cost=0.42..0.71 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=59960) Index Cond: (id = qc.image_id) -> Index Scan using index_images_subjects_on_image_id on images_subjects qs2 (cost=0.42..0.48 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=59960) Index Cond: (image_id = images.id) -> Index Only Scan using subjects_pkey on subjects s2 (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=59960) Index Cond: (id = qs2.subject_id) Heap Fetches: 59960 -> Index Scan using index_images_topics_on_image_id on images_topics qt (cost=0.42..0.48 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=59960) Index Cond: (image_id = images.id) -> Hash (cost=32.91..32.91 rows=991 width=8) (actual time=0.311..0.311 rows=1035 loops=1) -> Seq Scan on topics t (cost=0.00..32.91 rows=991 width=8) (actual time=0.007..0.155 rows=1035 loops=1) Planning time: 30.718 ms Execution time: 941.265 ms

Table Stats	Node Stats

The query took 900 ms to execute, which resulted in a terrible user experience since users would have to wait for the home page to load. It also stresses the database instance when the traffic is higher.

There are 8 joins, of which 6 are nested loop joins and 2 are hash joins. Almost all of the joins result in an index scan. There are few sequential scans since the number of rows in the table is so few that the query planner decided to go with it. Sequential scans can be ignored in this scenario since they are not contributing to latency.

Looking at the per node and table stats, the primary contributor to the latency is nested loop joins and the index scans, which contribute about 85% of the overall latency.

The nested loops join is the most fundamental join algorithm. It works like using two nested queries: the outer or driving query to fetch the results from one table and a second query for each row from the driving query to fetch the corresponding data from the other table.

The hash join algorithm aims for the weak spot of the nested loops join: The many B-tree traversals when executing the inner query. Instead, it loads the candidate records from one side of the join into a hash table that can be probed quickly for each row from the other side.

Optimizations

1. Joins

Joins take more work to optimize. The hash joins perform better than nested loop joins when the driving and outer tables are larger. But we do not have control over when the query optimizer uses hash join.

But there are a few optimizations that we can apply on joins as well.

1. Reduce the number of columns selected in join, which results in less heap seek.
2. Join the tables on indexed columns to avoid full table scans.
3. Appropriate filter predicates so that the query optimizer works with fewer rows.

2. In memory sort

The final results are sorted by images.id using external merge Disk. If we set the work_mem to a more significant value, we could make the query optimizer use in-memory quick sort. But looking at the per node stats, the overall latency for sort is just 30 ms which is negligible compared to overall query execution. So we avoided this optimization

3. Join tables based on filter predicate

If we look at our original query, whatever may be the filter predicate, we are joining all four classes through the mapping tables. The query is sub-optimal and not required. If a user wants to filter ABSTRACT images, we could get away with just joining sections via the images_sections table since we are interested in images.external_uuid. This approach would significantly reduce the execution time since we only join fewer tables.

So the app service should decide which tables to join based on the filter predicate.

1 2 3 4 5 6 7 8

EXPLAIN ANALYSE SELECT DISTINCT ON (images.id) external_uuid FROM images JOIN images_sections qs ON images.id = qs.image_id JOIN sections s ON s.id = qs.section_id WHERE s.name = 'ABSTRACT' ORDER BY images.id OFFSET 50 LIMIT 10

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Limit (cost=1842.98..1843.03 rows=10 width=45) (actual time=238.740..238.748 rows=10 loops=1) -> Unique (cost=1842.73..1844.37 rows=329 width=45) (actual time=238.713..238.743 rows=60 loops=1) -> Sort (cost=1842.73..1843.55 rows=329 width=45) (actual time=238.711..238.726 rows=60 loops=1) Sort Key: images.id Sort Method: external merge Disk: 3376kB -> Nested Loop (cost=129.53..1828.97 rows=329 width=45) (actual time=2.609..210.119 rows=59336 loops=1) -> Nested Loop (cost=129.12..1595.68 rows=329 width=8) (actual time=2.592..22.494 rows=59336 loops=1) -> Index Scan using index_sections_on_name on sections s (cost=0.15..8.17 rows=1 width=8) (actual time=0.011..0.014 rows=1 loops=1) Index Cond: ((name)::text = 'ABSTRACT'::text) -> Bitmap Heap Scan on images_sections qs (cost=128.97..1519.71 rows=6780 width=16) (actual time=2.578..15.377 rows=59336 loops=1) Recheck Cond: (section_id = s.id) Heap Blocks: exact=1300 -> Bitmap Index Scan on index_images_sections_on_section_id (cost=0.00..127.27 rows=6780 width=0) (actual time=2.445..2.446 rows=59568 loops=1) Index Cond: (section_id = s.id) -> Index Scan using images_pkey on images images (cost=0.42..0.71 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=59336) Index Cond: (id = qs.image_id) Planning time: 0.442 ms Execution time: 240.182 ms

What would happen when a user filters all the categories?

We would be joining all eight tables since the query has many filter predicates. The number of rows is vastly reduced in every join, increasing the query performance.

Although pt.3 reduced the query execution times, more is needed. Even the optimised query can quickly become a bottleneck for an application serving at 20k at peak RPM.

Usage patterns

Even after a few optimizations, we could not achieve the <50 ms query execution times. So we decided on an entirely different approach and started looking at the read/write patterns of the service.

The service operations team usually uploads 10k images per month, and users access the home pages up to 1-1.5 million times a month.

The read-to-write ratio is 1M/10k, which is 100:1. For every 100 reads, there is one write. The service read heavy.

In the current use case, real-time data is not a requirement, and even if the images are delayed by a few hours to show up on the home page, it’s alright.

With all the new data points at hand, we decided to give the materialized view a try.

Materialized Views

In computing, a materialized view is a database object containing a query’s results. For example, it may be a local copy of data located remotely, a subset of the rows or columns of a table or join result, or a summary using an aggregate function. The query results used to create materialized view are snapshotted and persisted in the disk.

Once we create a materialized view, we can use SQL to query snapshots.

Creating the materialized view

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

CREATE MATERIALIZED VIEW homepage_images as SELECT images.id, images.external_uuid, images.title, images.grades, images.is_active, c.name AS chapter_name, c.slug AS chapter_slug, s.name AS section_name, s.slug AS section_slug, s2.name AS subject_name, s2.slug AS subject_slug, t.name AS topic_name, t.slug AS topic_slug FROM images JOIN images_chapters qc ON images.id = qc.image_id JOIN chapters c ON c.id = qc.chapter_id JOIN images_sections qs ON images.id = qs.image_id JOIN sections s ON s.id = qs.section_id JOIN images_subjects qs2 ON images.id = qs2.image_id JOIN subjects s2 ON s2.id = qs2.subject_id JOIN images_topics qt ON images.id = qt.image_id JOIN topics t ON t.id = qt.topic_id;

Now that we have created our materialized, lets query it.

1 2 3 4 5

SELECT DISTINCT ON (id) external_uuid FROM homepage_images WHERE section_name = 'ABSTRACT' ORDER BY id OFFSET <offset_page> LIMIT 10

1 2 3 4 5 6 7 8

Limit (cost=46.04..55.16 rows=10 width=45) (actual time=0.921..0.963 rows=10 loops=1) -> Result (cost=0.42..55665.11 rows=61011 width=45) (actual time=0.774..0.959 rows=60 loops=1) -> Unique (cost=0.42..55665.11 rows=61011 width=45) (actual time=0.773..0.950 rows=60 loops=1) -> Index Scan using homepage_images_on_id on homepage_images (cost=0.42..55505.51 rows=63838 width=45) (actual time=0.772..0.935 rows=64 loops=1) Filter: ((section_name)::text = 'ABSTRACT'::text) Rows Removed by Filter: 1356 Planning time: 0.133 ms Execution time: 0.989 ms

We got the results in less than <1 ms, which is a significant optimization from 200 ms and great for user experience. If needed, you can also add indexes on materialized like traditional tables.

Response times of the homepage post deployment

The significant difference between querying on tables and materialized view is that materialized view cannot subsequently return real-time results if we insert new rows into an underlying table.

We can update the materialized view using the below query. Note that the below query locks the materialized view and will be unusable until refreshed.

1	REFRESH MATERIALIZED VIEW homepage_images

To refresh the materialized view without locking, you can use CONCURRENTLY, but this option requires you to have a unique index on the materialized view.

1	REFRESH MATERIALIZED VIEW CONCURRENTLY homepage_images

Closing notes

When to use materialized views?

1. Queries require multiple joins or compute aggregate data during runtime.
2. Application can tolerate stale data for a few hours.

Materialized views can become a great resource and lead to significant performance increases if used appropriately. But if they are misused, they can lead to stale data and refresh bottlenecks.

Note

Thank you for coming this far. I hope you enjoyed the content.

I am a polyglot programmer interested in databases, and I have implemented a project to build your persistent key-value store based on bitcask paper. Please check it out at https://github.com/dineshgowda24/bitcask-rb.

from Hacker News https://ift.tt/vQ9bTUV