--- title: "B-tree vs BRIN" publication: "Postgres Weekly" issue: 479 published_at: "2022-11-02" canonical_url: "https://postgresweekly.com/issues/479" --- Plus Simon Riggs retires, a pg_dump to S3 AWS Lambda function, and how PGConf.EU 2022 went. | #​479 — November 2, 2022 [Web Version](https://postgresweekly.com/issues/479) Together with [![Timescale logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_350,h_90/e_make_transparent/co_white,e_outline:7/vpntubykyv5ujmbzw501.png)](https://tsdb.co/13-tips-data-agg-blog) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/uqerjns8xm775ihljkos.jpg)](https://www.cybertec-postgresql.com/en/btree-vs-brin-2-options-for-indexing-in-postgresql-data-warehouses/) [B-tree vs. BRIN Indexes in Postgres Data Warehouses](https://www.cybertec-postgresql.com/en/btree-vs-brin-2-options-for-indexing-in-postgresql-data-warehouses/) — Hans-Jürgen describes it as an ‘epic battle’ between the two popular index types and puts on a mini re-enactment that essentially demonstrates why doing your own benchmarking makes sense. Hans-Jürgen Schönig [Safely Renaming a Table with No Downtime Using Updatable Views](https://brandur.org/fragments/postgres-table-rename) — Just use `ALTER TABLE x RENAME TO y`? In production, you might not want to do that as it can confuse clients currently using the database, but there _is_ a way to pull it off cleanly.. Brandur Leach [![\](https://copm.s3.amazonaws.com/5da7dfeb.jpg)](https://tsdb.co/13-tips-data-agg-blog) [Tips for Better Data Aggregation in PostgreSQL and TimescaleDB](https://tsdb.co/13-tips-data-agg-blog) — Get familiar with TimescaleDB continuous aggregates, how they enable you to retrieve data aggregations with speed and convenience, and extra tips to make the most of them. Timescale sponsor [Data Loading in Postgres for Newbies](https://www.crunchydata.com/blog/data-loading-in-postgres-for-newbies) — Quickly covers various routes to ingest data from CSV, JSON, backup files, via foreign data wrappers, and even generating data with queries. Using `jq` along with `\copy` is a neat approach I hadn’t considered before. Elizabeth Christensen **IN BRIEF:** - Peter Eisentraut has [been collecting stats on Postgres releases](http://peter.eisentraut.org/blog/2022/10/25/postgresql-15-statistics) and finds that progress is incredibly stable between major versions. - PGConf.EU 2022 is over and [apparently it went very well.](https://www.postgresql.eu/events/pgconfeu2022/news/pgconfeu-2022-conference-wrap-up-66/) 604 registered, 599 attended. Simon Riggs ([interviewed here](https://postgresql.life/post/simon_riggs/)) announced his retirement from actively contributing to Postgres at the event and received a standing ovation. - The PGSQL Phriday blogging event [is back for week two.](https://andreas.scherbaum.la/blog/archives/1122-PGSQL-Phriday-002-PostgreSQL-Backup-and-Restore.html) This time, you're challenged to blog about how you backup your databases. [Postgres Databases and Schemas](https://www.crunchydata.com/blog/postgres-databases-and-schemas) — Craig has worked on Postgres databases of every scale, but they all have layers in common. This is aimed at beginners but quickly explains how clusters, databases, schemas and instances differ from each other. Craig Kerstiens [Cross-Partition Uniqueness Guarantees with Global Unique Indexes](https://www.highgo.ca/2022/10/28/cross-partition-uniqueness-guarantee-with-global-unique-index/) — More deep diving from the folks at Highgo. Cary Huang (Highgo) [Push-Based Outbox Pattern with Postgres Logical Replication](https://event-driven.io/en/push_based_outbox_pattern_with_postgres_logical_replication/) Oskar Dudycz [How to Upgrade PostGIS-Related Libraries on Ubuntu in Three Steps](https://www.cybertec-postgresql.com/en/postgis-upgrade-geos-with-ubuntu-in-3-steps/) — Such as GEOS or GDAL. Florian Nadler [Easier Upgrades and Image Management for Postgres in Kubernetes](https://www.crunchydata.com/blog/easier-upgrades-and-image-management-for-postgres-in-kubernetes) — Using Crunchy PostgreSQL for Kubernetes in particular. Andrew L'Ecuyer [How to Migrate Oracle Hierarchical Queries to Amazon Aurora](https://aws.amazon.com/blogs/database/migrate-oracle-hierarchical-queries-to-amazon-aurora-postgresql/) Raghav and Chintha (AWS) 🛠 Code and Tools [pg_stat_kcache: Gather Statistics About Disk Access and CPU Consumption](https://github.com/powa-team/pg_stat_kcache) — Sits atop pg_stat_statements and creates a couple of views you can use to measure CPU time, page faults, swaps, bytes read and written, and more at the database and query level. Dalibo and PoWA Team [Making Postgres More Affordable. Starting at $10/Month](https://www.crunchydata.com/blog/making-postgres-more-afforable-new-low-cost-plans-on-crunchy-bridge?utm_source=cppg-brf-1102) Crunchy Bridge sponsor [PostgreSQL 15 64-Bit FDWs for Windows](https://www.postgresonline.com/journal/index.php?/archives/405-PostgreSQL-15-64-bit-for-Windows-FDWs.html#pg1564wfdw) — Specifically, 64 bit binaries for _file_textarray_fdw_ (process arbitrarily delimited text) and _odbc_fdw_ (handy to connect to ODBC data sources). Leo Hsu and Regina Obe [pgdump-aws-lambda: Stream `pg_dump` Output to S3 via AWS Lambda](https://github.com/jameshy/pgdump-aws-lambda) — There are other ways to do this sort of thing on AWS, but this is a pretty direct and flexible way to take database backups. James H [PGroonga: Extend Full Text Search Features to All Languages](https://pgroonga.github.io/) — Brings the [Groonga](https://groonga.org/) fulltext search engine to Postgres which can suit certain use cases better than the Postgres default, such as with Japanese or Chinese. PGroona Project - [FerretDB 0.6](https://github.com/FerretDB/FerretDB) ↳ MongoDB alternative using Postgres for storage. - [PostgREST 10.1](https://github.com/PostgREST/postgrest) ↳ Serve a REST API from any Postgres database. - [pgMemento 0.7.4](https://github.com/pgMemento/pgMemento) ↳ Create an audit trail with schema versioning. - [trdsql 0.10.1](https://github.com/noborus/trdsql) ↳ Run PG-dialect SQL queries on CSV, LTSV, JSON & TBLN. - [aiopg 1.4](https://github.com/aio-libs/aiopg) ↳ Use Postgres from the Python `asyncio` world. - [GraphJin 0.21.5](https://github.com/dosco/graphjin) – Instant GraphQL to SQL compiler. **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|*)