--- title: "SQLite to Postgres and how it's difficult to do" publication: "Postgres Weekly" issue: 473 published_at: "2022-09-28" canonical_url: "https://postgresweekly.com/issues/473" --- Plus a look at pipelining queries to Postgres from Python, how TimescaleDB can supercharge Postgres, and making some TOAST. | #​473 — September 28, 2022 [Web Version](https://postgresweekly.com/issues/473) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/v1664368643/rkwxkdhjtmn08a20bjne.jpg)](https://innerjoin.bit.io/introducing-pgsqlite-a-pure-python-module-to-import-sqlite-databases-into-postgres-bf3940cfa19f) [pgsqlite: A Pure Python Module to Import SQLite Databases into Postgres](https://innerjoin.bit.io/introducing-pgsqlite-a-pure-python-module-to-import-sqlite-databases-into-postgres-bf3940cfa19f) — Postgres is popular, but SQLite is almost _universal._ [bit.io](https://bit.io/), a new-_ish_ Postgres platform, recognized this and has created [pgsqlite](https://github.com/bitdotioinc/pgsqlite) as a way to rapidly import SQLite databases into Postgres. This post focuses on some of the difficulties involved in such a process. Adam Fletcher (bit) [ICU Features in Postgres 15](http://peter.eisentraut.org/blog/2022/09/26/icu-features-in-postgresql-15) — ICU (International Components for Unicode) is a suite of libraries for reliably handling various Unicode related text operations. Postgres has supported ICU since version 10 for selective collation/sorting of columns, but Postgres 15 will let you specify an ICU collation for a _whole_ database. Peter Eisentraut [![\](https://copm.s3.amazonaws.com/e738e8ea.png)](https://resources.enterprisedb.com/elephant-in-the-room/?utm_source=edb&utm_medium=newsletter&utm_campaign=GL-NL-2022-0914--PGweeklynewsletter) [Accelerate Development with Fully Managed Postgres DBaaS](https://resources.enterprisedb.com/elephant-in-the-room/?utm_source=edb&utm_medium=newsletter&utm_campaign=GL-NL-2022-0914--PGweeklynewsletter) — The world’s leading Postgres provider is now fully managed in the cloud. Free your team to innovate while we manage and support mission-critical Postgres. No one does it better than EDB. [Learn more](https://resources.enterprisedb.com/elephant-in-the-room/?utm_source=edb&utm_medium=newsletter&utm_campaign=GL-NL-2022-0914--PGweeklynewsletter). EDB BigAnimal sponsor [`LISTEN` and `NOTIFY`: Postgres's Client Notification System](https://www.cybertec-postgresql.com/en/listen-notify-automatic-client-notification-in-postgresql/) — Rather than having many clients polling the database for the same information over and over, what about having them just _wait_ and you then send the information when it’s ready? `LISTEN`, `NOTIFY` and triggers can get you quite a long way for certain use cases. Hans-Jürgen Schönig [How TimescaleDB Can 'Supercharge' Postgres](https://www.timescale.com/blog/postgresql-timescaledb-1000x-faster-queries-90-data-compression-and-much-more/) — It’s important to note this post is _from_ the developers of TimescaleDB and they throw out some very bold numbers, but the gist is to show how merely bringing TimescaleDB into a standard Postgres setup can deliver performance gains even if you’re _not_ doing time-series work. Ryan Booz (Timescale) **IN BRIEF:** - _"Always start with Postgres,"_ says database expert Andy Pavlo [▶️ over on Twitter](https://twitter.com/andy_pavlo/status/1573118660885229569). - Up and coming global/edge platform [fly.io](https://fly.io/) has unveiled a preview of [a new Postgres offering](https://community.fly.io/t/preview-postgres-on-machines-fly-apps-v2/7483) that sits in a curious 'semi-managed' Postgres platform gap. ▶[Optimizing Query Throughput in Python with Psycopg 3.1](https://www.youtube.com/watch?v=EfT_KjBc_lY) — [Psycopg 3](https://www.psycopg.org/psycopg3/) is a modern, feature-rich Postgres adapter for Python and with v3.1 ([release post here](https://www.psycopg.org/articles/2022/08/30/psycopg-31-released/)) it supports _pipeline mode_ so numerous queries can be sent to Postgres simultaneously without waiting for a response each time. Lukas Fittl [Query Optimization with `pg_stat_statements`](https://www.crunchydata.com/blog/tentative-smarter-query-optimization-in-postgres-starts-with-pg_stat_statements) — Every week we read the latest [PostgreSQL Person of the Week](https://postgresql.life/) interview and it feels like most times the person being interviewed names `pg_stat_statements` as their favorite extension, so if you’re not using it on the regular, it’s worth becoming familiar.. _(This post dates from 2021, but we missed it at the time.)_ Kat Batuigas ['Preferred Types'](https://mydbanotebook.org/post/preferred-types/) — An interesting bit of spelunking where a weird query result leads to Laetitia discovering a quirk in Postgres’s behavior when it comes to determining data types and its preference of certain types over others. Laetitia Avrot [Handling Heroku's New `heroku_ext` Schema for Postgres Extensions](https://blog.testdouble.com/posts/2022-08-15-migrating-postgres-extensions-to-the-heroku_ext_schema/) — The Heroku cloud platform made a change that requires all extensions to be created inside a special `heroku_ext` schema. Heroku has just [posted an FAQ](https://help.heroku.com/ZOFBHJCJ/heroku-postgres-extension-changes-faq) about the issue (though logging in is required to see it.) Justin Searls [Seeing TOASTing in Action](https://www.dbi-services.com/blog/toasting-in-postgresql-lets-see-it-in-action/) — [TOAST](https://www.dbi-services.com/blog/toasting-strategies-in-postgresql/) (The Oversized-Attribute Storage Technique) is an approach Postgres uses to move (and even compress) large columns to a separate place from the rest of the table for efficiency reasons. Daniel Westermann [PDF Download: Understanding Kubernetes Concepts and Implementation](https://www.linode.com/content/kubernetes-guide/?utm_source=postgres_weekly&utm_medium=newsletter_sponsorship&utm_campaign=&utm_content=&utm_term=) Linode sponsor [Creating Continuous Aggregates with Ruby and Timescale](https://ideia.me/timescale-continuous-aggregates-with-ruby) — Continuous aggregates use materialized views to continuously and incrementally refresh a query so that only data that has changed needs to be computed, not the entire dataset. Jônatas Davi Paganini 🛠 Code and Tools - [Supabase Realtime 0.25](https://github.com/supabase/realtime) ↳ Realtime Postgres over WebSockets. - [Prisma 4.4](https://github.com/prisma/prisma) ↳ Next-gen ORM for Node.js and TypeScript. - [pg_activity 3.0.1](https://github.com/dalibo/pg_activity) ↳ `top`-like Postgres activity monitoring. - [pg-osc 0.7.4](https://github.com/shayonj/pg-osc) ↳ CLI tool for zero-downtime schema changes and backfills. - [OctoSQL 0.10](https://github.com/cube2222/octosql) ↳ CLI query tool for cross database joins. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)