Bookmarks (866)

Postgres 13's LIMIT ... WITH TIES — WITH TIES is a new SQL standard feature being implemented in Postgres that causes LIMIT (or FETCH FIRST) clauses to not just cut off at a specified limit but to also include rows with values that tie with the final one(s).

Crunchy Bridge: The Newest 'Postgres As A Service' — Crunchy Data is the latest company to get on the ‘Postgres as a managed service’ bandwagon with Crunchy Bridge which is available on AWS and Azure (and supports migration and replication between the two).

Highway to Zero Downtime PostgreSQL Upgrades — Get a comprehensive walk-through of how to perform a "near" zero downtime upgrade using pglogical in this free webinar. Learn how logical decoding presents a whole new world of opportunities for upgrades that require a very small amount of downtime.

PostgreSQL 13 Release Candidate 1 Released — We link to things about Postgres 13 all the time, if you hadn’t noticed, and it’s shaping up to be a huge release – so it’s great to see it close to completion with this first RC. The beta release notes cover the essentials, but we’ll do a full roundup of features at the final release.

A Battleship Game, Implemented with Postgres — See SQL taken to the next level with a working game running within Postgres, complete with a creative way of taking player input.

Why RudderStack Used Postgres Over Apache Kafka for a Streaming Engine — Kafka was a natural fit for what RudderStack, a data platform, does, but they found enough negatives about it to build their own queueing system on top of Postgres instead.

📄  Postgres and the Artificial Intelligence Landscape — It’s just slides for now (though a talk was given) but Bruce’s slides often provide value even on their own.

AWS Aurora Postgres Versions 'Vanished' for Days, Customers Claim — Greg Clough, a software engineer who uses AWS, noticed that several Postgres versions on AWS Aurora ‘vanished’ last week (in the sense they couldn’t be deployed – existing datbases didn’t disappear). Most now appear to be back, but it’s a curious story.

How 'HOT' Updates Yield Better Performance — An introduction to a feature included first with Postgres 8.3 but which, allegedly, are not properly covered in the docs. HOT updates (Heap Only Tuple) occur behind the scenes and improve performance in certain situations where lots of UPDATEs occur.

Lessons Learned from Running Postgres 13: Better Performance, Monitoring & More — We took a look at smaller indexes with B-Tree Deduplication, Parallel VACUUM, improved WAL Usage Stats, and more.

Diary of an Engineer: Delivering 45x Faster Percentiles using Postgres, Citus, and t-digest — Nils had a problem to solve for a customer but couldn’t meet their SLA of 30 seconds and didn’t have the customer’s data to experiment with.. nonetheless, he found a creative way to estimate which types of percentile calculations would meet their SLA and used t-digest to do it.

Faster CI/CD for All Your Software Projects - Try Buildkite ✅ — See how Shopify scaled from 300 to 1800 engineers while keeping their build times under 5 minutes.

Using Postgres to Offload Real-Time Reporting and Analytics from MongoDB — Weighs up the advantages and disadvantages of moving read-heavy analytics off a primary MongoDB database to PostgreSQL.

How (and Why) GitLab Upgraded from Postgres 9.6 to 11 — Some of the GitLab team explain the precise maintenance process they took to execute a major version upgrade of Postgres on their systems. And, even better, they recorded the whole 2 hour process so you can see how it was actually conducted! It doesn’t make for exciting viewing, but it’s a very uncommon look behind the ops curtain at a major company.

7 Best Practice Tips for Bulk Data Loading — Anyone who’s managed a database has needed to import a large quantity of data at some point, and anyone who’s tried this has rapidly discovered there are both fast and slow ways to do it 😉 This post looks at some some best practices for bulk importing data into Postgres databases.

Quickly Identify Slow-Running Postgres Queries in Datadog — Improve PostgreSQL performance in real-time with actionable alerts on slow-running queries and bottlenecks. Start proactively monitoring your PostgreSQL databases with a Datadog free trial.

A Few Hidden Gems of Postgres 13 — With all the blog posts we’re seeing each week, it feels like Postgres 13 is the most anticipated release ever 😄 Here, Jonathan reflects on a handful of ‘hidden gems’ that’ll be turning up in the release.

Picking Between Joins or Subqueries in Postgres: Lessons Learned — The author was advised “Try to use joins rather than subqueries.” Here, he works through an example of why and where the benefits are.

Index Improvements Coming to Postgres 13 — We’ve linked to numerous articles over the past few months about how indexes will be improved in Postgres 13 but this rounds up six of the biggest benefits in one place complete with code examples.

Which Partition Contains a Specific Row? — If you’re using hash partitioning, can you determine which partition contains a given row? Yes, you can.

allballs? A Rather Odd Time Keyword in Postgres.. — There's a ‘childish behavior ahead’ warning on this one.. 😆 but via this tweet I learnt that Postgres (still) supports allballs as a keyword to mean 00:00:00. We’ll let you guess why, or you can read this explanation on the mailing list. Amazingly, as of the 13 betas, it's still there.

pgtools: A Visual Way to Monitor Database Events in Real Time — A very new project built in Python and using Vue.js on the client side.

Mining for Logic Bugs in the Citus Extension to Postgres with SQLancer — One of those things you’re unlikely to need to do, but it’s nice to know how such problems are approached. SQLancer is a tool we’ve linked to before that helps you detect logic-related bugs in database systems.

A Look at B-Tree Index Deduplication in Postgres 13 — B-tree indexes are the default type of index created in Postgres so any changes to their operation is likely to have a lot of knock-on effects. Deduplicating these indexes, as possible in the forthcoming Postgres 13, will help keep these indexes smaller and has performance implications (most likely lower I/O usage at a cost of minor CPU increase, but with higher overall performance in most cases).

How to Get the Best Out of Postgres Logs — Postgres’s logging system is very tunable and there are lots of parameters to fiddle with. This post covers some basic practices for getting the most out of a Postgres server’s logs and what you can tweak.

[Whitepaper] Business Case for Professional Support — Learn the importance of Professional Support for your mission-critical PostgreSQL systems & how it can benefit your company. Discover how it increases database performance, helps scale, distributes data, reduces costs, saves you from known pitfalls, and more.

What’s New in the Citus 9.4 Extension to Postgres — Citus transforms Postgres into a distributed database, distributing your data and your SQL queries across multiple nodes. v9.4 improves EXPLAIN ANALYZE, has some performance and safety improvements, and can now calculate percentiles at scale using the t-digest extension.

Generating a Normal Distribution in SQL — Postgres’s tablefunc extension provides a variety of functions that return tables, including sets of normally distributed random values.

PostGIS and the Geography Type — The PostGIS geography type is a geospatial type that understands coordinates as spherical coordinates (in latitude and longitude) and here’s a basic introduction to them (and one of the reasons to use PostGIS too, really).

Tuning Postgres on ZFS — “The main reason to use ZFS instead of ext4/xfs is compression. With reasonable configuration you can achieve 3-5x compression ratio using LZ4. That means that LZ4 compresses 1 terabyte of data down to ~300 gigabytes.”

Building Microservices with Deno, Reno, and Postgres — Deno is a server-side JavaScript runtime built on top of V8 (a bit like Node, but not) and Reno is a routing library for Deno apps.

An Overview of Trusted Extensions in PostgreSQL 13 — Postgres supports extensions as a way to easily introduce new features and functions dynamically, but in Postgres 13 there’ll be the concept of a trusted extension which will allow any user with CREATE privileges on the current database to bring in an extension without superuser privileges.

Pitfalls and Quirks of Logical Replication in Postgres 12 — “I’d like to share some thoughts after setting up a logical replication in a large-ish (one terabyte) production database.”

Full-Text Search Battle: Postgres vs Elasticsearch — Less a battle and more a basic comparison of techniques with a simple benchmark, but may be of interest.

Using Envoy Proxy’s New PostgreSQL & TCP Filters to Collect SQL Statistics — The new PostgreSQL filter from Envoy Proxy makes it easy for developers and ops engineers to collect SQL statistics.

The Need for External Compression Methods in Postgres — If you could specify different compression systems for different tables or columns, based upon the most appropriate one for the type of data stored, that would be pretty neat.. and it seems the Postgres team are beginning to discuss such a potential feature in Postgres.

How JSON Aggregate Functions Are Pretty Cool — Rather than extract data with SQL queries and then convert it into JSON, you could get Postgres to do the heavy lifting instead and here’s how.

Parallelism Comes to VACUUM in Postgres 13 — One of the noteworthy new features in Postgres 13 will be the ability to speed up the execution time of index vacuuming. Masahiko provides a quick example of the benefits.

Three Easy Things To Remember About Postgres Indexes — Basic ideas, but worth reviewing if you’re not an index expert. Indexes can speed things up beyond simple queries, indexes aren’t always used even if you think they should be, and indexes add maintenance and storage overhead you need to be prepared to manage.

Tuning Autovacuum for Postgres Databases — Most of the time you don’t need to tweak the settings for autovacuum as Postgres will generally do the right thing, but sometimes the default configuration just isn’t enough and this article will help you with some approaches for tuning things.

Improvements for Handling Large Number of Connections Coming to Postgres 14 — Note that this is about Postgres 14 so you’re going to be waiting a while (as 13 isn’t even out yet!) but handling large numbers of connections is a perennial issue for many so these links to some discussion threads may be of interest.

Pgpool-II 4.1.3, 4.0.10, 3.7.15, 3.6.22 and 3.5.26 Released — Bug fixes all round for all maintained versions of the popular connection pooling system.

QuestDB: A Performance-Focused 'NewSQL' Time-Series Database System — A relational database (that isn’t built on Postgres but does support its wire protocol) focused on fast time-series data processing. Built in Java.

How to Setup Postgres on an IPv6 Enabled Network — If you’re confident with IPv6 already, this goes into more depth than you’ll need, but if not.. you may find some useful tips here.

A Look at TLS Related Updates Coming in Postgres 13 — Supporting a minimum of TLS 1.2, Postgres 13 brings a few improvements when it comes to secure connections including support for channel binding.

Calculating Standard Deviation with SQL — As is often the case, Postgres offers a function for this task: stddev().

Why Postgres 13 Will Be A 'Lucky' Release — See why the author thinks that why 13, often maligned as an ‘unlucky’ number, will be lucky for Postgres it adds features like incremental sort, parallel vacuum, and improves performance of B-tree indexes.