Elizabeth 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 MoreElizabeth ChristensenChristopher Winslett
Elizabeth ChristensenChristopher Winslett
Histograms were first used in a lecture in 1892 by Karl Pearson — the godfather of mathematical statistics. With how many data presentation tools we have today, it’s hard to think that representing data as a graphic was classified as “innovation”, but it was. They are a graphic presentation of the distribution and frequency of data. If you haven’t seen one recently, or don’t know the word histogram off the top of your head - it is a bar chart, each bar represents the count of data with a defined...
Read MoreElizabeth ChristensenDoug Hunley
Elizabeth ChristensenDoug Hunley
The Center for Internet Security (CIS) releases security benchmarks to cover a wide variety of infrastructure used in modern applications, including databases, operating systems, cloud services, containerized services, and even networking. Since 2016 Crunchy Data has collaborated with CIS to provide this security resource for those deploying Postgres. The output of this collaboration is a checklist for folks to follow and improve the security posture of Postgres deployments. The PostgreSQL CIS...
Read MoreElizabeth Christensen
Elizabeth Christensen
Working on big data loads and or data type changes can be tricky - especially finding and correcting individual errors across a large data set. Postgres versions, 16, 17, and newer have a new function to help with data validation: . is a sql function that can be queried that will determine if a given input can be parsed into a specific type like numeric, date, JSON, etc. Here’s a super basic query to ask if ‘123’ is a valid integer. This function gives a t-true and f-false response. So if I as...
Read MoreElizabeth Christensen
Elizabeth Christensen
Materialized views are widely used in Postgres today. Many of us are working with using connected systems through foreign data wrappers, separate analytics systems like data warehouses , and merging data from different locations with Postgres queries. Materialized views let you precompile a query or partial table, for both local and remote data. Materialized views are static and have to be refreshed. One of the things that can be really important for using materialized views efficiently is inde...
Read MoreElizabeth Christensen
I can’t get through a zoom call, a conference talk, or an afternoon scroll through LinkedIn without hearing about vectors. Do you feel like the term vector is everywhere this year? It is. Vector actually means several different things and it's confusing. Vector means AI data, GIS locations, digital graphics, and a type of query optimization, and more. The terms and uses are related, sure. They all stem from the same original concept. However their practical applications are quite different. So...
Read MoreElizabeth Christensen
Elizabeth Christensen
Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, and . Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting. We also have a web based tutorial that covers Postgres Functions for Rolling Up Data by Date if you want to try it yourself with a sample dat...
Read MoreElizabeth Christensen
SQL makes sense when it's working on a single row, or even when it's aggregating across multiple rows. But what happens when you want to compare between rows of something you've already calculated? Or make groups of data and query those? Enter window functions. Window functions tend to confuse people - but they’re a pretty awesome tool in SQL for data analytics. The best part is that you don’t need charts, fancy BI tools or AI to get some actionable and useful data for your stakeholders. Window...
Read MoreElizabeth Christensen
Elizabeth Christensen
If you missed the database news lately, you could have missed that we just fused DuckDB with Postgres to build a really fast analytics platform based on Postgres. There’s so many interesting things you can do with this platform so expect to hear from me again 😉. Today I just want to show off one really simple trick for getting big data sets or training data into Postgres through Hugging Face. Hugging Face is a community repository of datasets, LLMs, models and other resources for Machine L...
Read More