Bookmarks (159)

Sequences vs. Invoice Numbers — Postgres sequences provide a handy abstraction around the generation of ID numbers, so surely they’d be good for invoice numbers too? Not so fast, says Hans-Jürgen (note that his specified criteria for the legality of invoice numbers varies by jurisdiction).

🦀  PostgresML Moving to Rust For Its 2.0 Release — The idea of using Rust to build Postgres extensions is slowly gathering pace. The creators of the PostgresML extension that adds ML model training and querying to Postgres gave Rust a go and have been so struck by the performance improvements, they’re going all in.

Migrating Postgres from Heroku to Crunchy Bridge — Developers of a growing business were looking to migrate Postgres. They wanted a new vendor at least as good as Heroku, with new features and developer tools. They tested Amazon RDS and several others. Find out why they chose Crunchy Bridge for their cloud Postgres. Read the case study.

pg_activity 3.0: htop-Like Activity Monitoring Tool — Much as you might use top or htop to monitor processes and CPU usage, pg_activity gives you a similar look behind the scenes of Postgres with views over running queries, up to date performance stats, and more.

dblab: An Interactive Client for Postgres, MySQL, and SQLite3 — A cross-platform (yes, including Windows) TUI-style app for working with various database systems. Certainly looks rather different to the standard CLI clients!

pgenv 1.3.2: A Postgres Virtual Environment Manager — pgenv is a shell script based ‘manager’ for working with multiple versions of Postgres at the same time on the same system. GitHub repo.

pspg 5.5.7: A Unix Pager Designed for Postgres Tables — If you use psql you probably use less as your pager, but it doesn’t directly support tabular data. This does. We’ve linked it a few times over the years, but it’s come such a long way and deserves fresh attention, particularly as it now supports paging output from numerous database CLI tools. Plus it...

An Overview of Postgres Data Flow — A high-level look at where data in a Postgres-based system might actually be going to and coming from, including caches, connection poolers, and buffers. A diagram wraps up the main ideas.

Using the Timescale Gem with Ruby — TimescaleDB is the extension that turns Postgres into a fully-fledged time series database and this thorough walkthrough demonstrates its use from Ruby.

PostgreSQL 15 Beta 4 Released — One downside to being weekly is when a release drops the day after you publish, and so it went with last Thursday’s release ;-) Luckily it’s a relatively minor step on the road to Postgres 15, mostly fixing up bugs and crashes.

'6 Useful Postgres Features I Wish I Knew When I Started' — Identity syntax (vs SERIAL), COALESCE + NULLIF, grouping sets, CTEs, and domains all make an appearance.

pg_idkit: An Extension for Generating IDs — Covered in the Choosing a Postgres Primary Key article featured above, this Rust-built extension offers up a myriad of different unique ID schemes from UUIDs to NanoIDs and ksuids.

How and Why Citus Took Distributed Postgres Fully Open Source — The inside story on why Citus / Microsoft open sourced their few remaining enterprise features in Citus 11, what exactly was open sourced, and what it took to make it happen.

What's an INNER JOIN or OUTER JOIN in SQL? — Steer clear if you’re already a join expert, but otherwise this is a nice introduction / refresher that also breezes through full joins and ‘semi-joins.’

What pgcrypto Does — It made us laugh, but sadly we had to change Paul’s actual ‘crypto’ related headline to avoid hitting the spam filters ;-) Nonetheless, he takes a practical, high level look at the options pgcrypto presents for encrypting and decrypting data inside a database.

Apache AGE 1.1.0: Graph Extensions for Postgres — AGE (A Graph Extension) was inspired by AgensGraph, a multi-model database Bitnine built from PostgreSQL 10, and brings graph processing and analytics features to Postgres via both OpenCypher and SQL. Homepage.

Choosing a Primary Key Identifier Format: The Options — Figuring out what data, or even composite of data, to use for a primary key can be tricker than you’d expect, and if you go with a common unique ID approach, which should you use? Victor tours some of Postgres’s many options along with their pros and cons before showing off an extension he’s built...

pg_netstat: Monitor Your Database's Network Traffic — Runs a background worker that uses libpcap to capture packets and aggregates at an interval of your choice. You can then query your database’s realtime network stats through a pg_netstat view to see packets in and out, bytes in and out, etc.

PDF Download: Understanding Kubernetes — Updated for 2022! This resource covers everything from fundamental concepts, to cluster components and network model implementation. The new edition includes the Kubernetes Dashboard, high-availability control plane, and autoscaling. Get it instantly.

Working with Transaction Snapshots — This is another handy feature to have in one’s back pocket. Snapshots allow multiple transactions to share identical views of the same database state. This can come in handy when allowing multiple pg_dump instances to more quickly build a single dump using the -j option, or in more complex data syncing scenarios (there’s a neat example provided in the post).

Digging into Postgres and OS Memory Allocation — Few folks need to go down to this level, but if spying on the interaction between Postgres and Linux’s memory allocation systems interests you..

📊  How to Plot an ASCII Bar Chart with SQL — Want to see an epic SQL query (which you could spend a good twenty minutes studying on its own) that results in something arguably quite useful? It could be interesting to turn this into a function you could use from psql..

Full SQL/JSON Support Deferred from Postgres 15 — While Postgres has a lot of ways to work with JSON data already, SQL/JSON is more of a standard for working with JSON from SQL generally. Postgres has supported its path language for a while, but patches to bring more complete support to v15 have been reverted, with a hope it can become a headline feature...

The What, Why, and How of Time-Series Databases — Time-series databases are the fastest growing database category – but what are they? Get a primer on what time-series data is - complete with real-world examples - when and why “normal” databases aren’t enough and ways to get started.

Working with Postgres Dump Manifests — Postgres dumps store not just what’s in the database but metadata about those databases too (in the form of manifests). Why is this interesting? You can edit the manifests to affect how such dumps are restored later!

Parallel Vacuuming in Amazon RDS and Amazon Aurora — No, this isn’t about vacuuming in RDS and Aurora at the same time(!) but about using Postgres 13+’s parallel-operating vacuuming process on both services. (Most of the article applies to Postgres generally, however.)

The Diversity of Postgres's Configuration Parameters — PostgreSQL has a lot of parameters to be configured. Guess how many. We won’t spoil it (this time) but it’s enough that Stefanie has managed to break them down to 15 groups. She also provides some pointers for working with them.

"ERROR: nextval: reached maximum value of sequence" — Basic advice when using the serial pseudo-type (a handy notation for creating an integer-based auto-incrementing column). A better option is to use serial8 or bigserial to get 64 bits instead which should last you.. quite a while :-)

How to Create an Audit Trail for an Aurora PostgreSQL Table — You could use a trigger on a table and track everything that occurs in another table, but that introduces too much of a performance overhead, says Rana. AWS Aurora offers a different approach using AWS Database Migration Service (DMS) to create a continuous audit log table on a separate database instance.

▶  Discussing Postgres and Ruby with Andrew Atkinson — Ruby-flavored Postgres database discussion covering areas like constraints, catching unsafe migrations, and analyzing query plans.

Kanel: Generate TypeScript Interfaces from Postgres — It works by inspecting a live database, sort of like a reverse object/relation mapper. GitHub repo.

Where Did the Stats Collector Go in Postgres 15? — Postgres’ stats collector keeps tabs on how many times a table or index is scanned, when vacuums take place, etc. This information is usually stored in temporary files, but Postgres 15 has moved to a more direct memory-based approach – luckily, anything monitoring pg_stat_* views will continue to work as-is.

pgagroal 1.5.0: High Performance Protocol-Native Connection Pool — Adds log formatting and rotation support, plus more. There are also some new tutorials, including this ‘getting started’ guide.

PostGIS 3.3.0 Released: The Geospatial Extension — PostGIS is ahead of the game by being best used with Postgres 15 (which is still in beta) but works with 11 and up. The NEWS file covers the improvements, which are relatively minor.

Automate Schema Version Control and Migration on Aurora with Flyway and AWS Lambda — Flyway is a Java-based version-driven schema management tool that supports (most) SQL databases and it can be set up along with some AWS Lambda serverless functions to deploy SQL scripts into Aurora Postgres environments on a regular basis.

How Postgres 15 Improves Communication in Logical Replication — Technical enough that few of you will need to know about this, but in short: communication between the WAL sender and receiver becomes more efficient and robust, and replication becomes slightly faster as a result. 👍

▶  How to Become a DBA — What are the various tasks, expectations, and roles involved in both junior and senior database administration roles? Michael and Nikolay discuss.

ALTER TABLE ... ADD COLUMN Done Right — Adding a column to a table in production can certainly be a hair-raising experience.. Hans-Jürgen quickly looks at the implications.

Cloudflare Open Sources Its PgBouncer Fork — Cloudflare has been maintaining an internal fork of PgBouncer (the connection pooler) packed with authentication bug fixes and new features around per-user and connection pool isolation. Now it’s public.

Move Your Postgres Data Closer to Users — PolyScale automatically caches Postgres at the edge, making data-driven apps blazingly fast. Deploy it without writing code in a few minutes. See how fast it is with our Playground demo.

A 'public' Schema Change in Postgres 15 — Andreas explains how the CREATE privilege will no longer be set by default for the public schema and how this could rear its head during your eventual v14 to v15 upgrades. He concludes: “It’s best not to rely on a writable public schema at all.” More discussion (from 2020) here.

13 Postgres Tools That Aren't psql — We asked the PostgreSQL community: psql is by far the most popular tool for interacting with PostgreSQL, but it definitely isn't the only one. In this blog post, we look at which other tools PostgreSQL users love most.

How to Migrate Scheduled Jobs to pg_timetable from pgAgent — pg_timetable is an increasingly popular scheduled job extension for Postgres and while Pavlo recently wrote about migrating from pg_cron to pg_timetable, it’s now pgAgent’s turn!

Postgres to Make Aliases Optional for Sub-SELECTS in FROM Clause — The SQL standard makes aliases mandatory for subqueries, but not all databases adhere to this. Could Postgres, too, make them optional? Seems so.

Rise of the 'Anti-Join' — “Find me all the things in set A that are not in set B,” asks Paul. He compares a few ways to do this before settling on the ‘anti join’ approach (basically a subquery feeding a WHERE NOT EXISTS clause).

Learn Postgres at the Playground — You might be familiar with online tutorial ‘playgrounds’ (such as Ruby’s) – now Craig Kerstiens and CrunchyData have unveiled a set of interactive online tutorials for Postgres coupled with a fresh collection of Postgres tips. There's some more background here.