~postgresweekly | Bookmarks (121)

Postgres vs. SQL Server: A Look at B-Tree Index Differences — Both Postgres and Microsoft’s SQL Server use B-tree indexes as their default indexing mechanism but their approaches differ significantly. Even if you don’t use SQL Server, this is a good look behind the scenes and you’ll pick up a thing or two about how Postgres does things and how deduplication can lead to...

pg_sentence_transformer: Run A HuggingFace Sentence Transformer Model in a Background Worker — A prototype extension that starts a background worker and computes embeddings for a given source table and stores them into a newly created embeddings table without using an external LLM service.

Trunk: A Postgres Extension Registry — A Rust-powered open source package installer and registry for Postgres extensions with over 200 extensions already on offer. Trunk was built prior to Tembo’s hiring of PGXN’s David Wheeler to work on improving the Postgres extension ecosystem. PIG is another option in this space.

The Internals of MVCC: Hidden Costs of Updates vs Inserts — Postgres’ multiversion concurrency control (MVCC) features, while incredibly useful, add overhead to certain actions, such as updating, rather than inserting, rows. Rohan digs into the how and why.

Why Postgres Needs a Better API for Alternative Table Engines? — Postgres has mechanisms for allowing extensions to provide their own index types and table access methods, but it’s still not as easy to roll out powerful alternative table engines as it could be. Alexander of OrioleDB explains the limitations and some potential ways forward.

Life Altering Postgres Patterns — The author promises the title isn’t just clickbait and delivers twelve different bite-size, hard-learned tips and insights in areas from using UUIDs as primary keys and table naming to the use of schemas and views.

A Practical Guide to Taming Postgres Isolation Anomalies — A practical, Ruby-based (though SQL heavy) walkthrough of Postgres’ concurrency control, detailing real-world issues like lost updates, write skew, and other isolation anomalies. Even if you rarely run into conflicts, you’ll come away with valuable insights on managing transactions.

Semantic Search in Postgres: An Overview — Full text search is great, but if you want to query data based on its meaning rather than the literal data, you’ll need to bring in extensions like pgvector. Hans-Jürgen explains how it all works here.

🔒 A Postgres Security Checklist — The Center for Internet Security (CIS) releases security benchmarks for a variety of popular tools and services used in Internet infrastructure, and now there’s a set of Postgres guidelines (though it's behind an email wall with commercial-use restrictions, alas).

Creating Postgres Roles with Passwords Stored in Gopass — Gopass is a Go-powered CLI-based password manager for teams.

Reducing Cloud Spend: Migrating Logs from CloudWatch to Iceberg with Postgres — Postgres provider Crunchy Data has a lot of logs to store and storing those in AWS CloudWatch costs a lot too. Craig explains how Crunchy Data implemented a new workflow to store the logs in Iceberg tables on S3 instead, while still allowing SQL querying via Crunchy Data Warehouse.

Teable: Open Source Airtable Alternative atop Postgres — Airtable is a popular data table database SaaS, but here’s an open-source alternative along similar lines that sits atop Postgres and allows direct data querying with SQL. GitHub repo.

⭐ Electric 1.0: A Postgres Sync Engine — “Electric solves sync.” The idea is that if you have data in Postgres you need to sync elsewhere, Electric solves the hard problems of sync for you, including partial replication, fan-out, and data delivery, no matter your data model or Web framework. More info here.

Rainfrog 0.3: A Database Management TUI for Postgres — Think a little more structured than psql with vim-style navigation of queries, history, schemas, etc.

Sure, You Can Roll Your Own Auth - If You Hate Free Time — Don’t sacrifice your control. FusionAuth is downloadable, and can be developed and tested locally. A single-tenant architecture provides better scale and security than a purely multi-tenant SaaS model, even if you deploy via the cloud. Try it for free!

▶  🎤 Why Python Developers Just Use Postgres — Dawn Wages of the Python Software Foundation joins Claire Giordano to talk about why Postgres is particularly popular in the Python and Django communities.

Xata Agent: An 'AI Expert' in Postgres — An open source agent that monitors your database, finds root causes of issues, and suggests fixes and improvements. It’s early days in this space but it’s interesting to see different takes on the idea.

pgFormatter 5.6: A Tool to Format SQL Code — You can test it out online, or you can grab the source from GitHub (it’s written in Perl) and run it for yourself. What’s new in v5.6.

pg_ivm 1.10: Incremental View Maintenance (IVM) Extension — A more efficient way to update materialized views incrementally, applying only changes instead of fully recomputing the view like REFRESH MATERIALIZED VIEW does.

pgzx 0.3: Create Postgres Extensions using Zig — If you’re familiar with pgrx as a way to build Postgres extensions with Rust, this is a similar idea in the world of Zig, another modern systems language that’s a little more C-like than Rust.

Think About MERGE in Terms of a RIGHT JOIN — RIGHT JOIN isn’t encountered too often in SQL as you can just write a more universally understood LEFT JOIN alternative instead. However, right join semantics play an important role with the MERGE statement.

Can Artificial Intelligence Create Better Tables Than You? — As someone guilty of leaning on LLMs for a little SQL grunt work lately, this was an interesting read. As ever, you need to stay on your toes and check anything produced before rolling it into production!

You Can Make Postgres Scale — Newsflash: Postgres scales! After last week’s experiments with a 1 trillion row table using Citus, we know Postgres scales, but once you go beyond a certain point, bringing multiple machines into play becomes a potentially complex necessity and the PgDog horizontal scaling/sharding approach offers just one solution.

Troubleshooting Duplicate Primary Key Rows — If a table has unwanted ‘duplicate rows’ (which can happen due to a variety of quirks causing corrupt unique indexes) Greg has a multi-step technique for getting things back under control.