--- title: "We're back, with some Postgres hidden gems." publication: "Postgres Weekly" issue: 487 published_at: "2023-01-04" canonical_url: "https://postgresweekly.com/issues/487" --- Plus PGCon 2023's CFP, gifts for Postgres contributors, and ways to diff schemas. | #​487 — January 4, 2023 [Web Version](https://postgresweekly.com/issues/487) Together with [![logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_740,h_100/e_make_transparent/co_white,e_outline:10/d9sktlkk3ipcaszkhybd.png)](https://www.cockroachlabs.com/guides/oreilly-cockroachdb-the-definitive-guide/?utm_source=cooperpress&utm_medium=sponsor&utm_campaign=tofu-pipe-oreilly-definitive-guide&utm_content=guide-static-oreilly-postgres-definitive-download-free-newsletter-0104&utm_term=prosp-postgres-primary) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/rdmidinde5ho9k0u9hlz.jpg)](https://kmoppel.github.io/2022-12-23-generating-lots-of-test-data-with-postgres-fast-and-faster/) [The Different Levels of Generating Lots of Test Data with Postgres](https://kmoppel.github.io/2022-12-23-generating-lots-of-test-data-with-postgres-fast-and-faster/) — A neat article covering not only several ‘levels’ of generating dummy data for a database (including ‘exactly to the brim’) but also _why_ you might want to do so. A very useful post. Kaarel Moppel [PGCon 2023's Call for Papers](https://www.pgcon.org/2023/papers.php) — One of Postgres’s leading conferences is back (for the 17th time!) and taking place in Ottawa, Canada cross May 30-June 2. You’ve got until January 19 to get a proposal in if you want to speak (or you can wait for a later call for _lighting talks_). Need some inspiration? Here’s [last year’s schedule.](https://www.pgcon.org/events/pgcon_2022/schedule/) PGCon Team Don't want to travel? The [CFP for Citus Con 2023](https://www.citusdata.com/blog/2023/01/03/citus-con-2023-cfp-is-open/) is also still open. [![\](https://copm.s3.amazonaws.com/a7cc1aab.png)](https://www.cockroachlabs.com/guides/oreilly-cockroachdb-the-definitive-guide/?utm_source=cooperpress&utm_medium=sponsor&utm_campaign=tofu-pipe-oreilly-definitive-guide&utm_content=guide-static-oreilly-postgres-definitive-download-free-newsletter-0104&utm_term=prosp-postgres-primary) [A Definitive Guide to Cloud-Native, Distributed PostgreSQL](https://www.cockroachlabs.com/guides/oreilly-cockroachdb-the-definitive-guide/?utm_source=cooperpress&utm_medium=sponsor&utm_campaign=tofu-pipe-oreilly-definitive-guide&utm_content=guide-static-oreilly-postgres-definitive-download-free-newsletter-0104&utm_term=prosp-postgres-primary) — Learn what you need to know about building distributed applications with CockroachDB. PostgreSQL wire compatibility gives you the familiarity of SQL while designing and building apps for distributed infrastructure. Download the O’Reilly guide. CockroachDB sponsor [Improving `GROUP BY` with `CREATE STATISTICS`](https://www.cybertec-postgresql.com/en/improving-group-by-with-create-statistics/) — If you’re creating reports based on time series data, you might need to `GROUP BY` an expression rather than a column’s exact value – this can result in issues that `CREATE STATISTICS` can help address. Hans-Jürgen shows us how. Hans-Jürgen Schönig [Some Postgres 'Hidden Gems'](http://peter.eisentraut.org/blog/2022/12/27/postgresql-hidden-gems) — Peter picks eight different useful features from Postgres 9.4 through 14 that “flew under the radar” due to being overshadowed by other headline features. Peter Eisentraut **IN BRIEF:** - Did you know a group of volunteers sends gifts out to those who contribute to the Postgres codebase? If that includes you but you've been missed out, [Mark Wong wants you to know what to do.](https://markwkm.blogspot.com/2022/12/acknowledged-individuals-in-postgresql.html) He also shares a graph of the countries from which contributor gifts are claimed with the US, Germany, India, Russia, and France in the lead. - 📅 PG Day Chicago 2023 (taking place on April 20) is [looking for talk proposals, sponsors, and volunteers.](https://hdombrovskaya.wordpress.com/2022/12/19/pg-day-chicago-2023-cfp-open-call-for-volunteers/) - The latest _PostgreSQL Person of the Week_ [is Lukas Eder](https://postgresql.life/post/lukas_eder/), well known for his [jOOQ](https://www.jooq.org/) tools and [database blog.](https://blog.jooq.org/) - Greg Sabino Mullane has [continued to solve](https://www.crunchydata.com/blog/solving-advent-of-code-2022-using-postgres-day-10) the Advent of Code 2022 puzzles using Postgres. Prefer videos? [Ryan Booz has been doing them too.](https://www.softwareandbooz.com/advent-of-code-2022-days-6-10-with-postgresql/) [When `tsvector` Was Working as Expected on Most Hosts.. But Not on_One_](https://news.freshports.org/2022/12/18/when-tsvector-was-working-as-expected-on-most-hosts-but-not-one/) — A tale of chasing down a curious bug. Dan Langille [Don't Do This: Creating Useless Indexes](https://mydbanotebook.org/post/too-many-indexes/) — _“The worst I’ve seen was a database with 12 times more indexes stored on disk than data..”_ Lætitia AVROT [Free eBook: How to Create the Best Postgres Index for Your Queries](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklySecondary01042023) pganalyze sponsor [JSON Logs in Postgres 15](https://www.cybertec-postgresql.com/en/json-logs-in-postgresql-15/) — A look at taking advantage of Postgres 15’s new JSON-based logging feature. Hans-Jürgen Schönig 🛠 Code and Tools [Diffing Postgres Schema Changes](https://www.percona.com/blog/diffing-postgresql-schema-changes/) — Making changes to a schema is one thing, but what about identifying the _differences_ between two states? Using `diff` on dumps provides one option, but [apgdiff](https://www.apgdiff.com/) can take things to a higher level. Robert Bernier [A Look at the Pre-Release of PostgREST 11](https://supabase.com/blog/postgrest-11-prerelease) — [PostgREST](https://postgrest.org/en/stable/) is a popular Haskell-based HTTP server that offers up a REST API for the Postgres database of your choice. Steve Chavez (Supabase) [external_file v1.1: Work with Files on the Server](https://www.postgresql.org/about/news/external_file-extension-v11-released-2565/) — Adds similar functionalities as Oracle’s `BFILE` data type that stores unstructured binary data in flat files outside the database. Dominique Legendre and Gilles Darold - [Supabase Realtime 2.0](https://github.com/supabase/realtime) ↳ Broadcast, presence, and Postgres changes over WebSocket. - [PGSync 2.4](https://github.com/toluaina/pgsync) ↳ Postgres to Elasticsearch syncing middleware. - [pspg 5.7.1](https://github.com/okbob/pspg) ↳ Rich Unix pager with tabular data support. - [Orafce 4.0.2](https://github.com/orafce/orafce) ↳ Ports of some Oracle functions to ease migrations. - [FerretDB 0.8](https://github.com/FerretDB/FerretDB/releases/tag/v0.8.0) – MongoDB-a-like atop Postgres. - [plpgsql_check 2.2.6](https://github.com/okbob/plpgsql_check) – Linter for PL/pgSQL. - [epgsql 4.7](https://github.com/epgsql/epgsql) — Erlang Postgres client library. **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|*)