# Round-Tripping Mastodon and Saleor Through a Database Diagram: What Broke and What We Fixed

> We put two real PostgreSQL schemas, Mastodon (Rails) and Saleor (Django), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes PostgreSQL refused that SQL for both, and once patched by hand the copies refused rows the originals accept. After the fixes the rebuilt copies match their originals in every table, key, constraint and index, and behave the same in 16 probes. What a diagram does not hold, Mastodon's own function and Saleor's four extensions, is no longer a hand-written step: Import SQL keeps it and Save runs it first.

Source: https://schemity.com/blog/postgres-round-trip-mastodon-saleor/

A database diagram that reads a schema and writes it back has one job: give you the same database. To test that, we took two real open-source schemas, Mastodon v4.7.3 (Rails, 118 tables) and Saleor 3.23.41 (Django, 148 tables), put each through Schemity both from its `pg_dump` and from the live database, and rebuilt a database from the SQL Schemity wrote. Before this round of fixes, PostgreSQL refused the SQL Schemity wrote for both schemas. After them, the rebuilt copies match the originals in every table, column, key, constraint and index, apart from spelling, and accept and refuse exactly the same rows in 16 behaviour probes.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Reading a database you did not design is how most people start with it: you reverse engineer the PostgreSQL schema to an ERD to understand your schema, and later generate SQL from that diagram. Both directions are only as good as what survives the trip, so this post measures exactly that, including what broke.

## Which two schemas, and which two paths?

Two production schemas, each created by its own framework rather than written for the test, and chosen because they use different parts of PostgreSQL.

| | Mastodon v4.7.3 | Saleor 3.23.41 |
| --- | --- | --- |
| What it is | Federated social network | E-commerce platform |
| Created by | Rails, `rails db:schema:load` | Django, `manage.py migrate` |
| Tables | 118 | 148 |
| Columns | 1,018 | 1,348 |
| Foreign keys | 156 | 266, 265 of them `DEFERRABLE INITIALLY DEFERRED` |
| `CHECK` constraints | 0 | 43 |
| Unique constraints | 0 (uniqueness is in indexes) | 128 |
| Indexes besides primary keys | 217 | 616 |
| Partial (`WHERE`) | 44 | 5 |
| On an expression | 4 | 2 |
| With an operator class | 7 | 62 |
| With `DESC` | 7 | 0 |
| Keys numbered by the database | 106 serial | 132 `GENERATED BY DEFAULT AS IDENTITY` |
| Also has | 2 views, 1 function | 4 extensions, 1 function |

Rails leans on serial keys and on partial and sorted indexes; Django on identity keys, deferred foreign keys and operator classes for search. Between them they cover most of what a framework writes into a PostgreSQL schema.

Mastodon's 118 tables, imported from its dump and grouped into 17 legends by the models in Mastodon's own source (accounts, statuses, the social graph, moderation, federation and so on), look like this. [Open the full-size diagram](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-full.webp) to read the table names.

![Schemity diagram of Mastodon v4.7.3's 118 tables, imported from pg_dump and grouped into 17 coloured legends taken from Mastodon's model files: Accounts, Statuses, Social Graph, Users & Sign-in, OAuth, Media & Polls, Tags, Trends, Notifications & Filters, Moderation, Blocks & Allow Lists, Collections, Federation, Instance, Follow Suggestions, Imports & Exports and Housekeeping](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-overview.webp)

The status bar of that diagram says 168 relations, not 156. The extra 12 are relationships Schemity inferred from column names that have no foreign key in the database. They are drawn dashed and are never written out as constraints, so the exported script holds exactly Mastodon's 156 foreign keys. A schema this size is not read by shrinking it: the minimap shows where every legend sits, and search finds a column across all 118 tables. [Why the canvas works that way](https://schemity.com/blog/why-schemity-has-no-zoom/) has its own post.

![Part of the Mastodon diagram at full size, with the Imports & Exports legend in view and the minimap in the bottom right corner showing all 17 legends of the 118-table schema; the status bar reads Entities: 118, Relations: 168](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-minimap.webp)

![Schemity's search panel over the Mastodon diagram with the query account_id, listing the account_id column of account_aliases, account_conversations, account_deletion_requests and dozens more tables, each as table, column and BIGINT](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-search.webp)

Each went through Schemity on two paths, which run different code:

- **Import SQL:** the `pg_dump --schema-only` file read by Schemity's SQL parser, with no database connection.
- **Live connection:** Schemity connected to the database and read PostgreSQL's catalog.

Both paths end the same way: the diagram's SQL exported and applied to an empty database. The four runs were done in the app, in a development build with the fixes described below. The originals ran on PostgreSQL 17.11 and the rebuilt copies on PostgreSQL 18.3.

Import SQL lists every table it found before anything is added, then what it did with the rest. Mastodon's tables call a function a diagram does not hold, and the summary says it kept the SQL that creates it:

![Schemity's Import SQL summary for Mastodon's pg_dump: the last rows of the table list, each marked New, then the lines Kept the setup SQL for timestamp_id(), which 7 tables call, and the diagram's SQL runs it first; 2 views are not imported (instances, user_ips); Skipped 12 statements; and the button Import (118 new, 0 updated)](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-import-summary.webp)

Mastodon's ids come from its own `timestamp_id()`, which reads seven sequences no column owns. Import SQL keeps the dump's own text for the function and those sequences as setup SQL. It writes it before the tables in View SQL, and when Save migrates to a database that lacks the function, puts it at the top of the migration, with each sequence as `CREATE SEQUENCE IF NOT EXISTS` so one the database already has is left alone:

![Schemity's Migration confirm dialog for an empty database, pg_demo_imported, reporting no data loss, failures or dependent objects, whose SQL begins with CREATE SEQUENCE IF NOT EXISTS public.accounts_id_seq and the other sequences timestamp_id() reads, ahead of the tables](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/mastodon-migration-setup.webp)

Saleor's tables call no function of their own, but its search indexes need four extensions, among them `pg_trgm`. Import SQL keeps them, and the diagram's SQL creates them first:

![Schemity's Import SQL summary for Saleor's pg_dump: the last rows of the table list, each marked New, then Kept 4 extensions, which the diagram's SQL creates first: btree_gin, hstore, pg_trgm, pgcrypto, Skipped 1 statement, and the button Import (148 new, 0 updated)](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/saleor-import-summary.webp)

![Schemity's Migration confirm dialog for an empty database whose SQL begins with CREATE EXTENSION IF NOT EXISTS for btree_gin, hstore, pg_trgm and pgcrypto, then CREATE TABLE account_address with its id GENERATED BY DEFAULT AS IDENTITY](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/saleor-migration-extensions.webp)

None of that existed when the benchmark started. The first runs needed a hand-written prelude on each empty database: the function and its seven sequences copied from Mastodon's dump, and four `CREATE EXTENSION` lines for Saleor. The comparisons below were measured that way. Once Import SQL kept the setup SQL itself, both dumps went through the app again, imported and migrated to an empty database with Save and nothing run by hand: all 118 of Mastodon's tables and 148 of Saleor's came up, and all 16 probes still match their originals.

## How do you prove a rebuilt schema is the same?

Two ways, and they answer different questions: does the database describe itself the same way, and does it behave the same way.

**The catalog diff.** Each original was created by its own framework, not by us: Mastodon with `rails db:schema:load` in the official `ghcr.io/mastodon/mastodon:v4.7.3` image, Saleor with `manage.py migrate` in `ghcr.io/saleor/saleor:3.23.41`, both on PostgreSQL 17.11. The schema then went into Schemity two ways, as a `pg_dump --schema-only` file through Import SQL and as a live connection, and Schemity's exported SQL was applied to an empty database. A query over PostgreSQL's own catalog then lists, for both databases, one line per fact:

- every column with its type as `format_type` prints it, `NOT NULL`, its default and its identity
- every primary key, foreign key, unique and `CHECK` constraint as `pg_get_constraintdef` prints it
- every index as `pg_indexes` prints it, with its own name left out
- every sequence with its type, its start and the column that owns it

The two lists are diffed line by line. A difference is either a real change or the same thing spelled two ways, such as `DEFAULT now()` and `DEFAULT CURRENT_TIMESTAMP`, which are the same time, and each one is classified by hand in the results.

**The behaviour probes.** A catalog can agree while a database still behaves differently, so each copy also gets 16 probes, eight per app, run against the original and every copy. A probe is a few statements in one transaction that is rolled back, so nothing stays: insert an account with its own id, insert two usernames that differ only in case, insert a tax rate before the tax class it points to. Its outcome is "accepted" or "refused at step N" with PostgreSQL's reason, and a copy passes when its outcome is the original's.

| Check | What it compares | What it catches |
| --- | --- | --- |
| Catalog diff | Columns, keys, constraints, indexes, as PostgreSQL reports them | Anything missing, added or changed in the schema |
| Behaviour probes | Which rows each database accepts and refuses | A difference the catalog reads past, or one that only matters to the data |

Data is not part of this test. Both originals hold almost none: Saleor's migrations insert a default customer type, which one probe has to set aside first, and Mastodon's insert nothing the probes touch.

## What failed before the fixes?

Everything below is from Schemity as of 14 October 2026, through Import SQL. Rebuilding the live path at that point would have meant a second full build of the desktop app, so the "before" column has the import path only.

**Neither exported script ran.** Schemity imported both schemas, but PostgreSQL refused the SQL it wrote for each at the first `double precision` column, which Schemity had written as `DOUBLE`, a MySQL name: 5 columns in Mastodon, 9 in Saleor. With that patched by hand, and Saleor's one empty array default, written as the text `'ARRAY[]'`, patched too, both scripts ran, and the probes showed what the database would have done.

| Probe | Original | Before the fixes | After |
| --- | --- | --- | --- |
| Mastodon: a row with its own id in a serial table | accepted | refused | accepted |
| Mastodon: usernames that differ only in case | refused | accepted | refused |
| Mastodon: two statuses with the same URI | refused | accepted | refused |
| Saleor: any row that gives its own id | accepted | refused | accepted |
| Saleor: a rate inserted before its tax class, in one transaction | accepted | refused | accepted |
| Saleor: one country's rates under two tax classes | accepted | refused | accepted |
| Saleor: two customer types that are not the default | accepted | refused | accepted |

Mastodon's other five probes passed even before the fixes. Saleor's first failure hid the rest, since every probe that gives a row its own id stopped at step one, so its last three rows come from a second run that lets the database pick the ids. Each failure had its own cause.

### Why did serial ids come back as `GENERATED ALWAYS`?

Rails makes a key `bigserial`, and `pg_dump` writes that as a plain `bigint` column plus a separate `ALTER TABLE ... ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq')`. Import SQL ignored that second statement, so the column arrived with no default, and the export followed its old rule for a lone integer key with none: `GENERATED ALWAYS AS IDENTITY`. 104 of Mastodon's 106 keys came back that way, and `ALWAYS` refuses a row that gives its own id, which is exactly what fixtures, seed scripts and copied data do. The other two keys lost their default altogether. Import SQL now reads the `SET DEFAULT`, and a column on its own sequence is written back as `BIGSERIAL`, which creates the same sequence, default and ownership. The trade-offs between the two are in [PostgreSQL serial vs identity](https://schemity.com/blog/postgres-serial-vs-identity/).

On the canvas, a key the database numbers reads `auto`, whether it is a serial or an identity, and a key the application assigns stays blank. A column that draws from a sequence of its own, such as an invoice number, shows `nextval`, because it is a default and not a key.

![Five tables on the Schemity canvas: the id columns of accounts (bigserial), invoices (GENERATED ALWAYS), tax_class and tax_rate (GENERATED BY DEFAULT) all read auto, the id of statuses, which the application assigns, is blank, invoices.number reads nextval, and accounts.created_at reads NOW()](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/round-trip-key-defaults.webp)

### What does Django's `BY DEFAULT` identity need from a diagram?

To be recorded at all. Since Django 4.1, a key is `GENERATED BY DEFAULT AS IDENTITY`: the database numbers it, but a row may still bring its own id. `pg_dump` writes each one as `ALTER TABLE ... ADD GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME ...)`, and the SQL parser behind Import SQL cannot read the `SEQUENCE NAME` option, so all 132 of Saleor's statements went unread, and the export gave 133 keys `GENERATED ALWAYS`. A diagram now records each key's identity as `ALWAYS`, `BY DEFAULT` or none, from the live database and from a dump, writes it back as it was, and shows it in the field dialog as an Auto increment checkbox with the kind beside it.

![Schemity's field dialog for tax_class.id, an INTEGER column with PK and Auto increment ticked and Identity set to Generated by default](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/round-trip-identity-field.webp)

### Why does every Django foreign key say `DEFERRABLE INITIALLY DEFERRED`?

So a transaction can insert a child row before its parent, the way `loaddata` and Django fixtures load. 265 of Saleor's 266 foreign keys are declared that way, and before the fixes all 265 came back as ordinary keys, checked by each statement, so the rate inserted before its tax class was refused at once. The diagram now records when each key is checked, and the relationship dialog shows it.

![Schemity's Update relation dialog for tax_class.id to tax_rate.tax_class_id, constraint tax_rate_tax_class_id_fkey, with On delete and On update set to NO ACTION and Checked set to At commit (deferred)](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/round-trip-deferred-fk.webp)

### What happens to a partial unique index?

It used to become a full one, which is the dangerous direction: the copy refuses rows the original accepts. Saleor allows one default tax rate per country with `CREATE UNIQUE INDEX ... ON tax_taxclasscountryrate (country) WHERE (tax_class_id IS NULL)`. Without its condition that index makes `country` unique across the whole table, so a second rate for the same country, under a different tax class, was refused. Saleor's one-default customer type went the same way, and a `UNIQUE NULLS NOT DISTINCT` rule went the other, losing the clause that makes `NULL` count as a duplicate. How a unique index differs from a unique constraint in the first place is in [unique constraint vs unique index](https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/).

![Schemity's index dialog for one_default_rate_per_country on tax_rate: Fields country, Where (tax_class_id IS NULL), Type btree, Unique ticked; behind it the table's index list shows the same index as country WHERE (tax_class_id IS NULL)](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/round-trip-partial-index.webp)

### How do you keep `lower(username)` and `text_pattern_ops`?

By keeping the index's key list as SQL when its columns alone do not describe it. Mastodon makes usernames unique regardless of case with `UNIQUE (lower(username), COALESCE(lower(domain), ''))`, an index on expressions, and the old diagram held only column names, so the rule was dropped and the copy accepted `Alice` beside `alice`. Seven other Mastodon indexes use an operator class, `text_pattern_ops`, which the parser could not read either, so all seven were lost, three of them unique, which is why the copy accepted two statuses with the same URI. Saleor has 62 indexes with an operator class, among them 19 trigram `GIN` indexes for search. The diagram now keeps the expression, the operator class, a sort order such as `id DESC`, the `WHERE` condition and `NULLS NOT DISTINCT`, and the index dialog shows them.

![Schemity's index dialog for accounts_username_domain_lower: Keys lower((username)::text), COALESCE(lower((domain)::text), ''::text), Type btree, Unique ticked; behind it the accounts index list also shows accounts_uri as uri text_pattern_ops WHERE (uri IS NOT NULL)](https://schemity.com/images/blog/postgres-round-trip-mastodon-saleor/round-trip-expression-index.webp)

The keys read `lower((username)::text)` rather than `lower(username)` because that is how PostgreSQL stores the expression, and the diagram keeps what the database reports.

### Which bugs did only real dumps find?

The ones no hand-written test schema had:

- Newer versions of `pg_dump` wrap the file in psql's `\restrict` command, which is not SQL and has no semicolon, so it joined the statement after it.
- Saleor writes extension operator classes with their schema, `public.gin_trgm_ops`, which needed its own handling.
- The live reader reported a nullable column with no default as `DEFAULT NULL`, so the export wrote `DEFAULT NULL` on 171 of Saleor's columns.
- Import SQL turned every `CREATE UNIQUE INDEX` into a `UNIQUE` constraint, so 69 of Mastodon's unique indexes came back as constraints. It is the same rule, so no probe noticed, but not the same object: a later Rails migration that runs `remove_index` on one would fail, because PostgreSQL will not drop an index a constraint owns. A unique index now stays an index, as the live reader already kept it; on the canvas both still show as unique.
- A diagram imported from Mastodon's dump and then refreshed from the database it built reported 70 columns as changed in the database, because the two paths spell `false` and `FALSE`, or `DECIMAL` and `NUMERIC`, differently. Comparing the meaning instead of the spelling took the planned migration from 71 statements to none.

None of these broke a test, because every test schema had been written by hand to cover a feature. A framework's real output was the first input that had all of them at once.

One more turned up later, while taking the screenshots for [the SQLite post](https://schemity.com/blog/sqlite-round-trip-campfire-jellyfin/), which the scripts here could not see because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote `CREATE TABLE` in that order, so a copy built from the diagram met a `SELECT *` or an `INSERT` without a column list with its columns in another order. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where `ADD COLUMN` puts it. Read and written back the way the app does it, 102 of Mastodon's 118 tables and 125 of Saleor's 148 came back in a different column order before this fix.

## What does the round trip give you now?

The same database, on both paths, for both schemas. Each line below is one run in the app: the schema went in through one path, Schemity wrote the SQL, and the rebuilt copy was compared with the original.

| | Import SQL | Live connection |
| --- | --- | --- |
| Mastodon: catalog differences | none | none |
| Mastodon: sequences | all 113 identical | all 113 identical |
| Mastodon: probes that match the original | 8 of 8 | 8 of 8 |
| Saleor: catalog differences | 4, all spelling | 5, all spelling |
| Saleor: sequences | 111 identical, 24 renamed (below) | 111 identical, 24 renamed (below) |
| Saleor: probes that match the original | 8 of 8 | 8 of 8 |

A first version of this table gave Mastodon 92 differences, all `timestamp(6)` columns written back as `timestamp`. On PostgreSQL those are one type, because 6 digits is the default, so nothing was lost. The [MySQL round trip](https://schemity.com/blog/mysql-round-trip-invoice-ninja-sylius/) showed the same gap is not harmless there: a bare MySQL `timestamp` keeps whole seconds, so `timestamp(6)` written back that way rounds every microsecond away. Schemity now keeps a time column's precision as it was declared, on every database, and Mastodon's 92 columns come back as `timestamp(6)`. That rerun went through Schemity's core on the development build, not through the app.

Both paths start from the original database Rails created. A first version of this post read Mastodon's live path from the Import SQL copy instead, which made its number a repeat of the import's rather than its own; the line above is from the original.

All 132 identity keys come back `GENERATED BY DEFAULT AS IDENTITY`, all 106 serial keys `BIGSERIAL`, all 265 deferred foreign keys `DEFERRABLE INITIALLY DEFERRED`, and every partial, expression and sorted index with its condition, key list and operator class.

## What still differs, and why?

Two kinds of difference are left: the same thing spelled another way, and objects the diagram does not hold.

**Spelled differently, behaving the same:**

| In the original | In the rebuilt copy | Where |
| --- | --- | --- |
| `DEFAULT now()` | `DEFAULT CURRENT_TIMESTAMP` | 2 Saleor columns; both are the transaction's start time |
| `DEFAULT ARRAY[]::character varying[]` | `DEFAULT '{}'::character varying[]` | 1 Saleor column; both are an empty array |
| `UNIQUE NULLS NOT DISTINCT` constraint | Unique index with `NULLS NOT DISTINCT` | 1 Saleor rule, the same index under it |
| `DEFAULT NULL::character varying` | no default | 1 Saleor column, live path only; a column with no default is `NULL` anyway |
| An identity sequence named after a table's old name, `account_serviceaccount_id_seq` on `app_app.id` | The name PostgreSQL gives it now, `app_app_id_seq` | 24 Saleor keys, same type, start and column; this matters only to code that names the sequence itself |

**Not held by the diagram as objects:**

- **Functions.** Mastodon numbers seven tables with its own `timestamp_id()` function, and the column defaults that call it are kept. Without the function PostgreSQL stops at the first such table with `function public.timestamp_id(text) does not exist`. From a dump, Import SQL now keeps the function and the seven sequences it reads as setup SQL and runs them first, as shown above. A function read from a live database is only named: View SQL warns that the script calls a function it does not create.
- **Views.** A `CREATE VIEW` statement names its columns' types nowhere, so Import SQL skips Mastodon's two views and lists them as skipped. A live connection reads them, as read-only tables in the diagram, and they are not exported as SQL.
- **A sequence's options.** A sequence a column default draws on, or one a column owns with `OWNED BY`, comes back bare: `bigint`, starting at 1. Saleor's three such sequences, among them `order_order_number_seq` on `order_order.number`, match their originals because those were bare too.

The import summary counts what it skips: 12 statements for Mastodon (seven sequences `timestamp_id()` reads, two views, the function and two indexes on the materialized view), all but the indexes named, and 1 for Saleor, a function no table calls. Saleor's four extensions and their comments are kept, not skipped; before this round they were 8 of 9 skipped statements.

One gap was found only on the way to this post: Saleor's three owned sequences have no column default, nothing in the schema reads them, and the first version of the catalog diff did not list sequences, so they were missing from every rebuilt copy without a line of difference. Reading the skipped statements caught it. The diagram now keeps a sequence a column owns, and the catalog diff lists sequences.

## What this benchmark does not show

- **Other databases.** Everything here is PostgreSQL. MySQL, [SQL Server](https://schemity.com/blog/sqlserver-round-trip-bitwarden-smartstore/), SQLite and [Snowflake](https://schemity.com/blog/snowflake-round-trip-tpcds/) each get their own schema and their own post. Converting a diagram from PostgreSQL to one of them leaves identity kinds, `DEFERRABLE` and partial and expression indexes out, and the converted script's notes name each one.
- **Data.** The originals are empty, and no rows were copied. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
- **Prisma, and other frameworks.** Rails and Django are covered; a schema from Prisma, Laravel or a hand-written monolith may use features neither of these uses.
- **The live path before the fixes.** The "before" column covers Import SQL only.
- **Triggers.** Neither schema has one, so nothing here tests them. A trigger is not a table either, and one left behind fails nothing: the rebuilt database simply behaves differently, which is the case this benchmark exists to catch. A schema that leans on triggers, such as an audit table or an `updated_at` trigger, is the next one to test.
- **Schemas this size are not a stress test.** 118 and 148 tables are typical, not large; how Schemity handles a schema of a thousand tables is in [a separate post](https://schemity.com/blog/database-diagram-1000-tables-memory-cpu/).

## How to rerun it

The method needs nothing from us. Create each schema with its own framework in its official Docker image, take `pg_dump --schema-only --no-owner --no-privileges`, put it through Schemity on either path, export the SQL, and apply it to an empty database. Through the app, Save does that and runs the setup SQL first; by hand, View SQL's script already starts with it. Then list both catalogs with a query like this one and diff the output:

```sql
SELECT line FROM (
  SELECT format('column %s.%s %s%s%s', c.table_name, c.column_name,
         format_type(a.atttypid, a.atttypmod),
         CASE WHEN c.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
         COALESCE(' DEFAULT ' || c.column_default, '') ||
         CASE WHEN c.is_identity = 'YES' THEN ' IDENTITY ' || c.identity_generation ELSE '' END) AS line
  FROM information_schema.columns c
  JOIN pg_attribute a ON a.attrelid = format('public.%I', c.table_name)::regclass AND a.attname = c.column_name
  JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
  WHERE c.table_schema = 'public'
  UNION ALL
  SELECT format('constraint %s %s %s', conrelid::regclass, contype, pg_get_constraintdef(oid))
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace AND contype IN ('p', 'u', 'f', 'c')
  UNION ALL
  SELECT format('index %s %s', tablename, regexp_replace(indexdef, '^CREATE (UNIQUE )?INDEX \S+ ON ', 'CREATE \1INDEX ON '))
  FROM pg_indexes WHERE schemaname = 'public'
  UNION ALL
  SELECT format('sequence %s %s start %s%s', s.relname, format_type(q.seqtypid, NULL), q.seqstart,
         COALESCE((SELECT ' owned by ' || t.relname || '.' || a.attname FROM pg_depend d
                   JOIN pg_class t ON t.oid = d.refobjid
                   JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
                   WHERE d.objid = s.oid AND d.deptype IN ('a', 'i')), ''))
  FROM pg_class s JOIN pg_sequence q ON q.seqrelid = s.oid
  WHERE s.relnamespace = 'public'::regnamespace
) lines ORDER BY 1;
```

For behaviour, write a few statements per rule you care about, run each inside `BEGIN` and `ROLLBACK` on the original and on the copy, and compare which ones PostgreSQL accepts and at which step it refuses the rest.

## Frequently asked questions

### Does Schemity keep partial and expression indexes from a pg_dump?

Yes, on PostgreSQL. An index on an expression such as `lower(username)`, a partial index with a `WHERE` condition, an operator class such as `text_pattern_ops`, a sort order such as `id DESC` and `NULLS NOT DISTINCT` are kept in the diagram and written back as they were. Converting the diagram to another database leaves such an index out and names it in the script's notes, because a plain index there would accept or refuse different rows.

### Can a database diagram round-trip a Django schema?

In this test it did: Saleor 3.23.41, created by Django's migrate, came back with all 148 tables, its 132 `GENERATED BY DEFAULT AS IDENTITY` keys and its 265 `DEFERRABLE INITIALLY DEFERRED` foreign keys. Before the fixes described here, those keys came back as `GENERATED ALWAYS` and the foreign keys as ordinary ones, so Django fixtures could not load.

### What does Schemity not import from a pg_dump?

Views, on the Import SQL path, and the options of a sequence a column owns. Functions are not part of a diagram either, but Import SQL keeps the setup SQL the tables need, such as Mastodon's `timestamp_id()` and the seven sequences it reads, and Save runs it before the tables. Extensions such as `pg_trgm` are kept and created first. Views come in when you connect to the live database instead.