PostgreSQL Icon

PostgreSQL

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

PostgreSQL github.com

Solving Advent of Code puzzles with PostgreSQL

Mitchell Hashimoto is partaking in Advent of Code this year and he’s added an extra facet to the challenge: implementing each solution with a single SQL statement.

I’m not an expert at SQL (or PostgreSQL) by any means. One of the reasons I decided to use SQL was to regain some proficiency while also learning some new concepts. I expect that many of my solutions are suboptimal and there are likely much better ways (by various definitions) to reach the same answer. I’d love to hear about those (make an issue) but I won’t be merging any changes since this represents my approach.

Sounds like a fun challenge! But how does he find the time? By timeboxing each problem to just 30 minutes.

MongoDB github.com

MangoDB – a truly open source MongoDB alternative

The team’s goal is to become “the de-facto open-source alternative to MongoDB.” Here’s why:

MongoDB is a life-changing technology for many developers, empowering them to build applications faster than using relational databases. Its easy-to-use and well-documented drivers make MongoDB one of the easiest to use database solutions available. However, MongoDB abandoned its open-source roots, changing the license to SSPL - making it unusable for many open source and commercial projects.

Most MongoDB users are not in need of many of the advanced features offered by MongoDB; however, they are in need of an open-source database solution. Recognizing this, MangoDB is here to fill the gap by providing an alternative.

This is currently a proof of concept. The concept: a proxy that converts Mongo’s wire protocol to SQL and stores everything in PostgreSQL. Fascinating idea! Will it work?

PostgreSQL notion.so

Herding elephants: lessons learned from sharding Postgres at Notion

Earlier this year, we took Notion down for five minutes of scheduled maintenance. While our announcement gestured at “increased stability and performance,” behind the scenes was the culmination of months of focused, urgent teamwork: sharding Notion’s PostgreSQL monolith into a horizontally-partitioned database fleet.

This is a deep-dive with lots of lessons along the way.

PostgreSQL kubegres.io

Kubegres is a K8s operator for deploying Postgres clusters

Kubegres is fully integrated with Kubernetes’ lifecycle as it runs as an operator written in Go. It is minimalist in terms of codebase compared to other open-source Postgres operators. It has the minimal and yet robust required features to manage a cluster of PostgreSql on Kubernetes. We aim keeping this project small and simple.

We’ve struggled with running Postgres inside K8s around these parts. Maybe this would’ve helped?

Craig Kerstiens blog.crunchydata.com

Postgres 14: it's the little things

Craig Kerstiens on the little wins coming in Postgres 14, which is scheduled for the end of September:

Postgres is, and for some time will continue to be, the first database I turn to. As Postgres focuses on the little things, it just deepens my commitment to it. Why look elsewhere when the bond just grows over time? So today I wanted to call some extra attention to those little things, the ones that don’t get the spotlight, but simply make a developer’s life better.

He goes on to highlight the JSON syntax improvements, read-only roles, what’s new in psql, and more.

PostgreSQL dok.community

Why you should be deploying Postgres primarily on Kubernetes

This is a ~15 minute presentation (with transcript) by Álvaro Hernández at a Data on Kubernetes Community event about why he believes Kubernetes solves a big problem with running Postgres in production.

Running a Postgres installation, with or without containers, is trivial. However, setting up a production environment is a whole different matter. Postgres is not by itself a production-ready software: it requires a set of side tools to complement its functionality: connection pooling, monitoring, backup tools, high availability software, you name it. This is called the “Stack Problem”. This brief talk discusses the Stack Problem, understanding how Kubernetes is the platform that best solves it, and what the main advantages (and disadvantages!) are of running Postgres on Kubernetes.

Drew DeVault drewdevault.com

In praise of PostgreSQL

Not only does Drew DeVault have a lot of insightful and interesting things to say about Postgres, I’m just generally a fan of people praising good software. Many people are quick to offer criticism, but slow to heap some praise. Let’s change that, maybe?

After 25 years of persistence, and a better logo design, Postgres stands today as one of the most significant pillars of profound achievement in free software, alongside the likes of Linux and Firefox. PostgreSQL has taken a complex problem and solved it to such an effective degree that all of its competitors are essentially obsolete, perhaps with the exception of SQLite.

For even more Postgres praise, listen to our episode of The Changelog with Craig Kerstiens.

PostgreSQL blog.crunchydata.com

Generating JSON directly from Postgres

Too often, web tiers are full of boilerplate that does nothing except convert a result set into JSON. A middle tier could be as simple as a function call that returns JSON. All we need is an easy way to convert result sets into JSON in the database.

PostgreSQL has built-in JSON generators that can be used to create structured JSON output right in the database, upping performance and radically simplifying web tiers. Fortunately, PostgreSQL has such functions, that run right next to the data, for better performance and lower bandwidth usage.

I certainly wouldn’t advise this in many (most?) scenarios, but I can see a time and a place where “cutting out the middle man” would be quite advantageous, indeed. Keep it simple. Keep it lean.

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.

PostgreSQL Medium (via Scribe)

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.

TypeScript 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.

  0:00 / 0:00