Bookmarks (1533)

An Overview of Security Best Practices for AWS's Postgres Platforms — An AWS-oriented look at good practices to follow when using either RDS for PostgreSQL or the Postgres flavor of Aurora. Security groups, forcing SSL, IAM authentication, and encryption all appear.

A Few Lessons from Doing Advent of Code Challenges with Postgres — Advent of Code is an annual set of programming challenges that takes place each December. Commonly languages like Python or C# are used, but some folks set out to use things like SQL. While the authors didn’t complete all 25 days with SQL, they learnt some interesting things through the attempt.

How to Shape Sample Data with generate_series() and SQL — The last in a series of posts about generating large sets of test data with Postgres itself in order to benchmark, test workloads, or play with database features. This stage moves on to manipulating such generated data to fit a required profile.

Postgres Indexes for Newbies — If you understand the differences between B-Tree, GIN, and GIST indexes, scroll on, but otherwise this gives you a very high level overview. If you then want to go (a lot) further you could read more detailed guides like these on B-trees or BRIN indexes.

Que 1.0: A Ruby Job Queue That Uses Postgres's Advisory Locks — We mentioned advisory locks above and here’s a perfect use for them bringing more reliable job queue functionality to Ruby by using locks to protect the state of the jobs.

Reshape: A Zero-Downtime Schema Migration Tool for Postgres — An experimental tool written in Rust to perform complex migrations on Postgres 12 and higher in a gradual way so that no downtime is required.

How to Create an Amazon CloudWatch Dashboard to Monitor Postgres on AWS — Goes into quite some depth on creating a dynamic dashboard for monitoring RDS or Aurora including things like slow queries, the error log, vacuuming processes, and more.

Distributed Locking with Postgres Advisory Locks — Postgres’s advisory locks feature can provide your app with a useful and trustworthy locking mechanism that doesn’t even have to involve data stored in Postgres itself. This post (from 2020 – we missed it at the time) demonstrates how one company uses them.

Tips for Installing Citus and Postgres Packages — Citus is well known as an extension for horizontally scaling Postgres and this post answers some questions as to how it’s packaged and how it’s installed and managed.

Going Into 'Read Only' Mode with Postgres — If you have a need to temporarily put a Postgres instance into a ‘read only’ state, default_transaction_read_only can be used to prevent write transactions.

Accelerating VACUUM in Postgres — It’s been a while since we had a flurry of articles about vacuuming, but here’s a few quick tips to tweaking Postgres’s settings to speed up the autovacuuming process in particular.

Elevation Profiles and Flightlines with PostGIS — I’m always impressed what geographic work you can do with PostGIS and Postgres. This post demonstrates turning a variety of points a flight took into an actual flight path.

Database Lab Engine: A Way to Clone Postgres Databases Quickly — Promises ‘blazing fast’ database cloning (claiming an ability to clone a 1TB database in 10 seconds thanks to its copy-on-write approach) particularly for building dev or QA/staging environments based on full-size production-esque databases.

Pgweb: A Web Based Postgres Database Browser/Client — A long established client written in Go, so zero-dependency binaries are easy to build where need be. GitHub repo.

Five Easy to Miss Postgres Query Performance Bottlenecks — Postgres’s query planner usually does a fantastic job, but can sometimes throw up the odd surprise or weird strategy, so the common-sensical or obvious query you wrote might not always go to plan! This post presents a valuable look at optimizing such seemingly obvious queries with the help of EXPLAIN ANALYZE and metadata analysis.

More SQL, Less Code, with PostgreSQL — There are numerous schools of thought on how much work a database system should be doing in contrast to the apps querying it. This post shows off some practical SQL-based ways you can lean towards having Postgres pick up more of the little jobs that your app may currently be handling.

On Progress Bars for Postgres Queries.. Let's Dive Deeper — Last week we featured a fun post about monitoring the progress of long running queries, and this post digs somewhat deeper into some of the mechanisms involved and includes some recipes for practical monitoring of lengthy queries in production.

Bi-Directional Replication using pglogical — While Postgres has native replication features now, the pglogical extension still has a few extra tricks up its sleeve including bi-directional replication.

Why I Enjoy PostgreSQL: An Infrastructure Engineer's Perspective — Mostly it’s down to schema changes being (relatively) pleasant.

temBoard 7.9: A Postgres Remote Control/Dashboard — A Python-built Postgres Web dashboard and monitoring system. You install an agent on the Postgres servers in question and then the temBoard webapp runs wherever you want.

Redix: A Fast Persistent Pure Key/Value Store — A key value store that speaks the Redis protocol but uses Postgres as its storage engine. First unveiled three years ago as a learning experience, this has been frequently updated and turned into a neat project - written in Go.

Setting Up a Kubernetes and Postgres Cluster from Scratch on Rocky 8 — Rocky Linux is a Linux distro designed to be ‘bug for bug compatible’ with Red Hat Enterprise Linux.

Getting a Query 'Progress Bar' in Postgres — This has one of the best opening graphics of any post in this newsletter, and the end result is pretty interesting too. The idea is to use a sequence as a sort of counter to track the progress of a query. It’s a bit of a hack but you’ll learn something here.

A Brief Summary of 2021 in the Postgres Ecosystem — Our last issue rounded up some of the most popular links of 2021, but in this piece Avinash of MigOps reflects on Postgres’ DB Engines ranking, the Postgres 14 release, and some of the new extensions that arrived in 2021. A useful look back!

🐘 The Developer Experience You Always Wanted for Postgres — Database as a service for Postgres as it should be. Cloud agnostic, superuser access, performance tuned instances, and packed with all the extensions you need. From the Crunchy Data team that’s 100% focused on Postgres at any scale. Try it today.

Postgres in 2021: An Observer's Year In Review — Another look back at 2021 but perhaps from an even more technical angle than that above. Lukas of pganalyze focuses on performance, autovacuum improvements, extended statistics, and more.

PostGIS 3.2.0 Released — This release dropped just after our final regular issue last year, but it’s been out for a few weeks now. The popular geospatial extension is aimed at Postgres 14 users (but will work with 9.6+) and is named after the late Olivier Courtin, a long time contributor to the project.

Five Tips for a Healthier Postgres Database in the New Year — Craig Kerstiens does his own 2021 end of year wrap-up by presenting five ideas for things you can do for a ‘healthier’ Postgres database in 2022.

How to Get Started with Babelfish for Aurora PostgreSQL — Amazon has been working for the past 18 months on so on an (open source) compatibility layer for Postgres so that SQL Server users can run their existing T-SQL queries upon Postgres instead. Here’s an introductory tutorial for Aurora users.

Learn How to Write SQL Recursive CTEs in Five Steps — Franck reminds us that you don’t need a graph database to be able to follow a tree of nodes in a database like Postgres: recursive CTEs are what you need. In this post he starts from the basics and works up to a recursive CTE as they’re not always obvious to understand in...

Why Give a Conference Talk (or, Why Give a Postgres Talk) — Claire is one of the organizers of the forthcoming Citus Con event so is keen to encourage folks to speak and provides a lot useful encouragement and motivation here.

Finding Customer Names with Interesting Unicode Characters — This post ends up with a useful SQL query for finding rows containing ‘interesting’ characters like emojis and symbols.

Fixing a Postgres Cluster That Has No Superuser — Normally there’s an administrative ‘superuser’ you can use to get yourself out of trouble, but what can you do when there isn’t?

Defining 'High Availability' in a Postgres World — What does High Availability actually mean when discussing Postgres clusters?

2.  125 psql Tips — This was originally 100 tips when we first included it, but now there’s 125 bite-sized tips for the tried and tested psql client. If you have a psql session coming up any time soon, this is worth a browse. It’s also possible to load a single tip and hop through randomly if you don’t want to be overwhelmed.

Creating Views with Postgres and TypeORM — TypeORM is a popular ORM that supports the Active Record and Data Mapper patterns and can be used from Node.

A Look Inside Postgres's Extended Query Protocol — A technical look at an area of Postgres you probably don’t need to know about.. but if you like looking under the hood, knowing that Postgres’s wire protocol supports two types of query may be of interest.

The Difference Between WHERE and HAVING in SQL — It’s all about the time they come into play.

The Fastest Way to Load Data Into Postgres from Rails — A look at four approaches from inserting one record at a time (not fast, unsurprisingly) through to custom copy approaches (faster), or using a background job (a lot faster).

Postgres vs Python for Data Cleaning: A Guide — Before you analyze data, you might want to clean it up a bit, and often you might turn to a language like Python to do this. But Postgres (and TimescaleDB) can be used to do more than you might think.

Extracting and Substituting Text with Regular Expressions — Admittedly I’m a huge regex fan, but even if you’re not, this post looking into things you can do with regexes in Postgres includes a quick refresher.

The SEARCH and CYCLE Features in Postgres 14 — PG14 introduced two SQL standard features for working with recursive queries. SEARCH lets you specify depth or breadth-first searching and the columns to track for sorting, whereas CYCLE lets you specify columns to track for cycle detection. There’s more on these in the official recursive queries docs.

Case Study: How the UK COVID-19 Dashboard Built on Postgres and Citus Works — If you like deep diving case studies, this is for you. The UK government’s Health Security Agency has been proactive in sharing data about the COVID-19 pandemic with its citizens, and Postgres (running on Azure’s Postgres and Citus service) is at the heart of its popular dashboard powered by billions of...

📅  Citus Con: An Event for Postgres in 2022 — The Microsoft Azure team is running a free online developer event next April (so you’ve got some time to plan) and, currently most importantly, they are looking for speakers. You have until February 6, 2022 to get your talk proposal sent in.

pgFormatter 5.2: A Tool to Format SQL Code — You can test it out online, or you can grab the source from GitHub (it’s written in Perl) and run it for yourself.

Compressing pgdump Dumps with xz — “A not-scientific look at how to compress a set of SQL dumps,” says Luca who observes that most SQL dumps are highly compressible due to their textual nature. He uses xz, a tool that can achieve higher compression rates than standard gzip to compress said dumps with much success.

Tricks for Faster Spatial Indexes with PostGIS — If you’re storing information about geographical areas, there’s potential for a lot of overlap which can make indexes less efficient than they could be. Paul explains how spatial indexes can be sensitive to the order of their inputs and how to mitigate problems.

Understanding GIN Indexes: The Good and the Bad — Using fancy data types, JSONB documents, and want to index? Skip B-tree indexes and head straight for the GIN ;-) Lukas covers what GIN indexes (introduced into Postgres almost 15 years ago now) are useful for and some potential pitfalls.