Bookmarks (1010)

Re-Introducing Hash Indexes in Postgres — Billed as the ‘ugly duckling of index types’, this is a look at an uncommonly used type of index whose use was discouraged in the past but whose performance can sometimes beat the trusty B-tree as we see here.

pg_back 1.10: A Bash Script for Dumping Databases to Files — Essentially a wrapper around pg_dumpall and pg_dump to add a bit more structure to a simple database backup process. GitHub repo.

The Mapping of oid and relfilenode — Technical ‘under the covers’ stuff about how the location of tables are tracked internally. Few people need to understand this, but perhaps many more would like to :-)

Hosting Postgres on an AWS EC2 t4g Graviton2 ARM Instance — Sure, AWS has its managed RDS service for running Postgres, but if you fancy trying it out on EC2’s newer ARM-based instances, here are some pointers.

Sysbench: In-Memory Postgres, Postgres Is Boring — Sometimes no news is good news. “I am happy to claim that Postgres is boring as there are no CPU regressions from version 11 to 13.”

⏰ 12 Things You Need to Know About Time-Series Data — Get our top advice and time-series resources, from PostgreSQL pro tips and example projects to database benchmarks & more.

How to Limit Rows to At Most N Per Category (Take Two) — Several issues ago we linked to a handy tutorial demonstrating how to limit the rows returned by a query to a certain number per some second condition. It had a problem related to concurrency, however, so Hubert has a second go at it here.

Using R in Postgres for Logistic Regression Modeling — Continuation of a ‘Postgres for Data Science’ series of posts that moves on to doing data modeling and fire occurrence predictions with R right from the database.

Waiting for Postgres 14: Add idle_session_timeout — Have sessions terminated when they’re been waiting around for too long.

Amazon RDS for PostgreSQL Supports 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 — Support has also been added for pg_cron (for scheduling maintenance commands) and pg_partman (for managing partitions).

Free eBook: Efficient Search in Rails with Postgres — Speed up a search query from seconds to milliseconds and learn about exact matches, trigrams, ILIKE, and full-text search.

int4 vs. float4 vs. numeric — Postgres offers many different data types and this post focuses on three important ones for storing numbers and their differences.

All-In-One and Automated Website & Database Backups — Remove backups from your to-do list in 2 minutes. Automate all your server and database backups and store them where you want.

The Performance Impact of Idle Postgres Connections — Connections to a Postgres database consume resources even when they’re idle and this post digs into why, along with some benchmarks. If you need to get connection numbers down, consider poolers like Pgpool-II, PgBouncer or RDS Proxy (when appropriate).

Postgres is DB-Engines' 'DBMS of the Year 2020' — DB-Engines is a popular DBMS knowledge base and each year they have a ‘DBMS of the Year’ based on popularity growth. Postgres has won before but took back the crown from MySQL for 2020.

Security Best Practices for PostgreSQL — Here is a framework and a series of recommendations to secure and protect a PostgreSQL database. Download white paper.

The Shibboleth of 'PostgreSQL' — A shibboleth is a custom or tradition (which can be as simple as how a word is pronounced) which distinguishes one group from another – is the pronunciation of PostgreSQL or the choice of the term Postgres one?

Stop Worrying About Postgres Locks in Your Rails Migrations — Most common pitfalls that could happen when migrating a database, and how, at Doctolib, they mitigate them.

Loading Data into PostGIS: An Overview — PostGIS is a popular addition for Postgres when you need to work with geospatial objects and data. Here are some tools for loading such data into a PostGIS powered database.

Ensuring Consistent Performance After Version Upgrades with Amazon Aurora PostgreSQL Query Plan Management — You only need to read this if you’re using Aurora or you’re intrigued in what they’re up to tool-wise. Query Plan Management (QPM) is a feature Aurora has to compare and monitor query plans, such as across an upgrade, as here.

Redirect Query Result from psql to LibreOffice Calc — Let’s say you’ve got the results of a query in psql and you want to get them into a spreadsheet (LibreOffice Calc, in this case) – here’s one (Linux focused) approach.

A Postgres 13 Benchmark: Memory Speed vs. TPS — Does something as simple as running 2133MHz vs 3200MHz RAM on a machine impact Postgres benchmarks? Certainly not as much as you’d think!

postgres-deno: A Deno Postgres Extension — Run TypeScript in your Postgres functions and triggers. Still very much a proof of concept but a neat idea and an alternative to plv8.

Database Lab Engine 2.1: Instant Clones of Large Postgres Databases for Dev Environments — Provision independent non-production environments with multi-terabyte Postgres databases in a few seconds without extra costs.

ActiveRecordExtended: Add Extended Postgres Functionality to Ruby and Rails Apps — A continuation of work done on the previously popular postgres_ext library. This adds support for things like ANY, ALL and some JSON and HSTORE operators into Active Record, a popular Ruby-based ORM famously used in Rails apps.

RPM Repository for Unsupported PostgreSQL Releases — “If you are using an unsupported PostgreSQL version or Red Hat / CentOS / Scientific Linux / Oracle Linux release for some reason and need RPMs of those releases, we have archives of them.”

Highly Available PostgreSQL with Physical Replication — This white paper discusses the scenarios when designing a High Availability architecture for PostgreSQL. It looks at the desirable features and benefits of delivering High Availability in a business environment. Download white paper.

A Seamless MongoDB to PostgreSQL Migration — Coinbase is a large digital currency exchange so having a solid foundation for its data is a must. Here they share some lessons learned from a large cross-database data migration to AWS RDS PostgreSQL.

Wondering How You Should Optimize a Particular Query? — pganalyze uses auto_explain to automatically collect EXPLAIN plans. Identify slow sequential scans, sorts on disk & more.

Using Postgres Aggregate Functions in YugabyteDB to Analyze COVID-19 Data — Yugabyte is a Postgres-compatible ‘NewSQL’ database and here’s a practical look at using SQL functions for linear regression analysis of coronavirus statistics.

Cleaning Up the BLOBs — BLOB is the SQL standard’s binary string type which differs slightly to Postgres’s bytea approach, but either way, you need to take care if using the BLOB interface (such as via lo_import) for bringing binary data into your database because imported data can be left sitting around in your database even when you thought you deleted it.

When to Use Hyperscale (Citus) to Scale Out Postgres — Citus by Citus Data is now a part of Microsoft (though remains open source) and is a powerful way to horizontally scale Postgres. In this article, Claire explains when and why you’d need this sort of power.

▶️ Future of Database Architecture is Postgres Compatible — The language and the tools that you use with Postgres are important for your efficiency. Join us on Dec 16 for a live tech talk to learn how and why database architecture is evolving without leaving Postgres behind. Save your spot.

Replacing Lines of Code with 2 Little Regexes in Postgres — I love them but the term ‘regular expression’ admittedly sends many developers running for the hills. Is there a good use for them with Postgres? Yes, and Steve shows one off here.

Restoring An Individual Postgres Table — What if you want to restore an individual table from a dump?

Upgrading and Updating Postgres — Updating (such as from 12.0 to 12.1, say) is a different ball game to upgrading (such as from 12 to 13) and there are a few tools to consider.

Comparing Options for Bulk Loading into Postgres — A comparison of COPY, \copy, file_fdw, and pg_bulkload for loading in 624MB of CSV data under different scenarios.

A Look at pgtop, A top Clone for Postgres — Another way to look at currently running queries.

pg-listen: Postgres LISTEN and NOTIFY for Node.js — If you’re using Node.js and wish to use Postgres as a message broker with its notification channels, this will help. (If you’re unfamiliar with this Postgres feature, this article will help.)

PostGIS Trajectory Tricks: Playing with Space and Time — A fun, yet practical, post demonstrating how Postgres and PostGIS can be used for routing and calculating travel times and progress.

How a Postgres Extension Shellcode Execution Exploit Works — This would take a little work to pull off, as it requires a user be able to execute queries as a privileged user and also to place a specially crafted DLL on the server, but it’s useful to be aware of vectors like this.

Postgres Configuration Changes to Make Post-Installation — So you’ve installed Postgres onto your machine, and you want to start working with it. What’s next? Configuration changes!

How pgBackRest is Addressing Slow WAL Archiving — pgBackRest is a backup and restore tool. This post digs into one way it’s becoming faster with asynchronous archiving.

Running PgBouncer on AWS Elastic Container Service — RevenueCat has an Aurora-based database, wanted a connection pooler, and RDS Proxy didn’t fit the bill.

How to Write a Trigger for Checking a Given Value Before INSERT or UPDATE — Seen via Vlad Mihalcea.

Building a Modern Backend with TypeScript, PostgreSQL and Prisma — A four part written/video series focused on the role of the database in every aspect of backend development.