
![](/static-v2/og/blog/opendatamonitor.png)

## Tracking open government datasets
Say hello to [Open Data Monitor](https://open-data-monitor.splitgraph.io/), an open government dataset diff visualizer. Observe when [Socrata](https://dev.socrata.com/) datasets were added/deleted by day, week, and month, with upstream attribution and easy querying via the respective Splitgraph repository.

In a previous [post](https://www.splitgraph.com/blog/seafowl-sql-cdn) we discussed the benefits of Seafowl's approach to HTTP caching; in particular its effective use of [ETags](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/ETag) to deliver results on the edge with excellent latency characteristics. Today, we continue on our journey to:
- Ingest Splitgraph's Socrata catalog into Seafowl
- Insert tables that expose added/deleted status ("observe" Socrata)
- Swap Fly.io instances in a cost-effective way
- Build a mini Next.js app to render it all out in the browser, making sure to balance the best of SSR SEO benefits with Seafowl's browser-friendly cache semantics.

## Ingestion
In case you didn't already know, Splitgraph maintains a catalog of Socrata datasets, stored as a [Splitgraph repository at `splitgraph/socrata`](https://www.splitgraph.com/splitgraph/socrata/). Additionally, Splitgraph actually _indexes_ all of the 40,000+ datasets and makes each one queryable at its own URL (like [`cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r`](https://www.splitgraph.com.cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r)) and Splitgraph repository on the Data Delivery Network (DDN). It's possible to point your favorite Postgres client to the DDN and query arbitrary Socrata datasets in a familiar way. From [traffic accidents](https://neumark.github.io/splitgraph-map-demo/) in Fort Worth, TX [^1], to [taxi rides](https://nyctaxis.surge.sh/) as a proxy for "how's NYC Covid bounce back going"[^2], we've built several integrations that present data in visual and interactive ways without custom plumbing.
We also made [Socrata roulette](https://splitgraph.github.io/socrata-roulette/) for generating random queries against all of the available datasets.

As useful as the DDN is, today we will demonstrate how [Seafowl](https://seafowl.io) can make for an effective alternative that performs with low latency and generally has a smaller resource footprint. First, by starting with this catalog, we [fetch](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L42) all Socrata images courtesy of Splitgraph's GQL API. (Conveniently we can export directly in [Parquet format](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/ingest.py#L141)!) Then, we [insert](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L213) the results into Seafowl tables. This whole job is powered by Github [Actions](https://docs.github.com/en/actions), and occurs on a regular basis (currently nightly). After we've ingested the new state of all Socrata datasets, we [pre-compute a diff across time](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/sql/daily_diff.sql) of all datasets that were added or removed on a given day. We also downsample these diffs to week-over-week and month-over-month changes.

Because this involves a self-join of a table with several million rows, this currently requires a large amount of memory as you'll see later.

##  Dynamically scaling our Fly.io instance for ingestion
Similarly to the [Seafowl tutorial](https://seafowl.io/docs/getting-started/tutorial-fly-io/introduction), we again went with Fly.io for hosting. While the free tier generally offers enough performance for Open Data Monitor's read-heavy workload, when it comes to _ingestion_ and building the day-over-day diff table, this time we ran into memory issues because of a self-join. It is perhaps unsurprising that as Socrata has grown over time, so have the number and size of datasets on offer. That's why we added a step in the GHA to temporarily [scale up](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/sync.yml#L30) the Fly.io instance memory for ingestion purposes. We also include an [insurance policy](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/downsize-insurance.yml) to help reduce risk of job errors causing unexpected cloud costs.

## Rendering in the browser
With a loaded Seafowl instance ready to go, we can now build a [Next.js app](https://github.com/splitgraph/open-data-monitor) so we can fetch from Seafowl and render it in the browser.

By default, we render the current week, but the user can specify a certain day, week, or month.

## Disappearing data examples

<div style={{display: 'flex'}}>
<div>
Because traversing over time is now trivial, we can surface curious moments.
<br /><br />
For example, just a few clicks moving backwards from January 2023 yielded disappearing

- [greenhouse gas emissions](https://open-data-monitor.splitgraph.io/2023-01-13#data.bayareametro.gov)
- [commercial ovens](https://open-data-monitor.splitgraph.io/2023-01-13#data.energystar.gov)
- [hot spots](https://open-data-monitor.splitgraph.io/2023-01-05) 🤔
- [Austin Schools Rated as Improvement Required](https://open-data-monitor.splitgraph.io/2022-12-31#data.austintexas.gov)

</div>
<img src="/static-v2/seafowl/blog/open-data-monitor/opendatamonitor-hero-2.png"
  style={{float: 'right', width: '300px', paddingLeft: '2em'}}
  alt="detective smoking a pipe"
/>
</div>


## SSR by default, but browser talks to Seafowl directly on nav
To strike a balance between SSR-conferred SEO advantages, and Seafowl's HTTP cache friendly defaults, we pre-fetch inside [getInitialProps](https://github.com/splitgraph/open-data-monitor/blob/153e4464eb24994ce5503680ce4b907a8e1faa8d/pages/index.tsx#L26), which means the page renders properly even with JS disabled.
But since we don't want the Vercel server to intermediate subsequent fetches, when the user clicks Previous/Next, their browser requests from Seafowl directly. Because we are using the GET API, we benefit from the nice cache behavior, so subsequent loads are nice and zippy, even on Fly.io's free tier, and even if the actual ingest was a memory hungry job.

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/opendatamonitor-demo.mp4" type="video/mp4" />
</video>

## Read the code

The code for all of this is available on GitHub, in two repositories:

- [splitgraph/socrata-to-seafowl](https://github.com/splitgraph/socrata-to-seafowl): GitHub Actions for syncing from Socrata to Seafowl instance hosted on Fly.io
- [splitgraph/open-data-monitor](https://github.com/splitgraph/open-data-monitor): The Next.js frontend application



## Conclusion

We hope this post has illustrated a pathway to combine Seafowl's "analytics at the edge" benefits with Socrata's bevy of government data, in a cost-effective way.
While today we focused on open government data, regardless of the data you're working with, consider checking out what Seafowl offers.

If you discover any curious dataset mysteries, or build an integration with Seafowl, we'd love to hear from you. Tweet us [@splitgraph](https://twitter.com/intent/tweet?text=@splitgraph).

Happy sleuthing!


[^1]: https://www.splitgraph.com/jumpstart/mapping-geo-data
[^2]: https://github.com/splitgraph/nyctaxis


##### Image credits
[mediamodifier](https://unsplash.com/photos/yx17UuZw1Ck), [sammywilliams](https://unsplash.com/photos/ocvLVIrC7c0), [ayosake](https://unsplash.com/photos/esOA3o92zxE)


How can we track open government datasets over time? Say hello to Open Data Monitor, a Socrata tracking tool powered by Seafowl and Splitgraph.

Open Data Monitor


## Measuring Urbanness

Sometimes a hard-to-measure property has significant impact on performance. For example, it's safe to assume sexier lingerie sells better,
but devising an algorithm to classify the "sexiness" of an article of clothing based on its design is far from trivial.

Another ill-defined quality impacting countless more measurable metrics is "urbanness", an individual's preference for densely or sparsely populated areas.
All sorts of trends, from [voting patterns](https://government.cornell.edu/news/exploring-widening-chasm-between-urban-and-rural-voters),
to life expectancy and religious affiliation all have marked differences in urban versus rural populations.

Statistically, it makes sense to classify someone as urban or rural based on their current residence, regardless of where they may have lived in the past.
On an individual level, it's not always so clear-cut. I currently live in a city with a population of over 1.5 million,
but I've also lived in a small town (less than 4000 residents). Today, a census would classify me as urban, but is that who I really am?

I decided to find out using photo location data. Smartphones usually encode the longitude and latitude in the EXIF data of photographs.
Over the years, I've collected thousands of photos (21808 to be exact), enough to give me a representative sample of where I prefer to spend my time.

## Classifying locations

The geometric center of a city is not always its most densely populated spot. What's a better proxy for "urbanness"?

If urban individuals are those who choose to spend their time in the city, areas offering many places to "hang out" are probably ideal locations for spotting urbanites.
Conversely, if one frequents such areas, chances are they can be labelled as urban. And what could possibly be a more urban place to hang out than the largest coffeehouse chain in the world, Starbucks?

Following in the footsteps of [Golden Arches Theory of Conflict Prevention](https://en.wikipedia.org/wiki/The_Lexus_and_the_Olive_Tree#Golden_Arches_theory),
let's define the equally tongue in cheek **Omnipresent [Siren](https://stories.starbucks.com/stories/2016/who-is-starbucks-siren/) Metric of Urbanness** (OSM) as the number of Starbucks locations within 2 kilometers of any given point on the globe.

Conveniently, there's a dataset of Starbucks locations [which can be downloaded from Kaggle](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download), which I used to classify my photos by this metric.

## Loading photo coordinates

An easy way to extract EXIF metadata from images is to let Google Photos do it for you. For users of Google Photos, [Google Takeout](https://takeout.google.com/) offers a way to download photos along with their EXIF data extracted to separate JSON files.
Although not covered here, it's also possible to read location data (and much more) directly from images using [exiftool](https://exiftool.org/).

After downloading and extracting the Google Takeout archives, I ran the following to get a CSV file with the coordinates of each photo:

```bash
# enter Google Photos directory within Takeout directory
cd 'Takeout/Google Photos'
# extract image metadata from JSON files
find 'Photos from'* -name '*.json' -exec jq -c '{latitude: .geoData.latitude, longitude: .geoData.longitude, timestamp: .photoTakenTime.timestamp, filename: input_filename}' {} >> unfiltered_image_attributes.ndjson \;
# only keep image metadata with GPS data (non-smartphone cameras don't write location data)
grep -v '{"latitude":0,' unfiltered_image_attributes.ndjson  > filtered_image_attributes.ndjson
# write CSV header
echo "latitude,longitude,timestamp,filename" > photo_attributes.csv
# write CSV data
jq -r '([.latitude, .longitude, .timestamp, .filename]) | @csv' < filtered_image_attributes.ndjson >> photo_attributes.csv
```

## Loading data into Seafowl

Seafowl has great CSV support. To see it in action, [install Seafowl](https://seafowl.io/docs/guides/installing) if you haven't yet, then run:

```bash
curl -v -F "data=@starbucks_directory.csv" "http://localhost:8080/upload/public/starbucks_directory"
curl -v -F "data=@photo_attributes.csv" "http://localhost:8080/upload/public/photo_attributes"
```

Note that I changed the CSV header of the [Kaggle Starbucks dataset](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download) for more convenient querying to:
```
brand,store_number,store_name,ownership_type,street_address,city,state,country,post_code,phone_number,timezone,longitude,latitude
```

## Calculating distances

Seafowl doesn't come with built-in functions for calculating the distance between two points on the planet,
but fortunately there is an excellent [Rust library](https://docs.rs/geoutils/latest/geoutils/) we can use.

To call it from Seafowl queries, we must create a user-defined function (UDF) and compile it to [WebAssembly](/blog/seafowl-wasm-udfs).
The code for the UDF is available [on GitHub](https://github.com/splitgraph/seafowl-udf-geo).

After running the [`create_udf.sh`](https://github.com/splitgraph/seafowl-udf-geo/blob/main/create_udf.sh) script, the previously unavailable `distance()` function may be used in queries
like so:

```sql
-- calculate the distance between Berlin and Paris in meters
-- distance(longitude_1, latitude_1, longitude_2, latitude_2) -> distance in meters
SELECT distance(52.5200, 13.4050, 48.8566, 2.3522)
```
We're ready to start classifying our photo collection by the Omnipresent Siren Metric!

## Calculating proximity: the naive approach

A simple way to calculate nearby Starbucks count for each photo is to find the distance to each Starbucks, then filter out distant Starbucks in the `WHERE` clause:

```sql
SELECT
    filename,
    COUNT(0) AS nearby_starbucks_count
FROM (
    SELECT
        filename,
        photo_lat,
        photo_lon,
        starbucks_lat,
        starbucks_lon,
        distance(photo_lat, photo_lon, starbucks_lat, starbucks_lon) as dist
    FROM (
        SELECT
            filename,
            latitude as photo_lat,
            longitude as photo_lon
        FROM photo_attributes
    ) p
    JOIN (
        SELECT
            latitude as starbucks_lat,
            longitude as starbucks_lon
        FROM starbucks_directory
    ) s ON true
) t
WHERE t.dist < 2000 -- distance is in meters, in this case a 2km radius
GROUP BY 1
ORDER BY 2
```

One of the great advantages of using an SQL database is that we can ask how a query will be executed with the `EXPLAIN SELECT ...` statement.
The logical plan for this query is:

```
Sort: nearby_starbucks_count ASC NULLS LAST
  Projection: t.filename, COUNT(Int64(0)) AS nearby_starbucks_count
    Aggregate: groupBy=[[t.filename]], aggr=[[COUNT(Int64(0))]]
      Projection: t.filename, alias=t
        Projection: p.filename, alias=t
          Filter: distance(p.photo_lat, p.photo_lon, s.starbucks_lat, s.starbucks_lon) < Float64(2000)
            CrossJoin:
              Projection: p.filename, p.photo_lat, p.photo_lon, alias=p
                Projection: photo_attributes.filename, photo_attributes.latitude AS photo_lat, photo_attributes.longitude AS photo_lon, alias=p
                  TableScan: photo_attributes projection=[filename, latitude, longitude]
              Projection: s.starbucks_lat, s.starbucks_lon, alias=s
                Projection: starbucks_directory.latitude AS starbucks_lat, starbucks_directory.longitude AS starbucks_lon, alias=s
                  TableScan: starbucks_directory projection=[latitude, longitude]
```

Notice the `CrossJoin` (also called Cartesian product)! It's the type of join between two tables where every row in table A is joined with every row in table B.
The resulting table will have a total row count of `rows in A * rows in B`.
The `distance()` function is pretty fast: a single call returns in 5-6 microseconds on my laptop. Even so, calculating all `25600 * 21808 = 558284800` distances took me about an hour.
Can we do better?

## Calculating proximity: using pre-filtered pairs

Calculating the precise distance between two points on a globe is more complex than on a plane.
Still, we can use a rough heuristic to filter out photo - Starbucks pairs which are obviously much farther away than 2 km.
At the equator, one degree latitude / longitude corresponds to 111km. I've never been so close to the poles that 2 degrees would be less than 2km apart, so any Starbucks
more than two degrees latitude or longitude away from the photo's location will surely be out of the 2km range.

### Pre-filtering pairs
First, I created a table of candidate pairs based on differences in latitude and longitude:

```sql
CREATE TABLE filtered_pairs_less_than_2_degrees_apart AS WITH
    all_pairings AS (
        SELECT
                filename,
                photo_lat,
                photo_lon,
                starbucks_lat,
                starbucks_lon,
                ABS(photo_lat - starbucks_lat) AS diff_lat,
                ABS(photo_lon - starbucks_lon) AS diff_lon
        FROM (
            SELECT
                filename,
                photo_lat,
                photo_lon,
                starbucks_lat,
                starbucks_lon
            FROM (
                SELECT
                    filename,
                    latitude as photo_lat,
                    longitude as photo_lon
                FROM photo_attributes
            ) p
            JOIN (
                SELECT
                    latitude as starbucks_lat,
                    longitude as starbucks_lon
                FROM starbucks_directory
            ) s ON true) r
    )
SELECT *
FROM all_pairings
-- A difference of 2 degrees or more latitude or longitude will definitely
-- result in more than 2 kilometers between the points in the range we're
-- working with.
WHERE diff_lat < 2.0 AND diff_lon < 2.0;
```

The `filtered_pairs_less_than_2_degrees_apart` table has 687911 rows, less than 1% of the rows resulting from the cross join in the previous query.

### Calculating the distance of candidate pairs
The following query filters the pairs further based on the precise distance:

```sql
CREATE TABLE filtered_pairs_less_than_2_km_apart AS SELECT * FROM (
    SELECT
        filename,
        photo_lat,
        photo_lon,
        starbucks_lat,
        starbucks_lon,
        distance(photo_lat, photo_lon, starbucks_lat, starbucks_lon) as dist
    FROM filtered_pairs_less_than_2_degrees_apart) t
-- Filter the list according to the actual distance in meters, so
-- < 2000 results in a 2km radius.
WHERE t.dist < 2000;
```

This second filter throws out over 94% of the rows in the `filtered_pairs_less_than_2_degrees_apart` table, leaving only
Starbucks locations within the 2km radius of each photograph.

The `filtered_pairs_less_than_2_km_apart` table contains 39715 rows, the same number we got with the original hour-long calculation, but the two
`CREATE TABLE` queries take a total of 15.1 seconds, less than 1% of the original runtime.

## My most urban photograph

There's a clear winner among the 21808 contestants for "most urban" photo, with an OSM of 68:

![Sheep in Shanghai](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/IMG_6092.JPG)

Directing the [Starbucks Store Locator](https://www.starbucks.com/store-locator?map=31.232686,121.460572,15z) to the coordinates where the photo was captured helps explain why.
Considering Shanghai has the most Starbucks locations of any city on Earth, more than double that of Seoul at second place, it's not surprising that my "winning" photo was taken there.

## How urban am I

To find out, I needed the number of photographs for each OSM score:

```sql
-- query.sql
SELECT
    nearby_starbucks_count,
    COUNT(0) as num_photos
FROM (
    SELECT
        filename,
        COUNT(0) AS nearby_starbucks_count
    FROM filtered_pairs_less_than_2_km_apart
    GROUP BY 1
) t
GROUP BY 1
ORDER BY 1 ASC;
```

I used the following bash command to get the results of the query as CSV with the help of `jq`:

```bash
curl  -H "Content-Type: application/json" http://localhost:8080/q -d@- << END |
{"query": $(cat "query.sql" | jq -Rsa .)}
END
jq -r '[.nearby_starbucks_count, .num_photos] | @csv' > photos_by_osm.csv
```

Using the [Node.js Seafowl client](https://github.com/splitgraph/seafowl/blob/main/examples/clients/node/seafowl-client.js) is also an option:

```bash
seafowl/examples/clients/node/seafowl-client.js -f filtered_step3_simplified_osm_histogram.sql | tail -n +2 | jq -r '.[] | [.nearby_starbucks_count, .num_photos]' | @csv > photos_by_osm.csv'
```

With most of my photos having an OSM of 0, the answer seems to be that I'm not very urban, especially when compared to someone who feels at home in Shanghai:

![Number of photos by OSM](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/number_of_photos_by_osm.svg)

## Urbanness over time

If "urbanness" is an innate quality unlikely to change much once our adult personality has developed, any reliable indicator should be pretty stable over time.
Looking at the number of photos I've taken with OSM ≥ 1 each month makes it seem like that is indeed the case.

![OSM ≥ 1 photo count by month](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/osm_gte_1_photo_count_by_month.svg)

The exception is a trip to Shanghai in October of 2015 when I took the picture with the plastic sheep shown above.

## Conclusion

It was fun analyzing where I've taken photos over the last decade. Seafowl has been a convenient tool throughout the process:
- CSV import made it easy to work with data from [Kaggle](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download) and Google Photos.
- We can easily extend Seafowl's builtin functions with [our own UDFs](https://github.com/splitgraph/seafowl-udf-geo).
- JSON output is convenient in most cases and can easily be converted to CSV using `jq` when necessary.

I look forward to all the cool stuff people build with this fast new database!


I used Seafowl to analyze how much of a city slicker I am—with geographic user-defined WASM functions and caffeine addiction!

Deciding if I'm urban with WebAssembly and Seafowl


## Do we really need User-Defined Functions?

User-defined functions (UDFs) are bits of application-specific logic which can be invoked within database queries, extending the builtin capabilities of the database.
You might be wondering why not keep application-specific logic in the _application_ layer? Are user-defined functions (UDFs) the relic of a past when databases were designed to be directly queried by end-users?

Far from it! Placing domain-specific logic in the database is often the best (sometimes only) way to implement functionality such as:
- Security policies such as PostgreSQL's [RLS feature](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), which rely on UDFs to enforce application-specific security policies.
- Custom aggregation functions similar to `MIN`, `MAX` and `AVG` which must run within the database to avoid shipping the entire set of input rows to the client to calculate output.
- General, application-independent domain specific capabilities such as working with JSON fields, calculating geographical distances or full text search, extending the core DB's capabilities without having to modify the database itself.
- Finally, if raw and cleaned data live in the same database, it makes sense to use the database to transform that data with tools like [dbt](https://www.getdbt.com/) instead of passing it through a separate process -- and UDFs might be needed to implement some special functionality for that. [^1]

UDFs are an essential building block supported by the majority of databases one way or another, and in this regard Seafowl is no exception. What makes Seafowl special is its commitment to running "at the edge": as close to the user as possible, which means pretty much anywhere.
But if Seafowl is to run _anywhere_, UDFs referenced by Seafowl queries should be just as portable.
How can Seafowl offer UDF authors a platform which makes this possible?

## Platforms

Like any program, a UDF targets a specific platform. This platform should be hardware-independent so UDFs keep working if the database is moved to a different machine, but also efficient enough to process large datasets in acceptable time.

Mature databases often have a custom language for defining UDFs such as [PostgreSQL's PL/PGSQL](https://www.postgresql.org/docs/current/plpgsql.html) or [PL/SQL](https://www.oracle.com/database/technologies/appdev/plsql.html).
For simple functions just beyond the capabilities of an SQL statement, these languages offer an ideal solution.
For more complex functionality or code with lots of dependencies, many developers choose a general programming language, preferably one used in the application layer.

PostgreSQL and Redis both support authoring UDFs in Lua, for example. As a high-level language,
Lua abstracts away the lower layers of the stack such as the CPU's instruction set or the flavor of the C standard library.
Instead, the platform becomes the Lua virtual machine and standard library.
Lua is designed to be embedded in the host process, allowing scripts to efficiently invoke native functions exposed by the host.
It isn't the only scripting language embedded in databases. PostgreSQL for example supports many scripting languages including javascript and python.
While the source of the UDF may be portable, Lua, Javascript, Python, Ruby rely on native modules typically written in C or C++ for performance-intensive code such as crypto libraries.
These modules are co-located with the database and must be recompiled for each architecture supported by the database, limiting the portability of UDFs using these functions.

Custom logic written in compiled languages such as C++, Rust or Java is more difficult to invoke from a native database process, requiring
either running the UDF in a separate process or compiling against the C ABI of the database and executing it with full access to the database.
The security implications of the second option rule out this approach for UDFs.

Inter-process communication (IPC) with a separate JVM process provides a portable solution supported by [Oracle](https://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm) and [IBM DB2](https://www.ibm.com/docs/en/db2-for-zos/11?topic=java-stored-procedures-user-defined-functions),
but introduces a performance penalty for each UDF invocation along with additional corner cases arising from the need to synchronize a JVM process with the database.
For example, should updating the Java bytecode for a UDF impact running transactions?
If not, how many versions of the UDF must be kept in memory and how should the database keep track of which is the current version in each transaction?
When is it safe to remove classes upon which a previous version of the UDF depends?
If a transaction defining a UDF is rolled back, which classes' bytecode needs to be ejected from the JVM?
The list goes on and on, and it's mostly independent of Java, stemming instead from the marriage of a transactional database and another process which dynamically loads UDF code.

To summarize, there's hardly a silver bullet: each solution for providing UDFs has its pros and cons.
Worse, none of the solutions considered so far are truly portable in the sense that they can be used in the browser, on dedicated servers and desktops without serious limitations.

## Enter WebAssembly

Predating Seafowl and Splitgraph by decades, developers of large native applications faced a similar conundrum.
As new devices and platforms appeared, customers wanted to run their applications in previously unsupported environments.
Porting existing applications to new platforms was the traditional remedy, a slow and expensive process.

In the 90s, Java tried to address this problem, promising developers they could "write once, run everywhere".
As Java eventually found success in the very different niches of backend services and Android devices,
its early focus on "running everywhere" understandably changed to working best where it's used.

With the explosion of web apps, the browser appeared as yet another platform for applications to support, albeit a highly unusual one:
instead of a CPU instruction set, developers were to target a high level language, JavaScript.
Garbage collection came with the package, but integers were out (JavaScript has only floating point numbers).
Being so different from other platforms, porting existing applications to the web seemed more difficult than a complete rewrite in many cases.

Even green field projects struggled to find a stack sufficiently portable and efficient to ensure the application's long-term growth in desktop, mobile and browser environments.

As the performance of computers in general and JavaScript engines in particular improved, it became feasible to emulate a CPU in JavaScript.
A single C/C++ codebase could now be compiled to run natively on desktops or mobile devices, but also run in the browser with [emscripten](https://emscripten.org/), which targeted the javascript-powered CPU emulator.
It was hacky, but it worked and the world started to [take notice](https://kripken.github.io/mloc_emscripten_talk/#/).

With growing demand for efficiently running compiled code in the browser, Chrome, Firefox and Safari started to include new virtual machine called WebAssembly (WASM), very different from the high-level javascript VM:
- It wasn't optimized for a specific programming language like V8 or the JVM.
- It doesn't support sophisticated just-in-time compilation.
- It doesn't even have a garbage collector.

Instead, the WebAssembly specification provides a limited set of low-level instructions and linear memory model.
Thanks to the relative ease of implementation, today there are [many](https://wasmer.io/) [high](https://wasmedge.org/) [quality](https://github.com/wasm3/wasm3) [implementations](https://wasmtime.dev/), allowing code targeting WebAssembly to run
anywhere from embedded devices to [browsers](https://developer.mozilla.org/en-US/docs/WebAssembly), [CDNs](https://blog.cloudflare.com/announcing-wasi-on-workers/), and yes- in [databases](https://seafowl.io/docs/guides/custom-udf-wasm) as well.

## WebAssembly as a platform for UDFs

How does WebAssembly as a platform for implementing UDFs compare to scripting languages like Lua or more traditional compiled solutions like Java or C?
Since WebAssembly is designed to be used in very diverse environments, it cannot afford to make many assumptions:
- Memory is a sequence of bytes: there is no support for typed memory.
- As mentioned earlier, there is no garbage collector.
- WebAssembly code has no access to host process resources such as the filesystem, `stdout` or environment variables unless explicitly provided via functions that the WASM module can call at runtime.
- Catering to multi-tenant scenarios, hosts have find-grained control over how much computational power they grant a WASM function.
- The builtin types are limited to 32 and 64 bit floating point and integer numbers, bytes and booleans.

That last point presents a significant challenge when using WebAssembly for authoring UDFs. Many of the [types](https://seafowl.io/docs/reference/types) supported by Seafowl such as `TEXT` and `DECIMAL`
cannot be simply represented solely by scalar numeric values.

One option is to limit UDFs to consuming and returning types natively supported by WebAssembly (this is [ScyllaDB's approach](https://www.scylladb.com/2022/04/14/wasmtime/)).

For Seafowl's UDFs, we wanted users to be able to work with any datatype including strings, decimal numbers and dates.

In WebAssembly, memory is just a sequence of bytes and pointers are just 32 bit integers, similar to an array index.
Any object which can be serialized to a byte stream can be passed by the host to a WebAssembly function by first writing the serialized bytes to WASM memory,
then invoking the function with a pointer to the freshly written memory address as its argument.
Similarly, the WASM function can serialize it's output as a byte stream to which it returns a pointer.

Seafowl uses [MessagePack](https://msgpack.org/index.html) for serialization - think of it like a faster binary version of JSON.

## We're just getting started

Today, Seafowl supports creating UDFs in any programming language which compiles to WASM. Check out our [Rust example UDF repository](https://github.com/splitgraph/seafowl-udf-rust)! Any Seafowl type may be used for input values and return type.

One of the biggest advantages of using UDFs is aggregated records _within the database_. As of 2022 December, Seafowl does not yet support user-defined aggregation functions, but this support is planned. When it does arrive, you can be sure it will also use WebAssembly!

[^1]: There's an interesting discussion on this approach in this [Hacker News thread](https://news.ycombinator.com/item?id=33874842).


How can Seafowl users extend the database's builtin capabilities in a safe, portable and efficient way? The answer is WebAssembly, read on to learn how!

Extending Seafowl with WebAssembly


The primary specialty of Seafowl is data-crunching over large slowly-changing data sets at the edge. It does so by leveraging the
standard HTTP cache semantics [natively](/blog/seafowl-sql-cdn) in the provided query execution endpoint(s).

The query result then becomes a regular static HTTP asset, eligible for caching at various locations (from the browser to the CDN), thus
saving on the compute time. Seafowl also utilizes the usual HTTP cache revalidation mechanism, so that when the underlying data
does change, the query is re-run in a performant manner (thanks to Rust and [Apache DataFusion](https://arrow.apache.org/datafusion/)).
In a sense, it can be said that Seafowl is a fast, lightweight and self-contained version of the [Splitgraph DDN](/blog/data-delivery-network-launch)

Despite being an analytical query engine at it's core, Seafowl (like the DDN) also supports writing data using DDL
(`CREATE`, `ALTER` and `DROP`) as well as DML (`INSERT`, `UPDATE` and `DELETE`) SQL statements. In this blog post we'll
take a look at the interplay between such statements and the way Seafowl chunks and stores the data in partition objects.
We'll also describe how table versioning and query time travel concepts naturally arise in this context.

## Table version and partition dynamics

To start off, let's create a new table with some dummy data after [spinning up](/seafowl/docs/guides/installing) a Seafowl instance.
There are a number of options for getting data into Seafowl, such as pointing a table to a source table from a
[remote database](/seafowl/docs/guides/remote-tables), or by pointing a table to a [local or remote](/seafowl/docs/reference/ddl-queries#create-external-table)
CSV/Parquet file.

For this example though let's use the [upload endpoint](/seafowl/docs/guides/uploading-csv-parquet). It is intended
for when you can't or don't want to place your data file on the same machine where Seafowl is running, nor spin up
a one-off server for serving it over HTTP:

```bash
$ $ cat > some_numbers.csv << EOF
> name,number
> one,1
> two,2
> EOF
$ curl \
  -H "Authorization: Bearer write_password" \
  -F "data=@some_numbers.csv" \
  http://localhost:8080/upload/some/numbers
done
```

Even though the primary speciality of Seafowl is querying over HTTP, for the sake of conciseness we'll run the rest of the
queries in this blog post via a client. Specifically, we'll exploit the fact that Seafowl can also expose a [PostgreSQL-compatible endpoint](/seafowl/docs/guides/querying-postgresql)
and use `psql`:

```sql
seafowl=> SELECT * FROM some.numbers;
 name | number
------+--------
 one  |      1
 two  |      2
(2 rows)

seafowl=> SELECT column_name, data_type FROM information_schema.columns
seafowl-> WHERE table_name = 'numbers';
 column_name | data_type
-------------+-----------
 name        | Utf8
 number      | Int64
(2 rows)
```

No surprises there; the data was uploaded into a table and the schema got introspected since we haven't specified one[^1].
Since we've just created it, our table so far only has a single version and a single partition, which in Seafowl are stored
as Parquet files. To verify this we can utilize a couple of [system tables](/seafowl/docs/reference/database-structure#system-schema)
that Seafowl provides, providing insights into the table metadata:

```sql
seafowl=> SELECT * FROM system.table_versions;
 table_schema | table_name | table_version_id |    creation_time
--------------+------------+------------------+---------------------
 some         | numbers    |                1 | 2022-11-18 13:11:24
(1 row)

seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count FROM system.table_partitions;
 table_version_id | table_partition_id |                            object_storage_id                             | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
                1 |                  1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
(1 row)
```

Given that we haven't specified a custom object store, Seafowl defaults to using the local file system to store the objects in the `seafowl-data` folder:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu    854 Nov 18 13:11 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
```

Our table consists of only one partition file since our example data is tiny; had the uploaded file been
sufficiently large[^2], Seafowl would have partitioned it into multiple Parquet files.

#### Modifying the data with the `INSERT` command

When it comes to adding new data via an `INSERT` statement Seafowl will create new partition(s) from the input, and link-up
with existing partitions of the referenced table to make a new version of it. Hence, each separate invocation generates at least one
partition[^3]:

```sql
seafowl=> INSERT INTO some.numbers VALUES ('three', 3), ('four', 4), ('five', 5);
...
seafowl=> INSERT INTO some.numbers VALUES ('one', 1), ('two', 2);
seafowl=> SELECT * FROM some.numbers;
 name  | number
-------+--------
 one   |      1
 two   |      2
 one   |      1
 two   |      2
 three |      3
 four  |      4
 five  |      5
(7 rows)
```

The combined effect of the preceding two `INSERT` statements is the creation of two new table versions (2 and 3). Each one
inherits all the partitions from the previous version:

```sql
seafowl=> SELECT * FROM system.table_versions;
 table_schema | table_name | table_version_id |    creation_time
--------------+------------+------------------+---------------------
 some         | numbers    |                1 | 2022-11-18 13:11:24
 some         | numbers    |                2 | 2022-11-18 13:12:58
 some         | numbers    |                3 | 2022-11-18 13:13:35
(3 rows)

seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count  FROM system.table_partitions;
 table_version_id | table_partition_id |                            object_storage_id                             | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
                1 |                  1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
                2 |                  1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
                2 |                  2 | b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet |         3
                3 |                  1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
                3 |                  2 | b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet |         3
                3 |                  3 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
(6 rows)
```

However, note that the last `INSERT` added the same data that was used to create the table initially via the upload endpoint.
Therefore, even though our table now has three logical partitions, two of them (`table_partition_id` 1 and 3) are
physically identical, pointing to the same file:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu    854 Nov 18 13:13 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
-rw------- 1 ubuntu ubuntu    883 Nov 18 13:12 b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet
```

This example illustrates how this partitioning scheme naturally lends itself to supporting time travel querying that we'll discuss shortly.
In particular, what makes this proposition very appealing is that by storing the objects for the latest/current table version,
we're also able to re-construct prior table versions with minimal additional storage overhead due to partition re-use.
In this particular example three different table versions can be obtained from just two objects.

#### Modifying the data with the `UPDATE` and `DELETE` command

Unlike `INSERT`, the two remaining DML statements will in general replace some of the existing partitions with new ones.
The specifics depend on the exact qualifier used in the `WHERE` clause, as well as the statement type:

```sql
seafowl=> DELETE FROM some.numbers WHERE number = 3 OR number = 5;
...
seafowl=> UPDATE some.numbers SET name = 'two', number = 2 WHERE number = 1;
seafowl=> SELECT * FROM some.numbers;
 name | number
------+--------
 four |      4
 two  |      2
 two  |      2
 two  |      2
 two  |      2
(5 rows)

seafowl=> SELECT * FROM system.table_versions where table_version_id > 3;
 table_schema | table_name | table_version_id |    creation_time
--------------+------------+------------------+---------------------
 some         | numbers    |                4 | 2022-11-18 13:15:18
 some         | numbers    |                5 | 2022-11-18 13:15:53
(2 rows)
```

Here, the `DELETE` statement specifically targets the 2nd partition (created by the first `INSERT` statement), while leaving the
other two partitions (which are actually identical) unchanged and eligible for re-use. The targeted partition is processed, and the rows matching
the qualifier selection are filtered out, thus leading to a new, in this case smaller, partition (or no partition at all if all rows are filtered out).
When there are multiple partitions that match the selection, for the sake of optimal partition size, we bundle them together, and then perform
required row filtration followed by potential re-chunking of partitions.

The `UPDATE` on the other hand leaves the total number of rows unchanged, but is also subject to merging of partitions
that need to be processed. In the above case, `UPDATE` targets the two "twin" partitions, changes two out of four rows,
and merges the result in one final partition:

```sql
seafowl=> SELECT table_version_id, table_partition_id, object_storage_id, row_count
seafowl-> FROM system.table_partitions WHERE table_version_id > 3;
 table_version_id | table_partition_id |                            object_storage_id                             | row_count
------------------+--------------------+--------------------------------------------------------------------------+-----------
                4 |                  1 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
                4 |                  3 | 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet |         2
                4 |                  4 | 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet |         1
                5 |                  4 | 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet |         1
                5 |                  5 | da56d46a18b13f3d67c2816f107a837d41c760c8d67afcc015c334375f5a5e87.parquet |         4
(5 rows)
```

As a final result we now have a total of four partition objects:

```bash
$ ls -l seafowl-data/ | grep .parquet
-rw------- 1 ubuntu ubuntu    854 Nov 18 13:13 073cfac570ff05e6b0c23670dff787b2d8e15399af4654715ce078f1d0ad53ef.parquet
-rw------- 1 ubuntu ubuntu    844 Nov 18 13:15 78ae77c39b4eac1a80d7238af473af82a8038b41aebb908c890dd78323a48d65.parquet
-rw------- 1 ubuntu ubuntu    883 Nov 18 13:12 b78bb0a0336ed215c3c0cbb0451f676ced19f3987f3c5519e776507ef832dfd8.parquet
-rw------- 1 ubuntu ubuntu    835 Nov 18 13:15 da56d46a18b13f3d67c2816f107a837d41c760c8d67afcc015c334375f5a5e87.parquet
```

This partition-scoping mechanism for `UPDATE`/`DELETE` ultimately builds on top of [partition pruning](/seafowl/docs/learning/partition-pruning)
that Seafowl uses for reducing the number of objects needed for scanning in response to usual `SELECT` queries with filtration. In turn, partition pruning
relies on simple statistics gathered during partition generation, such as max/min value for each column,
as well as the presence/count of null values. Consequently, if a particular column can't/doesn't have such statistics collected
the partition pruning doesn't work, and all table partitions need to be processed.

## Table time travel

Having demonstrated how older table versions can be re-constructed from the constituent objects, let's discuss how that looks in practice. First, a
bit of background on the way table resolution works in general.

Each query invocation is accompanied by a fresh Seafowl context, wrapping the inner DataFusion context itself. The wrapped
context in turn contains the nested maps of all databases (also referred to as catalogs) -> schemas -> tables, so that it can always determine
which particular table implementations are needed for executing the given query. By default all latest table versions at the
moment of query invocation are populated in the nested maps.

#### Temporal querying syntax

To support time travel queries, we opted for (ab)using the table function syntax[^4], whereby the target table reference
can have a timestamp value specified inside parenthesis. For instance to go back and inspect the initial table contents
or the contents after the two `INSERT`'s (versions 1 and 3, respectively) we can use:

```sql
seafowl=> SELECT * FROM some.numbers('2022-11-18 13:13:35');
 name  | number
-------+--------
 one   |      1
 two   |      2
 three |      3
 four  |      4
 five  |      5
 one   |      1
 two   |      2
(7 rows)

seafowl=> SELECT * FROM some.numbers('2022-11-18 13:11:24');
 name | number
------+--------
 one  |      1
 two  |      2
(2 rows)
```

This works by Seafowl doing an initial walk over the query AST during the logical planning phase, and recording
the presence (and value) of temporal versions used in the query. If none are found, then the query proceeds as usual,
with the default context table map unchanged.

On the other hand, if Seafowl detects that time travel querying is used for some table(s) it then performs triage for the exact `table_version_id`
required. Once it has that information it will rewrite the affected table names in the AST to a unique one using the specific `table_version_id` fetched.

Finally, Seafowl updates the context table mapping for each table version specified with the unique name that was used in the AST rewrite.
Note that this only involves fetching the table metadata, such as which particular partitions correspond to a given `table_version_id`.
Loading of the partitions themselves is only done during the scan phase, once all the optimizations have been performed,
and we have the table filters handy for partition pruning.

## Future work

There are a couple of limitations in Seafowl time travel implementation that we aim to improve down the line. One such example
is lack of support for using time travel in write queries, as this mechanism is only supported for `SELECT` queries at present.

Another would be adding more flexibility to our existing time travel syntax, for instance by adding relative time
(e.g. table version from exactly 2 days ago) and version specification support (e.g. table version 2 versions prior to the latest one).
Alternatively we may also eventually align it with the `AS OF` syntax standard that is gaining more traction in the industry.

Nonetheless, the present mechanism is quite robust and versatile, capable of supporting advanced queries, such as CTEs and table JOINs
that use multiple different versions of the same table at once.

[^1]:
    For CSV file uploads you can also explicitly specify a desired schema. Parquet files have an embedded schema so this is not required.
[^2]:
    The threshold size for partition chunking is currently controlled via the
    [`max_partition_size`](/seafowl/docs/reference/seafowl-toml-configuration#max_partition_size) config parameter.
[^3]:
    Since Seafowl usually assumes relatively infrequent and large writes this shouldn't lead to suboptimal partition sizes.
    However, if that happens to be the case one can always force re-partitioning of an entire table using `CREATE TABLE AS` statement.
[^4]:
    Temporal table version selection syntax has been part of the ANSI SQL [since 2011](https://en.wikipedia.org/wiki/SQL:2011),
    but in our case it was technically simpler to go with the table function syntax.


We discuss how Seafowl performs table partitioning to enable efficient versioning and time travel queries

Table partitioning and time travel queries: Seafowl case study


## Introduction

**[Seafowl](https://seafowl.io)** is a database for **analytics at the edge**:
data-driven Web visualizations, dashboards and notebooks that require
low-latency execution of analytical queries.

To put it plainly, Seafowl lets you stop building these kinds of custom APIs for
every visualization in your application:

```bash
curl https://custom-api.myproject.io/basic_summary?country_of_import=BRAZIL&commodity=COFFEE&calculate=country_of_production,avg_volume,total_entries
```

and execute plain SQL queries straight from the user's browser instead:

```bash
curl https://demo.seafowl.io/q \
  -XPOST -H "Content-Type: application/json" \
  -d@- <<EOF
{"query": "SELECT \
    country_of_production, \
    AVG(volume) AS avg_volume, \
    COUNT(*) AS total_entries \
FROM supply_chains \
WHERE country_of_import = 'BRAZIL' \
  AND commodity = 'COFFEE' \
GROUP BY 1 ORDER BY 2 DESC"}
EOF
```

The key feature of Seafowl is being **CDN- and HTTP-cache friendly**. Seafowl's
SQL query results can be cached by CDNs like Cloudflare or HTTP caches like
Varnish (as well as the end user's browser cache).

This decreases the latency of applications that are powered by Seafowl, as well
as the resource requirements for running a Seafowl instance.

## Try it out

You can run Seafowl on any infrastructure provider. For this example, we have a
sample Seafowl deployment running on [Fly.io](https://fly.io) behind Cloudflare:

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-hit.png)

This is an Observable notebook backed by a 3M row dataset served by this
deployment (thanks to [Trase](https://trase.earth) for the data):

<iframe
  width="100%"
  height="900"
  frameBorder="0"
  src={`https://observablehq.com/embed/@seafowl/interactive-visualization-demo?cells=viewof+productionCountry,viewof+importCountry,viewof+commodity,topExportersL,topExportersP,volumeByYearL,volumeByYearP,allImportsL,allImportsP`}
/>

Because you loaded this page, the result for the aggregation queries that
produced these visualizations is already cached in your browser. On first load,
it should have also been served from Cloudflare's edge cache.

Press F12 to go to your browser's Developer Tools and go to the Network tab. If
you refresh this page now, you should see HTTP requests to
`https://demo.seafowl.io` being cached by your browser.

![](/static-v2/seafowl/tutorial/observable/observable-network-cached.png)

You can also check "Disable Cache" and refresh the page again: you should see
requests going directly to the source. If you inspect one of them, it should
have a `CF-Cache-Status: HIT` header, meaning it was cached by Cloudflare.

![](/static-v2/seafowl/tutorial/observable/observable-cdn-cached.png)

So, how does this work? Let's dive in![^1]

## Query results are static assets

HTTP has provisions for caching **static assets**. If we can make our query
results behave like other assets (images, JavaScript, documents), we can benefit
from caching as well.

How do caches handle static assets? They:

- use the URL of the asset as part of the cache key
- usually only cache GET requests
- cache based on the MIME type or the
  [file extension (Cloudflare)](https://developers.cloudflare.com/cache/about/default-cache-behavior/)

Seafowl is designed to power Web applications backed by slowly-changing data.
This means we can make some assumptions that make this easier to build.

In particular, we can assume that the result of a single SQL query is
**identified by the text of the SQL query** (what if the query result changes
because the source data changed? More on that later).

This means also assuming that the original SQL query doesn't contain
**volatile** functions (like `random()`).

## Let's put the query in the URL!

A first approximation of getting this to work is putting the query text into the
URL. We can URI-encode special characters in the query to make it a valid URL.
However, this has a couple of drawbacks.

First, we can hit URL length limits. These vary between browsers and HTTP
intermediaries (like CDNs). The limit can be anywhere from 2KB to 64KB
([source](https://stackoverflow.com/a/417184)), depending on the exact
implementation.

It also looks suspicious. A SQL query inside of the URL looks too much like an
attempt at SQL injection. We haven't explicitly confirmed it, but it could be
something a Web application firewall or an intrusion detection system would
flag.

## Let's put something else in the URL?

How about we use a string that changes with the query but isn't as long as the
query itself? Something like a SHA-256 hash of the query?

This raises another question: how does the server now recover the original query
to actually execute it? There are two ways we can do this.

We can pass the original query as a **GET request body**. This is unusual but is
sometimes used by servers like ElasticSearch.
[RFC7231](https://www.rfc-editor.org/rfc/rfc7231#section-4.3.1) doesn't
explicitly forbid it, but does warn against using it:

> A payload within a GET request message has no defined semantics; sending a
> payload body on a GET request might cause some existing implementations to
> reject the request.

Unfortunately, it's also unsupported by some clients that can reject or quietly
drop the GET body, to the point where the Fetch standard explicitly disallows it
(see the discussion in the
[GitHub issue](https://github.com/whatwg/fetch/issues/551)).

This leaves us with the other option: **using an HTTP header**. We can pass the
original query as the header, percent-encoding it as well. HTTP headers support
some characters that would normally have to be encoded in a URL, like spaces.
This means that we only need to encode non-ASCII characters and newlines,
decreasing the length of the query (HTTP headers have length limits too!).

Seafowl supports both of these methods. For example, with the HTTP header:

```bash
curl -H "X-Seafowl-Query: SELECT 'Hello, Seafowl'" \
https://demo.seafowl.io/q/7468d08fa2c3a2dc59905511dc478784a550e2d4ad67d101802a6411099d690f

{"Utf8(\"Hello, Seafowl\")":"Hello, Seafowl"}
```

You can also bypass the cached GET API altogether and execute queries with POST
requests instead:

```bash
curl https://demo.seafowl.io/q \
  -XPOST -H "Content-Type: application/json" \
  -d@- <<EOF
{"query": "SELECT 'Hello, Seafowl'"}
EOF
```

### Client code

Instead of making a plain HTTP request, the client does have to do extra work by
hashing the query and URL-encoding it. This, however, is very straightforward to
do.

We have a sample
[Observable client](https://observablehq.com/@seafowl/client#querySeafowl) that
implements the
[DatabaseClient specification](https://observablehq.com/@observablehq/database-client-specification).

You can also use `curl` and Bash:

```bash
#!/bin/bash -e
SEAFOWL_HOST = "http://localhost:8080"
query=$1
hash=$(echo -n "$query" | sha256sum | cut -f 1 -d " ")

curl -v\
 -H "X-Seafowl-Query: ${query}" \
 "$SEAFOWL_HOST/q/$hash"
```

Or
[Node.js](https://github.com/splitgraph/seafowl/blob/main/examples/clients/node/seafowl-client.js),
or
[run JavaScript from the browser](https://seafowl.io/docs/guides/querying-cache-cdn#querying-from-the-browser-using-the-fetch-api).

### Do we need to normalize the query?

You'll notice that multiple queries with the same semantic meaning but different
text (for example, with different formatting) will hash to different values,
resulting in a cache miss.

This is not that big of a deal. If you control the application that queries
Seafowl, you also control the shape of SQL queries it will make and will be able
to minimize cache misses. You can also use the changes in the formatting (or add
comments to your SQL query) as an "escape hatch" to bust the cache and force a
query to re-execute.

## What about invalidation?

What happens when the source dataset changes and we need to update the query
result? Luckily, the HTTP standard has provisions for that: it supports
["Entity tags", or ETags](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/ETag).

An ETag is something an HTTP server can attach to a response, denoting the
version of the resource that has been served. Clients (including HTTP caches and
CDNs) can then use this ETag to make a **conditional request**:

- give the ETag back to the server as an `If-None-Match` header
- the server will return the resource only if the version provided by the server
  is newer than the provided
- otherwise, it will return a `304 Not Modified` HTTP response ETag.

Seafowl keeps track of the latest version of each table. When you write data to
Seafowl, it bumps the table version. It then uses the version numbers of all
tables participating in a query to compute the ETag, which it attaches to all
responses to its cached GET API.

## Putting it all together

Let's now take a look at how this works with Seafowl running behind a CDN. In
this case, we're using our demo deployment running on Fly.io behind Cloudflare,
but you can use any other CDN or an
[HTTP cache like Varnish](https://github.com/splitgraph/seafowl/tree/main/examples/varnish),
or no CDN at all and rely on your user's browser cache.

### Request 1: cache miss

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-initial-miss.png)

When a browser requests a query from Seafowl's GET API for the first time, it's
uncached by Cloudflare.

Cloudflare forwards the query to the origin (Seafowl). Seafowl executes it and
computes an **ETag** for the latest version of all tables that participated in
the query.

Cloudflare caches the ETag and the response and forwards it to the client. The
client's browser caches the response as well.

We do a small hack here: instead of requesting the query hash, we also append a
`.csv` extension to the URL. This is because by default, Cloudflare only
[caches certain extensions](https://developers.cloudflare.com/cache/about/default-cache-behavior/)
and this tricks it into caching our response. The extension is discarded by
Seafowl (it always returns responses in the JSON Lines format).

### Request 2: cache hit

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-hit.png)

If another browser (or the same browser with the browser cache disabled)
requests the same query within a certain time window (a few hours), Cloudflare
won't forward it to the Seafowl origin. Instead, it serves it from its own
cache. We can see this happened because Cloudflare returns a
`CF-Cache-Status: HIT` header.

### Request 3: cache revalidation

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-revalidation.png)

Let's now say some time has passed since the original query and the CDN needs to
**revalidate** the response to make sure it's still up to date.

Cloudflare makes a **conditional** HTTP request with the `If-None-Match` header
set to its stored ETag. Seafowl inspects the inbound ETag in the `If-None-Match`
header, calculates the current ETag for a given query and compares them.

This operation is much more lightweight than actually executing the query (where
Seafowl would need to potentially download and scan through all
[partitions](/seafowl/docs/learning/storage)).

In this case, the source dataset hasn't changed and Seafowl responds with a
`304 Not Modified` HTTP response (without the query result).

Cloudflare returns the cached response back to the user with a
`CF-Cache-Status: REVALIDATED` header to note that it has revalidated the
response and it's still up to date.

### Request 4: cache invalidation

![](/static-v2/seafowl/blog/seafowl-sql-cdn/cache-invalidation.png)

Finally, let's say the source data has been updated. The user's browser makes
the same request to Cloudflare and Cloudflare makes the same conditional request
to Seafowl with a stale ETag.

In this case, Seafowl's computed ETag will be different from the one passed to
it by Cloudflare. That is, the response has **expired**. Seafowl re-executes the
query and returns the new result to Cloudflare, with a new ETag.

Cloudflare caches the new response and the new ETag. Finally, it forwards the
response to the user's browser with a `CF-Cache-Status: EXPIRED` header to note
that the response was stale and got reloaded from the origin.

## Conclusion

And that's it! With a few tricks we made our HTTP API cache-friendly. Any HTTP
cache or a CDN can now cache Seafowl query results and invalidate them when the
source data changes. This makes applications powered by Seafowl respond faster
and require less resources.

If you're interested in learning more about Seafowl, feel free to check out our
[Observable demo](https://observablehq.com/@seafowl/interactive-visualization-demo),
go through our [quickstart](/seafowl/docs/getting-started/quickstart) or through
a longer [tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction)
in which you'll deploy Seafowl to Fly.io, put it behind Cloudflare or Varnish
and reproduce our Observable demo notebook.

## Other questions

### Bringing the data or the query results to the edge?

You might ask, why go through all this trouble to cache query results when we
could deploy our application at the edge instead? The key insight lies in how
analytical queries work:

- They usually reduce a big (millions/billons of rows, a few GB of data) dataset
  into a small (hundreds/thousands of rows, a few KB) summarized response
- They take hundreds of milliseconds or seconds instead of milliseconds or
  microseconds (like transactional queries would)

Let's say:

- an analytical query normally takes 500ms
- the latency to the origin is 100ms
- the latency to the edge location is 10ms

In that case, executing the query closer to the user would turn a 600ms
end-to-end latency to 510ms, which is not a noticeable improvement. It also has
to come at the expense of replicating the data and running the query engine at
the edge location[^2].

However, if a query result is cacheable, caching it at the edge instead of at
the origin would bring a 100ms response time down to 10ms.

Simply put, analytical queries normally take too much time to benefit from
moving the execution closer to the user.

### Using a non-HTTP cache?

An alternative to using HTTP for this would be implementing caching in Seafowl
itself or deploying a cache like Redis or Memcached. However, this would also
mean either requiring some scratch disk space for a Seafowl deployment to store
the query result cache or requiring Seafowl users to run caches at edge
locations.

We'd also need to write a more sophisticated client for querying Seafowl in
order to get query results cached in your application user's browser.

Using HTTP caches gives us all of this for free, including the ability to rely
on existing HTTP infrastructure.

### How does this break?

There are a few known issues with this kind of caching as we implemented it:

- Updating the definition of a
  [user-defined WASM function](/seafowl/docs/guides/custom-udf-wasm) doesn't
  invalidate the cache for queries that use those functions
  ([GitHub issue](https://github.com/splitgraph/seafowl/issues/57))
- Updating a table and then applying an opposite update still changes the table
  version ID, even though the content of the table is effectively unchanged
  ([GitHub issue](https://github.com/splitgraph/seafowl/issues/57))
- Queries that reference
  [external tables](/seafowl/docs/guides/csv-parquet-http-external) don't
  support the invalidation mechanism
- You can't currently change the Cache-Control headers sent out by Seafowl
  ([GitHub issue](https://github.com/splitgraph/seafowl/issues/56))

[^1]:
    This is a longer-form version of
    [our tutorial](/seafowl/docs/getting-started/tutorial-fly-io/part-3-cached-get-api)
    and the [documentation](/seafowl/docs/guides/querying-cache-cdn) for the
    cached GET API feature.

[^2]:
    In the extreme case, the "edge location" is the user's browser itself (see
    [DataStation](https://datastation.multiprocess.io/) or
    [this blog post](https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/)),
    which works pretty well if the query engine doesn't need to load more than a
    few MB of data (because it's on the user's machine already or because it's
    small enough to begin with).


A deep dive into how we designed Seafowl's REST API to be HTTP cache and CDN friendly, including some discussion of ETags and other HTTP cache mechanics.

(Ab)using CDNs for SQL queries


For the past few months, we've been hard at work on something interesting and
it's finally ready for people to start experimenting with.

**[Seafowl](https://seafowl.io)** is our new standalone project, inspired by the
design of modern cloud data warehouses like Snowflake and BigQuery, as well as
the lessons we learned over the past four years of building Splitgraph and the
Splitgraph DDN.

It's a **CDN and HTTP-cache friendly** database designed for **analytics at the
edge**: dashboards, notebooks, visualizations and other data-driven
read-intensive Web applications that need to be responsive while operating on
large datasets.

```bash
curl https://demo.seafowl.io/q/30dcf5da00f94e3fbd28cf98242f7de3b164a440cadfaa6f7de74369ffc2177d.csv \
  -i -XGET -H "Content-Type: application/json" \
  -d@- <<EOF
{"query": "SELECT country_of_production, COUNT(*) AS count \
FROM supply_chains \
GROUP BY 1 ORDER BY 2 DESC"}
EOF

HTTP/2 200
...
cf-cache-status: HIT

{"country_of_production":"BRAZIL","count":2386600}
{"country_of_production":"ARGENTINA","count":260293}
{"country_of_production":"INDONESIA","count":155751}
{"country_of_production":"ECUADOR","count":96842}
{"country_of_production":"PARAGUAY","count":29848}
...
```

## Fast analytics...

Seafowl is written in Rust and relies on
[Apache DataFusion](https://github.com/apache/arrow-datafusion) to plan and
execute queries. For analytical queries, it's:

- up to 10x faster than vanilla PostgreSQL
- ...or up to 4x faster than PostgreSQL with the `parquet_fdw` extension
- ...or around the level of DuckDB in performance, sometimes beating it (apart
  from some pathological queries where it has memory issues, as well as queries that use a lot of subqueries where the optimizer might not be able to build an optimal query plan)

We have some [benchmarks](https://observablehq.com/@seafowl/benchmarks) which
aren't as in-depth as TPC-DS, but do show that Seafowl makes a lot of seemingly
heavy SQL queries now feasible for powering interactive applications.

<iframe
  width="100%"
  height="400"
  frameBorder="0"
  src="https://observablehq.com/embed/@seafowl/benchmarks?cells=viewof+benchmarkRun,viewof+databases,viewof+queries,benchmarkPlot"
/>

For `SELECT` queries, Seafowl supports a large subset of the PostgreSQL dialect.
If there's something missing, you can
[write a user-defined function](/seafowl/docs/guides/custom-udf-wasm)
for Seafowl in anything that compiles to WebAssembly.

You can write data to Seafowl with:

- [standard SQL statements](/seafowl/docs/guides/writing-sql-queries)
  (including `CREATE TABLE AS` statements for future dbt support!)
- by
  [uploading a CSV or a Parquet file](/seafowl/docs/guides/uploading-csv-parquet)
- by
  [setting up a "live" table](/seafowl/docs/guides/querying-http)
  that points to a file on an HTTP server

You can also
["bake" a dataset into a Docker image](/seafowl/docs/guides/baking-dataset-docker-image)
by bundling Seafowl together with its data. This is a nifty scaling strategy for
smaller (well, up to 1GB, which can be as big as the 55M row subset of the New
York Taxi dataset we use for our benchmarks) and read-only data.

## ...at the edge

"The edge" is a handwavy and marketing-y term, but it describes what Seafowl is
all about well enough. You can deploy Seafowl to the edge or cache query results
at the edge.

Seafowl ships as a single 50MB binary that starts in 10ms, making it easily
deployable to modern serverless environments, anywhere in the world. Our own
[tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction) uses
Fly.io as an example.

Seafowl's query execution API follows HTTP cache semantics. This means you can
[put Seafowl behind a CDN](/seafowl/docs/guides/querying-cache-cdn)
like Cloudflare or a cache like Varnish and have query results cached and
delivered to your users in milliseconds.

Even without a dedicated cache, you can get the benefits of caching query
results in your user's browser (to see it in action, go to our
[demo](https://observablehq.com/@seafowl/interactive-visualization-demo), press
F12, go to the Network tab and refresh the page).

## Try it out!

Seafowl is now available to download and experiment with, though, like with all
early stage projects, it likely has plenty of bugs and missing features. As we
continue its development, we might break APIs or change storage formats in a
backwards-incompatible way.

Feel free to check out our
[Observable demo](https://observablehq.com/@seafowl/interactive-visualization-demo),
go through our
[quickstart](/seafowl/docs/getting-started/quickstart)
or through a longer
[tutorial](/seafowl/docs/getting-started/tutorial-fly-io/introduction)
in which you'll deploy Seafowl to Fly.io, put it behind Cloudflare or Varnish
and reproduce our Observable demo notebook.

When something breaks, please do
[raise a GitHub issue](https://github.com/splitgraph/seafowl/issues/). And, if
it doesn't (or doesn't break badly enough), **give us a
[GitHub star](https://github.com/splitgraph/seafowl)**!

## Future features

There are many features we're planning for Seafowl. Where appropriate, we'll
also aim to upstream these changes into DataFusion itself.

### JSON support

Seafowl currently supports a limited subset of PostgreSQL operators and
functions. The most notable missing feature is the JSON data type for storage of
arbitrary semi-structured objects, as well as helper functions like
`json_extract_path` / `json_agg`.

See the [GitHub issue](https://github.com/splitgraph/seafowl/issues/137) for more details.

### Live querying

You can currently query Parquet/CSV files over HTTP from Seafowl, but you can't
query any other databases "live", like you could with Splitgraph. It's always
been difficult for us to write PostgreSQL foreign data wrappers in C, since we'd
have to do a lot of work to correctly push down various parts of the PostgreSQL
query tree. With the extensibility of DataFusion and the memory safety of Rust,
we expect manipulating the query plan to be much easier.

See the [GitHub issue](https://github.com/splitgraph/seafowl/issues/136) for more details.

### Advanced PostgreSQL client support

Splitgraph has always been mostly PostgreSQL-first, with great support for
PostgreSQL clients (including ones that run complex introspection queries, like
Metabase).

With Seafowl, we flipped this upside down: it's HTTP-first, with basic
PostgreSQL wire protocol support as an afterthought. Perhaps it would be
interesting to polish that more, add more `information_schema` tables and make
Seafowl queryable by existing PostgreSQL clients, dashboarding and BI tools.

This would also open up more data sources, for example, using
[Airbyte](https://airbyte.com/) to load data into Seafowl.

### ...Seafowl Cloud?

No open-source project announcement nowadays is complete without teasing a
managed version of it.

In our case, we already have [Splitgraph](https://www.splitgraph.com) and we're
currently working on a better UI for it, one that moves us from obscure concepts
of "data images" and "repositories" to more familiar ones like "tables" and
"databases". Over time, we're also planning on migrating Splitgraph's query
execution engine from PostgreSQL to Seafowl.

With that in mind, we could provide a management interface for your Seafowl
instance or, later on, a true multi-tenant pay-for-what-you-use analytical query
execution API, powered by Seafowl.

In the words of a certain company, "we're just getting started".


Our new project: a CDN-friendly analytical database that's up to 10x faster than PostgreSQL and up to 5x faster than Splitgraph.

seafowl

Open Data Monitor

Deciding if I'm urban with WebAssembly and Seafowl

Extending Seafowl with WebAssembly

Table partitioning and time travel queries: Seafowl case study

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

Product

Support

Company

Splitgraph

seafowl

Open Data Monitor

Deciding if I'm urban with WebAssembly and Seafowl

Extending Seafowl with WebAssembly

Table partitioning and time travel queries: Seafowl case study

(Ab)using CDNs for SQL queries

Seafowl: a database for analytics at the edge

Product

Support

Company

Community

Splitgraph