Query Airtable with all the power of SQL.

Benchmarking Airtable API performance


Since we launched in December, all of our API request traffic to Airtable has been GET requests. Specifically, we poll their API by requesting pages of 100 records at a time.

The performance of these GET requests is good, averaging just below 600ms. The slowest 95th percentile is about 1100ms:

<div style={{textAlign: "center"}}>
    <img src="./airtable-api/api-get-request_time-avg.png" alt="graph of get requests" style={{maxWidth: "550px"}} />
    <img src="./airtable-api/api-get-request_time-95th.png" alt="graph of get requests" style={{maxWidth: "550px"}} />
</div>

> _We're in AWS' us-west-2 (Oregon) and Airtable is in us-east-1 (N. Virginia). We expect that to add about 75ms of round-trip latency to each request._

At the beginning of this month, [we released our proxy](https://docs.syncinc.so/reference#writes). Our customers make write requests through our proxy, like this:

```bash
curl -v -X POST https://proxy.syncinc.so/api.airtable.com/v0/appi7vQMBr0oPikls/Main \
  -H "Authorization: Bearer keyshhhhhhh" \
  -H "Content-Type: application/json" \
  --data '{ "records": /* payload */ }
```

Those writes are committed to both Airtable and their [Sync Inc](https://syncinc.so) database simultaneously.

As a result, we're now starting to service Airtable API traffic that isn't just GET requests, but PATCH/POST/DELETE requests as well.

Along with these new request types, we've noted API performance statistics that seemed unusual. For example, we're working with some bases that consistently take up to 10 seconds to service all PATCH/POST/DELETE requests.

I wanted to get to the bottom of what caused writes to slow down so significantly. So I wrote up some quick benchmarks. Each benchmark you see below is the result of running a given type of request against a base at least 100 times in succession.

### Testing different base sizes

The first hypothesis was the most obvious: Perhaps as a base gets larger (ie holds more records), writes get slower.

To test this, I used bases of three sizes that we already had in our account (1k, 10k, and 49k records). These bases did not have the same structure, ie they had different fields (columns). I ran my benchmarks against each base. Here's the average duration at each base size:

<div className="full-width">
    <img src="./airtable-api/duration-by-base-size.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

The result was a bit surprising: The average duration of write requests against the 10K base was by far the largest. All write requests to the 10k base took about the same amount of time to service. And there was no meaningful difference between the 49K base and the 1K base.

Given how similar the performance between the 1K base and 49K base is, this benchmark illustrates that the correlation between base _size_ and the service time for writes is weak. This instead suggests that something about the _fields_ in a base play a role.

Something is happening with the fields in the 10K base that is not present in the 1K and 49K bases.

### Computed fields

It turns out that the 10k base had _a lot_ of Formula and Lookup field types. These fields are _computed_. You supply a little function that Airtable runs to derive the cell value, based on other records in the base.

My next hypothesis was that these computed fields were responsible for the slowdown on writes. I began adding arbitrary formula fields to the base, but that didn't seem to make an impact. I suspected that the [Pareto Principle](https://en.wikipedia.org/wiki/Pareto_principle) was at work and that one or two computed fields were responsible for a majority of the latency.

So I stripped out all the computed fields in the 10k base and confirmed the slowdown for writes disappeared. Then I added back computed fields, one-by-one, until I got my first jump:

<div className="full-width">
    <img src="./airtable-api/10k-only.png" alt="benchmarking 10k with different fields" style={{maxWidth: "900px"}} />
</div>

The yellow bar is the full 10k base. The orange bar is the base with all its computed fields stripped out. The turquoise bar is what I got after I added back one of the computed fields.

This base was based off a [de-duplication base from the community](https://airtable.com/shrMnI6K6OIKvLNDN/tbl25vkJL5apr0Kqm/viwBFyJ1CNk3T1lgU?blocks=bipCb8vTGNvvgtnJc). It has some fairly complex computed fields. Here's the computed field that tipped the scales:

<div className="full-width">
    <img src="./airtable-api/dedupe-field.png" alt="benchmarking 10k with different fields" style={{maxWidth: "300px"}} />
</div>

The full snippet below:

```
IF(
    MatchKey,
    IF(
        (LEN(
            values&''
            )-LEN(
                SUBSTITUTE(
                    values&'',
                    MatchKey,''
                    )
                )
            )>LEN(
                MatchKey
                ),
        1
        )
    )
```

You can see that the operation it's performing is non-trivial. It's also operating on another lookup field which is in turn operating on a formula. So, to calculate a given cell value, there's a small function stack that has to be executed!

Check out the standard deviation of these results:

<div className="full-width">
    <img src="./airtable-api/std-10k.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

This relatively small standard deviation tells us the results are pretty consistent request-to-request. This suggests that these computed fields don't add an _erratic_ service time. They add a _consistent_ overhead to each and every request.

### Takeaway

We can reason about what is happening here: When you create or update a record in Airtable, Airtable constructs all of its computed fields. Whether these computed fields are stored or cached anywhere is unclear. _But_, regardless, Airtable still needs to generate these fields because they return them to you in their API response:

```js
200 OK

{
    "records": [
        {
            "id": "recgm4yapfePwBTmq",
            "fields": {
                "ID": "00001",
                "Job Title": "Building superintendent",
                "First": "Deborah",
                "Last": "Harris",
                "Company": "Gottschalks",
                "Link2DeDupe": [
                    "reck9Y37hBxarHHIw"
                ],
               // ...
            }
        }
        // ...
    ]
}
```

This means your creates/updates/deletes will only work as fast as it takes to calculate all your computed fields. (The fact that deletes take the same amount of time as creates and updates suggests that the computed fields for a base are indeed being re-calculated whenever the base is mutated.)

Now that we've isolated computed fields as the main culprit, I was still curious if there was any correlation with base size. So, I doubled the size of the 10k base with the problematic lookup field and ran the benchmarks again:

<div className="full-width">
    <img src="./airtable-api/10k-2x.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

Turquoise is the base with 10k records which contains that one heavy computed field. Purple is the turquoise base, but doubled in size. Doubling the size of the base almost tripled the service time for all write requests!

So, it feels safe to draw the following conclusion:

**A sufficiently complex computed field can easily increase the write time for your base dramatically. This impact is proportional to the size of your base.**

This means that if your base is highly transactional (expecting a lot of reads/writes through the API), you should be mindful about how computed fields fit into your schema.

As an alternative to complex, inter-connected computed fields, you can lean on automations or scripts to populate derivative fields. Or, you can move such logic out of your Airtable base entirely.

### Other notes: Outlier requests

It's worth noting that in my results, I pruned out some outlier requests. About every ~200 requests or so, I'd have one API request that would take nearly 30 seconds to complete. It is not clear to me why these occurred or what would have happened if halfway through waiting I'd canceled the request and simply tried to make a new one. (Worth future investigation.)


Prototyping apps on Airtable: Best practices


The first iteration of most software is a _prototype_. The purpose of a prototype is to validate a hypothesis or learn more about a market. Because a prototype is likely to undergo radical changes or total abandonment in face of the information it uncovers, the faster you can generate a prototype, the better.

To learn the most, great teams don't just mock up UIs. They use modern tools to rapidly transform ideas into working software. These [high-fidelity prototypes](https://svpg.com/high-fidelity-prototypes/) let a team quickly de-risk the project.

Airtable is a killer tool for quickly standing up high-fidelity prototypes.

Airtable can be to your prototype's data model what Figma is to your prototype's interface. It allows you to quickly create, iterate, and change the data that powers your app in minutes. Helping you learn more about your ultimate use case and product. From the bits to the pixels.

But as with all powerful tools, there is an 80/20 rule that applies. A couple tricks can make building prototypes on Airtable much easier.

## Two limitations to keep in mind

There are a couple Airtable limitations you should be aware of from the start. For many prototypes this shouldn't be an issue (and if these items are an issue - I would ask, "have you scoped your prototype?").

### Record limits

Airtable maxes out at around 100,000 records per base on the enterprise plan. We've seen companies go beyond 100,000 records in a base, but the front-end of Airtable begins to noticeably slow.

Different [Airtable plans](https://support.airtable.com/hc/en-us/articles/115010928147-Airtable-plans) have different data limits (records, automations, etc). So keep these in mind when you're prototyping:

![Airtable data limits](./prototype/001_pricing.png)

If prototyping your application is going to require hundreds of thousands of records, Airtable isn't for you.

### API rate limits

The Airtable REST API is [limited to 5 requests per second per base](https://support.airtable.com/hc/en-us/articles/203313985-Public-REST-API).

So if multiple clients are making request to the same base, you might see some 429s.

This limit can be particularly onerous for reads. We'll talk about this more later on - but tools like [Sync Inc](https://syncinc.so) abstract the API quota and make prototyping on your Airtable data far simpler.

## Best practices for building Airtable prototypes

You probably get the picture by now, Airtable is endlessly adaptable - it's what makes prototyping on Airtable so powerful. But endless configuration comes with all the intellectual yack shaving and bike-shedding that can slow projects way down. So we would propose a couple conventions to help you build quickly.

### Create a developer environment

To build quickly, you need to be able to break things. To give you the this freedom with an Airtable prototype you should create a **production** and **dev** base in your workspace.

Airtable makes this simple. First, set up your production base. Then duplicate the base to create a development environment with the same structure. Now, you can change the schema and make improvements knowing you have a fallback.

![Create a dev environment](./prototype/002_duplicate.png)

Once your dev environment is stable and reflects the new changes, you can "deploy" by simply making the dev environment the new "production." Or, add the updates to the production base.

If you don't need to perform _writes_ in the development environment, then you should consider "syncing" the data (one way) from your production base to the development base using [Airtable's sync](https://support.airtable.com/hc/en-us/articles/360052521254) functionality. Any updates to the data in the table shared from the production base will then be reflected in the development base.  Keep in mind a synced view is _read only_ - so this can work for some use cases but not all.

### Schema design

As much as possible, treat Airtable as a traditional, relational database. You want your schema to help you maintain data integrity and understand the relationships between your records.

Airtable actively fights traditional schema design in two ways:

1. It is so easy to add fields and views that you can quickly begin to duplicate data across many tables with `lookups`
2. You can begin to mix business logic with raw data using calculated fields (i.e. `formulas`, `rollups`, and `counts`).

In the big scheme of things, lookups and calculated fields are helpful. These capabilities make Airtable easy for everyday users to work with their data in Airtable. <a name="schema-design"></a>

However, for the developer, these field types can create a mess. So to make things easier, implement a simple naming convention: **add some sort of delineation (like an underscore) in front of any Airtable column that is a lookup or calculated field.**

You'll find that this will make working with your data in your prototype easier without sacrificing the underlying power of Airtable. When you are pulling data into your prototype you will be able to quickly see which fields are derived or linked.

This naming convention will also come in handy should your prototype evolve into a production app that is too big for Airtable to handle. A simple `_` in front of each field that is calculated will help you see and properly deal with any Airtable dependencies.

> Pro tip: use the Airtable [base schema app](https://support.airtable.com/hc/en-us/articles/360044409793-Base-schema-app) to spin up an ERD for your base to better visualize these risks.

### Users, personas, and permissions

It's a timeless question: "who is my user?" Luckily, an Airtable prototype can help you come to an answer.

With Airtable, you can not only manage your prototype's users and permissions - but you can also easily enrich your user data with persona and demographic tags to help you segment and introspect your users.

To manage users and permissions in Airtable, you'll create a `users` table just as you would in a relational database. Here is how:

1. Create a table where you will store user information. The primary column should be a unique identifier for the user - like their email.
2. In other fields in the user table, record helpful user data like referral source, persona, job role, and more.
3. Use [linked records](https://support.airtable.com/hc/en-us/articles/206452848-How-to-link-records-in-Airtable) to then associate users to their data in other tables.
4. Store hashed passwords and login information in the `users` table as well.

> This [post](https://docs.stacker.app/data-connection/prepare-your-data) from [Stacker](https://docs.stacker.app/data-connection/prepare-your-data) lays out the structure well.

The beauty of Airtable is that managing users is not only possible, but you can make it as manual or automated as you like. For instance, if security isn't a big deal for your early prototype (i.e. you aren't working with real users or any PII) you can manually create users and store simple passwords. Or, you can go the extra mile and create entire login flows that store hashed passwords in Airtable. It's up to you.

### Seed your database

Oftentimes an early prototype can run on mock data to help a team quickly understand the shape of their data model. Airtable makes working with mock data easy.

Of course, you can just type in data with all the edit tools you are familiar with from spreadsheets.

Or, you can also import existing data from [CSV](https://support.airtable.com/hc/en-us/articles/115013249187-CSV-import-app) and [XML](https://support.airtable.com/hc/en-us/articles/115013739848-XML-import-app) documents.

There is also a [helpful script](https://airtable.com/marketplace/scrRkRnEjgerYYihJ/randomize-values) in the marketplace for filling a base with dummy values.

> Tip: To generate dummy data, check out [Mockaroo](https://www.mockaroo.com/). Just export the mock data as a CSV and import it into Airtable.

### Isolate any business logic

As much as possible, put all the logic and automations that manipulates the raw data in your Airtable base in one place so you can manage it.

For many prototypes, you'll likely house the business logic in the front-end application you are building. So try to keep all derived calculations and validation in the front-end as well.

For other prototypes, you might manage this business logic in Airtable. If you take this approach, instead of using calculated fields that spread logic across each table of your base, use [Airtable automations](/the-complete-developers-guide-to-airtable) to detect when a row is created or changed - and then use an [Airtable script](/the-complete-developers-guide-to-airtable) to calculate and insert derived fields. This can make managing the business logic much simpler because you can see it all in one place as plaintext.

In instances where you need to use calculated fields, use the [naming convention suggested previously](#schema-design) to help you keep things straight.

Additionally, try to stick to one automation service. If you use Zapier, don't split automations between Zapier AND Airtable automations. Try to use just one.

In fact, to help you and your team keep everything in order, document all integrations as a sort of `README` using [Airtable's Description app](https://support.airtable.com/hc/en-us/articles/115013405088-Description-app). In the description block list all the services that are pushing data into your Airtable base (i.e. forms, webhook integrations, or automations) and what fields they touch.

### Simplify reads

The [Airtable REST API](https://airtable.com/api) is not great for prototyping. After working with many customers, we've learned that our product, [Sync Inc](https://syncinc.so/), works great for prototyping on Airtable because you can access all your Airtable data using SQL. Here is why:

Even with [Airtable.js](https://github.com/airtable/airtable.js/) to help manage retries, reading data through the API is an uphill climb. Between the rate limits, pagination, and Airtable's unique `filterBy` parameter you'll find many little quirks. The API is especially slow and onerous when you need to make nested calls to source data from several tables to create a complete data view. And, as you leverage Airtable's ease of use to make updates to your base, your API calls can break as columns and tables change.

Sync Inc allows you to skip the API by replicating Airtable to a Postgres database. You can then use SQL to quickly `SELECT`, `JOIN`, and `GROUP BY` to get the data you need. For example, you can become agnostic to column changes when sourcing data into your app by simply activating the **Sync all tables** setting in Sync Inc and then use `SELECT *` in your query. As new columns and tables, appear, they'll load into your app and you can work with them directly.

This reduces the amount of code you need to write, speeds up your data request, and simplifies maintenance as you iteratively improve your prototype.

You can also connect your Sync Inc Postgres database to all sorts of ORMs like [Prisma](https://www.prisma.io/) or generate a GraphQL API using [Hasura](https://hasura.io/). The entire Postgres ecosystem is at your disposal to help you build.

## Conclusion

Prototypes built on Airtable are uniquely powerful. By giving you a flexible datastore that anyone on your team can use, Airtable can help you quickly build a data model that powers working software. When built with these six, simple conventions, you'll be able to not only build functional software on Airtable - but you'll also be able to quickly iterate, operate, and maintain that prototype as you grow. Get building.

The Complete Developer's Guide to Airtable Part 2: Webhooks


Airtable continues to launch new tools for developers to build in and on Airtable. Which means it is time for a new installment of [The Complete Developer's Guide to Airtable](/the-complete-developers-guide-to-airtable).

In this *part 2* of the Complete Developer's Guide to Airtable, we'll cover how to use Airtable's [incoming webhooks for automations](https://community.airtable.com/t/incoming-webhooks-for-automations/38411/9).

## What are webhooks

Webhooks are messages an application can send when a specific event occurs. Messages are most often HTTP requests. They are a common method for integrating applications and services.

In the absence of webhooks, to know if an event has occurred in another application, you need to constantly _poll_ the other application for an update. Think of a small child incessantly asking "are we there yet?" on a road trip.

Webhooks, by contrast, are a more elegant solution. When an event happens in another application - for example a new contact is created in Salesforce - an HTTP request will be sent to your app describing the event and its details. Then, your app needs an always-on endpoint for receiving these webhook requests and taking the proper action (easier said than done).

With Airtable's new support for incoming webhooks, you get an always-on webhook endpoint that is ready to receive events from internal or third party services. You can then send webhooks to Airtable to trigger automations that take the inputs of the webhook to manipulate your airtable base or trigger other actions like emails and more.

With webhooks, you no longer need to set up a scheduled automation to poll a service for updates every couple minutes. This makes integrating Airtable into your stack much nicer.

## Who get's Airtable webhooks

Webhooks are included on all Airtable plans as a part of the *Automations* feature:

![Airtable Webhook Plans](./webhooks/001_who_gets.png)

Keep in mind, that webhooks can happen frequently. For example, if you trigger a webhook every time a new contact is created in Salesforce, this might happen several times each day. So you can burn through the 100 automations allotted on the free tier in a number of days.

Beyond the total number of Webhooks, there are a couple other limitations developers should consider with Airtable webhooks:

* Airtable [caps webhook payloads to 100kb](https://community.airtable.com/t/incoming-webhooks-for-automations/38411/12?u=sync_inc_test_user_2) right now.
* Airtable only works with `JSON`. No `XML` or `urlencoded` webhooks for now.

> Note: Thank you to [Bill French](https://community.airtable.com/u/Bill.French), [kuovonne](https://community.airtable.com/u/kuovonne) and [ScottWorld](https://community.airtable.com/u/ScottWorld) for investigating these limits in the Airtable community.

## Building a webhook automation

To give you a practical example of how Airtable automations work with webhooks, let's build one using a [Stripe webhook](https://stripe.com/docs/webhooks).

### The scenario

Let's say your accounting team wants to use Airtable to help manage customer subscription renewals that are processed through Stripe. The team wants to be alerted before any subscription renews so they can verify the details of the subscription.

Luckily, you can have Stripe send an `invoice-upcoming` webhook several days ahead of a subscription renewing. You can create a new record in an Airtable base with the details of the webhook so the team can create a workflow to manage this process.

### A three step process

As you'll see in [Airtable's official webhook help docs](https://support.airtable.com/hc/en-us/articles/1500003044161#h_01EZFJ2CNX8M1RFABS6VEBHJZN), creating an automation that is triggered by a webhook is a three step process:

1. Create the webhook and test it.
2. Upon a successful test of the webhook, add an automation action.
3. Turn your new automation on.

### Create and test your webhook

Starting with a blank Airtable base, click the **Automations** button and select the **Create a custom automation** option:

![Add Airtable Automation](./webhooks/002_add_custom_auto.png)

Name your new automation something along the lines of **Upcoming Stripe Invoice**. Then click the blue **+ Choose a trigger** button and select **When webhook received**:

![Name automation and start](./webhooks/003_name_and_start.png)

Airtable will now generate the URL where you want Stripe to post the webhook. Copy this URL:

![Get webhook url](./webhooks/004_webhook_url.png)

Now that Airtable is ready to receive a webhook, head over to [Stripe](https://dashboard.stripe.com/test/webhooks) to configure a new outbound webhook.

> Note: for this tutorial we're using Stripe, but all sorts of platforms - from [Salesforce](https://salesforce-webhook-creator.herokuapp.com/), to [Jira](https://developer.atlassian.com/server/jira/platform/webhooks/), to [Shopify](https://shopify.dev/docs/admin-api/rest/reference/events/webhook) - use webhooks. Just check their developer docs.

After you login to your Stripe account, flip the switch to **View test data** (so you can test your webhooks). Then click the **Developers** option, select **Webhooks** and click to **+ Add endpoint**:

![Create stripe endpoint](./webhooks/005_create_stripe_endpoint.png)

In the **Add a webhook endpoint** modal that appears, paste in the URL you copied from Airtable. Then add a description and under the **Events to send** section select **invoice.upcoming**. Finally, click **Add endpoint**:

![Configure stripe endpoint](./webhooks/006_configure_endpoint.png)

Stripe will create the new endpoint for you. As mentioned earlier, you first need to send a _test_ webhook to Airtable in order to build out your automation. To do so click the **Send test webhook...** button:

![Configure stripe endpoint](./webhooks/007_test_webhook.png)

In the modal that appears, select the **invoice.upcoming** event and then click the **Send test webhook** button:

![Configure stripe endpoint](./webhooks/008_test_webhook_2.png)

Stripe will now send a test webhook to Airtable. You'll see the payload that was sent, and (if everything worked correctly) a `"success": true` in the response:

![Configure stripe endpoint](./webhooks/009_test_webhook_3.png)

Now that you have sent the test webhook from Stripe to Airtable, head back to Airtable to complete the test of the webhook.

To do so, click the **Run test** button in your automation. Airtable will verify the webhook and then present you with the data payload:

![Add resource](./webhooks/010_run_test.gif)

### Build your automation

Now that Airtable has validated the webhook trigger, you can build the rest of your automation by adding actions.

You could build a custom script that takes in the payload from the webhook as inputs. But for this use case, we'll just use the no-code "Create record" action.

But before we add the action, set up the Airtable base to properly store the contents of the webhook. I find it's helpful to do this after you've tested the webhook so that you can see the structure of the data you're working with.

For instance, you can see that the Stripe webhook will deliver the data with currency formatted as cents and time formatted as a Unix timestamp:

![Webhook data types](./webhooks/011_formats.png)

As a result, it's best we store these values as integers in the Airtable base and then use formulas to make them more friendly to users. Taken together, this is one way you could configure your Airtable base to take in the data from this webhook:

![Configure Airtable base](./webhooks/012_table_setup.png)

Here, you plan to record the `event_id` from the webhook as the primary key in the table. Then you store data from the webhook in additional columns. Last, you store the status of each upcoming invoice so your accounting team can track their workflow.

With your table set up, you can now easily configure the action step of your automation to create a new record. Click the blue **+ Add action** button and select **Create Record**:

![Create action](./webhooks/013_add_action.png)

Now map the values in the webhook payload to the fields in your table. It just takes some clicking:

![Add resource](./webhooks/014_map_fields.gif)

Once you've mapped all the fields, test your automation by clicking the **Run test** button:

![Add resource](./webhooks/015_test.gif)

Voila - your automation works!

### Turn your automation on

To finish your webhook automation, there are two more steps.

First, you'll recall that the webhook endpoint you created in Stripe is using test data. Now that you've confirmed everything is working, go back to Stripe and flip the switch so you are now working with _real_ Stripe data. Then create a new webhook endpoint that points to your Airtable webhook trigger.

> Note: Stripe is a bit unique in that it offers "test" webhooks. Most platforms require you to work with real data from the start.

Second, turn your Airtable automation on:

![Turn Airtable automation on](./webhooks/016_turn_on.png)

You've completed all the steps: You created a webhook trigger in Airtable, configured Stripe to send events to that webhook, tested the integration end to end, added an action to create a new record, and turned everything on.

Now, whenever a new upcoming invoice event fires, your accounting team will see it appear in Airtable automatically.

If ever there is an error, Airtable will alert you with an email. So far, we've been running our operations at [Sync Inc](https://syncinc.so/) on an Airtable base populated with Stripe webhooks for a month with no errors on either end.

## Conclusion

For developers, the addition of inbound webhooks to trigger automations is a nice upgrade to the Airtable tool stack. Yes, you could do this with low-code tools like Zapier - but now having this raw capability in Airtable alongside Airtable scripts makes the platform even more programmable.

The Complete Developer's Guide to Airtable

What is RDS Proxy? Exploring with benchmarks


I was surprised when a customer wrote in to explain that he noticed - for simple SQL queries - the Airtable API was about as performant as querying his [Sync Inc](https://syncinc.so/?utm_source=blog&utm_medium=post&utm_campaign=rds)-provisioned Airtable database. We'd expect a database query to be at least an order of magnitude faster than an API query.

So I had to investigate: What was the explanation for this?

But these outlier queries still bothered me. What was the explanation for them?

Airtable's API is pretty consistent and responsive, with a mean and median request time of 350ms:

<img src="./rds-proxy/airtable-api.png" style={{maxHeight: "500px", margin: "auto"}} alt="airtable api performance" />

This is about what you'd expect from a standard list request against a third-party API with 100 items returned per request.

So how could a database query get up to 350ms? A little digging, and we discovered that the customer was querying his database via a Lambda function. This meant opening a connection to his Sync Inc db before each query. A solid lead.

I wanted to see how much overhead the connection times were adding. So I wrote some simple benchmark functions in Go.

The timing for the benchmarking is handled by this simple function:

```go
func timeTrack(start time.Time, name string) {
	elapsed := time.Since(start)
	log.Printf("%s took %s", name, elapsed)
}
```

You can log how long a function execution took in Go using this function in combination with `defer` like so:

```go
func benchmarkQuery(count int) {
	defer timeTrack(time.Now(), "benchmarkQuery")
	for i := 0; i < count; i++ {
		funcUnderTest()
	}
}
```

Above, the arguments to `timeTrack()` are evaluated immediately, meaning the first argument is set to `time.Now()` - the time `benchmarkQuery()` was invoked.

But `defer` waits to _invoke_ `timeTrack()` until right before the function `benchmarkQuery()` returns. Which, in this case, will be after the for loop executing `funcUnderTest()` `count` times has completed. Neat trick.

With a timing function in place, I wrote out some benchmarks. We want to benchmark two different kinds of behavior:

- A function that opens a connection _on every loop_ and then makes a request
- A function that opens a connection _then_ loops and makes requests

This should help us understand how much time is spent establishing the connection and how much time is spent executing the query.

Here's what the function that opens a connection every loop looks like:

```go
func benchmarkOpen(count int) {
  defer timeTrack(time.Now(), "benchmarkOpen")
	for i := 0; i < count; i++ {
		db := openDb()
		defer db.Close()
		rows, err := db.Query("SELECT id from purchase_orders limit(100);")
    rows.Close()
		if err != nil {
			log.Printf("Got error: %v", err)
		}
	}
}
```

And the one that opens a connection then makes a bunch of SQL queries on that connection:

```go
func benchmarkQuery(count int) {
  defer timeTrack(time.Now(), "benchmarkQuery")
	db := openDb()
	defer db.Close()

	for i := 0; i < count; i++ {
		rows, err := db.Query("SELECT id from purchase_orders limit(100);")
    rows.Close()
		if err != nil {
			log.Printf("Got error: %v", err)
		}
	}
}
```

(Note: It's important to run `rows.Close()`, otherwise the connection is not immediately released - which means Go's `database/sql` will default to just opening a new connection for the next request, defeating this test.)

Using these two functions, we're prepared to find out:

1. What kind of impact does re-creating a db connection on each run have on the wall time of a 1-off execution?
2. Does using RDS proxy have an effect on that wall time?
3. What are the basic load characteristics of a db.r5.large getting hammered with new connection requests? How does RDS Proxy help it perform?

This inquiry is all motivated by the nature of Lambda functions, which the customer uses. And indeed Amazon recently released [RDS proxy](https://aws.amazon.com/rds/proxy/) to address this precise architecture:

> Many applications, including those built on modern serverless architectures, can have a large number of open connections to the database server, and may open and close database connections at a high rate, exhausting database memory and compute resources. Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability.

## RDS Proxy gotchas

Before we get into the benchmarks, I want to touch on the gotchas that sucked up a lot of my time. (I guess after all these years I haven't learned my lesson: The AWS UX is just not going to help you use their products. You have to read the user guide from end-to-end.)

Here are the two that tripped me up big time:

**1. RDS Proxy only supports specific database engine versions** – and the latest RDS Postgres is not one of them.

The first time I went to setup a new RDS proxy, I didn't see any available databases in the dropdown:

![No listed databases](./rds-proxy/empty-dropdown.png)

After much trial and error, I ended up spinning up a new Aurora db. At last, I saw _something_ populate the dropdown.

It wasn't until much later I learned that Postgres 12 - the engine I use everywhere - just isn't supported yet.

(What would have been nice: Showing me all my RDS databases in that drop-down, but just _greying out the ones that are not supported by Proxy_.)

**2. You can only connect to your RDS Proxy from inside _the same VPC_**

This one was a time sink. There are few things I dread more than trying to connect to something on AWS and getting a timeout. There are about a half-dozen layers (security groups, IGWs, subnets) that all have to be lined up _just so_ to get a connection online. The bummer is that there's no easy way to debug _where_ a given connection has failed.

So, the first wrench was when I discovered - after much trial and error - that I couldn't connect to my RDS Proxy from my laptop.

(How about a small bone, right next to "Proxy endpoint," that lets me know the limitations associated with this endpoint?)

## Now that we're up and connected

**RDS vs RDS Proxy**

With my RDS Proxy 101 hard-won, we're ready to run some benchmarks.

I copied the benchmark binary up to an EC2 server co-located with the RDS database and RDS proxy.

Per above:

- `benchmarkOpen` opens a connection and then runs a query on each loop (sequential)
- `benchmarkQuery` opens a connection first, then each loop is a query on that connection (also sequential)

Here's what I got running this against the RDS database directly, 1000 times per, with a 10s pause between:

```bash
# RDS direct, same datacenter
$ ./main
2020/12/25 00:08:14 Starting benchmarks...
2020/12/25 00:08:19 benchmarkOpen took 5.846447324s
2020/12/25 00:08:30 benchmarkQuery took 504.32703ms
```

So:

- `benchmarkOpen` - ~5.85ms per loop
- `benchmarkQuery` - ~0.500ms per loop

Fair to assume the connection handshake takes 5ms, which isn't bad.

This benchmark runs too fast to register on any RDS graphs. But if I loop the benchmark to sustain it over a couple minutes, I can see a healthy connection spike for the first test:

![graphs for direct queries](./rds-proxy/db-direct-graphs.png)

I recognize that in `benchmarkOpen`, instead of deferring `db.Close()` I could be nicer to my database and close it immediately. But I kind of like the DB slow boil.

Let's move on to the RDS Proxy. Remember, RDS Proxy has to be in the same VPC as the entity calling it as well as the database. So all three are co-located:

```bash
# RDS Proxy, same datacenter
$ ./main
2020/12/25 00:15:21 Starting benchmarks...
2020/12/25 00:15:28 benchmarkOpen took 7.094809555s
2020/12/25 00:15:39 benchmarkQuery took 1.239892867s
```

Comparing to the first benchmark: It appears the Proxy adds an overhead to each request of 0.7ms (1.2ms - 0.5ms). As we'd expect, connection openings take a bit longer, about 1.15ms longer.

And, sure enough, I can't get the graph to budge. It sustains about 100 connections, with no real CPU spike, with the benchmark sustained over several minutes:

![graphs using proxy](./rds-proxy/rds-with-proxy.png)

We'll see soon how all this performance translates to Lambda, but the motivation for RDS Proxy is clear: **It's less about the client, more about the database**. RDS Proxy is just a layer of indirection for our database connections. It doesn't speed up establishing new connections - in fact, we pay a small tax on connection opening. It just saves our database from the thundering herd.

**Cross-region**

Because we're here, I'm curious: What happens if we run this benchmark cross-region, from US-East-1 to US-West-2?

```bash
# RDS - different datacenter
$ ./main
2020/12/25 00:11:12 Starting benchmarks...
2020/12/25 00:15:53 benchmarkOpen took 4m41.607514892s
2020/12/25 00:17:15 benchmarkQuery took 1m11.767803425s
```

Different story:

- `benchmarkOpen` - ~281.6ms per loop
- `benchmarkQuery` - ~71.77ms per loop

Connections take about 200ms, presumably because they involve a lot of back and forth that require coast-to-coast travel.

The difference is over a full order of magnitude. At this point, opening a database connection and making a query starts to have performance characteristics similar to querying Airtable's API.

Indeed, the customer that opened this inquiry operates out of US-East-1. Bingo.

Opening connections is certainly a factor here. But RDS Proxy won't help, because as we've learned it's not intended to reduce client-side connection opening costs. And even if it did, we couldn't use it cross-region!

So co-locating the database in the customer's data center is the move, and will give their team performance an API can only dream of.

### Benchmarking Lambda

Lambda got us into this mess, so let's do an end-to-end benchmark with it. Our direct-to-RDS test will route like this:

```bash
API Gateway -> Lambda -> RDS
```

And our RDS Proxy test will look like this:

```bash
API Gateway -> Lambda -> RDS Proxy -> RDS
```

Each lambda function call will open a new database connection and issue a single query. I just adapted the function above to make one open/query as opposed to doing so inside a `for` loop.

I'll run a benchmark locally on my computer that will call the API Gateway endpoint. Because I'm running locally, I'm able to save a little work by using Go's native benchmarking facilities:

```go
func BenchmarkRequest(b *testing.B) {
	for i := 0; i < b.N; i++ {
		resp, err := http.Post("https://[REDACTED]/run", "application/json", nil)
		if err != nil {
			panic(err)
		}
		defer resp.Body.Close()
		if resp.StatusCode != 200 {
			fmt.Println("Received non-200 response. Continuing")
		}
	}
}
```

The first test routes to RDS directly:

```bash
$ go test -bench=. -benchtime 1000x
goos: darwin
goarch: amd64
pkg: github.com/acco/rds-proxy-bench
BenchmarkRequest-8   	    1000	 128684974 ns/op
PASS
ok  	github.com/acco/rds-proxy-bench	130.351s
```

So, 128ms per request.

Now using RDS Proxy:

```bash
$ go test -bench=. -benchtime 1000x
goos: darwin
goarch: amd64
pkg: github.com/acco/rds-proxy-bench
BenchmarkRequest-8   	    1000	 118576056 ns/op
PASS
ok  	github.com/acco/rds-proxy-bench	120.529s
```

118ms per request.

We're not impressed until we look at the graphs:

![graphs using proxy](./rds-proxy/proxy-lambda.png)

The first big spike is the first benchmark, hitting RDS directly. The second non-spike is the second benchmark, routed through the proxy.

The end-to-end Lambda benchmark takes it all home: The purpose of RDS Proxy is foremost to tame connections (and related load) on the database. Any happy gains on the _client_ side will be a result of a relieved database.


How to go further with Airtable using Postgres Arrays


While Airtable looks and feels like a database, you can't query it like a database.

That's why we built [Sync Inc](https://syncinc.so/).

In this guide, you'll first replicate Airtable to Postgres using [Sync Inc](https://syncinc.so/). Then, you'll unlock your Airtable data with some killer PostgreSQL array methods.

## Airtable and Postgres Arrays

Airtable's power lies in its flexibility. Thanks to Airtable's friendly UI, I can add new tables, new columns, or change the format of a column at will. It's great.

For example, let's say I have a Products table with a column that contains the product's image. But what happens later when I decide to keep more than one image per product? I just drag another image into the row! No need to migrate the column or my previous images.

To reflect this flexibility, when Airtable data is pulled into Postgres via Sync Inc, that data is often represented as [Postgres Arrays](https://www.postgresql.org/docs/9.1/arrays.html). So when a new image pops into the Products table, your Postgres table can support it immediately, without migrations or changes to existing queries. (The new image is simply another value in the array.)

Many field types in Airtable can contain multiple values: attachments, multiple select, lookups, and collaborators. With Sync Inc, these field types will all end up in Postgres as arrays. These might not be as familiar to you, but arrays are first-class in Postgres.

In a couple minutes you'll get the hang of Postgres Arrays and never look at Airtable the same way.

## Wait...a growing small business needs your help?

To make this guide more interesting, imagine you are helping a small business owner grow their business.

They've been using Airtable to catalog products, purchase orders, and sales orders. The business owner wants their Airtable base to remain simple and easy to use - but she needs more insight to grow.

To play along with this story, take a look at the [small business's inventory tracker base](https://airtable.com/shrV2tLjq2FbrRtO2) and add it to your workspace by clicking **Copy base** in the top right corner:

![Copy base](./postgres-arrays/copy_base.png)

> I’m going to assume that if you’ve made it this far, you already know Airtable is the bees knees and know how to set it up.

## Connect Airtable to Postgres

Now, let’s get your Airtable base into Postgres using [Sync Inc](https://syncinc.so/):

1. Go to https://app.syncinc.so/signup and create an account.
2. Connect your base to Sync Inc using the tutorial or check out the [Quickstart guide](https://docs.syncinc.so/get-started).
3. In a couple seconds, you’ll be provisioned a Postgres database and Sync Inc will begin filling it with all your Airtable data.
4. Now [connect to your new Postgres database](https://docs.syncinc.so/reference#tools-and-applications) with any standard SQL client. Tools like [TablePlus](https://tableplus.com/) and [PopSQL](https://popsql.com/) are great options (I’ll be using TablePlus).

## How Airtable looks in Postgres

Take a look at your Postgres schema.

First, you’ll see that all your Airtable table names have been converted to snake_case:

![Schema](./postgres-arrays/schema.png)

Second, you’ll notice that any field type in Airtable that could hold multiple values (like `lookup`, `multiple select` , and `attachment`) have been stored in columns of type `text[]`. `text[]` is an array of text fields:

![arrays](./postgres-arrays/product_array.png)

Arrays in Postgres are delimited with curly braces `{}`, instead of the common square bracket `[]` you are probably familiar with. (It will keep you on your toes.)

## Airtable and the PostgreSQL `ARRAY`

Before you start solving real problems for the small business you are assisting, let's just get comfortable with Postgres arrays.

For example, in your Inventory Tracker base, you can see that the Warehouse Locations table contains a `lookup` field where you can select which products are associated to a given warehouse location:

![product inventory](./postgres-arrays/warehouse_product_inventory.png)

You can select multiple products for each location. So in your synced Postgres database, the `warehouse_locations` table shows the `product_inventory` column as an array type containing multiple `record_id`s representing each selected product.

![tableplus](./postgres-arrays/tableplus.png)

Let's play with the `product_inventory` arrays.

Selecting the `product_inventory` column from the `warehouse_locations` table is simple:

```sql
SELECT product_inventory FROM warehouse_locations;
```

This will return all the `product_inventory` as a column full of arrays:

<img src="./postgres-arrays/product_inventory_arrays.png" alt="product inventory" style={{width: "400px"}} />

This is okay, but let’s say you want to select the first product listed on every warehouse shelf.

To do so, you need to access the values in the array. Postgres arrays are base-1 indexed (another fun way to keep you on your toes) - so to access the first value in the array we use `[1]` with square brackets. Like so:

```sql
SELECT product_inventory[1] FROM warehouse_locations;
```

Now you'll see the `record_id` for the first product on each shelf.

<img src="./postgres-arrays/product_inventory.png" alt="locations" style={{width: "300px"}} />

Again, when using Postgres with Airtable, any field that can contain multiple values will be an array. So this simple approach of extracting the first value of the array comes in handy.

As a practical example, in the Product Inventory table in your Airtable base, you can see that the Product Name column is a multi-select field. This is probably better represented as a text field - but then you don't get the pretty colors 😌:

![product name](./postgres-arrays/product_name.png)

To query for just the text of the product name, you'll use the array syntax again:

```SQL
SELECT product_name[1] FROM product_inventory;
```

This will return a clean list of product names:

<img src="./postgres-arrays/product_name_output.png" alt="product names" style={{width: "300px"}} />

## `unnest()`

With a grasp of how Postgres arrays work, let's begin to use SQL to extract some insights.

For example, let’s say you want to see which product colors are most appealing to customers. So you want to pull the list of colors for products that sell well. Let's say "sell well" for this small business means more than 5 sales.

To make get this data, you need to get a list of `colors` from the `product_inventory` table where the units sold are greater than 5:

```sql
SELECT colors FROM product_inventory WHERE units_sold[1]::integer > 5;
```

> Tip: The double colon next to `WHERE units_sold` is short-hand for `CAST`. Casting allows you to change the data type of a field - in this case from `text` to `integer` - so that you can evaluate the `> 5` statement. You'll use `CAST` often with your Airtable data as Sync Inc works to support more data types beyond `text`, `text[]`, and `bool`;

The results from this query are a little convoluted because all the colors return in arrays. Some of the colors are duplicated across rows so you can't really tell what the most popular colors are:

<img src="./postgres-arrays/color_arrays.png" alt="color arrays" style={{width: "400px"}} />

It would be nice to see each unique color in a clear list.

`unnest()` allows you to _un-nest_ each item of an array onto its own row:

```sql
SELECT
    DISTINCT UNNEST(colors) AS "Color",
    count(colors) AS "Frequency"
FROM product_inventory
WHERE units_sold[1]::integer > 5
GROUP BY "Color"
ORDER BY "Frequency" DESC;
```

Here, you use `DISTINCT` in conjunction with `unnest()` to get a clean list of the most popular colors. You are then using `AS` to give the column an easy to understand name - in this case "Color".

Then you use `count(colors)` (again, using `AS` to alias the column to the name "Frequency") to calculate how frequently each unique color is appearing. `count(colors)` works with `GROUP BY` so that `count(colors)` is tabulated against each unique Color.

Lastly, you use `ORDER BY` to sort the table so that the most popular color is at the top.

It looks like "Desert Brown" is a common color amongst the most popular products:

<img src="./postgres-arrays/colors.png" alt="colors" style={{width: "300px"}} />

## `ANY`

Building on your new insights around the popularity of "Desert Brown", you now want to understand which products are available in this desirable color.

We can use `ANY` to quickly check if a product's color array contains our desired color - in this case "Desert Brown"

```sql
SELECT product_name[1] FROM product_inventory WHERE 'Desert Brown' = ANY(colors);
```

Okay, some ideas are beginning to come together. Let’s unpack (dare I say unnest) this query.

First, because `product_name` is represented in your database as an array (you'll recall that product name is a `multiple select` in our Airtable base) we use `product_name[1]` to select the first item in the array. In this case, the name of the product.

Then, the `WHERE` clause is checking that the product colors array (`colors`) contains a string matching `Desert Brown`.

This produces a list of all the products with a "Desert Brown" color option:

<img src="./postgres-arrays/product_color.png" alt="Desert Brown Products" style={{width: "300px"}} />

## Airtable and PostgreSQL `JOIN`

Now, you're ready for some heavy lifting. Warehouse optimization.

It would be nice to see how much inventory is on each shelf in the warehouse. This might help the business owners use their inventory space more efficiently.

The first tool you might reach for is an Airtable lookup. But it won't render exactly what you want:

<img src="./postgres-arrays/airtable_mess.png" alt="Airtable mess" style={{width: "300px"}} />

In Airtable, you'd need to resort to writing complex functions. But now that your data is in Postgres, you can just use SQL to express what you want in a few lines.

To do so, you can use `ANY` with a `JOIN` to see how much inventory is on each shelf:

```sql
SELECT
    warehouse_locations.name AS "Shelf",
    SUM((product_inventory.units_ordered[1]::INTEGER) - (product_inventory.units_sold[1]::INTEGER)) AS "Inventory"
FROM warehouse_locations
JOIN product_inventory
    ON product_inventory.id = ANY (warehouse_locations.product_inventory)
GROUP BY warehouse_locations.name
ORDER BY warehouse_locations.name ASC;
```

You've already worked with most of the elements in this query, so let's highlight how this is all working together.

First, you use `CAST` to turn `units_ordered` and `units_sold` into `INTEGERS` so you can calculate the inventory of each product. You use `AS` to give each returned column a nice name.

Then, you use `ANY` to `JOIN` the available product inventory to each shelf.

Lastly, as with the prior query, you use `GROUP BY` and `ORDER BY` to organize and sort the output.

<img src="./postgres-arrays/shelf_inventory.png" alt="shelf inventory" style={{width: "300px"}} />

## Airtable and PostgreSQL `UNION`

There's one more SQL operator I find myself reaching for all the time: `UNION`. (It's not strictly related to Postgres arrays, so let's call this a bonus.)

`UNION` is a particularly helpful tool when working with Airtable because it can help you pull together similar data from across different tables.

As an example, perhaps you want to see all the store's product images in one place to prepare for a new marketing campaign. To do so, you'll need to pull product images from both the Product Inventory table as well as from the Purchase Orders table.

In SQL this is no big deal:

```sql
SELECT product_inventory.images[1] FROM product_inventory
UNION
SELECT purchase_orders.image[1] FROM purchase_orders;
```

Now, in one list you have access to all the product images appearing across the system. To do this natively in Airtable you would have needed to create a unique `Images` table with lookups...and restructure the entire base.

<img src="./postgres-arrays/product_images.png" alt="SQL union" style={{width: "300px"}} />

## Best of both worlds

When you combine Airtable with a Postgres database, you get all the amazing capabilities of Airtable plus the flexibility and reliability of PostgreSQL.

Now you can query, integrate and build on your data with the ease of Airtable and the power of SQL.


Posts

Benchmarking Airtable API performance

Prototyping apps on Airtable: Best practices

The Complete Developer's Guide to Airtable Part 2: Webhooks

The Complete Developer's Guide to Airtable

What is RDS Proxy? Exploring with benchmarks

How to go further with Airtable using Postgres Arrays