--- title: "Super fast Postgres database cloning" publication: "Postgres Weekly" issue: 439 published_at: "2022-01-26" canonical_url: "https://postgresweekly.com/issues/439" --- Plus indexes for newbies, using Postgres to complete Advent of Code, and some events to consider. | #​439 — January 26, 2022 [Web Version](https://postgresweekly.com/issues/439) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/gb9ywcridxgik91rsomr.jpg)](https://blog.crunchydata.com/blog/elevation-profiles-and-flightlines-with-postgis) [Elevation Profiles and Flightlines with PostGIS](https://blog.crunchydata.com/blog/elevation-profiles-and-flightlines-with-postgis) — I’m always impressed what geographic work you can do with PostGIS and Postgres. This post demonstrates turning a variety of points a flight took into an actual flight path. Paul Ramsey [How to Shape Sample Data with `generate_series()` and SQL](https://blog.timescale.com/blog/how-to-shape-sample-data-with-postgresql-generate_series-and-sql/) — The last in a series of posts about generating large sets of test data with Postgres itself in order to benchmark, test workloads, or play with database features. This stage moves on to manipulating such generated data to fit a required profile. Ryan Booz (Timescale) [![\](https://copm.s3.amazonaws.com/682ff0d8.png)](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary01262022) [Free eBook: Effective Indexing in Postgres](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary01262022) — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance. Pganalyze sponsor [Database Lab Engine: A Way to Clone Postgres Databases Quickly](https://github.com/postgres-ai/database-lab-engine) — Promises ‘blazing fast’ database cloning (claiming an ability to clone a 1TB database in 10 seconds thanks to its copy-on-write approach) particularly for building dev or QA/staging environments based on full-size production-esque databases. Postgres.ai [Postgres Indexes for Newbies](https://blog.crunchydata.com/blog/postgres-indexes-for-newbies) — If you understand the differences between B-Tree, GIN, and GIST indexes, scroll on, but otherwise this gives you a _very_ high level overview. If you then want to go (a lot) further you could read more detailed guides like these on [B-trees](https://habr.com/en/company/postgrespro/blog/443284/) or [BRIN](https://habr.com/en/company/postgrespro/blog/452900/) indexes. Elizabeth Christensen **In brief:** - 📅✈️ Looking for an in-person Postgres event to attend? [Nordic PgDay 2022's schedule](https://www.postgresql.eu/events/nordicpgday2022/schedule/) has been published and takes place this March in Helsinki, Finland. The same is true [for pgDay Paris](https://www.postgresql.eu/events/pgdayparis2022/schedule/) which takes place also in March but in Paris, France. - 📅💻 If you'd prefer to stay online, [the CFP for CitusCon](https://www.citusdata.com/cituscon/2022/cfp/) is still open and the actual event takes place in April. - Amazon RDS for Postgres [now supports versions](https://aws.amazon.com/about-aws/whats-new/2022/01/amazon-rds-postgresql-amazon-rds-outposts-postgresql-minor-versions/) 13.5, 12.9, 11.14, 10.19 and 9.6.24. - [Fly.io](https://fly.io/), an edge-located app hosting platform, is offering [a free Postgres hosting offering](https://fly.io/blog/free-postgres/) for small projects, if that interests you. [A Few Lessons from Doing Advent of Code Challenges with Postgres](https://heap.io/blog/lessons-from-completing-a-few-advent-of-code-problems-in-postgres-sql) — [Advent of Code](https://adventofcode.com/) is an annual set of programming challenges that takes place each December. Commonly languages like Python or C# are used, but some folks set out to use things like SQL. While the authors didn’t complete all 25 days with SQL, they learnt some interesting things through the attempt. Matt Dupree and Amanda Murphy [An Overview of Security Best Practices for AWS's Postgres Platforms](https://aws.amazon.com/blogs/database/overview-of-security-best-practices-for-amazon-rds-for-postgresql-and-amazon-aurora-postgresql-compatible-edition/) — An AWS-oriented look at good practices to follow when using either RDS for PostgreSQL or the Postgres flavor of Aurora. Security groups, forcing SSL, IAM authentication, and encryption all appear. Baji Shaik and Divya Sharma (AWS) [Accelerating VACUUM in Postgres](https://dataegret.com/2022/01/accelerating-vacuum-in-postgres/) — It’s been a while since we had a flurry of articles about vacuuming, but here’s a few quick tips to tweaking Postgres’s settings to speed up the autovacuuming process in particular. Alexei Kozlov [Going Into 'Read Only' Mode with Postgres](https://www.jkatz05.com/post/postgres/postgres-read-only/) — If you have a need to temporarily put a Postgres instance into a ‘read only’ state, `default_transaction_read_only` can be used to prevent write transactions. Jonathan Katz [Use Flux and PostgreSQL to Enrich Data for Real-Time Apps](https://www.influxdata.com/blog/flux-join-tutorial-enrich-time-series-data-with-data-postgresql/?utm_source=vendor&utm_medium=referral&utm_campaign=2022-01-26_spnsr-nl_flux-to-query-postgres-blog_global&utm_content=postgresweekly) InfluxData sponsor [Tips for Installing Citus and Postgres Packages](https://www.citusdata.com/blog/2022/01/22/tips-for-installing-citus-and-postgres-packages/) — [Citus](https://github.com/citusdata/citus) is well known as an extension for horizontally scaling Postgres and this post answers some questions as to how it’s packaged and how it’s installed and managed. Gürkan İndibay (Citus Data) [Distributed Locking with Postgres Advisory Locks](https://rclayton.silvrback.com/distributed-locking-with-postgres-advisory-locks) — Postgres’s [advisory locks](https://www.postgresql.org/docs/12/explicit-locking.html#ADVISORY-LOCKS) feature can provide your app with a useful and trustworthy locking mechanism that doesn’t even _have_ to involve data stored in Postgres itself. This post (from 2020 – we missed it at the time) demonstrates how one company uses them. Richard Clayton [How to Create an Amazon CloudWatch Dashboard to Monitor Postgres on AWS](https://aws.amazon.com/blogs/database/create-an-amazon-cloudwatch-dashboard-to-monitor-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/) — Goes into quite some depth on creating a dynamic dashboard for monitoring RDS or Aurora including things like slow queries, the error log, vacuuming processes, and more. Shunan Xiang and Li Liu (AWS) **🔧 Code and Tools:** [Reshape: A Zero-Downtime Schema Migration Tool for Postgres](https://github.com/fabianlindfors/reshape) — An _experimental_ tool written in Rust to perform complex migrations on Postgres 12 and higher in a gradual way so that no downtime is required. Fabian Lindfors [Que 1.0: A Ruby Job Queue That Uses Postgres's Advisory Locks](https://github.com/que-rb/que) — We mentioned advisory locks above and here’s a perfect use for them bringing more reliable job queue functionality to Ruby by using locks to protect the state of the jobs. Chris Hanks [Pgweb: A Web Based Postgres Database Browser/Client](http://sosedoff.github.io/pgweb/) — A long established client written in Go, so zero-dependency binaries are easy to build where need be. [GitHub repo](https://github.com/sosedoff/pgweb). Dan Sosedoff **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|*)