Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Greg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 21's challenge "Monkey Math" 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. Tech used: • The file_fdw extension to read the input • Functions such as regexp_substr • Unlogged tables The file_fdw extension to read the input Functions such as regexp_substr Unlogged tables As always, we will use file_fdw to put our text input into...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 20's challenge "Grove Positioning System" 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. Will I get these all posted before next year's AOC starts? Consider it a bonus challenge! :) Tech used: • CTEs (Common Table Expressions) • Using a non-integer type to help simulate a linked list • The ever useful file_fdw extension • sequence...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Version 1.21 of PgBouncer , the Postgres connection pooler, has added a long awaited feature: support for prepared statements inside of transaction mode. Prior to this, one had to choose between using prepared statements (a performance win), and using PgBouncer's transaction mode (also a large performance win). Now, we can have our cake and eat it too 🎂 🎉. In Postgres, every SQL command you send to the server is parsed , prepared , and then executed . If you are running the exact same quer...
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 MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 18's challenge "Boiling Boulders" 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. (heatmap slicing a 3-D piece of lava - all in SQL!) We've also loaded a tutorial for Day 18's challenge if you want to try it with a pre-loaded data set. Tech: 🐘 The ever important file_fdw 🐘 Using sequences as a crude numbering aid 🐘 A recursi...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
(Yes, this image was generated completely by SQL statements!) We've also loaded a tutorial for Day 17'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 17's challenge "Pyroclastic Flow" 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. Another puzzle featuring elephants! (❤️ ❤️ ❤️). This time, the elephants are involved in...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
A question I hear from time to time with Crunchy Data clients and the Postgres community is: When was my Postgres database table created? Postgres does not store the creation date of tables, or any other database object. But fear not, there are a plethora of direct and indirect ways to find out when your table creation happened. Let's go through some ways to do this, ranging from easy to somewhat hard. All these solutions apply to indexes and other database objects, but tables are by far the mos...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 16's challenge "Probscidea Volcanium" 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. My solutions may not be the "best" solution, as the goal is to provide a quick solution. We've also loaded a tutorial for Day 16's challenge if you want to try it with a pre-loaded data set....
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
In an earlier post, I went into a lot of detail about unlogged tables . But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the keyword to your statement: The use case for unlogged sequences in Postgres is primarily to keep the sequence data for an unlogged table out of the WAL stream. Although unlogged tables provide a significant performance boost,...
Read MoreGreg 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 More