Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul 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 MoreElizabeth Christensen
Elizabeth Christensen
Postgres databases are very compliant, they do what you tell them until you tell them to stop. It is really common for a runaway process, query, or even something a co-worker runs to accidentally start a never ending transaction in your database. This potentially uses up memory, i/o, or other resources. Postgres has no preset default for this. To find out your current setting: A good rule of thumb can be a minute or a couple minutes. This is a connection-specific setting, so you’ll need to rec...
Read MoreChristopher Winslett
Christopher Winslett
Note: We have additional articles in this Postgres AI series . Vector data has made its way into Postgres and I’m seeing more and more folks using it by the day. As I’ve seen use cases trickle in, I have been thinking a lot about scaling data and how to set yourself up for performance success from the beginning. The two primary trade-offs are performance versus accuracy. When seeking performance with vector data, we are using nearest neighbor algorithms, and those algorithms are built around p...
Read MoreBob Pacheco
Bob Pacheco
When working with containers you always have to be mindful of the age of the containers. Every day new CVEs are being discovered and are turning up in image scans. One benefit of having a CI/CD pipeline is the ability to implement security automation. Let's assume you release a monthly update of your containers that are built on the latest version of the base image and all of the most recent patches have been applied. This ensures that each month you can remediate any CVEs that might have popped...
Read MoreElizabeth Christensen
Elizabeth Christensen
Beyond a basic query with a join or two, many queries require extracting subsets of data for comparison, conditionals, or aggregation. Postgres’ use of the SQL language is standards compliant and SQL has a world of tools for subqueries. This post will look at many of the different subquery tools. We’ll talk about the advantages and use cases of each, and provide further reading and tutorials to dig in more. I’ll take a broad definition of “subquery”. Why am I calling all of these subqueries? The...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
We've also loaded a tutorial for Day 19's challenge if you want to try it with a pre-loaded data set. This article will contain spoilers both on how I solved 2022 Day 19's challenge "Not Enough Minerals" 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. Day 19 tasks us with creating lots and lots of mini robots to gather resources and feed our herd of elephants (rescued a few days back ). We'll do...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres wasn't always the cool kid. It didn't emerge from millions of dollars of VC funding, it didn't have a team of DevRel evangelists championing it, it simply started and evolved. Postgres just passed the 25 year milestone as an open source project. With the upcoming release of Postgres 16 in coming months, we thought we'd pause for a minute and take a look back at Postgres and how it got to where it is today. If for some reason you've been living under a rock and are unfamiliar with Postgr...
Read MorePaul Ramsey
Paul Ramsey
Last week, Randall Munroe dropped his latest XKCD "Bad Map Projection", number six, " ABS(Longitude) ", which looks like this: Truly this is a bad map projection, on a par with the previous five: • Liquid Resize • Time Zones • South America • Greenland Special • Madagascator Liquid Resize Time Zones South America Greenland Special Madagascator The last two are just applications of common map projections with very uncommon projection parameters that accentuate certain areas of the globe, a c...
Read MorePaul Ramsey
Paul Ramsey
In my last blog post , I showed four ways to access a remotely hosted CSV file from inside PostgreSQL: • Using the command with the option, • Using the http extension and some post-processing, • Using a PL/Python function, and • Using the ogr_fdw foreign data wrapper. Using the command with the option, Using the http extension and some post-processing, Using a PL/Python function, and Using the ogr_fdw foreign data wrapper. In this post, we are going to explore ogr_fdw a little...
Read More