For this PGSQL Phriday, Pavlo Golub has chosen the topic “POSTGRESQL EVENTS” What a topic! Do you know how many things I can say about Postgres events? A lot! Last spring, I published the blog post “Complete History of PG Day Chicago”, which included the story of my involvement with the Chicago PostgreSQLUser Group. In December 2016, seven years ago, when I became a Chicago PUG local organizer, my goal was to prove that Chicago could host a conference, but I realized very soon that the User Group was vital for the Postgres community regardless. Like no other community, the Postgres community needs live interactions, and I can’t recall anybody more unhappy than frequent conference speakers at the time of the pandemic. One time, I will give a talk, “How we survived the pandemic,” but today, all my thoughts are directed to the second PG Day Chicago, which will take place on April 26, 2024. If you are coming to PG Conf EU, which starts in just ten days, you will have a chance to listen to the talk that I will give together with Teresa Giacomini: So you want a PG day in your city, where we will share our many “lessons learned”. For now, I just want to repost my Complete History of PG Day Chicago (because nobody clicks on the links!) I felt compelled to write a blog explaining why I am so excited for PG Day Chicago finally being a reality, not just my dream. Many people heard at least some parts of this story, but it’s the first time I am presenting the whole timeline. I started working with PostgreSQL in July 2011, and shortly after, I attended the first Postgres conference, PG Open, which was back then happening in Chicago and was a three-day event with huge attendance! Before that, I only attended academic conferences, and that one looked very different. Needless to say, in addition to its novelty, I felt completely stupid because I barely understood what it was all about. I had enough courage to ask somebody (maybe even Magnus) whether they plan to have subtransactions inside functions :)[...]

The topic of this month’s PGSQL Phriday #014 community blogging event—where people from different companies and different countries all blog about the same topic on the same day—is PostgreSQL Events. Big thanks to Pavlo Golub for organizing this month's PGSQL Phriday. Deciding what event to blog about was a bit of a challenge—there are so many Postgres events worth shining a light on! Top of mind at this moment are PGConf EU which will happen in Prague in December—and the Path To Citus Con monthly podcast that I co-host for developers who love Postgres. So what Postgres event did I choose for this PGSQL Phriday post? I thought y’all might appreciate this “Illustrated Guide” to my Postgres experience at the PASS Data Community Summit 2023 which happened in Seattle, WA on November 14-17. Let’s dive in. Figure 1: The og:image social graphic for this PGSQL Phriday blog post about the PASS Data Community Summit 2023. What is PASS Data Community Summit? Historically the PASS Data Community Summit focused on the SQL Server community, bringing together several thousand database people in the SQL community. This year the organizers surprised the world by adding a focus on PostgreSQL as part of PASS. Which is how I ended up in chilly Seattle a few weeks ago. Figure 2: Screenshot from the About page for PASS Data Community Summit 2023. My PASS talk about contributing to the Postgres community Like many conference speakers, I danced a jig when this talk was accepted for PASS. My talk on How to get involved with Postgres without being a PG expert is all about the many ways you can contribute to the Postgres community—beyond code. And just like sailboat racing—in which every single person in the crew influences the final score—all these different types of contributions to Postgres are valuable. Slides on speakerdeck. Figure 3: Slides from my Postgres talk at PASS Data Summit are on Speakerdeck, titled: How to get involved with Postgres without be[...]

Photo from PGDay UK 2023, credit: Jimmy Angelakos This month’s PGSQL Phriday topic, proposed by Pavlo Golub, is Postgres Events. I recorded an episode of Postgres FM on this with Nikolay, but somehow even in 52 minutes I didn’t manage to cover a couple of things I wanted to share, so here we are. IRL events Pavlo mentions in his invitation that “we all know that the community’s heartbeat lies in PostgreSQL Events”. I’m not sure I fully agree, but it certainly seems like a shortcut to getting embraced if you’re new and want to get involved! I’m a big fan of local meet-ups and try to attend any near me when they happen. I was also honoured to be invited to be on the talk selection committee for PGConf EU 2022, which was a lot of work, but a nice way to meet people and give back a little. More recently, I gave my first in person Postgres talk at PGDay UK 2023, which was terrifying but an overall very positive experience. In person events are a huge amount of work to put on. I hope we can figure out ways to pay the folks organising them, and also to facilitate more peer to peer connections at them. Online events During the travel-restricted years of 2020 and 2021, there were tons of online events. I personally attended a lot of these, and gave a couple of explain related talks myself (a beginner’s guide, and a beyond the basics talk). It was nice to get to know a few people better through these, but that part was pretty incomparable to in person events in my experience. I also found myself working between talks more, and attending a far lower proportion of them, than an in person event — I guess that’s natural. I thought more events would stay online, or at least go hybrid, given the wider reach possible there. I totally get that recording is tricky and expensive though, and admire the events that are able to pull it off. There are all sorts of other kinds of online events in the community, here are a few I’m aware of and have had good experiences attending/watching/followin[...]

This month’s PGSQL Phriday #014 is about PostgreSQL Events, and it’s a great time to give an update about the Seattle Postgres User Group where I’ve been working together with long-time organizer Lloyd Albin. At the beginning of this year, I wrote about re-starting our meetups post-pandemic. This Thursday is our final meetup of 2023 (Jennifer Scheuerell is talking about migrations) and it’s been a great year! We’ve seen attendance steadily grow; our November 2 in-person meetup (Tommy Li about Database Binpacking) was around 20. There are first-timers visiting every month. We’ve talked this year about architecture, replication, churn and vacuum, performance, corruption, and more. We’ve had social events ranging from informal meetups to our big annual July summer BBQ. I’m excited that our Seattle Postgres User Group Spring 2024 schedule is ready and looks amazing! The first 2024 meetup will be Thursday January 18 and Chelsea Dole will be giving her talk … besides that it’s a great topic and a knowledgeable presenter, I love the name she came up with. There’s a lot more amazing content coming in 2024 including Paul Ramsey and Joe Conway, as well as more first-time local presenters. Mark your calendars! But the most ambitious thing that we tackled this year as a user group… I first heard from Ryan Booz about a big database conference historically focused on SQL Server, which allegedly wanted to add a full PostgreSQL track. I’m talking about sessions, a coordinated “learning pathway” track, a panel discussion, a booth and even a community zone PostgreSQL AMA. The PASS Data Summit Team went ALL-OUT. I really enjoy traveling for conferences, but I’m limited right now for some family reasons. When I found out this conference was in Seattle, I was excited! [...]

It is PgSQLPhriday time again! This month's event is PgSQLPhriday (#014) and is hosted by Pavlo Golub. I'm barely making the deadline, but didn't want to miss this one! Pavlo chose PostgreSQL Events for the focus for this month's topic. See his post for the full details. As always, I can't wait to read the rest of the contributions this month. This post addresses roughly three of his questions. Networking It just so happens, I finally got to meet Pavlo in person at the PASS 2023 summit in Seattle, Washington! 👋 Q: "Discuss the importance of networking. Have you formed valuable connections or partnerships as a result of these events?"

Disclaimer This article will contain spoilers both on how I solved 2022 Day 23's challenge "Unstable Diffusion" 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. AOC Day 23 Tech used in this Day: The file_fdw Foreign Data Wrapper Materialized (and not materialized) CTEs aka Common Table Expressions Custom data types Various handy functions like string_to_table and array_agg and unnest Tweaking the plan_cache_mode parameter Using the create as / truncate / copy trick to remove table bloat Windowing functions along with avg and stddev More ANSI/Unicode fun For this challenge, we received an ASCII map representing where elves are standing and where they are not: ....#.. ..###.# #...#.# .#...## #.###.. ##.#.## .#..#.. The octothorpes are the elves. The idea is that a number of rounds happen. During the first half of the round, the elves look around and decide on which direction they would like to move, as long as the area in that direction is not occupied. In the second half of the round, they try to move. If two or more elves try to move into the same spot, then neither of them moves. Our goal is to calculate how diffuse the grid becomes after ten rounds. To start, we do our usual setup of using a foreign data wrapper to import the text file. Not that the test file above is small: the actual one is a 72 x 72 character grid. CREATE EXTENSION if not exists file_fdw; CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw; DROP SCHEMA if exists aoc2022_day23_grove CASCADE; CREATE SCHEMA aoc2022_day23_grove; SET search_path = aoc2022_day23_grove, public; CREATE FOREIGN TABLE aoc_day23 (line text) SERVER aoc2022 options(filename '/tmp/aoc2022.day23.input' -- SERVER aoc2022 options(filename '/tmp/aoc2022.day23.testinput' ); The first step is to transform that text file into a SQL table. We will throw things into an unlogged table, with X a[...]

Disclaimer This article will contain spoilers both on how I solved 2022 Day 22's challenge "Monkey Map" 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. AOC Day 22 Tech used: The file_fdw extension to read the input Unlogged tables Sequences Building and modifying arrays via regexp_split_to_array and array_remove More ASCII animation! The first step is to read the text-based input file into a Postgres table: CREATE EXTENSION if not exists file_fdw; CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw; DROP SCHEMA if exists aoc2022_day22_monkeymap CASCADE; CREATE SCHEMA aoc2022_day22_monkeymap; SET search_path = aoc2022_day22_monkeymap; CREATE FOREIGN TABLE aoc_day22 (line text) SERVER aoc2022 options(filename '/tmp/aoc2022.day22.input' -- SERVER aoc2022 options(filename '/tmp/aoc2022.day22.testinput' ); AOC Day 22 - Part One This puzzle asks us to chart a route through a maze, following specific directions about how far to walk and when to turn. The input file looks like this: ...# .#.. #... .... ...#.......# ........#... ..#....#.... ..........#. ...#.... .....#.. .#...... ......#. 10R5L5R10L4R5L5 This is the small test file: the actual one is always much larger and more complex. We can see it is divided into two parts: the maze, and the instructions. Our first step will be to translate that input into SQL tables. For now, we will only focus on the map part, which we will put into a new table: CREATE UNLOGGED TABLE monkeymap ( id INT GENERATED ALWAYS AS IDENTITY, y SMALLINT, x SMALLINT, item CHAR(1), eswn TEXT[] ); We will need some supporting sequences, and then we can read the file line for line and transform it into the columns above: CREATE SEQUENCE aoc; CREATE SEQUENCE aoc2; WITH x AS (SELECT nextval('aoc') AS myrow, setval('aoc2',1,false), line[...]

Disclaimer 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. AOC Day 21 Tech used: 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 a virtual Postgres table: CREATE EXTENSION if not exists file_fdw; CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw; DROP SCHEMA if exists aoc2022_day21_monkeymath CASCADE; CREATE SCHEMA aoc2022_day21_monkeymath; SET search_path = aoc2022_day21_monkeymath; CREATE FOREIGN TABLE aoc_day21 (id text, action text) SERVER aoc2022 options(filename '/tmp/aoc2022.day21.input', -- SERVER aoc2022 options(filename '/tmp/aoc2022.day21.testinput', FORMAT 'csv', DELIMITER ':'); AOC Day 21 - Part One The puzzle directions are odd but parseable: Each monkey is given a job: either to yell a specific number or to yell the result of a math operation. All of the number-yelling monkeys know their number from the start; however, the math operation monkeys need to wait for two other monkeys to yell a number, and those two other monkeys might also be waiting on other monkeys. We don't speak monkey, but the elephants we freed in the previous rounds do. This puzzle is pretty straightforward. First, let's pull apart the text strings in the puzzle, which look like this: cgrb: gzwb * rcfd gfbz: bwgp - qlfm jrbf: 2 gvvg: rjch + tjdp vwsh: grwp * ddsv tpwb: 1 We will separate the data in each line and store one monkey per row in a new unlogged table. As each row is guaranteed to have a colon, we declared the foreign table as a csv with a delimiter of a colon, which saves us a step. But we still need to break apart the other items into specific columns. Some simple regular expression functions can help us do this: CREATE UNLOGGED TABLE puzzle ( id[...]

This post supports my session titled PostgreSQL: Extensions Shape the Future at PASS Data Community Summit 2023 on November 15. Thank you to everyone who joined this session during PASS. I believe the audio recording with slides should be made available at some point a few months in the future. Slides The following download is the PDF version of the slide deck. Extensions Shape the Future slides (PDF)

Postgres is a robust data platform. Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Citus and pg_partman extensions. Citus and sharding Citus is an extension that allows you to shard your database across multiple nodes, while allowing your application to remain largely unaware. Citus can be beneficial to your app if: You expect to outgrow the performance a single Postgres instance can deliver Your schema/data model can be mapped cleanly to Citus The queries/workload pattern can be mapped cleanly to Citus Lucky for us, IOT workloads check the boxes for all of the above. Starting with our IoT dataset We're going to begin with a simple schema that relates to vehicles and tracks a few basic measurements against them. We'll also have a table that tracks the location of the vehicle at the time of each sensor sampling as well. CREATE TABLE sensor_data ( id SERIAL, car_id VARCHAR(17) NOT NULL, sensor_type VARCHAR(20) NOT NULL, sensor_value INT NOT NULL, timestamp TIMESTAMP WITH TIME ZONE NOT NULL ); CREATE TABLE location_data ( id SERIAL, car_id VARCHAR(17) NOT NULL, latitude float8, longitude float8, timestamp TIMESTAMP WITH TIME ZONE NOT NULL ); While our above schema is simply it's not unrealistic of many IoT data models, though yours could be more complex. How do we shard this dataset? The key to sharding is that you can push down most of your joins to the node where the data is located. If you're having to move data in between nodes in order to join your performance will suffer. In the case of IoT workloads device_id is a very common pattern[...]