Deleting in batches from PostgreSQL in Ruby

PostgreSQL doesn't support LIMIT in DELETE queries. But it's a bad idea to delete huge numbers of records at a time if the operation will be more than a few seconds. So deleting in small batches can be done like this:

DELETE FROM mytable WHERE id = ANY(ARRAY(SELECT id from mytable WHERE myfield = 10 LIMIT 10))

To use this code from ActiveRecord in Ruby and get the number of affected rows:

rows_affected_count = ActiveRecord::Base.connection.execute(query).cmd_tuples

You can do this in a loop to keep the queries moving through the system without slow queries choking things up. When rows_affected_count is less than the size of the batch, you're done.

How to find a community's pain points

A business's job is to meet customers' needs. Businesses that find unmet needs and serve them well are rewarded in the marketplace. I want this. But how do I identify these unmet needs?

Dipping into market ethnography

If a representative part of this group meets on the Internet, I want to find places where they meet online. The more expression of their social customs is present, the better. I want to find the places where community members relax and speak their minds. I'm looking for a group whose members have been together long enough that discernible patterns have formed. Alternatively, if I can find one that's been marginalized by society, that is an extremely powerful galvanizing force.

Brandon Savage, a participant in Amy Hoy's 30x500 class that teaches a method of market ethnography, used blog analytics, Twitter, Quora, Stack Overflow, and other forums to do his research leading to his decision to write Mastering Object-Oriented PHP.

When I find this group, I read what members are writing and mine it for key information:
  • Cultural themes or issues
  • Vocabulary
  • Goals and aspirations
  • Conflicts within the group, or between group members and others outside the group
  • What they currently buy
The Wikipedia article on ethnography lists some helpful procedures for conducting ethnographic studies.

Then what?

Now I find a need experienced and voiced by people who are spending money in this community, and create a solution to this need. I will systematically search and destroy each objection to a purchase, building trust by teaching members of this group that I understand their problem and can solve it. Sean D'Souza's Brain Audit lays out a framework for persuading people of this.

A pattern that often works to begin serving this community with a solution for their need:
  1. Each fear or threat becomes a topic that I cover with a blog post and email. 
  2. An email sequence leads users through an overview of the free content, putting it into context and beginning to apply it to the subscriber.
  3. A small paid work connects the dots, organizing all the information and going deeper into applying it.
  4. Further services such as workshops, seminars, or kits allow people to pay for thorough solutions.
  5. A masterclass and/or personal coaching are available for those who want to really nail it.
  6. I build an ecosystem of products to create recurring, more passive revenue from solving the needs.

Using more of our brains: visualizing bridge data

Lots of bridges need maintenance. We can't fix them all at once. We're already collecting useful data during bridge inspections, but it's hard to interpret in raw form.

So this professor was inspired to take advantage of the fact that we're in 2015. He created a rich document (he used SketchUp) to bring together inspection data, photos, and a simplified 3D model of a bridge. It's immediately obvious how helpful this would be for the vital work of prioritizing infrastructure repair.

Simple but effective. We have to see it to understand it.