Crunchy Data joins Snowflake. Read the announcement
Christopher 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 MorePaul Ramsey
Paul Ramsey
Generating random numbers is a surprisingly common task in programs, whether it's to create test data or to provide a user with a random entry from a list of items. PostgreSQL comes with just a few simple foundational functions that can be used to fulfill most needs for randomness. Almost all your random-ness needs will be met with the function. The function returns a double precision float in a continuous uniform distribution between 0.0 and 1.0. What does that mean? It means that you c...
Read MoreChristopher Winslett
Christopher Winslett
Postgres has been steadily building on the JSON functionality initially released more than 10 years ago . With Postgres 16, working with JSON has gotten a couple nice improvements. Primarily, this release added features that ease the manipulation of data into JSON and improve the standard SQL functionality using JSON. TL;DR: • A SQL/JSON data-type check. For instance, this lets you ask with SQL if something • Addition of SQL-standard JSON functions: , , , and A SQL/JSON data-type check. For...
Read MoreBrian Pace
Brian Pace
Support for logical replication arrived at Postgres just over five years ago with Postgres 10. Since then it's had a steady stream of improvements, but mostly logical replication has been limited to migrating data or unidirectional change data capture workflows. With Postgres 16 freshly released today, Postgres now has a better foundation to leverage logical replication for active-active setups. If you're unfamiliar with the concepts of logical replication or what does active-active mean we've g...
Read MorePaul Ramsey
Paul Ramsey
A user on the postgis-users had an interesting question today: how to generate a geometry column in PostGIS with random points, linestrings, or polygons? Random data is important for validating processing chains, analyses and reports. The best way to test a process is to feed it inputs! Random points is pretty easy -- define an area of interest and then use the PostgreSQL function to create the X and Y values in that area. Filling a target shape with random points is a common use case, and...
Read MoreChristopher Winslett
Christopher Winslett
Postgres’ pgvector extension recently added HNSW as a new index type for vector data. This levels up the database for vector-based embeddings output by AI models. A few months ago, we had written about approximate nearest neighbor pgvector performance using the available list-based indexes . Now, with the addition of HNSW, pgvector can use the latest graph based algorithms to approximate nearest neighbor queries. As with all things databases, there are trade-offs, so don’t throw away the list...
Read More