Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Greg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 20's challenge "Grove Positioning System" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. Will I get these all posted before next year's AOC starts? Consider it a bonus challenge! :) Tech used: • CTEs (Common Table Expressions) • Using a non-integer type to help simulate a linked list • The ever useful file_fdw extension • sequence...
Read MoreChristopher Winslett
Christopher Winslett
Over the past 12 months, AI has taken over budgets and initiatives. Postgres is a popular store for AI embedding data because it can store, calculate, optimize, and scale using the pgvector extension . A recently introduced gem to the Ruby on Rails ecosystem, the neighbor gem, makes working with pgvector and Rails even better. An “embedding” is a set of floating point values that represent the characteristics of a thing (nothing new, we’ve had these since the 70s). Using the OpenAI API or any o...
Read MoreDavid Christensen
David Christensen
Recently we published an article about some of the best sql subquery tools and we were talking about all the cool things you can do with CTEs. One thing that doesn’t get mentioned near enough is the use of CTEs to do work in your database moving things around. Did you know you can use CTEs for tuple shuffling? Using CTEs to update, delete, and insert data can be extremely efficient and safe for your Postgres database. PostgreSQL 15 included the MERGE statement, which can be similar. There ar...
Read MoreBob Pacheco
Bob Pacheco
Modern resilient data infrastructure ensures that there's not a single point of failure. In the cloud, this means eliminating single points of failure at the data center and availability zone. So when deploying highly available Postgres in Kubernetes, you will want to ensure that each availability zone has one or more Postgres pods running in it. Today I want to walk through the basics of setting up a multi-zone Kubernetes implementation using Crunchy Postgres for Kubernetes . We will use a c...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Version 1.21 of PgBouncer , the Postgres connection pooler, has added a long awaited feature: support for prepared statements inside of transaction mode. Prior to this, one had to choose between using prepared statements (a performance win), and using PgBouncer's transaction mode (also a large performance win). Now, we can have our cake and eat it too 🎂 🎉. In Postgres, every SQL command you send to the server is parsed , prepared , and then executed . If you are running the exact same quer...
Read MoreChristopher Winslett
Christopher Winslett
I just spent last week at Rails World in Amsterdam and had a blast digging back into the Rails and Active Record world. In conversations with developers over the week, I had some notable takeaways from the newest version of Ruby on Rails that I just had to get written up. A quick summary before we dig in: • async queries : send long-running queries the background while the code runs along, great for pages with multiple long-running queries that can be run in parallel • composite primary keys : n...
Read MoreElizabeth Christensen
Elizabeth Christensen
Wouldn’t it be awesome if money worked just like time in Postgres? You could store one canonical version of it, it worked across the globe? Well sadly, money is a whole different ball of wax. Though like time, money is part of most database implementations and I wanted to lay out some of the best practices I’ve gathered for working with money in Postgres. I also have a tutorial up if you want to try this with Postgres running in a web browser. Postgres actually does have a data type. This...
Read MoreChris Bandy
Chris Bandy
We recently participated in a community solution for using huge pages when you’re running Postgres in containers or with Crunchy Postgres for Kubernetes . We worked on a patch to the underlying OCI (Open Container Initiative) runtime specification with our partner Red Hat and also worked on a patch for Postgres 16. For those of you using huge pages or running in containers, we have some additional notes on our solution in this write up. We’re really proud of the improvements we’ve made because...
Read MoreKeith Fiske
Keith Fiske
After much testing and work the PostgreSQL Partition Manager, pg_partman , version 5 is now available for public release. Thanks to everyone involved for helping me get here! My recent post discusses many of the big changes, so please see that post or the CHANGELOG for a full summary of version 5. What I'd like to do today is take a step back and review five notable features that make pg_partman an important tool for managing large tables in PostgreSQL: • Retention • Background Worker • Add...
Read MoreJohn Dalton
John Dalton
Disk IOPS (Input/Output Operations Per Second) is a key metric for measuring the performance of any disk system. It represents the number of read and write operations that can be performed per second. For PostgreSQL, which relies heavily on disk access, understanding and optimizing disk IOPS is crucial for achieving optimal performance. Today I want to go through the key topics involved with IOPs - what is it, how does it impact Postgres, how to measure it, and what to change for better performa...
Read More