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 MoreCraig Kerstiens
Craig Kerstiens
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 Ci...
Read MoreCraig Kerstiens
Craig Kerstiens
If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities. When you begin building your app, time to market is essential, but you don't want to make decisio...
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 MoreChristopher Winslett
Christopher Winslett
Over the past 12 months, AI has taken over budgets and initiatives. Postgres is a popular store for AI embedding data because it can store, calculate, optimize, and scale using the pgvector extension . A recently introduced gem to the Ruby on Rails ecosystem, the neighbor gem, makes working with pgvector and Rails even better. An “embedding” is a set of floating point values that represent the characteristics of a thing (nothing new, we’ve had these since the 70s). Using the OpenAI API or any o...
Read MoreDavid Christensen
David Christensen
Recently we published an article about some of the best sql subquery tools and we were talking about all the cool things you can do with CTEs. One thing that doesn’t get mentioned near enough is the use of CTEs to do work in your database moving things around. Did you know you can use CTEs for tuple shuffling? Using CTEs to update, delete, and insert data can be extremely efficient and safe for your Postgres database. PostgreSQL 15 included the MERGE statement, which can be similar. There ar...
Read MoreBob Pacheco
Bob Pacheco
Modern resilient data infrastructure ensures that there's not a single point of failure. In the cloud, this means eliminating single points of failure at the data center and availability zone. So when deploying highly available Postgres in Kubernetes, you will want to ensure that each availability zone has one or more Postgres pods running in it. Today I want to walk through the basics of setting up a multi-zone Kubernetes implementation using Crunchy Postgres for Kubernetes . We will use a c...
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 MoreChristopher Winslett
Christopher Winslett
I just spent last week at Rails World in Amsterdam and had a blast digging back into the Rails and Active Record world. In conversations with developers over the week, I had some notable takeaways from the newest version of Ruby on Rails that I just had to get written up. A quick summary before we dig in: • async queries : send long-running queries the background while the code runs along, great for pages with multiple long-running queries that can be run in parallel • composite primary keys : n...
Read MoreElizabeth Christensen
Elizabeth Christensen
Wouldn’t it be awesome if money worked just like time in Postgres? You could store one canonical version of it, it worked across the globe? Well sadly, money is a whole different ball of wax. Though like time, money is part of most database implementations and I wanted to lay out some of the best practices I’ve gathered for working with money in Postgres. I also have a tutorial up if you want to try this with Postgres running in a web browser. Postgres actually does have a data type. This...
Read More