Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Greg Sabino Mullane
Greg Sabino Mullane
While supporting customers at Crunchy Data , I sometimes see users add unlogged tables. In this post, I'm going to review some of the specifics about this. Unlogged tables in Postgresql are a way of preventing specific tables from generating WAL (Write Ahead Log) information by adding the keyword to a command: You can also change existing tables to unlogged, and switch them back again: While there are strong advantages to using unlogged tables, you must use them carefully as their disadvant...
Read MoreBrian Pace
Brian Pace
The Postgres Write Ahead Log ( WAL ) is a functional component to the database. WAL makes a lot of key functionality possible, like Point-in-Time-Recovery backups , recovering from an event , streaming replication , and more. From time to time, those deep inside the database will need to work directly with WAL files to diagnose or recover . Recently in working with one of Crunchy Data's customers, I came across a situation where understanding the names and sequence numbers was important....
Read MoreMartin Davis
Martin Davis
In a previous post we announced the CQL filtering capability in . It provides powerful functionality for attribute and spatial querying of data in PostgreSQL and PostGIS. Another important datatype which is often present in datasets is temporal . Temporal datasets contain attributes which are dates or timestamps. The CQL standard defines some special-purpose syntax to support temporal filtering. This allows to take advantage of the extensive capabilities of PostgreSQL for specifying qu...
Read MoreCraig Kerstiens
Craig Kerstiens
We spend a lot of time at Crunchy Data helping people dig into the performance of their Postgres. If you're setting up a new Postgres database or already running on in production there are a number of very basic steps you can take that will save your tail in the future when it comes to investigating performance. Here is your guide that'll take less than 5 minutes to get in place. Future you will thank you for doing this today. Pg_stat_statements records and parameterizes queries, how long the...
Read MoreBob Pacheco
Bob Pacheco
Postgres clusters should be updated regularly and have routine maintenance. This regular maintenance is often referred to as “Day 2 operations” and can include a wide variety of tasks like restarting services, resetting passwords, or updating versions. Performing Day 2 operations can be complex and time consuming, especially if you are supporting a large number of Postgres clusters. With the adoption of GitOps and a little help from continuous delivery tools like Argo CD you can simplify your...
Read MorePaul Ramsey
Paul Ramsey
Over and over when I look at applications for performance, the lesson I learn and re-learn is, do more things right inside the database . Create, read, update, delete! All the things you do to a table or collection of tables to work with your ever-changing data. Most CRUD examples, and most CRUD thinking, tend to focus on one table at a time. That's easy to understand. It's also unrealistic. Even the simplest application will be working with several interlinked normalized tables. Here's our wor...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 15's challenge "Beacon Exclusion Zone" 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. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformattin...
Read MoreCraig Kerstiens
Craig Kerstiens
For developers who have been around SQL long enough, they know that there is often more than one way to get the same result. Today I wanted to look at a few different ways to aggregate and show a total with paid and unpaid status. First, we will use a common table expression ( CTE ), which is a nice method for organizing subqueries. Second, we use statements within aggregation context to filter out the values we want. Lastly, we use to clean up the syntax, but effectively do the same as th...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a great database with a ton of features including really rich indexing . Postgres itself maintains all sorts of data under the covers about things like cache hits and misses, when indexes are and aren't used , and more. If you're staring at a complex explain plan you may think some well targeted indexes may help, but how can you be sure? Enter HypoPG , a Postgres extension for adding hypothetical indexes to Postgres so you can do index planning. HypoPG supports hypothetical inde...
Read MoreElizabeth Christensen
Elizabeth Christensen
Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance. There’s two main ways to customize data types in Postgres: • Create s, which are value constraints added to bu...
Read More