--- title: "Using Postgres alone to shape and prepare data" publication: "Postgres Weekly" issue: 379 published_at: "2020-10-28" canonical_url: "https://postgresweekly.com/issues/379" --- Plus more news, tutorials, and tips from the Postgres world this week. #379 — October 28, 2020 [Web Version](https://postgresweekly.com/issues/379) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/op8ufo6wzgsrbo2yfw0z.jpg)](https://www.youtube.com/watch?v=d5jK4BzbgXw) ▶[How a DBA Used Postgres to Discover 40K Voters Purged by Mistake](https://www.youtube.com/watch?v=d5jK4BzbgXw) — A long-time database administrator was running queries on voter data to learn how to use Postgres and stumbled over a significant voter purge error. _(The part mentioning Postgres is at [around 4 minutes in.](https://youtu.be/d5jK4BzbgXw?t=243))_ VICE (on YouTube) [Improving Postgres Connection Scalability: Snapshots](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462) — Following on from his look at [connection scalability](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266), Andres has moved on to explaining the improvements he’s making in Postgres 14 regarding snapshots, complete with a look at before and after performance. Andres Freund [![\](https://copm.s3.amazonaws.com/783370b8.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary10282020) [Best-Practices on How to Speed Up Your Postgres Queries. Free eBook](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary10282020) — We share our learnings from helping companies like Atlassian, Robinhood, and others speed up their queries. pganalyze sponsor [Slonik: A Sophisticated Node.js Postgres Client Library](https://github.com/gajus/slonik) — A battle tested framework that abstracts repeating code patterns, protects against unsafe behavior, and provides a rich debugging experience. If you’re working with Postgres from Node at all, this deserves a look. Gajus Kuizinas [Using Postgres to Shape and Prepare Scientific Data](https://info.crunchydata.com/blog/1-using-postgresql-to-shape-and-prepare-scientific-data) — Rather than lean on Google Sheets or a quick Python script (which are both fine!) Steve wanted to use _just_ Postgres for a data modelling task and here’s what happened. Steve Pousty [Using SQL to Randomly Sample Data](https://info.crunchydata.com/blog/randomly-sampling-data-using-sql-and-postgresql) — A follow-up to the data modelling post (above) that moves on to _sampling_ the data. Steve Pousty [Detecting Gaps in Time-Series Data in Postgres](https://www.endpoint.com/blog/2020/10/26/postgresql-finding-gaps-in-time-series-data) — A creative solution to the problem worth looking at even if you don’t have this problem (yet). David Christensen [Automating Postgres Extension Releases on GitHub and PGXN](https://justatheory.com/2020/10/release-postgres-extensions-with-github-actions/) — I’d be intrigued to know how many folks actually write their own extensions, but if you do, there’s some useful knowledge here on automating the release of extensions on both GitHub and PGXN by way of GitHub Actions. David E. Wheeler [Keep Your Data In-House with Scalefield - Your Private Postgres Cloud](https://www.cybertec-postgresql.com/en/products/scalefield/) — Scalefield allows you to easily host PostgreSQL clusters within your own cloud – fully flexible, scalable and reliable. CYBERTEC sponsor [Foreign Keys and Insertion Order in SQL](https://www.cybertec-postgresql.com/en/postgresql-foreign-keys-and-insertion-order-in-sql/) Hans-Jürgen Schönig [Joins Using `LIKE` or Why PostgreSQL FTS is a Powerful Alternative](https://postgrespro.co.il/blog/joins-using-like-or-why-postgresql-fts-is-a-powerful-alternative/) Vadim Yatsenko ▶[An Extensive Webinar on Postgres Partitioning](https://www.2ndquadrant.com/en/blog/postgresql-partitioning-by-simon-riggs-full-webinar-video/) 2ndQuadrant supported by [![Cockroach Labs\](https://res.cloudinary.com/cpress/image/upload/v1603718648/a5mw1prei9raflmetz8t.png)](https://www.cockroachlabs.com/?utm_campaign=cooperpress-2020-Q4&utm_source=postgresweekly&utm_medium=sponsor&utm_content=postgressweekly-tip-of-week-partial-indexes) 💡**Tip of the Week** Using partial indexes to improve performance A partial index, as the name implies, indexes only a subset of rows in a table. Partial indexes can improve performance compared to full indexes, because queries that use partial indexes don’t need to scan as many rows. You can see the performance difference by running an `EXPLAIN` statement with both a full index and a partial index and comparing the scanned row count. Suppose we have an orders table, with 1,000,000 rows of data: CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, status TEXT ); INSERT INTO orders (id, customer_id, status) SELECT i, (random()*10000)::INT, CASE (random() * 2)::int WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' WHEN 2 THEN 'completed' END FROM generate_series(1, 1000000) i; First we add a secondary index on the `customer_id` column: CREATE INDEX full_idx ON orders (customer_id); Assume that incomplete orders of specific customers are frequently queried. A simple `EXPLAIN ANALYZE` with an example `SELECT` query shows that 108 rows are scanned in `full_idx`, with 36 being removed by the filter afterward: EXPLAIN SELECT * FROM orders where customer_id = 1001 AND status != 'completed'; QUERY PLAN ------------------------------------ Bitmap Heap Scan on orders (actual time=0.041..0.116 rows=72 loops=1) Recheck Cond: (customer_id = 1001) Filter: (status <> 'completed'::text) Rows Removed by Filter: 36 Heap Blocks: exact=108 -> Bitmap Index Scan on full_idx (actual time=0.026..0.026 rows=108 loops=1) Index Cond: (customer_id = 1001) By creating a partial index that indexes only incomplete orders, we can reduce the number of rows scanned. Running the same `EXPLAIN ANALYZE` as above shows only the 72 rows that satisfy the query are scanned in `partial_idx`: CREATE INDEX partial ON orders (customer_id) WHERE status != 'completed'; EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status != 'completed'; QUERY PLAN ------------------------------------ Bitmap Heap Scan on orders (actual time=0.043..0.100 rows=72 loops=1) Recheck Cond: ((customer_id = 1001) AND (status <> 'completed'::text)) Heap Blocks: exact=72 -> Bitmap Index Scan on partial_idx (actual time=0.034..0.034 rows=72 loops=1) Index Cond: (customer_id = 1001) This week’s tip is sponsored by [Cockroach Labs](https://www.cockroachlabs.com/?utm_campaign=cooperpress-2020-Q4&utm_source=postgresweekly&utm_medium=sponsor&utm_content=postgressweekly-tip-of-week-partial-indexes), the creators of CockroachDB. Partial indexes will be available soon in Version 20.2. Learn about CockroachDB in this demo: [“Distributed SQL: a modern, cloud-native PostgreSQL.”](https://www.cockroachlabs.com/webinars/distributed-sql-a-modern-cloud-native-postgresql-thank-you?utm_campaign=cooperpress-2020-Q4&utm_source=postgresweekly&utm_medium=sponsor&utm_content=postgressweekly-tip-of-week-partial-indexes) **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! --- **Want to sponsor?** Email `[kristina@cooperpress.com](mailto:kristina@cooperpress.com)` for details. --- **Want more?** You might like [DB Weekly](https://dbweekly.com/?utm_source=pg&utm_medium=email&utm_campaign=footer-promo), our more general database newsletter covering data storage trends, new database types, and more. --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)