Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Joey Mezzacappa
Joey Mezzacappa
We just launched our Postgres Playground . Running Postgres in the web browser was not exactly commonplace before, so naturally, people are wondering how it works. It actually started as a fun weekend experiment. Here's a screenshot I saved, just moments after recovering from the initial "whoa, it's working!" effect. The next morning, I shared this screenshot in our internal Slack channel for web frontend engineering. Our mental gears began to turn as we imagined what might (and might not) b...
Read MoreCraig Kerstiens
Craig Kerstiens
Today I'm excited to introduce a new place for devs to polish their Postgres skills, a Postgres Playground and Tutorials from Crunchy Data. What is the playground? Put simply it is: 1. Postgres running in your local web browser 2. With canned datasets you can load 3. Guided tutorials to follow along to learn about the power of Postgres Postgres running in your local web browser With canned datasets you can load Guided tutorials to follow along to learn about the power of Postgres Wait?!?!? Pos...
Read MorePaul Ramsey
Paul Ramsey
Find me all the things in set "A" that are not in set "B". This is a pretty common query pattern, and it occurs in both non-spatial and spatial situations. As usual, there are multiple ways to express this query in SQL, but only a couple queries will result in the best possible performance. The non-spatial setup starts with two tables with the numbers 1 to 1,000,000 in them, then deletes two records from one of the tables. The spatial setup is a 2M record table of geographic names, and a 3K rec...
Read MoreElizabeth Christensen
Elizabeth Christensen
In a world where everything is stored in following IaC ( infrastructure as code ) you may want the same from your database. For many following this style of engineering modernization we see a focus on IaC and K8s. We have many users standardizing on our PGO Kubernetes Operator to help. But following an IaC approach doesn’t mean you always want to manage your database and be in Kubernetes. For those wanting to forget about their database and trust the uptime, safety, and security of it to s...
Read MoreRekha Khandhadia
Rekha Khandhadia
Recently I worked with one of my Crunchy Data PostgreSQL clients on implementing caching for pg_tileserv. pg_tileserv is a lightweight microservice to expose spatial data to the web and is a key service across many of our geospatial customer sites. pg_tileserv can generate a fair amount of database server load, depending on the complexity of the map data and number of end users, so putting a proxy cache such as Varnish in front of it is a best practice. Using Paul Ramsey's Production PostGIS...
Read MoreElizabeth Christensen
Elizabeth Christensen
Vanilla Postgres has native partitioning? Yes! And it's really good! We frequently get questions like: Can Postgres handle JSON? Can Postgres handle time series data? How scalable is Postgres? Turns out the answer is most usually yes! Postgres, vanilla Postgres, can handle whatever your need is without having to go to a locked in proprietary database. Unless you're really close to the Postgres internals and code releases you might have missed that Postgres natively has partitioning. Our head of...
Read MorePaul Ramsey
Paul Ramsey
The PostgreSQL BRIN index is a specialized index for (as the documentation says) "handling very large tables in which certain columns have some natural correlation with their physical location within the table". For data of that sort, BRIN indexes offer extremely low insert costs (good for high velocity data) and extremely small index sizes (good for high volume data). But what data has this "natural correlation"? Most frequently, data with a timestamp that is continuously adding new rows. • A l...
Read MorePaul Ramsey
Paul Ramsey
It should be the easiest thing in the world: you are modeling your data and you need a column for some numbers, what type do you use? PostgreSQL offers a lot of different number types, and they all have advantages and limitations. You want the number type that is going to: • Store your data using the smallest amount of space • Represent your data with the smallest amount of error • Manipulate your data using the correct logic Store your data using the smallest amount of space Represent your da...
Read MoreElizabeth Christensen
Elizabeth Christensen
Logical replication has been around since Postgres 10 and it's just one of the most useful things out there. Need to get your data to a business analytics engine? Want to create an ETL pipeline from Postgres to your data warehouse? Want to test a new version of Postgres locally with your data? Want to consolidate a few databases into one? Logical replication can do all those things! It is a replication tool, which means it will copy some or all of your data to a new location and continue updatin...
Read MoreDavid Christensen
David Christensen
When using monitoring tools like PgMonitor or pganalyze , Crunchy clients will often ask me about high numbers of locks and when to worry. Like most engineering-related questions, the answer is: "it depends". In this post, I will provide a little more information about locks, how they are used in PostgreSQL, and what things to look for to spot problems vs high usage. PostgreSQL uses locks in all parts of its operation to serialize or share access to key data. This can come in the form of two...
Read More