From my tech blog:

Static HTML comments

If you have a static HTML website, but you want to include comments, here’s an interesting way to do it using PostgreSQL’s NOTIFY and LISTEN.

The big idea is to write the comments as static HTML, only when comments change, instead of doing a database query to display them every time. This prevents the “hug of death” if you get a burst of traffic.

I’ve been doing it this way for over six years, and it works great. Here’s the recipe, using Ruby as the glue, though you could use any scripting language.

  1. PostgreSQL database table for comments
  2. Ruby receives form posts, inserts into database
  3. When comments change, PostgreSQL trigger sends NOTIFY
  4. Ruby runs PostgreSQL LISTEN, exporting updated comments to HTML
  5. JavaScript on static page includes HTML

PostgreSQL database table for comments

Ruby receives form posts, inserts into database

Put this on any HTML page where you want comments:

Put this next code in your Nginx config, to send /comments to localhost.

Ruby Sinatra receives form posts.

Run that in a terminal on the server, and it should default to listen on port 4567.

When comments change, PostgreSQL trigger sends NOTIFY

Load that function into the PostgreSQL database that has your comments table.

It sends the listener (below) a notification that comments for this URI have changed. Then the listener will re-output comments just for this URI, instead of all.

Ruby runs PostgreSQL LISTEN, exporting updated comments to HTML

Make a directory in your web root called /commentcache/, to hold the static comments.

Then keep this Ruby script running in a terminal to listen for database changes, and write the updated comments to disk as HTML.

JavaScript on static page includes current HTML when viewed

Use JavaScript to show the form to post a comment, and load the list of comments from the /commentcache/ path.

That’s all. I’ve simplified it a bit from my real usage, where I have constraints and checks that would have distracted from the core point of this example.

There are other ways to do it. The NOTIFY and LISTEN isn’t necessary. The Ruby Sinatra route that receives the posted comment could just write the HTML to disk immediately. But I have other scripts that delete and update comments, and I like how the combination of NOTIFY trigger and LISTEN script always keeps them updated on disk.

Another interesting approach would be to write the comments into each HTML file directly, instead of in a separate file, so you wouldn’t need JavaScript at all.

Optional upgrade: NOTIFY on delete

I simplified the PostgreSQL trigger for the example, but with a few more lines of code, you can use the same trigger to notify of deleted comments, too. The value of a deleted row is in “old”, whereas inserted and updated is in “new”, so we have to make a uri variable, and an if/then/else to know which to use.