<p dir="auto">Using SQLAlchemy 1.2.14, I attempted to reflect a schema from a customer which contained several thousand tables, and noticed this was very slow.  Using echo="debug" as an option on the engine, I noticed that the SQLAlchem reflection code is iteratively querying the database for table metadata, instead of issuing one query.</p>
<p dir="auto">I believe this is sub-optimal, and performance would be greatly enhanced if the SQLAlchemy reflection code issued one query to the backend instead of iterating over the table list.</p>
<p dir="auto">Example times: MS SQL Server database with 3,300 tables: 15 minutes.<br>
PostgreSQL database with 694 tables: 4 minutes.<br>
PostgreSQL database with 18,000+ tables: 45 minutes.</p>
<p dir="auto"><strong>Migrated issue, originally created by Mark Amery (<a href="https://github.com/ExplodingCabbage">@ExplodingCabbage</a>)</strong></p>
<p dir="auto"><a href="https://www.postgresql.org/docs/current/static/datatype-json.html" rel="nofollow">https://www.postgresql.org/docs/current/static/datatype-json.html</a> recommends using "jsonb" over "json" for most use cases, since its performance is superior. (The "json" type just stores the input as text, which has to be parsed in order to do any queries, and keys within the JSON can't be indexed. "jsonb" stores a proper structured representation of the given JSON that can be indexed for faster querying.) This guidance seems obviously good to me.</p>
<p dir="auto">For that reason, wouldn't it make sense for sqlalchemy.types.JSON to translate to "jsonb" on Postgres instead of "json"?</p>
<p dir="auto">Besides the "jsonb is just plain better in almost all cases" argument, it also seems relevant that PostgreSQL's "jsonb" type is a closer match behaviourally to MySQL's "JSON" type than PostgreSQL's "json" type is. Since SQLAlchemy translates sqlalchemy.types.JSON to "JSON" on MySQL, this perhaps means that translating to "jsonb" on Postgres is more portable?</p>