Bookmarks (167)

Scaling Postgres with Amazon S3 (on Timescale Cloud) — This is specific to Timescale’s cloud platform, but an interesting development where Postgres can be used normally with data seamlessly ‘tiered’ and stored on lower cost S3 storage where appropriate. For a time series database where you might rarely want to access older data, this could be a big win in the long term.

Searching for Performance Regressions in Postgres 11–15 — What do you find if you use sysbench to search for performance regressions in Postgres v11 through to v15.2? Good news mostly. Broadly speaking, Postgres continues to get slightly faster with no serious performance slip-ups.

Postgres: The Graph Database You Didn't Know You Had? — Postgres is flexible enough to give you graph database like functionality out of the box, if you’re prepared to model out the right schema for it. If you need something more advanced, though, Apache AGE will bring more complete support including OpenCypher querying.

Learn How to Create the Best Postgres Index for Your Queries (eBook) — Creating the right indexes can improve your query performance significantly. In this PDF eBook, we provide detailed information around index types, operators, data types and more. After reading this book, you'll be able to create the best index for your queries.

The Importance of max_wal_size — work_mem and max_wal_size are two parameters that can have an outsized effect on performance. Here’s a basic technique for setting max_wal_size appropriately. (If you’re more interested in work_mem, Christophe did a separate blog post on that ;-))

Is Your Postgres Ready for Production? — Craig gets you ready for production with a quick production checklist of essentials, from backups and logging to statement timeouts and connection pooling.

Neon's Edge-Compatible Serverless Driver for Postgres — Neon is a multi-cloud ‘serverless Postgres’ platform, but one problem with certain serverless function platforms is an inability to make direct TCP connections to systems like Postgres. What to do? Use HTTP instead. Neon now offers this for V8/Node users in the shape of a node-postgres replacement that uses WebSockets instead.

pg_gpt: Experimental Extension for Making Queries with Natural Language — Yes, picture huge flashing lights around the word ‘experimental’, but it’s always interesting to see OpenAI’s ChatGPT taking on SQL query generation. Also a neat example of a simple extension built with Rust and PGX.

PgCat 1.0: Modern Postgres Pooler and Proxy — Built in Rust, PgCat offers support for sharding (including, experimentally, sharding based upon SQL syntax), load balancing and failover support.

Postgres 16 Highlight: More Patterns for pg_ident.conf — Postgres 16 makes auth configuration improvements to pg_ident.conf, allowing for the use of patterns for pg-user entries like in pg_hba.conf.

The New Old debug_parallel_query Setting in Postgres 16 — The force_parallel_mode will be called debug_parallel_query in Postgres 16. Why? To reinforce it’s for debugging purposes and not a magical way to get parallel query execution.

Basic Time-Series Data with Rails and Postgres — A good place to start if you are dealing with time-series data and aren’t ready to jump to something more dedicated.

DELETE vs. TRUNCATE — Find out how DELETE and TRUNCATE differ when you want to get rid of data. The main difference is between operating at the row and table level.

Frenzy: Postgres Wire Protocol Aware Mirroring Proxy — It’s early days for this Go-based Postgres proxy, but it’s fun to see something at this stage that does actually work and may be useful if you need to build something similar of your own.. “It’s suuuuuper hacky but in 2 hours of work I was able to proxy some queries!”

PgLock 1.0: Use Advisory Locks to Isolate Code Execution Across Machines — Aimed at Rubyists, but it’s interesting to see Postgres so directly used to manage locks across non-database related processes.

Easy Postgres Time Bins — Some tricks to ‘bin’ time-based data for generating useful graphs and reports. With functions like generate_series(), width_bucket(), floor(), and date_bin(), you can group data sets in different ways and quickly retrieve query-based reports.

Site Reliability Engineer And Software Engineer C++ — Join our "kick ass" team. Our software team operates from 17 countries and we're always looking for more exceptional engineers.

Announcing Postgres 15 on Heroku — Once the front runner in providing a managed Postgres service, Heroku has now brought Postgres 15 on board for all new apps going forward and you’re encouraged to upgrade ASAP if you’re using 9.6 or 10.

Table Maintenance after Bulk Modifications — Making large modifications to tables can skew the statistics used by Postgres’s query planner, so recalculating these statistics can be a wise move after making such changes. ANALYZE table_name to the rescue.

How Collation Works — Collation refers to the rules around how a system should compare and sort strings. Here, Peter Eisentraut explains how collations work in Postgres and the role of Unicode in defining collation standards.

pgwarehouse: Sync a Postgres Database to a Snowflake or Clickhouse Warehouse — The author said on Hacker News: “Built this recently to help a friend setup a Snowflake warehouse from their Postgres database. Also tested it with ClickHouse which is cool for running locally. Uses simple COPY which is fast and doesn’t require binlog access, but doesn’t support real-time replication as a result.”

Using Rust, Axum, Postgres, and Tokio to Build a Blog — This is only tangentially about Postgres, but shows off a way to use it from an up and coming Rust framework.

The PostgreSQL 14 Internals Book — In development for quite some time and based around a series of fantastic technical articles by Egor Rogov, PostgreSQL 14 Internals is a now fully completed book and available as a PDF here. Don’t be worried that it’ll be too technical for you – it’s packed with code-driven examples and diagrams and it’s easy to narrow down to...

HA and DR Recipes for PostgreSQL on Kubernetes — Watch this on demand webinar to learn how Percona Distribution for PostgreSQL Operator allows you to deploy and manage highly available and production-grade PostgreSQL clusters on Kubernetes.

The Wonders of Logical Decoding Messages — Did you know that you can write data directly to Postgres’s write-ahead log (WAL)? Logical decoding can then be used to retrieve this data and process and relay it in various ways. Gunnar explains how and looks at why you might want to do this (spoiler: it could be used for audit logging.)

System Roles: What, Why, and How? — Since v9.6, Postgres has introduced a variety of built-in roles, such as pg_read_all_data and pg_monitor. Hubert walks through them and what they allow users to do, but warns us of their global nature.

Introduction to Postgres Backups — An overview and comparison of some backup options available to Postgres users, including pg_dump, pg_basebackup, and pgBackRest.

What's Postgres Got to Do with AI? — A look at using Ruby and OpenAI’s API to get embeddings for your data (recipes in this case), feeding (ha!) them into Postgres and storing and querying the resulting vectors.

Adopting PgCat: A Next-Gen Postgres Proxy — The story of Instacart’s exploration and adoption of PgCat (a Postgres connection pooler/proxy with load-balancing, replica failover and concurrency features.)

pg_tiktoken: Postgres Extension for Fast BPE Tokenization — Streamlines text tokenization in Postgres databases. You can use tiktoken_encode for easy tokenized outputs and tiktoken_count to check text length limits, such as those in OpenAI’s language models.

pg_timetable v5.3 Released — pg_timetable is an advanced standalone job scheduler for Postgres. This is a logging focused release. You can now disable logging (possibly useful in high load environments), enable log rotation, and more. GitHub repo.

Mathesar: Spreadsheet Interface for a Postgres Database — A newly launched open-source tool that provides a grid-style interface to a Postgres database of your choice, with the aim being to enable developers to open up databases more directly to end users. It’s written in Python and is on GitHub too.

DBeaver 23: A Universal Database Tool — Written in Java, this is a long standing, open-source, cross-platform database tool for building and running queries, looking at tables, execution plans, etc. Recent commits seem to show they’re working on adding GPT-3 powered ‘smart completion’ features too..

Pigsty 2.0: Postgres In Great STYle? — I’m not sure about their choice of abbreviation, but Pigsty bills itself as an open-sourced ‘RDS alternative’ – not a cloud platform of its own, but essentially a Postgres distribution with a lot of stuff packed in.

Five Interesting Patches From January’s Commitfest — Postgres developer Chris Travers shares his thoughts on improvements to psql, a new synonym for infinity, transaction ID wraparound problems, and more.

Geocoding with Web APIs in Postgres — The steps to set up a geocoder with the US census geocoding API inside your database with a PL/Python function and triggers.

PostgREST: Get Started with Automated Postgres APIs — Why write your own code when Copilot.. ChatGPT.. PostgREST can automatically generate an API atop your Postgres database? Hans-Jürgen shows off the basics.

The Tale of the 11TB Write Ahead Log — There’s nothing like an alert out of the blue on the weekend to keep you on your toes. Adjust’s team were alerted to rapidly spiralling disk usage on their database server and a WAL file was to blame. These stories are always worth a read because they might pop back into your mind when similar...

The Integer at the End of the Universe: Integer Overflow in Postgres — When you’ve got too big an integer for the data type you’re using, trouble occurs. There are a few solutions (or workarounds) in Postgres. Jesse has a (rather epic) query to help you spot the problem on sequences and shares recommendations for a short term or long term fix.

Amazon RDS Now Supports Postgres 15 — Postgres 15, which landed last October, is now fully supported in RDS complete with over 80 extensions.

pgtt 2.10: Manage and Use Oracle-Style Global Temporary Tables — For those situations where you want to reproduce Oracle’s behavior rather than rewrite your code to use standard Postgres temporary tables.

Postgres Raster Query Basics — Paul takes us through how rasters (think pixels/cells, as opposed to vectors) are stored and queried with PostGIS and how to think about the challenges they raise.

ALTER TABLE … SET WITHOUT OIDS: A Big Gotcha — When you drop a column, it gets marked as inactive and that’s that. Unless you use SET WITHOUT OIDS which ends up rewriting the whole table and can result in a long running lock.

Can We Make Permissions Management More User-Friendly? — Grants and permissions have continued to evolve with new versions of Postgres but there are still some sharp edges and inconsistencies.

152 psql Tips — This was originally 100 tips when we first included it, but this list of bite-sized tips for the tried and tested psql client continues to grow. 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 prefer your tips snack-sized.

The History of Databases at Netflix — What does it take, at the infrastructure level, to deliver pixel-perfect and buffer-free streams to millions of concurrent users worldwide? Take a deep dive into the resilient, binge-ready architecture behind the streaming giant’s success.

European Route Planning with Postgres — We love Mark because when he discovers a dataset of bus routes across Europe, he fires up Postgres and builds visualizations and a route planner using pgRouting. There’s nothing particularly novel about this aprooach, but it’s the sort of post you’ll turn to when you need to solve related problems.