Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Craig Kerstiens
Craig Kerstiens
A question recently came up in the internal Crunchy Data slack channel: Does anyone actually use enums out in the wild? If you're unfamiliar with enums, they’re enumerated types, a static set of values in a database like days of the week or a shipping status. Enums are a powerful feature of Postgres that allows you to define a set of predefined values that can be assigned to a column. However, enums can have some limitations and drawbacks that make them less than ideal for certain scenarios. L...
Read MoreElizabeth Christensen
Elizabeth Christensen
Crunchy Data hosted the 4th annual PostGIS Day on November 17, 2022. PostGIS Day always comes a day after GIS Day which occurs annually on the 3rd Wednesday of November. We had speakers from 10 different countries and attendees from more than 70 countries. PostGIS is the most popular spatial relational database worldwide with: • An extensive catalog of spatial functions • Rich ecosystem of in-db extensions for routing, event management, external database linkage, point clouds, rasters and m...
Read MorePaul Ramsey
Paul Ramsey
One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV. Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form. But how can we interact with that extra data? If you want to try this blo...
Read MorePaul Ramsey
Paul Ramsey
Back when I first learned SQL, calculating percentages over a set of individual contributions was an ungainly business: • First calculate the denominator of the percentage, • Then join that denominator back to the original table to calculate the percentage. First calculate the denominator of the percentage, Then join that denominator back to the original table to calculate the percentage. This requires two passes of the table: once for the denominator and once for the percentage. For BI queries...
Read MoreMatt Hudson
Matt Hudson
The operator has to be in the top-3 SQL-specific operators that people learn, the close competitors are and . Thus, feels familiar when crafting SQL: you can use it with nested SQL statements, or create your own list. But, it does have limitations — what if you wanted to send a list of unknown length? You can’t really use SQL placeholders without also modifying the SQL statement. What if I told you there was a less ubiquitous operator that offered all of the power and less of the drawback...
Read MoreJoseph Mckulka
Joseph Mckulka
Crunchy Postgres for Kubernetes can be used for cross-datacenter streaming replication out of the box. With so many folks asking for cross-cloud / cross-datacenter replication, we wanted to give people a large explanation of how that works. For this post, we use streaming replication, and prioritize reducing latency and adding stability. Cross-cloud streaming replication can be used: • To enable multi-cloud disaster recovery • For moving clusters between cloud providers • For moving clusters be...
Read MoreElizabeth Christensen
Elizabeth Christensen
Multi-PgBouncer is our affectionate in-house name for a special use case of running multiple instances of PgBouncer. PgBouncer is the venerable go-to tool for managing connection pooling at the database layer for Postgres. For some of our customers with notably large databases and high throughput, running multi-PgBouncer has been a great way to keep up with load and connections. David Christensen recently did a talk at PGConf.NYC entitled “ How to Tame a Mastodon: Lessons for PostgreSQL at Sca...
Read MoreCraig Kerstiens
Craig Kerstiens
I wanted to take a little time today to walk through some of the security and team controls you get out of the box on Crunchy Bridge . Within teams for Crunchy Bridge you have the ability to: • Restrict authentication for a team to SSO • Event logs of all actions for a team • Audit logs of queries run against your database Restrict authentication for a team to SSO Event logs of all actions for a team Audit logs of queries run against your database We'll take a look at each of these in dee...
Read MorePaul Ramsey
Paul Ramsey
Last week Craig Kerstiens published a great introduction to Postgres terminology , covering some of the basics you might run into when just getting started. The funny thing about jargon is how quickly we get used to it, and forget we are even using it. It becomes part of the secret handshake, the way we signal to other members of our tribe that we're part of the group. When I first started going to Postgres conferences and listening to talks by Postgres core developers I suddenly found myself...
Read MoreBrandur Leach
Brandur Leach
When we started building Crunchy Bridge two years ago, we chose Ruby as the language to write our database state machine and control plane API. Ruby may not have been the most popular language choice in 2022, but we picked it anyway. A major reason is that everyone on the team already knew it well and liked it. Terse and elegant syntax is perfect for expressing our database state machine logic. Another reason we picked Ruby is that it lets us have a REPL running in production so we can carry o...
Read More