# How to Migrate Airtable to PostgreSQL: A Step-by-Step Guide

Moving an Airtable base to PostgreSQL is far less scary than it sounds, provided you do it in the right order and treat the relationships, not the rows, as the hard part. This guide walks through the whole thing: mapping your schema, turning linked records into foreign keys, handling rollups and attachments, validating the result, and cutting over without taking your team offline.

## Key takeaways
- The data is the easy part. The real work is rebuilding relationships, rollups, and automations correctly.
- Linked records become foreign keys (one-to-many) or junction tables (many-to-many).
- Rollups and lookups become SQL joins, aggregates, or materialised views: computed on demand, not stored.
- Attachments are files: copy them to object storage and store the URL in Postgres.
- Validate row counts and spot-check values on both sides before you cut over.

## Before you start: map the territory
Resist the urge to export a CSV and start importing. First, inventory what you actually have. For each table, list the field types, note every linked-record relationship, and flag the rollups, lookups, and formulas that depend on those links. This map is what turns a chaotic migration into a checklist. It also surfaces the question worth answering up front: which fields hold *real* data and which are just derived values you can recompute later?

## Step 1: Design the relational schema
Each Airtable table becomes a Postgres table. Each field becomes a column with a real type: `text`, `numeric`, `boolean`, `date`, `timestamptz`, and so on. Single selects map naturally to a text column (or a Postgres `enum` if the options are fixed); multi-selects map to a `text[]` array or a related lookup table. Give every table a real primary key. Keep the Airtable record ID in a column so you can trace records back during validation.

## Step 2: Convert linked records into foreign keys
This is the heart of the migration and the thing CSV exports get wrong. Airtable's linked records are relationships, and a relational database models relationships explicitly:

- **One-to-many** (e.g. a Project links to many Tasks): add a foreign key column on the "many" side: `tasks.project_id` referencing `projects.id`.
- **Many-to-many** (e.g. Authors link to many Books and vice versa): create a junction table (`authors_books`) holding one row per relationship with both foreign keys.

Done right, this is a strict upgrade: foreign keys enforce referential integrity, so you can no longer end up with a task pointing at a project that does not exist.

> Watch out for: Airtable lets a single linked-record field point at multiple records without declaring a direction. Decide explicitly whether each link is one-to-many or many-to-many before you build the schema. Getting this wrong is the most common source of rework.

## Step 3: Rebuild rollups, lookups, and formulas
In Airtable these fields store a recalculated value. In Postgres you usually do not store them at all. You derive them when you query, which keeps them always-correct and removes the recalculation cost:

- **Lookups** become a `JOIN` back to the related table.
- **Rollups** (sum, count, max of linked records) become an aggregate query: `SUM(...)`, `COUNT(...)` with a `GROUP BY`.
- **Formulas** become a `GENERATED` column for simple per-row math, or a computed expression in the query for anything involving other rows.

If a derived value is read constantly and expensive to compute, wrap it in a **materialised view** so it is precomputed and refreshed on a schedule: the best of both worlds.

## Step 4: Move attachments to object storage
Attachments are not database values; they are files that happen to be referenced from a cell. The standard pattern is to copy each file into object storage (such as Amazon S3 or a compatible bucket) and store its URL in a Postgres `text` column. That keeps the database lean and serves files efficiently. AT Migrator moves attachments as part of the migration, or writes them to your S3-compatible storage.

## Step 5: Migrate the data and validate
With the schema in place, load the data table by table, parents before children so foreign keys resolve. Then, before anyone trusts the new database, validate:

- **Row counts match** on every table, source versus target.
- **Relationships resolve:** no orphaned foreign keys, junction rows line up with both parents.
- **Spot-check values**, especially dates, numbers with precision, and anything that passed through a formula.

This validation step is where hand-rolled scripts tend to fall down and where dedicated tooling earns its keep. AT Migrator runs these checks automatically and reports any mismatch before you rely on the data.

## Step 6: Cut over without downtime
Two patterns cover almost every team:

| | Freeze & cutover | Dual-write |
| --- | --- | --- |
| How it works | Pause edits, run final migration, validate, switch apps to Postgres | Write to Airtable and Postgres simultaneously, then cut over once at parity |
| Downtime | A short, planned freeze window | None |
| Complexity | Low: best for most teams | Higher: needs sync code |
| Best for | Internal tools, business hours edits | Always-on production systems |

For the majority of bases, a short freeze over a quiet evening is more than enough. Only reach for dual-write when the system genuinely cannot pause.

## How long does it take?
The data and schema move in minutes with the right tooling. The *project* timeline depends on everything attached to the base. A clean single base can be done and validated in a day. A multi-base workspace with heavy automations and integrations is usually a one-to-four week effort, and most of that is rebuilding automations and testing, not moving rows.

## FAQ
**How long does an Airtable to PostgreSQL migration take?** A single base with clean schema and a few thousand rows can be migrated and validated in a day. A multi-base workspace with linked records, attachments, and downstream automations is usually a one-to-four week project, most of which is rebuilding automations and testing, not moving the data itself.

**How are Airtable linked records converted in PostgreSQL?** Each linked-record field becomes a foreign key relationship. A one-to-many link maps to a foreign key column on the child table; a many-to-many link maps to a junction (join) table holding both record IDs.

**What happens to rollups, lookups, and formula fields?** Lookups and rollups become SQL joins and aggregate queries (or materialised views if you want them precomputed). Formula fields become generated columns or are computed at query time.

**Can I migrate Airtable attachments to PostgreSQL?** Yes. Attachments are files, so the standard pattern is to copy each file into object storage (such as S3) and store its URL in a Postgres column.

**How do I migrate without downtime?** Use a dual-write or freeze-and-cutover approach. For most teams a short freeze works: pause edits, run the final migration, validate row counts, then point applications at Postgres.

---
*Considering a migration? Book a free discovery call with AT Migrator: https://www.atmigrator.com*