I'm lately back from Chicago, where PG DATA 2026 is now in the books! This is the two-day successor to PGDay Chicago, where I've been involved for the past few years volunteering, speaking, and finally organizing (and also volunteering and speaking). Everything went blessedly smoothly with only a few close calls, and we're already starting to plan next year's event. I've been on program committees…
A "fitness landscape" is a topographical metaphor for evolutionary success. A latent space described by genes or physical features, if you will, where fitness, or the suitability of a genotype or morphotype to its own physical environment, corresponds to elevation. Peaks therefore represent combinations of genes or features with high suitability, valleys those which struggle or even fail entirely.…
At my day job, we use row-level security extensively. Several different roles interact with Postgres through the same GraphQL API; each role has its own grants and policies on tables; whether a role can see record X in table Y can depend on its access to record A in table B, so these policies aren't merely a function of the contents of the candidate row itself. There's more complexity than that,…
In what I can't say isn't a tradition at this point, we're in an odd-numbered year so there's news on the pdot front! Get it here ! The biggest change (and the reason for the big 1-0-0) is simplifying usage: rather than requiring a shell function to plug the graph body into a template for interactive use, pdot now outputs the entire digraph or flowchart markup. The old behavior is still available…
Let's say you have two rasters, and you want to combine them with some extra value math -- perhaps you want to grade grassland around 50°N 100°E by latitude and elevation, so blue to green to red the more northerly or higher up the point. ST_MapAlgebra to the rescue! select c.rid, st_mapalgebra( e.rast, c.rast, ' case when [rast2.val] in (13, 14, 16, 17, 18) then -[rast1.y] + [rast1.val] else 0…
Of late, I've been falling down a bunch of geospatial rabbit holes. One thing has remained true in each of them: it's really hard to debug what you can't see. There are ways to visualize these. Some more-integrated SQL development environments like pgAdmin recognize and plot columns of geometry type. There's also the option of standing up a webserver to render out raster and/or vector tiles with…
Welcome to Squaria. Squaria is a continent of highly unstable geography defined by a single SQL query (with, as we'll see, many, many CTEs). Its only consistent properties at the moment are its boxy shape and the two unnervingly straight mountain ranges that cross its breadth and meet on its lower eastern edge. Those mountains are impossible, but today's topic is fluviation , that is, rivers and…
A couple years ago, I wrote about exploring a running database by plotting relevant subsets of the foreign key relationship graph in dot and piping the resulting images directly to the terminal. Things have progressed since then : I supplemented the original fks shell script with others plotting view dependencies, role hierarchies and grants, and finally started to map the effects of triggers and…
Another Phriday in the books, and it's time to see what all happened: Hari Kiran offered an introduction to the concepts and processes used in schema evolution with examples showing how to use Flyway, a popular Java-based tool. Grant Fritchey discussed what it means to roll a change back , why catastrophic failures are the good kind of failure, and how to work with deployment processes to adapt to…
I've used several migration frameworks in my time. Most have been variations on a common theme dating back lo these past fifteen-twenty years: an ordered directory of SQL scripts with an in-database registry table recording those which have been executed. The good ones checksum each script and validate them every run to make sure nobody's trying to change the old files out from under you. But I've…
It's almost Phriday again! This is a monthly blogging event for the PostgreSQL community. The rules : publish something on-theme on or near Friday, June 2nd include "PGSQL Phriday #009" in your title or first paragraph, and link to this invitation post share it! The best way to reach the greater Postgresphere is to get syndicated on Planet Postgres , but you can also share on #pgsqlphriday in the…
pg_stat_statements for May . As luck would have it, it's been invaluable to me over the past few weeks as I've been solving some performance problems of the "tens of millions of rows, row-level security, inverted indices, tens of thousands of rows returned, oops I never did get around to double-checking work_mem in production did I?" variety. The big lesson this time around: pay attention to the…
Here is a summary of the rules for substitution ; this assumes that braces are present around the substitution, i.e. ${...} . Some particular examples are given below. Note that the Zsh Development Group accepts no responsibility for any brain damage which may occur during the reading of the following rules. I'm doing inadvisably complicated things with zsh again; you'll need this to use it as…
It's triggers this time ! I've said it before and I'll say it again: if you need to compute, do it as close to your data as you can get away with . But programmed databases, and especially programmed databases that use triggers to encode automatic behaviors and responses, are infamously hard to understand, and the more programmed the more difficult. Why is this, and what can we do about it?…
While we are concentrating on our task, both our tools and our materials merge into one entity of perception which gives us feedback about state and progress of our work.... Software tools generalize our ways of handling aspects of the world around us; they organize our actions and condense them into gestures. — Reinhard Budde & Heinz Züllighoven, Software Tools in a Programming Workshop An…
Eight days, eight data analysis puzzles , eight solutions. After working out the password I imported the SQLite database into Postgres the simplest possible way (with a couple of tweaks at the end of the giant sed replacer; items.array seems to have been replaced by the orders_items junction table): createdb hanukkah sqlite3 noahs.sqlite .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL…
The team I work with at my day job maintains many applications and processes interacting across a smaller number of databases. This is hardly exceptional. We are also constantly adding, subtracting, and refining not only the client programs but also the database schemas themselves. This too is hardly exceptional: business requirements change, external systems expose new information and deprecate…
In "things you can do with a terminal emulator that renders images" : One way to look at a database's structure is as a graph of foreign key relationships among tables. Two styles of visual representation predominate: models or entity-relationship diagrams (ERDs) created as part of requirements negotiation and design, and descriptive diagrams of an extant database. The former are drawn by hand on…
Update: mcfly already existed, with a slightly different approach (neural network instead of structural analysis) with a lack of fuzzy searching its only real downside, so I added that there. Use mcfly instead! I use a lot of Rust command-line tools: ripgrep , fd , dust , and more. So when I had my own idea for a better command-line mousetrap, it seemed like the way to go. Shells log the commands…
A little while back I got my hands on a copy of Software Development and Reality Construction , the output of a conference held in Berlin in 1988. Among a variety of other more or less philosophical treatments of the theory and practice of software development, Don Knuth analyzes errors he made in his work on TeX; Kristen "SIMULA" Nygaard reviews his collaboration with labor unions to ensure that…
MassiveJS version 6 is imminent. This next release closes the widest remaining gap between Massive-generated APIs and everyday SQL, not to mention other higher-level data access libraries: JOIN s. This is something of a reversal for Massive, which until now has had very limited functionality for working with multiple database entities at once. I've even written about this as a constraint not…
Anybody doing something interesting to a production Cassandra cluster is generally advised, for a host of excellent reasons, to try it out in a test environment first. Here's how to make those environments effectively disposable. The something interesting we're trying to do to our Cassandra cluster is actually two somethings: upgrading from v2 to v3, while also factoring Cassandra itself out from…
One of my favorite good ideas to ignore is the maxim that you should have your deployment pipeline ready to go before you start writing code. There's always some wrinkle you couldn't have anticipated anyway, so while it sounds good on paper I just don't think it's the best possible use of time. But with anything sufficiently complicated, there's a point where you just have to buckle down and…
There's a new button at the bottom of this (and each) post. Try clicking it! (If you're reading this on dev.to or an RSS reader, you'll need to visit di.nmfay.com to see it) By now everyone's run into Twitter bots and automated text generators that combine words in ways that almost compute. There's even a subreddit that runs the user-generated content of other subreddits through individual…
NDC talks are up ! There's also the FullStack London version which is slightly condensed for a shorter timeslot, if you have a SkillsMatter account and want to get right to the fun parts. If you've read (almost) anything I've written, text or code, odds are you've run into Massive.js . On the off chance you haven't, the elevator pitch is that PostgreSQL exclusivity lets you get a lot more mileage…
At a talk I gave earlier this month, an audience member asked if Massive supported joining information from multiple tables together. It's come up on the issue tracker before as well. Massive does not currently have this functionality, and while I'm open to suggestions it's not on my own radar. The central reason for this is that join logic can be tricky to manage from the application architecture…
The shell has just about all the tooling I need for day-to-day operation of a computer: navigating and managing directories and files, text editing, and building, testing, and running projects I'm working on. What it isn't so great at is layouts, or really, displaying anything that isn't a text file (as fun as it is, I'm unwilling to switch out a proper image viewer for tiv ). Directory trees are…
Maven's probably the only all-in-one build tool I've ever really appreciated . I'll probably come to like make eventually and cement my status as old-before-her-time *nix crone, but I haven't had a reason to really dig into it yet so Maven it is. And I'm back at a mostly-Java shop, so let's have some fun! This week's goal: automating releases from our CircleCI instance. Sounds simple enough,…
I should probably say up front that I love working with Postgres and could die happy without ever seeing a mysql> prompt again. This is not an unbiased comparison -- but those are no fun anyway. The scenario: two applications, using Massive.js to store and retrieve data. Massive is closely coupled to Postgres by design. Specializing lets it take advantage of features which only exist in some or no…
I write a lot of integration tests that operate on data. The usual format for this is a setup function which gets the database into a particular state, a test or tests which validate the appropriate application functionality, and then a teardown function which cleans everything up so the next test suite can do its thing. There are different names and some little complexities (Mocha and AVA offer a…
This is a feature I added to Massive recently. I had cases where I was querying views on hierarchies of multiple JOIN ed tables to reference data. For an example, here's a query that returns a list of wineries, some of their wines, and the grapes that go into each: SELECT ws.id, ws.name, ws.country, w.id AS wine_id, w.name AS wine_name, w.year, va.id AS varietal_id, va.name AS varietal_name FROM…