--- title: "A Postgres language server" publication: "Postgres Weekly" issue: 518 published_at: "2023-08-09" canonical_url: "https://postgresweekly.com/issues/518" --- Plus procedures to store table changes, why pgvector likes fewer dimensions, and projecting bad maps with PostGIS. | #​518 — August 9, 2023 [Web Version](https://postgresweekly.com/issues/518) Together with [![Polyscale logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_600,h_160/e_make_transparent/co_white,e_outline:12/rrcbnnamutiazyfczv81.png)](https://www.polyscale.ai/) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/xxnh7tmfkpiolqqtllgq.jpg)](https://github.com/supabase/postgres_lsp) [A Language Server for Postgres](https://github.com/supabase/postgres_lsp) — A _language server_ adds features like auto-complete, 'go to definition', or documentation on hover to IDEs like VS Code. Now, courtesy of Supabase, Postgres has its own in the shape of [postgres_lsp.](https://github.com/supabase/postgres_lsp) **It’s early days and not considered production ready yet,** but Supabase wants to involve the community in its development. Folks on Hacker News were [certainly excited about the potential](https://news.ycombinator.com/item?id=37020610). Supabase [Introducing PGMQ: Simple Message Queues Built on Postgres](https://tembo.io/blog/introducing-pgmq/) — Imagine AWS SQS but running within your Postgres database. PGMQ is written in Rust and provides ‘exactly once’ delivery semantics within a visibility timeout. Adam Hendel (Tembo) [![\](https://copm.s3.amazonaws.com/aa911279.png)](https://www.polyscale.ai/) [Faster Postgres. Everywhere.](https://www.polyscale.ai/) — PolyScale.ai is a fully autonomous distributed database cache at the edge. Supporting both TCP and HTTP, accelerate reads and lower global latency across any platform. Deploy in minutes, without writing code. PolyScale.ai sponsor [Hydra 1.0 Beta: Column Oriented Postgres](https://hydra.so/beta) — The pitch is simple: _“You can query billions of rows instantly on Postgres without code changes. Parallelized analytics in minutes, not weeks.”_ The 1.0 beta introduces columnar-optimized vacuuming, vector storage and similarity search (naturally), and a new column cache for more optimized JOINs. It’s built atop Citus Columnar which itself is a modernized version of the [cstore_fdw](https://github.com/citusdata/cstore_fdw/) extension. [GitHub repo](https://github.com/hydradatabase/hydra). Joseph Sciarrino / Hydra If the whole columnar tables versus row oriented tables thing seems confusing, Hydra has a [quick write up on the key differences here](https://docs.hydra.so/organize/data-modeling/row-vs-column-tables). [With pgvector, Fewer Dimensions are Better](https://supabase.com/blog/fewer-dimensions-are-better-pgvector) — It’s currently all the rage to store LLM embeddings in Postgres using [pgvector](https://github.com/pgvector/pgvector) (other vector use cases do exist, honest) but if you have millions of 1536 dimensional vectors from OpenAI, say, that adds up to a _lot_ of bytes. Are there benefits to using embeddings with far fewer dimensions? Ricahrdson, Rice and Romanov (Supabase) [Why Does My pg_wal Keep Growing?](https://www.cybertec-postgresql.com/en/why-does-my-pg_wal-keep-growing/) — The [`pg_wal` directory](https://www.postgresql.org/docs/current/wal-internals.html) stores your Postgres server’s write ahead logs (WAL) and if it’s growing relentlessly, you could have a config setting or two to blame.. Laurenz Albe [The Differences Between JSON and JSONB](https://ftisiot.net/postgresqljson/what-are-the-differences-json-jsonb-postgresql/) — One underlying data type (JSON), but two types in Postgres (JSON and JSONB). Francesco explains why. Francesco Tisiot [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/nf0fbinx6w4tnco3vfcd.jpg)](https://www.crunchydata.com/blog/xkcd-bad-map-projection-with-postgis) [Reproducing XKCD's 'Bad Map Projection' with PostGIS](https://www.crunchydata.com/blog/xkcd-bad-map-projection-with-postgis) — Popular webcomic [XKCD](https://xkcd.com/) had a strip showing off a `longitude=abs(longitude)` map (so 45ºW and 45ºE, say, would be in the same place). To honor this curious concept, Paul set out to trick PostGIS into rendering the same thing. Paul Ramsey [The Current State of Major Postgres Upgrades with CloudNativePG](https://www.enterprisedb.com/blog/current-state-major-postgresql-upgrades-cloudnativepg-kubernetes) — [CloudNativePG](https://www.enterprisedb.com/blog/introducing-cloudnativepg-new-open-source-kubernetes-operator-postgres) is an open source Kubernetes operator for Postgres from EDB. Gabriele Bartolini (EDB) [Partitioning as a Query Optimization Strategy?](http://ashutoshpg.blogspot.com/2023/08/partitioning-as-query-optimization.html) Ashutosh Bapat Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/oho2ncws31bemjkssihi.jpg)](https://github.com/Vonng/pigsty/releases/tag/v2.2.0) [Pigsty 2.2: A 'Batteries Included' Postgres Distribution](https://github.com/Vonng/pigsty/releases/tag/v2.2.0) — A curiously named Postgres distribution that calls itself ‘free RDS’. Essentially there are a lot of extensions pre-bundled, observability, self-healing HA, and now a large effort has been put into improving Pigsty’s _dashboard_ which you can [see on this demo site.](http://demo.pigsty.cc/) There’s also [an official site](https://pigsty.cc/en/) if you want to learn more. Feng Ruohang [PGHist: Store a History of Table Changes](https://pghist.org/en/) — An interesting and rather epic set of PL/pgSQL procedures (no, it’s not an extension) that dynamically creates triggers within the specified schema in order to audit table changes. [GitHub repo.](https://github.com/PGHist/PGHist) PGSuite [Effortlessly Migrate Your Heroku Postgres Database](https://www.crunchydata.com/migrate-from-heroku?utm_source=cppg-lnk-0809) Crunchy Bridge sponsor [RisingWave: Distributed SQL Stream Processing with a 'Postgres-like' Experience](https://github.com/risingwavelabs/risingwave) — RisingWave is one of those new databases that’s _not_ Postgres but opens itself to the Postgres ecosystem by way of wire protocol compatibility. You can learn more [in this introductory post.](https://www.risingwave.dev/docs/current/intro/) RisingWave Labs [SQLedge: Replicate Postgres to SQLite on the Edge?](https://github.com/zknill/sqledge) — An alpha/experimental Go-powered Postgres proxy that uses a local SQLite database for reads and forwards writes to an upstream Postgres server. Zak Knill - [PLV8 3.2](https://github.com/plv8/plv8) – JavaScript language add-on for Postgres. v3.2 improves Postgres 16 support but _drops_ Windows support (for now, at least). - [pgwire 0.16](https://github.com/sunng87/pgwire) – Postgres wire protocol in a Rust library. - [Patroni 3.1](https://github.com/zalando/patroni/blob/master/docs/releases.rst#version-310) – Template for Postgres High Availability. - [Orafce 4.5](https://github.com/orafce/orafce) – Oracle compatibility functions for Postgres. [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/mxgddlohnxvlt98krqt3.jpg)](https://github.com/danvergara/dblab) - [dblab 0.21](https://github.com/danvergara/dblab) ↳ Interactive CLI client for Postgres, MySQL & SQLite3. - [pspg 5.8](https://github.com/okbob/pspg) – Unix pager for tabular data. - [Psycopg 3 v3.1.10](https://github.com/psycopg/psycopg) – Next-gen Python database adapter. - [Psycopg 2 v2.9.7](https://github.com/psycopg/psycopg2) – And prior gen.. 😉 **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|*).