Author: Steven Vance

A new map for finding COVID vaccination sites in Illinois

The State of Illinois map of COVID vaccination sites is pretty bad. 

Screenshot of the Illinois Department of Public Health map, taken February 14, 2021.

It’s slow (caused my browser tab to crash after a couple minutes), has misspelled county and city names, missing ZIP code digits, and cannot be searched by address. There are duplicate entries, too.

I made a new version of the state’s COVID vaccination sites map.

I didn’t make any COVID maps earlier because I didn’t want to spend the time to ensure that I understood the right and wrong ways to map disease, because people make decisions based on maps and I don’t want my maps to end up harming anyone. 

The new map of COVID vaccination sites on Chicago Cityscape.

Aside from the state website’s usability issues, I’m very disappointed that there is zero data about COVID in the state’s #opendata portal.


These cities and counties have the most COVID vaccination sites, according to the IDPH’s dataset. 

For the top 10 or so, it seems to correlate with population. Except Skokie has 7 sites, and Evanston has 4, despite Evanston having 10,000 more residents. Nearly 100% of Illinois is within 60 minutes driving of the current COVID vaccination sites. (More are coming, at least in Chicago.) 

Nearly 100% of Illinois is within 60 minutes driving of the current COVID vaccination sites. (More are coming, at least in Chicago.)

And a lot of Illinois is still within 45 minutes driving of the current COVID vaccination sites. Really big gaps in geography appear at the 30 minutes driving threshold.

A map of Illinois showing 30 minute driving areas around each of the 862 COVID vaccination sites.

I’m working with some people to show access via transit. This is super important. I predict that upwards of 75 percent of Chicagoans will be able to access a vaccination site or two within 45 minutes and 100 percent within 60 minutes.

Here’s another shortcoming of the state’s map: Each site’s unique ID is not persistent, making it difficult to compare one day’s list to the following day’s list. I got around that by making a “hash” of each vaccination site and comparing between two versions.

The map has been updated once since I started. The “hash” creates a unique ID based on the attributes of each vaccination site (name, address, city, county, ZIP code). Any time one of those attributes changes, the hash will also change and thus I can more easily find new or modified vaccination sites.

Two-flat owner journal 3: Choosing the right water heater

Read all of the “Two-flat owner journal” entries

This journal entry is all about the several hours of time I spent researching which water heaters to buy for my two-flat. While my architect works on drawing the plans, I am doing a lot of research to answer questions for him, so he knows what appliances are going to be hooked up to the house’s mechanical systems.

I started researching water heaters on a cold Saturday in January so I could fill out the “appliance schedule” for the project. My first journal entry was about my new distaste for Peoples Gas. Since then, my view has evolved and we (my architect and I) are designing an all-electric house.

SCROLL DOWN FOR THE RESEARCH – Read on while I discuss my thoughts about electricity and natural gas as a power source for homes.

Discussion

Let me step back a moment. I also have a “distaste” (a weird word to use when talking about something that would literally kill you if you drank it) for fossil fuels, too. When I approached this research task I thought that an electric tankless water heater had some kind of inherent efficiency over an electric or natural gas-powered tank type.

But it doesn’t, and that’s partly dependent on how much water a household uses. Tankless water heaters are sometimes marketed as providing “unlimited” hot water, which scared me. Since I don’t go to an office anymore (because of COVID-19 and because I quit my office-attached job), I take a shower every other day. And they’re long. Just imagine that your shower never ran out of hot water. I might not get out. So that’s a personal critique of the type of water heater.

(Another con of tankless water heaters: When the electric is off, water cannot be heated; with a tank water heater, there is always residual hot water in the tank, which can still flow. Additionally, electric tankless water heaters have special electrical box requirements because they draw so much electricity.)

Let’s talk about the fuel. Natural gas is cheap right now – to purchase. But it has awful costs elsewhere, namely its contribution to pollution and carbon dioxide emissions when burnt. Burning it in the home also releases additional gases, which is why I think you should run your range hood vent/exhaust when you cook food on the stovetop.

Gas stoves emit a host of dangerous pollutants, including particulate matter, formaldehyde, carbon monoxide, and nitrogen dioxide. 

Mother Jones, “How the Fossil Fuel Industry Convinced Americans to Love Gas Stoves”, by Rebecca Leber, FEBRUARY 11, 2021

Going electric in the kitchen is pretty easy, I think. I didn’t do much research and I picked out all Energy Star-certified appliances. For cooking I chose a range with an induction cooktop and an electric convection oven (which means there’s a fan inside to blow the heat around for even cooking). Induction cooktops, while being a really efficient user of energy, have cooking benefits: Water reaches a boil faster, the surface is easy to clean, and there are fewer burns because the surface doesn’t get hot.

Creating an all-electric house is pretty easy, actually, until you get to the heating and cooling part, and knowing how to heat a house with electricity in the very cold climate of northern Illinois requires even more research.

Water heaters are actually easy to figure out (after 5 hours of research) because, in the end, all you do is plug them in to a 240 volt receptacle and connect to the already-existing water pipes.

I am still in the middle of researching electric heating and cooling and I’ve opened a conversation with two HVAC contractors (one that sells Carrier and one that sells Mitsubishi).

Water heater research

I originally tweeted all of this on January 16, 2021, because I like using Twitter as a sounding board.

I have researched five types of water heaters because I want to fully understand the purchase price and energy price of each.

  1. Natural gas, tank
  2. Natural gas, tankless
  3. Electric, tank
  4. Electric, tank (hybrid w/heat pump)
  5. Electric, tankless 

Based on my research of manufacturers’ reported “Energy Guide” stickers (the yellow stickers required by federal law) for these five types, the Electric, tank (hybrid w/heat pump) (option 4) is far and away the most efficient water heater. 

The Electric, tank (hybrid w/heat pump) has an annual operating cost of $104. What is this thing? It takes the standard tank, uses electric heating elements (think of the wires inside your bread toaster), and extracts heat from the air in your house with the heat pump. That’s pretty amazing: There is free heat to be extracted from the air.

The Electric, tank (hybrid w/heat pump) has less than half the operating cost of the next lowest type: Natural gas, tankless.

Rheem (a water heater manufacturer, which also owns the Richmond brand) says that an electric tank water heater with hybrid heat pump uses less electricity than an old-fashioned incandescent light bulb.

Note that the electrical prices in the yellow “Energy Guide” sticker is 12 cents per kWh, and ComEd just charged me ~7 cents per kWh, so the annual operating costs of electric are less than the stickers say. 

Next, as part of the “total cost of ownership” (well, minus maintenance) I added in the purchase price. The Electric, tank (hybrid w/heat pump) costs $1,400. That’s 1.5-4x more expensive than the other types of water heaters!

However, I’ve since found that Rheem/Richmond makes a couple cheaper models that don’t have high-tech features, so the purchase price is anywhere from $1,000 (if you can find that model) to $1,175.

I calculated the “5 year cost of ownership” price for water heaters of each type because it makes sense to distribute the purchase price over a period of its lifetime. I could have easily made this a 10-year amortization since water heaters come with 6 to 12-year warranties. I interpret the length of the warranty as the manufacturer’s assessment as to how durable they’ve created the machine.

How they compare

Of the five types, I compared 11 machines (view the comparison chart).

The cheapest water heaters over five years are: 

  1. Electric tankless
    $1,479 (caveats in that annual energy cost was extrapolated because a direct Energy Guide sticker wasn’t found)
  2. Electric, tank (hybrid w/heat pump)
    $1,695 (does not include federal tax credit)
  3. Natural gas, tank
    $1,999

Remember, though, that the Energy Guide stickers for the electric water heaters use a 41.7 percent higher energy cost than ComEd currently charges, and ComEd offers hourly pricing so the price can be much, much lower per kWh and the prices for the electric water heaters are EVEN LOWER. (Thank you for pointing this out, Troy.)

Guess what…the price of the Electric, tank (hybrid w/heat pump) can come down even more because of (1) a ComEd rebate, and (2) federal tax credit worth 10 percent of the purchase price + installation, up to $300.

Bonus: By getting electric you are part of the carbon-free future. 

Further discussion

Now that I’ve convinced you that an Electric, tank (hybrid w/heat pump) water heater is the cheapest option, here are a couple of other things that came up in the Twitter conversations.

  • Including utilities (i.e. electricity cost) in the rent for the rental apartment in my two-flat benefits both me and the tenant. The tenant has a fixed and predictable energy cost and fewer bills to pay, while I am able to charge a bit more than I predict the cost will be in order to pay back the higher upfront costs of the water heater and the Energy Star-certified appliances (as well as the heating and cooling system).
  • The calculations might be different if I looked into having individual tankless water heaters at the point of demand, or using them as “boosters”. Tankless water heaters come in a variety of capacities and energy outputs (measured by how much energy it takes to increase the temperature from how cold the water is when it enters the house or heater to the desired temperature). One could be added to the kitchen, the bathroom, and next to the laundry, and sized for the differing demands of each location.
  • A complex system could be built that is programmed to buy energy from ComEd when the hourly cost is the lowest and use the power from the battery when the hourly cost is highest.
  • A couple people asked about geothermal. I looked into it and I wrote it off quickly: A drilling rig needs to access the yard to drill multiple horizontal wells. The garage blocks that from happening. However, an apartment building renovation in Rogers Park designed by PMP Architects is converting its heating system to use geothermal energy.

Two-flat owner journal 2: Demolishing the interior

In order to get the two-flat ready for a gut rehab, one has to gut it.

The rehab stage is still months away, as my architect and I continue to develop plans. He does most of the work, but it’s quite collaborative because there are layout, design, and finishing choices that we need to make.

Gutting the house is also necessary for the plans because my architect needs to know what’s behind the walls.

I hired Amplify Property Solutions for the job. APS has a social mission of training and employing young Black men. You can ask me personally how much it cost, but it was between $5,000 and $10,000 (that range includes the cost of six Dumpsters).

I am very happy with the crew’s work and Ron and Ted’s dedication to communication, clarity, and customer service. The work took a week longer than they predicted because of some aspects that I think were next to impossible to know:

  • There were 1-2 “extra” layers of flooring in some places. From top to bottom in the upstairs living room there were carpet, wood, linoleum, wood, and subfloor layers.
  • Because the center beam in the basement has been failing (sagging) for years, the centers of each floor were sinking making the floors unlevel. Platforms were built in each kitchen to raise and level the floor.
  • The house is one of two row houses, so one exterior wall is shared. On this wall, behind the drywall was a 1″ layer of plaster that took awhile to chip away.

The subfloor boards are very wide and old growth and original (so they’re at least 130 years old). I’ve been told that some people find these valuable; if you’re interested in purchasing them, please get in touch!

See more photos below and in the house photo album.

Permitting

With this level of demolition, a permit is required! I pulled an easy permit for this project (which I think cost $375). The Dumpster company obtained their own permits to occupy the street right of way.

Also, as a way for the Chicago Department of Buildings to discourage gut rehabs being permitted with a series of easy permits, when a Standard Plan Review is most likely required, the DOB required that I apply for a renovation/alteration permit and show them in-progress drawings.

Consultants and contractors used so far


First floor kitchen
The demolition exposed the shape of the chimney. The right side was filled in and the wall was flat so we didn’t know how wide the chimney was.
The kitchen platform
The kitchen platform had a level top, but the floor beneath was uneven. The framing underneath the platform had angles cut to ensure the platform was level.

2020 year in review

Rear view of the two prefab single-unit detached houses and their backyard ADUs
I was biking around in Denver and I came across these identical modular houses with backyard houses under construction. The City of Denver posts building permit plans online, which is fantastic because it makes design more transparent. Architects from around the world can be inspired by this project.
  • Due to the (ongoing) COVID-19 pandemic, I started working from home on March 12, 2020. I visited the office once in the fall for an hour to perform a minor task.
  • My small business, Chicago Cityscape, hired Casey Smagala to run business development for the real estate information website.
  • I bought a two-flat in East Garfield Park in the summer after touring dozens of houses. I had first toured this house, and met the owner, Carl, who lived there, in February. I toured it a second time that month, with my friend, Kevin. After several more months of touring houses and realizing that I couldn’t afford an already-renovated house, I made an offer to Carl in May. The house closed in July.
  • Friends in Denver asked me and my friend, R, to come visit and camp, hike, and bike in and around Aspen in August. Denver also has allowed accessory dwelling units (ADUs) for years, so I talked to a local housing organizer and biked around the city looking at backyard houses.
  • I didn’t move into my two-flat because it turned out to need more renovation than I had believed. I also started a journal, which has one post so far.
  • I left my job as of December 31, 2020, so that I could focus on developing and growing Chicago Cityscape.
  • By the end of the year, I published eight blog posts. I didn’t have a goal; two of them were based on books I was reading: (1) Before the Lake Street elevated (now the Green Line) was built, a monorail was proposed (via a book about the history of the CTA’s predecessors)! (2) I read Beryl Satter’s book about her father, a landlord in Chicago, “Family Properties”, and I biked by one of the extant buildings.
  • My “hot air balloon” was used for some cool projects that needed aerial photography (both co-produced by Paola Aguirre and her consulting firm, Borderless Studio). I filmed some the massive mapmaking happening at the former Overton school in Bronzeville – which can only be seen from the sky – for an AIA film challenge; I also snagged some clips of the Pink Line ‘L’ going in and out of the California Ave station to visualize an area being studied for a corridor revitalization.

The high rises of the Bronzeville lakefront

3600 S King Dr

I shot this aerial photo yesterday from the 3600 block of South Dr. Martin Luther King Jr. Drive.

The “towers in the park” are three groups of apartment buildings (none are public housing).

From north (background) to south (foreground):

  • Prairie Shores (5 identical buildings that are very hard to see)
  • Lake Meadows (4 identical buildings behind the associated one-story shopping center plus multiple buildings to the east, closer to the lakefront)
  • T.K. Lawless Gardens (3 identical buildings, that appear the tallest because they’re the closest, 746 units, and 54 townhouses not seen)

John Warren Moutoussamy (an architecture graduate of Illinois Institute of Technology who studied under Ludwig Mies van der Rohe) designed Lawless Gardens, according to this IIT Magazine article.

Two-flat owner journal 1: Peoples Gas charges a lot of money for no gas

One of the first things I did after I bought a two-flat in July was contact Peoples Gas and Comed to ensure utilities were in my name, and that the utility connections would not be interrupted.

A few days later I decided that I wasn’t going to move in, because I wanted to make a good amount of changes and the best time to do that would be when nobody is living there. “Good amount of changes” turned into “gut rehab”. One of my friends is an architect and we (mostly him) are drawing permit plans right now.

It wasn’t until a week ago (8 weeks since I bought the house) that I realized there’s no reason to be paying for Peoples Gas to maintain a connection when I’m not using natural gas.

I’m writing this journal entry to exclaim how expensive it is to just “leave the gas line connected”.

It costs $50 per month per unit to have the privilege of possibly purchasing the delivery of natural gas through a pipe. Both units used 0 therms in the longest-period bill I received. (I received three bills, only one of which was for 30 days.)

The bill for the only 30-day period Peoples Gas served one of the units in my two-flat. Both units are unoccupied until after the gut rehab is completed. See the “customer charge”, which is the charge just to have an account open and for the potential to use gas.

I’ve got to pay $50 per unit for no gas.

I visited a three-flat under construction in Pilsen on Friday, and talked to the developer, Brent. He described how he’s following high-efficiency building wall standards to create a “tight envelope” (one in which very little air can leak) so that the tenants can “receive the comfort they’re paying for”. When it comes to setting the thermostat, the air delivered by HVAC machines should match that exactly, no more, no less. No oversized furnaces pushing too much heated air because so much of the air leaks through the walls and windows.

And, as a way to control costs, Brent will not connect a natural gas pipe to the building, mostly because of the expensive and default customer charge that persists even when no gas is used. A VRF (variable refrigerant flow) and heat pump machines will be entirely powered by electricity to serve the tenant’s heating and cooling needs.

Brent said that the tight building envelope coupled with the high-efficiency HVAC means that it’s more cost effective to use electricity to heat a house than natural gas.

After our meeting, I looked again at my final bill from Peoples Gas (I closed the account two days prior) and understood what Brent was saying about controlling costs. With an electric water heater and an electric range, there’s no need to have any gas connection.

I will probably have to keep the gas at my two-flat, to power the furnaces, because I don’t have the expertise or financial resources to renovate an existing building to have a tight enough envelope to make electrically-generated heat more cost effective than gas-generated heat.

Update January 13, 2021: I turned off the gas and closed the accounts so I don’t have to waste any money while I’m not living there (a gut rehab still needs to happen).

To keep water pipes from freezing and bursting I cleared the vast majority of water lines and added an electric pipe heating cable to keep the remaining sections warm.

The two water service pipes (I don’t know why there are two) have an electric pipe heating cable and are wrapped in foam insulation. The cable has a thermostat that touches the pipe and starts heating when the pipe drops to 38°F. The pipe is heated until the thermostat detects ~46°F.

What is this place? Des Plaines “park” edition

Screenshot shows the “park” in Des Plaines, Illinois.

I was methodically reviewing features on OpenStreetMap that could benefit from additional attributes, namely missing names and cities. The information on OpenStreetMap feeds into Chicago Cityscape so that the real estate information service I created can show people looking up addresses the locations of nearby amenities, including parks.

This particularly large park in Des Plaines had no name and no city, so I started investigating. From the overhead imagery view, it looked to me like a landfill.

Since there was a forest preserve to the east, I looked in the Forest Preserve District of Cook County’s nice interactive map for any properties near Beck Lake. Nothing found.

Overhead imagery of the so-called “park”. Source: Mapbox

I looked on Google Maps Street View for some insight and there was no name. (Note that data from Google Maps cannot be used to amend OpenStreetMap because of Google Maps having a data license that isn’t compatible with OSM’s openness requirements).

Next I looked at the Cook County parcel map that Chicago Cityscape has to identify its “Property Index Number” (PIN). All of it fits within 04-31-300-003-0000 – the database pointed to “Catholic Cemeteries” as the property owner.

I looked up that PIN on the Cook County Recorder of Deeds to try and find more information. Lo and behold, the most recent document that was recorded against the PIN was for a “Memorandum of Option Agreement” that identified the Catholic Bishop of Chicago as the landlord and Patriot Acres, LLC, as the tenant.

Patriot Acres has a website that describes a new commercial composting facility being built at this location.

I re-tagged the feature on OSM with “amenity=waste_disposal” and “waste=organic” to properly describe it as a composting facility, and not a park. Case closed.

Chicago bonfire rules

I believe that more people are hosting more backyard bonfires in Chicago in order to spend time with friends outdoors, because it’s one of the lowest risk ways to still enjoy life during the COVID-19 pandemic that Trump is prolonging.

The City of Chicago has rules on what can be burned. Be sensitive to neighbors who rely in keeping their windows open to cool and ventilate their homes.

The summary version is this: You can burn manufactured firewood that you purchased, and you can burn wood logs from any trees someone manually cut. All logs have to be untreated and two feet long or shorter.

You can’t burn scrap wood, lumber, plywood, or particle board. You also cannot burn garbage. Avoid having bonfires when the wind speed is 15 miles per hour or faster.

Read all the rules in this PDF. The rules aren’t in the municipal code; the code in section 11-4-740 says that the environment commissioner shall make rules (Chicago doesn’t currently have a department of environment).

This is my process to improve the performance of my PostgreSQL database and queries

Every now and then I’ll run the queries below to help me figure out ways to optimize the queries my application is using, and to figure out if certain tables and views (relations) need to be optimized.

Updated January 18, 2020, to add a query to alter the autovacuum and autoanalyze factors on a per table basis because I have a “cache” table that is updated quite frequently and generates many dead tuples every minute. The table would balloon from 250 MB to 15 GB in a few hours. I hope that reducing these factors to just a threshold of 100 dead tuples will prevent the inflation. I also need to monitor this that it doesn’t make my RDS database work too hard.

/* Show the last time at which every table and view was vacuumed and analyzed */
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze  
FROM pg_stat_all_tables  d_d
WHERE schemaname = 'public';  

/* See which queries are most popular */
SELECT * FROM pg_stat_statements where calls >=1000; /* replace 1000 with any integer, depending on how active your database is queried */
select pg_stat_statements_reset();

/* Kill a specific query */
select pg_terminate_backend(INTEGER); /* replace INTEGER with the process ID of a specific query */

/* Get the size of the database */
select pg_size_pretty(pg_database_size('DATABASE_NAME')); /* replace DATABASE_NAME with the name of your database */

/* Kill queries that are taking too long; replace DATABASE_NAME with the name of your database */
SELECT query, pg_terminate_backend(pid) 
    FROM pg_stat_activity 
    WHERE datname = 'DATABASE_NAME' 
      AND pid <> pg_backend_pid() 
      AND (state = 'active' 
      OR state = 'idle') 
      AND state_change < current_timestamp - INTERVAL '15' MINUTE; /* change 15 to any integer that you think means a query is taking too long */

/* See which queries are running */
select * from pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend';

/* Find very large indexes and indexes that aren't used much */
WITH table_scans as ( 
    SELECT relid, 
        tables.idx_scan + tables.seq_scan as all_scans, 
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes, 
                pg_relation_size(relid) as table_size 
        FROM pg_stat_user_tables as tables 
),
all_writes as ( 
    SELECT sum(writes) as total_writes 
    FROM table_scans 
),
indexes as ( 
    SELECT idx_stat.relid, idx_stat.indexrelid, 
        idx_stat.schemaname, idx_stat.relname as tablename, 
        idx_stat.indexrelname as indexname, 
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes, 
        indexdef ~* 'USING btree' AS idx_is_btree 
    FROM pg_stat_user_indexes as idx_stat 
        JOIN pg_index 
            USING (indexrelid) 
        JOIN pg_indexes as indexes 
            ON idx_stat.schemaname = indexes.schemaname 
                AND idx_stat.relname = indexes.tablename 
                AND idx_stat.indexrelname = indexes.indexname 
    WHERE pg_index.indisunique = FALSE 
),
index_ratios AS ( 
SELECT schemaname, tablename, indexname, 
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC 
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct, 
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2) 
        as scans_per_write, 
    pg_size_pretty(index_bytes) as index_size, 
    pg_size_pretty(table_size) as table_size, 
    idx_is_btree, index_bytes
    FROM indexes 
    JOIN table_scans 
    USING (relid) 
),
index_groups AS ( 
SELECT 'Never Used Indexes' as reason, *, 1 as grp 
FROM index_ratios 
WHERE
    idx_scan = 0
    and idx_is_btree 
UNION ALL 
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp 
FROM index_ratios 
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10 
    and idx_scan > 0 
    and writes > 100 
    and idx_is_btree 
UNION ALL 
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp 
FROM index_ratios 
WHERE
    index_scan_pct < 5
    and scans_per_write > 1 
    and idx_scan > 0 
    and idx_is_btree 
    and index_bytes > 100000000 
UNION ALL 
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp 
FROM index_ratios, all_writes 
WHERE
    ( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02 
    AND NOT idx_is_btree 
    AND index_bytes > 100000000 
ORDER BY grp, index_bytes DESC ) 
SELECT reason, schemaname, tablename, indexname, 
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups; 

/* Modify the autovacuum and autoanalyze factors for a single table 
https://klotzandrew.com/blog/posgres-per-table-autovacuum-management */
ALTER TABLE cache SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 100);

WITH raw_data AS (
  SELECT
    pg_namespace.nspname,
    pg_class.relname,
    pg_class.oid AS relid,
    pg_class.reltuples,
    pg_stat_all_tables.n_dead_tup,
    pg_stat_all_tables.n_mod_since_analyze,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ) as c_analyze_factor,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_threshold=' ) as c_analyze_threshold,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_scale_factor=' ) as c_vacuum_factor,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_threshold=' ) as c_vacuum_threshold,
    to_char(pg_stat_all_tables.last_vacuum, 'YYYY-MM-DD HH24:MI:SS') as last_vacuum,
    to_char(pg_stat_all_tables.last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as last_autovacuum
  FROM
    pg_class
  JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
    LEFT OUTER JOIN pg_stat_all_tables ON pg_class.oid = pg_stat_all_tables.relid
  WHERE
    n_dead_tup IS NOT NULL
    AND nspname NOT IN ('information_schema', 'pg_catalog')
    AND nspname NOT LIKE 'pg_toast%'
    AND pg_class.relkind = 'r'
), data AS (
  SELECT
    *,
    COALESCE(raw_data.c_analyze_factor, current_setting('autovacuum_analyze_scale_factor'))::float8 AS analyze_factor,
    COALESCE(raw_data.c_analyze_threshold, current_setting('autovacuum_analyze_threshold'))::float8 AS analyze_threshold,
    COALESCE(raw_data.c_vacuum_factor, current_setting('autovacuum_vacuum_scale_factor'))::float8 AS vacuum_factor,
    COALESCE(raw_data.c_vacuum_threshold, current_setting('autovacuum_vacuum_threshold'))::float8 AS vacuum_threshold
  FROM raw_data
)
SELECT
  relid,
  nspname,
  relname,
  reltuples,
  n_dead_tup,
  ROUND(reltuples * vacuum_factor + vacuum_threshold) AS v_threshold,
  n_mod_since_analyze,
  ROUND(reltuples * analyze_factor + analyze_threshold) AS a_threshold,
  c_analyze_factor as caf,
  c_analyze_threshold as cat,
  c_vacuum_factor as cvf,
  c_vacuum_threshold as cvt,
  analyze_factor as af,
  analyze_threshold as at,
  vacuum_factor as vf,
  vacuum_threshold as vt,
  last_vacuum,
  last_autovacuum
FROM
  data
ORDER BY n_dead_tup DESC;

My investment in crowdfunded projects hasn’t gone well

A second project that I invested in through the crowdfunding portal, Small Change, has “failed”. It’s a project for a worker-owned bed and breakfast. According to the project owner, the project needed more funds than what they were able to obtain from Small Change investors and other investors. The owner found a new investor, who required that all Small Change investors be removed from the project. They announced that to us earlier this year, and said we would receive our initial investment + 3%.

The Small Change leadership didn’t think this plan was good enough, starting with disagreeing the removal of Small Change investors.

Eventually, the project owner returned our initial investment + 8%. I don’t know if the project owner had the right to cancel and return our investments, but I’m happy to have my money back + 8%.

I’m disappointed in the ultimate outcome of not being able to participate in 1476 Magazine because this is the second project I invested in via Small Change that has not succeeded. The first was for a group of three single-family houses built to a high energy efficiency standard that have not yet sold.

I have an investment in a third project (in Philadelphia) that is converting a disused industrial building into mixed-use with residential and I hope that one succeeds, but I haven’t received an update since it was funded. Update: I inquired and the project owner sent me the update they sent in the last couple of months, which I seemed to have missed.

Golaski Labs in Philadelphia. A Small Change poster advertises the project. The photo was taken in 2018, and significant progress has been made since then.


I believe in the Small Change mission, and crowdfunding broadly, but once I made my third investment I paused so that I could wait to see what happens with all of the projects.