David Christensen
David Christensen
Supporting PostgreSQL DBAs is an important part of daily life here at Crunchy Data . I’ve recently run across a few use cases where utility queries based on the current state of the database are needed. A simple example could be where you have a table that is the target of logical replication and the column becomes out of sync with the sequence that generated the data. This would result in new rows having primary key conflicts. To correct this issue, you would need to set the sequence to gene...
Read MoreCraig Kerstiens
Craig Kerstiens
A lot of years Postgres will have some big pillar or theme to the release. Often this is thought of after the fact. Everything that is committed is looked at and someone thinks, "This is the key thing to talk about." In Postgres 9.2 it was JSON , in 9.4 it was JSONB, in 10 it was logical replication , 12 was a broader performance theme. While I look forward to each of these big highlights, in each release I'm equally excited to browse through and pull out the small things that simply make my l...
Read MoreKat Batuigas
Kat Batuigas
In my last post, I did a simple intro to foreign data wrappers in PostgreSQL . postgres_fdw is an extension available in Postgres core that allows you to issue queries against another Postgres database. It's just one of many foreign data wrappers that you can use in Postgres, so for today's post we'll look at another that works especially well with spatial data formats: ogr_fdw. I had also previously talked about some different ways to get spatial data into a Postgres/PostGIS database , but...
Read MoreDavid Christensen
David Christensen
An interesting question came up on the #postgresql IRC channel about how to use native PostgreSQL features to handle queuing behavior. There are existing solutions for queuing, both in PostgreSQL, with the venerable pgq project, or dedicated message queues like RabbitMQ, Kafka, etc. I wanted to explore what could be done with native Postgres primitives and I thought this warranted an entry in my Devious SQL series . So what makes up a minimal queuing solution? Effectively, we need the followi...
Read MoreDavid Christensen
David Christensen
Generally, one appreciates new features of PostgreSQL on the release date after anxious inspection of the release notes or having skimmed through the git logs. Every once in a while, particularly when dealing with an older version of PostgreSQL, you will discover a feature that didn't get the necessary fanfare in order to come to your attention, but when you discover it, you're all the happier for it. I recently ran into an issue with some strange performance issues on a client's PostgreSQL...
Read MorePaul Ramsey
Paul Ramsey
PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite . A huge amount of reporting is about generating percentages: for a particular condition, what is a value relative to a baseline. Here's a quick "sales table" with three categories ("a" and "b" and "c") and one million random values between 0 and 10: In the bad-old-days,...
Read MorePaul Ramsey
Paul Ramsey
Raster data access from the spatial database is an important feature, and the coming release of PostGIS will make remote access more practical by allowing access to private cloud storage. Previous versions could access rasters in public buckets, which is fine for writing blog posts , but in the real world people frequently store their data in private buckets, so we clearly needed the ability to add security tokens to our raster access. Putting rasters in a database is not necessarily a good ide...
Read MoreKat Batuigas
Kat Batuigas
The idea of writing a database query that can then go out to an external source may not occur to someone who is not a DBA early on. That is: instead of figuring out how to grab then load multiple data sets into the same store, or configuring your application backend to connect to a bunch of disparate sources, why not use query JOINs like you usually would across tables within one database? In case you're not familiar, the dblink module in PostgreSQL, along with the concept of database links o...
Read MoreBrian Pace
Brian Pace
There is no cure-all when it comes to security. Making things open to the public internet can leave you vulnerable to various security risks, including zero-day vulnerabilities or various password attacks . You can put your services in a VPN or a private network, but you can still be susceptible to an internal compromise. While security for most people isn't a "fun" thing to spend time on, it's a necessity. You shouldn't have to trade off between security and ease-of-use. This was one big rea...
Read MoreKat Batuigas
Kat Batuigas
Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this: Then I would throw in some wildcard operators or regular expressions if I wanted to get more specific. Later on, I worked with a client who wanted search functionality in an app, so and regex weren't going to cut it. What I had known all along was just pattern matching . It works perfectly fine for certain purposes, but what happens when it's not just a matter of checki...
Read More