Latest posts
Tuple shuffling: Postgres CTEs for Moving and Deleting Table Data
David has some tricks and sample code for using CTEs to manipulate data and move things around inside your database. This can be especially handy for sorting, moving, or labeling data and moving it to an archive.
5 min readDeploying Crunchy Postgres for Kubernetes in a Multi-Zone Cluster
Bob has all the instructions for getting your Postgres in a Kubernetes multi-AZ environment.
6 min readPrepared Statements in Transaction Mode for PgBouncer
The newest version of PgBouncer lets you run prepared statements while using a connection pooler. Greg breaks down the performance gains of prepared statements, why they have traditionally been a challenge, and this new feature.
10 min readPostgres Goodies in Ruby on Rails 7.1
We are excited about some of the Active Record updates with Rails 7.1! Chris reviews some of the notable new features for working with Postgres including async queries, composite primary keys, native support for CTEs, unlogged tables, and syntax normalization.
6 min readWorking with Money in Postgres
Elizabeth has a primer for working with money in Postgres including what data type to choose, storing currency, and some sample functions.
Huge Pages and Postgres in Containers
We love it when we get to work with the open source community! We recently helped with patches to OCI and Postgres 16 to make huge pages work with Postgres in containers and Kubernetes.
4 min readFive Great Features of the PostgreSQL Partition Manager
Everyone's favorite Postgres partition project, pg_partman, just released version 5 this week. Keith takes a step back and reviews five notable features that make pg_partman an essential tool for managing large tables in Postgres.
12 min readUnderstanding Postgres IOPS: Why They Matter Even When Everything Fits in Cache
John gives us the basics of i/o in Postgres from how it works, to what parts of the database use IOPS, how to measure it, and what to do to optimize it. John also explains new view for pg_stat_io just released with Postgres 16 and how this can be used for performance analysis.
18 min readRolling the Dice with the PostgreSQL Random Functions
Paul shows examples of generating random numbers, random integers, random text values, and random groups. He also shows off the new random_normal function which just came out with Postgres 16.
6 min readJSON Updates in Postgres 16
Chris digs into some example code for the new JSON features that just launched with Postgres 16 last week. A new check for finding json objects and some new object and array functions.
6 min readActive Active in Postgres 16
Postgres 16 is out and it has some exciting updates to logical replication. Using a new WAL origin filter, you can avoid loopback transactions. Brian lays out the updates and how to get started with an active active cluster.
8 min readRandom Geometry Generation with PostGIS
Did you know you can use random() with spatial data types? Paul has sample code for generating random points, lines, polygons, hulls, and even some shapes using a Voronoi diagram. Bonus examples of random_normal(), coming in Postgres 16.
6 min readHNSW Indexes with Postgres and pgvector
pgvector for Postgres recently got a big upgrade with the new HNSW indexes. Chris explains what they are, how to use them, and what the accuracy and performance implications are for adding these.
12 min readTop 10 Postgres Management Tasks
The must know Postgres management tasks to look at for any scale. Plus a bonus image included showing the recent and upcoming Postgres release schedule.
Scaling Vector Data with Postgres
pgvector for AI in Postgres catch your eye? Start thinking about scaling and performance now. Chris has some tips about partitioning and truncating data to strike that perfect balance between performance and accuracy.
12 min readUse CI/CD to Automatically Update Postgres Images with Argo CD
Bob continues his series on using Argo CD to create GitOps workflows with Crunchy PostgreSQL for Kubernetes. In this post, he shows you how to create automated image updates. You can get a new Postgres image all the way from source to tested and ready for production with this workflow automation.
10 min readPostgres Subquery Powertools: CTEs, Materialized Views, Window Functions, and LATERAL Join
Wondering when to use a Materialized View or a CTE? Elizabeth has summaries, example queries, and comparisons for the most popular subquery tools.
10 min readFun with PostgreSQL Puzzles: Recursive Functions with Animations
Greg continues with Advent of Code series in Postgres with puzzle 19. This one uses recursive functions to follow blueprints and return the results. These functions even have terminal animations to show results as they are processed!
14 min readWhen Did Postgres Become Cool?
Craig takes a look at the history of Postgres. From the origins of the project through some of the key production features that make Postgres what it is today.
10 min read
XKCD Bad Map Projection with PostGIS
In honor of the beloved XKCD comic, Paul recreates some bad maps using PostGIS functions. Check out these funky cartographic contortions and learn a little spatial sql along the way.
4 min read
