How can we store static ACS data outside of the database?

[iandees]

Problem: we have 100s of GB of ACS data sitting around in a database that's running 24/7 and a very small amount of it is queried.

Idea: Could we store data in S3 in such a way that we could do a range request for data without having to query the database?

[iandees]

After doing another data update for 2015 ACS data, I'm really interested in making this happen. I'd like to figure out a way to keep Census Reporter running for longer by not paying ~$40/mo in PostgreSQL data storage.

/cc @migurski @jedsundwall

[migurski]

What are some example queries made against this data?

[JoeGermuska]

@migurski it is mostly just filtering, either for an explicit list of geographies and/or a containment query (geographies of type X contained by Y).

The API supports selecting one or more specified columns from diverse tables (instead of just "all columns in the table) but that's a pretty specialized use case, probably rarely used.

For our profile pages, we gather data from many tables. We could consider preprocessing those, especially because API users have expressed interest in being able to query the whole set of profile page figures for geographies rather than having to do the aggregation themselves.

I'm pretty sure that's it. @iandees am I forgetting anything?

[iandees]

The way I like to describe it is that it's a big huge spreadsheet with thousands of columns (columns are grouped into tables of related columns – these are only minorly related to DBMS column/tables) and tens of thousands of rows. In Census-speak, the columns are things like "Population" and "Median Household Income" and rows are geographies like Milwaukee, WI or the ZCTA 24450. Each cell in this spreadsheet has an estimate and a measurement of error (aka uncertainty).

	A	B	C	…
X	e / u	e / u	e / u	…
Y	e / u	e / u	e / u	…
Z	e / u	e / u	e / u	…
…	…	…	…	…

(where A, B, C are columns and X, Y, Z are geographies)

Like Joe said, the API supports taking chunks out of this gigantic spreadsheet by letting the user specify a comma-separated list of geographies and a comma-separated list of columns. So you might send ?geo_ids=X,Z&table_ids=B you'd get the highlighted data:

	A	B	C	…
X		e / u		…
Y				…
Z		e / u		…
…	…	…	…	…

Like Joe said, the majority of the queries come from our profile pages, which make several requests like my example here, do some math to put the data together, and show a page like this.

[migurski]

I guess you could PUT an enormous slab of data into S3, and then bake a number of b-tree indexes into it that would support range queries. I’ve never done anything like this — it’d be like network-mapping a SQLite file, I suppose, so maybe someone has done that? Seeking into the indexes will also be tricky, I expect that each one would be pretty large. What's querying the DB? Something on EC2?

Do you need to retrieve the full row each time, or could this behave more like a column-oriented DB?

Also for the spatial queries I guess you'd want a GIN-style inverted index of some kind of tile to each geography.

[migurski]

If something on EC2 is doing the querying, what about a mounted EBS volume containing database files? SQLite or even Postgres could work.

[iandees]

The current setup is one EC2 instance querying an RDS instance. The 300GB EBS volume attached to the RDS instance is currently the biggest expense we have and what I want to get rid of :).

[migurski]

Ah okay, that makes sense. I’d love to play with this; it’s an interesting problem that I toyed with on S3 back when it first came out and I wanted to see if there was a way to interact with indexed data client-side. Would you ever search on values in the table other than geography, or is it really just ID lookup?

[iandees]

It's only ever an ID lookup. There's currently no functionality to do things like WHERE population > 50000, only WHERE geo_id IN ('04055', '04023').

[migurski]

The universe of GEOIDs for ACS looks to be 7,845 — any reason to not keep each of these in separate S3 objects so they can be requested individually? Then all that's left is a spatial index.

[migurski]

…or, should this process also work for the much-larger 5-year estimates as well?

[iandees]

There are many, many more in the 5-year, but splitting into files per geoid does make sense for the kinds of queries people would tend to do (focused on geographies).

[migurski]

Right, makes sense. I’m planning to pick at a basic b-tree for GEOIDs as well as a quadtree-based spatial index for geographies. It would require something like GEOS or Shapely client-side to work.

[iandees]

You can probably leave out geospatial indexing for now. We can pre-generate vector tiles for all of TIGER.