PostgreSQL Icon

PostgreSQL

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

Kubernetes enterprisedb.com

CloudNativePG – a new Kubernetes operator for Postgres

CloudNativePG is distributed under the Apache License 2.0, and is now owned and governed by a newly formed community of contributors to the project, built on solid principles and values inspired by the Cloud Native Computing Foundation (CNCF).

Among these are openness, fairness, inclusivity, technical excellence, “community over product/company,” built-in quality and built-in security. I’m part of the initial group of maintainers of the project, currently made up by the top six committers of the project within EDB.

If you’ve been following Ship It’s Kaizen episodes, you know we had troubles running Postgres in K8s and recently moved to a managed (sorta) database. I wonder if Gerhard will be tempted to try this operator out anyhow…

Ben Johnson github.com

A Postgres wire compatible SQLite proxy

Hot off the press from our friend Ben Johnson:

Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax.

It appears that Ben is taking code contributions this time around, but only for bug & documentation fixes.

PostgreSQL supabase.com

Postgres auditing in 150 lines of SQL

Where by “auditing” they mean change tracking, not security testing:

Auditing is particularly useful for historical analysis. To demonstrate, imagine you have a users table that tracks when a user is online. You might add a status column which can have one of two values: online and offline. How would you track how long a user is online for throughout an entire month? An auditing system would track every change with timestamps, and so you can measure the difference between each timestamp and sum them up for the entire month.

What really impresses me about the Supabase team is how much they’re leaning in to Postgres’ rich feature set to build high-quality-yet-low-level abstractions for their users. Paul Copplestone emphasized this when we had him on the show back in January.

PostgreSQL github.com

Building a cloud native storage engine for Postgres

One of the things we discussed with Paul Copplestone from Supabase was what, exactly, might a cloud native Postgres look like? Well, perhaps it will look like OrioleDB:

A new storage engine for PostgreSQL, bringing a modern approach to database capacity, capabilities and performance to the world’s most-loved database platform.

OrioleDB consists of an extension, building on the innovative table access method framework and other standard Postgres extension interfaces. By extending and enhancing the current table access methods, OrioleDB opens the door to a future of more powerful storage models that are optimized for cloud and modern hardware architectures.

PostgreSQL ardentperf.com

A hairy PostgreSQL incident

If learning from incidents grabbed your attention, here’s a good one we can all learn from:

It was 5:17pm today, just as I was wrapping up work for the day, and my manager pinged me with the following chat:

<manager>: Hi Jeremy - we have a <other team> ticket - escalated to <leader>, <leader>, etc. <principal> is on trying to advise as well. Are you available this evening if needed for diagnostics? <coworker> is on the call now

No obligation; just checking in to see what my availability is. Quickly thinking it over – I didn’t have any plans tonight, nothing in particular on my agenda. Why not? If I can help then someone else on the team won’t have to, and I don’t have anything better to do tonight.

The game is afoot. It might be a long night…

Shayon Mukherjee shayon.dev

Why I enjoy PostgreSQL (an infra engineer's perspective)

This post by Shayon Mukherjee is in response to the recently logged post by Mike Coutermarsh in praise of MySQL for infra folks:

This is not a MySQL vs PostgreSQL post. This is just a small summary of what I have come to appreciate about PostgreSQL as an Infrastructure Engineer.

Always good to keep in mind whenever comparing technologies: trade-offs abound and certain people in certain situations will value certain things about different technologies differently. That’s for certain. 😉

Mike Coutermarsh mikecoutermarsh.com

Why infrastructure engineers prefer MySQL

From Mike Coutermarsh:

For years I’ve been noticing this pattern of infrastructure engineers I really respect preferring MySQL and product engineers preferring Postgres. It took quite a while for me to understand it. Especially coming from my background as a product engineer. Infrastructure engineers generally…

Read on to hear why (from Mike’s perspective).

PostgreSQL github.com

An easy-to-use, zero-downtime schema migration tool for Postgres

Reshape… automatically handles complex migrations that would normally require downtime or manual multi-step changes. During a migration, Reshape ensures both the old and new schema are available at the same time, allowing you to gradually roll out your application.

Designed for Postgres 12+ and experimental for now. Do not use in production until it shapes up.

GraphQL supabase.com

A native GraphQL extension for PostgreSQL

The Supabase folks have open sourced a work-in-progress native PostgreSQL extension adding GraphQL support. First, they surveyed the field and liked what they saw from Graphile and Hasura, but…

we host free-tier projects on VMs with 1 GB of memory. After tallying the resources reserved for PostgreSQL, PostgREST, Kong, GoTrue, and a handful of smaller services, we were left with a total memory budget of … 0 MB 😬. Unsurprisingly, our pathological memory target disqualified any option that required launching another process in those VMs.

By their estimates, this effort will reduces the platform’s memory requirements by 525 TB/hours every month. Seems worth it!

Mitchell Hashimoto 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.

0:00 / 0:00