Elizabeth ChristensenDavid Christensen
Elizabeth ChristensenDavid Christensen
New to Postgres 18, features like asynchronous i/o, uuid v7, b-tree skip scans, and virtual generated columns.
Paul Ramsey
Paul Ramsey
I am kicking off a short blog series on PostGIS performance fundamentals. For this first example, we will cover fundamental indexing. We will explore performance using the Natural Earth “admin0” (countries) data (258 polygons) and their “populated places” (7342 points). A classic spatial query is the “spatial join”, finding the relationships between objects using a spatial contain. “How many populated places are there within each country?” This returns an answer, but it takes 2200 millisecond...
Read MoreElizabeth ChristensenJesse Soyland
Elizabeth ChristensenJesse Soyland
Moving a Postgres database isn’t a small task. Typically for Postgres users this is one of the biggest projects you’ll undertake. If you’re migrating for a new Postgres major version or moving to an entirely new platform or host, you have a couple options: • Using pg_dump and pg_restore : pg_dump is a very reliable way to collect an entire database and restore it to a new place. This includes the entire schema, all tables, and special database elements. If you’re migrating a small database, that...
Read MoreBrandur Leach
Brandur Leach
Postgres 18 was released today . Well down page from headline features like async I/O and UUIDv7 support, we get this nice little improvement: This release adds the capability to access both the previous (OLD) and current (NEW) values in the RETURNING clause for INSERT, UPDATE, DELETE and MERGE commands. It's not a showstopper the way async I/O is, but it is one of those small features that's invaluable in the right situation. A simple demonstration with to get all old and new values: Say w...
Read MoreElizabeth Christensen
Elizabeth Christensen
I had a chance last week to sit down and read the original academic paper announcing Postgres as a platform and the original design goals from 1986. I was just awestruck at the forethought - and how the original project goals laid the foundation for the database that seems to be taking over the world right now. The PostgreSQL creators totally nailed it. They laid out a flexible framework for a variety of business use cases that would eventually become the most popular database 30 years later....
Read MorePaul Ramsey
Paul Ramsey
I am excited to announce PostGIS 3.6 and GEOS 3.14. The PostGIS spatial extension to PostgreSQL and the GEOS computational geometry library taken together provide much of the functionality of PostGIS, and are the open source focus of the ( Crunchy Data ) Snowflake PostGIS team. Each year we work hard to ensure a release before the autumn PostgreSQL release, to ensure that the latest and greatest PostGIS and GEOS ready to be packaged with the latest PostgreSQL . All the critical issues are...
Read MoreElizabeth ChristensenDavid Christensen
Elizabeth ChristensenDavid Christensen
Postgres 18 will be released in just a couple weeks! Here’s some details on the most important and exciting features. Postgres 18 is adding asynchronous i/o. This means faster reads for many use cases. This is also part of a bigger series of performance improvements planned for future Postgres, part of which may be multi-threading. Expect to see more on this in coming versions. What is async I/O? When data isn’t in the shared memory buffers already, Postgres reads from disk, and I/O is needed...
Read MoreElizabeth Christensen
Elizabeth Christensen
A modern-day Postgres instance creates robust and comprehensive logs for nearly every facet of database and query behavior. While Postgres logs are the go-to place for finding and debugging critical errors, they are also a key tool in application performance monitoring. Today let’s get set up with logging for Postgres - starting with the basics of what to log, how to log what you want, and as reward for your hard work - how to use these to monitor and improve performance. The Postgres docs on l...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is amazing for many reasons. One area that doesn't get near enough attention is datatypes. Postgres has a rich set of datatypes and one important one for devs to be especially excited about is JSONB. JSONB which is structured and indexable. In JSON, the B stands for binary (or as we like to think of it B is for better), which means data is pre-parsed as it is stored. How do you get the most out of JSONB from a retrieval perspective? Enter Postgres' rich indexing support. Most databases...
Read MorePaul Laurence
Paul Laurence
We are excited to announce that Crunchy Data is joining Snowflake to bring Postgres to the AI Data Cloud. While every year feels like the year of Postgres these days, 2012 did not. For most, 2012 was the year of "Big Data" as NoSQL technologies were demonstrating powerful new data management use cases, and the idea of building a new company around SQL technology was either foolish or contrarian depending on who you asked. At the same time, Crunchy Data was still just an idea and was beginning to...
Read MoreBrandur Leach
Brandur Leach
The API powering our Crunchy Bridge product is written in Go, a language that provides a good compromise between productivity and speed. We're able to keep good forward momentum on getting new features out the door, while maintaining an expected latency of low double digits of milliseconds for most API endpoints . A common pitfall for new projects in fast languages like Go is that their creators, experiencing a temporary DX sugar high of faster compile and runtime speeds than they've previous...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres comes with built-in partitioning and you can also layer in for for additional help with maintenance of your partitioning. It works quite well for partitioning your data to make it easy to retain a limited set of data and improve performance if your primary workload is querying a small time series focused subset of data. Oftentimes, when implementing partitioning you only keep a portion of your data then drop older data as it ages out for cost management. But what if we could move ol...
Read More