PostgreSQL Icon

PostgreSQL

PostgreSQL is an open source database system.
46 Stories
All Topics

Deno github.com

Deno gets an ORM

DenoDB has a fully-typed API (which is great for editor integration) and supports a whole host of backends: MySQL/Maria, SQLite, Postgres, and MongoDB.

Broad database support is great for library adoption, but as a user I’d prefer something that leans in to a specific ecosystem, which usually lets you squeeze more out of it.

Regardless of that, it’s great to see the Deno community building foundational tools like this.

Craig Kerstiens blog.crunchydata.com

Better JSON in Postgres with PostgreSQL 14

Craig Kerstiens:

Postgres has had “JSON” support for nearly 10 years now. I put JSON in quotes because well, 10 years ago when we announced JSON support we kinda cheated. We validated JSON was valid and then put it into a standard text field. Two years later in 2014 with Postgres 9.4 we got more proper JSON support with the JSONB datatype. My colleague @will likes to state that the B stands for better. In Postgres 14, the JSONB support is indeed getting way better.

A small but solid improvement to how you query JSONB, making it more JSON-y than ever.

Medium Icon Medium

10 things I hate about PostgreSQL

Long-time readers/listeners know I’m a Postgres fan, but I sometimes wonder if I heap too much praise on my favorite database. Enter Rick Branson:

While much of this praise is certainly well-deserved, the lack of meaningful dissent left me a bit bothered. No software is perfect, so exactly what are PostgreSQL’s imperfections?

I’ve been hands-on with PostgreSQL in production since 2003 with deployments ranging from small (gigabytes) to modest to very large (~petabyte). My perspective is largely from building and running systems that are at least intended to be continuously available. Needless to say, I have gained first-hand experience with PostgreSQL’s particular idiosyncrasies through some painful production issues over the years.

Rick has worked with much larger PG installs than I have, so his insights on this subject are well-grounded.

Martijn de Haan nullbyt.es

A new TypeScript Postgres query builder

Martijn de Haan:

It’s been almost 3 years since I started working on this query builder idea of mine. Today is finally the day Mammoth hits 1.0. Mammoth is a no-batteries-included type-safe Postgres query builder for TypeScript. Hooray!

Congrats on shipping, Martijn! Here’s a peak at the API:

const rows = await db
    .select(db.foo.id, db.bar.name)
    .from(db.foo)
    .leftJoin(db.bar)
    .on(db.bar.fooId.eq(db.foo.id));

Craig Kerstiens info.crunchydata.com

Building a recommendation engine inside Postgres with Python and Pandas

Craig Kerstiens told me about this on our recent Postgres episode of The Changelog and my jaw about dropped out of my mouth.

… earlier today I was starting to wonder why couldn’t I do more machine learning directly inside [Postgres]. Yeah, there is madlib, but what if I wanted to write my own recommendation engine? So I set out on a total detour of a few hours and lo and behold, I can probably do a lot more of this in Postgres than I realized before. What follows is a quick walkthrough of getting a recommendation engine setup directly inside Postgres.

Craig doesn’t necessarily suggest you put this kind of solution in production, but he doesn’t come out and say don’t do it either. 😉

CockroachDB cockroachlabs.com

Why are CockroachDB and PostgreSQL compatible?

There was a discussion in Slack today about the recent Postgres episode on The Changelog and a mention of considering CockroachDB in order to be distributed-by-default and Postgres compatible. But why is CockroachDB Postgres compatible? Here’s a breakdown from Ben Darnell, CTO and Co-Founder of Cockroach Labs…

CockroachDB is built to be largely compatible with PostgreSQL, meaning that software written to use PostgreSQL can sometimes (often!) be used with CockroachDB without changes.

Initially, CockroachDB toyed with the idea of compatibility with MySQL. What tipped the balance in PostgreSQL’s favor was a combination of multiple factors. There was initially a clear impression that PostgreSQL’s documentation of its network protocol was clearer, more detailed and overall more supportive of a third party implementation than MySQL’s documentation of its own protocol.

PostgreSQL rhaas.blogspot.com

Don't manually modify the Postgres data directory!

A general rule, but as with most things in software, there are exceptions:

Is Manual Modification of the Data Directory Ever Justified?

Sadly, I can’t answer “no” to this question. There are circumstances under which there is no reasonable alternative.

I’m definitely guilty of this and have mucked things up in the past (in dev, not prod!).

I still manually delete postmaster.pid a few times a month as Postgres doesn’t shut down completely sometimes when I reboot my computer. That file doesn’t get cleaned up, which results in Postgres not launching after the reboot.

PostgreSQL 2ndquadrant.com

Opinion: PostgreSQL is the world's best database

The title is not clickbait or hyperbole. I intend to prove that by virtue of both design and implementation that PostgreSQL is objectively and measurably a better database than anything currently available, with or without money considerations.

He goes on to detail 15(ish) reasons why Postgres stands out from the crowd. A compelling argument. I’d love to see similar write-ups by people who disagree.

CockroachDB openmymind.net

Migrating from Postgres to CockroachDB

This is a nice lessons learned post from one engineering team making a database switch.

Overall, I’m happy with how the effort turned out and with CockroachDB in general. Because it uses PostgreSQL’s wire protocol, existing PostgreSQL drivers should work as-is. But we did run into some challenges that are worth pointing out. Here’s a list of things you might want to consider…

I like the update at the end, which emphasizes the important of tests for making a switch of this magnitude:

The system that was migrated has solid tests and good coverage. While a lot of the differences we ran into are obvious (like lack of range types and triggers), others were more subtle (especially the odd on conflict behavior). Test coverage made a pretty significant impact in the speed of the migration and our confidence in pushing live.

PostgreSQL pg-structure.com

Extract the structure of a Postgres database into JavaScript

When your database is the source of truth, it’s often useful to inspect that truth and reuse it elsewhere in your application.

import pgStructure from "pg-structure";

async function demo() {
  const db = await pgStructure({ database: "db", user: "u", password: "pass" }, { includeSchemas: ["public"] });

  const table = db.get("contact");
  const columnNames = table.columns.map(c => c.name);
  const columnTypeName = table.columns.get("options").type.name;
  const indexColumnNames = table.indexes.get("ix_mail").columns;
  const relatedTables = table.hasManyTables;
}

PostgreSQL layerci.com

Postgres is a great pub/sub & job server

We all know Postgres is a great relational database (you do know that, don’t you?). When it comes time for a pub/sub solution, however, we often reach for Kafka, Redis, or RabbitMQ. But did you know that Postgres is pretty well suited as a persistent pub/sub server as well?

There are very few use cases where you’d need a dedicated pub/sub server like Kafka. Postgres can easily handle 10,000 insertions per second, and it can be tuned to even higher numbers. It’s rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.

Check the linked article for how they use Postgres in this fashion and a nice list of other benefits. For my money, the fact that I’m not adding another moving part to my infrastructure is reason enough to start with Postgres and go from there.

Derek Sivers sivers.org

PostgreSQL self-contained stored procedures example

Based on Derek’s now page he has ended his 7 year sabbatical and he’s taking Seth Godin’s advice to publish something every day. What Derek shared here is part of that commitment…

This week, I wrote a shopping cart to sell my books directly from my own site. So I took a couple extra hours today to put my code into public view, so anyone can play around with it.

It’s a working self-contained shopping cart / store. It’s a very concrete example of using stored procedures to keep all the data logic together in one place. You can use it from JavaScript, Python, Ruby, or any language you want, since all the functionality is in the database itself. It works.

0:00 / 0:00