Elizabeth Christensen
SQL makes sense when it's working on a single row, or even when it's aggregating across multiple rows. But what happens when you want to compare between rows of something you've already calculated? Or make groups of data and query those? Enter window functions. Window functions tend to confuse people - but they’re a pretty awesome tool in SQL for data analytics. The best part is that you don’t need charts, fancy BI tools or AI to get some actionable and useful data for your stakeholders. Window...
Read MoreMarco Slot
Marco Slot
Crunchy Data is excited to announce the next major feature release for Crunchy Bridge for Analytics : Geospatial Analytics . We have developed a variety of features to connect Postgres and PostGIS to S3 and public web servers to make spatial data access easier than ever. This release includes: • Creating an analytics table directly from a geospatial data set by providing only the URL, for ad-hoc queries and data transformations. • Creating a regular PostGIS table directly from a URL. • Automat...
Read MoreBrian Pace
Brian Pace
Backups are dead. Now that I have your attention, let me clarify. Traditional backups have earned a solid reputation for their reliability over time. However, they are dead in the sense that a backup is essentially useless until it's restored—essentially "resurrected." In this post, we'll explore best practices for managing PostgreSQL snapshots and backups using pgBackRest . We will then provide some guidance of how you apply these techniques in Kubernetes using the Postgres Operator (PGO) from...
Read MoreMarco Slot
Marco Slot
Data pipelines for IoT applications often involve multiple different systems. First, raw data is gathered in object storage, then several transformations happen in analytics systems, and finally results are written into transactional databases to be accessed by low latency dashboards. While a lot of interesting engineering goes into these systems, things are much simpler if you can do everything in Postgres. Crunchy Bridge for Analytics is a managed PostgreSQL offering that integrates DuckDB...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
The Postgres hackers mailing list ( pgsql-hackers@postgresql.org ) is an invaluable resource for anyone wanting to contribute to the PostgreSQL code. The Postgres project does not use PRs (pull requests) or GitHub issues. So if you want to contribute an idea, or help with code reviews, the hackers mailing list is the canonical way to do so. More information on contributing is on the Postgres wiki at: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer ? My colleague Elizabeth Christ...
Read MoreKeith Fiske
Keith Fiske
Crunchy Data is pleased to announce a new open source pgMonitor Extension . Crunchy Data has worked on a pgMonitor tool for several years as part of our Kubernetes and self-managed Postgres deployments and recently we’ve added an extension to the tool set. Two primary scenarios motivated the creation of the pgMonitor extension : 1. Quicker Metrics : Monitoring metrics often need quick response times to allow for frequent updates. We've noticed that certain metrics become slower as the datab...
Read MoreMarco Slot
Marco Slot
Postgres for analytics has always been a huge question mark. By using PostgreSQL's extension APIs, integrating DuckDB as a query engine for state-of-the-art analytics performance without forking either project could Postgres be the analytics database too? Bringing an analytical query engine into a transactional database system raises many interesting possibilities and questions. In this blog post I want to reflect on what makes these workloads and system architectures so different and what br...
Read MoreJesse Soyland
Jesse Soyland
There’s a couple super common Postgres errors you’re likely to encounter while using this database, especially with an application or ORM. One is the PG::DiskFull: ERROR: could not resize shared memory segment. It will look something like this. We see a good amount of support tickets from customers on this topic. If you see this error pass by in your logs. Don’t worry. Seriously. There’s no immediate reason to panic from a single one of these errors. If you’re seeing them regularly or all th...
Read MorePaul Ramsey
Paul Ramsey
Back in the 1990s, before anything was cool (or so my children tell me) and at the dawn of the Age of the Meme, a couple of college students invented a game they called the " Six Degrees of Kevin Bacon ". The conceit behind the Six Degrees of Kevin Bacon was that actor Kevin Bacon could be connected to any other actor, via a chain of association of no more than six steps. Why Kevin Bacon? More or less arbitrarily, but the students had noted that Bacon said in an interview that "he had worked...
Read MoreGreg Nokes
Greg Nokes
One of the major changes that the cloud brought to application and database management was the concept of "thin provisioning." With large amounts of compute or storage resources available behind an API, you can provision what you need now and expand your infrastructure as required. Frameworks like 12Factor and Cloud Native emerged to help developers leverage these new paradigms. In the past, these capabilities were only accessible to those leveraging public clouds. On-premises, with lead tim...
Read More