Bookmarks (1291)

Cut Out the Middle Tier: Generating JSON Directly from Postgres — As well as being able to store JSON data structures, Postgres has long been able to generate JSON which it can return directly for use in your app (or to pass directly to users, if you're confident).

pgAdmin 4 v5.5 Released — The popular Postgres web-based administration tool takes another step forward. 5.5 gains OAuth 2 support and introduces the ability to highlight long running queries on dashboards.

Benchmarking CentOS vs Rocky Linux with Postgres — Benchmarks are always tricky to interpret, I find, but it was interesting to learn about Rocky Linux which, like CentOS before it, is a Red Hat Enterprise compatible Linux distro.

Control 'Runaway' Queries with a Statement Timeout — A well-tuned production database is a beautiful thing to see handling queries by the thousands until.. a query that brings everything to a crashing halt. Setting a default statement timeout for your database can help avoid some tricky moments.

How to Get a List of Elements from Multiranges? — Another look at working with Postgres’s forthcoming multirange dataypes.

The Logical Replication of In-Progress Transactions — Postgres 14 is gaining the ability to stream large in-progress transactions and this post goes into quite some detail on the problems involved and how it came together.

Postgres Skipping Unexpectedly When Generating Sequential IDs? — The original title “One, Two, Skip a Few…” sounds more fun, but I wanted to explain better what this is about :-) Essentially Postgres can pass over preallocated values in a sequence when it crashes or otherwise gets restarted in an unexpected way.

Scheduling Jobs with pg_cron on an Amazon RDS or Aurora Database — RDS supports the pg_cron extension in 12.5 and higher, Aurora supports it in 12.6 and higher. You can use it to automate routine maintenance tasks.

Timescale Adds 'Hyperfunctions' to Improve Time Series Queries — TimescaleDB is a time-series oriented extension/distribution for Postgres and they’ve extended Postgres’s SQL support with a variety of ‘hyperfunctions’ for working with time series data (things like time-weighted averages, hyperloglog counting, and dataset smoothing).

pgSCV 0.7.0 Released — pgSCV is a Postgres metrics collector that exposes said metrics in Prometheus format. GitHub repo.

Heroku Database Connection Calculator — Optimizing your database pool configuration relies on quite a few variables (number of dynos, workers, processes, threads) so this guide does the math for you while also explaining each calculation.

Using GDB To Trace Into a Parallel Worker Spawned By Postmaster During a Large Query — ‘Postmaster’, by the way, is referring to the part of Postgres that handles incoming client connections and routes them to a server process (though the term itself is now deprecated).

DBCritic: Constructive Criticism for Your Postgres Schema — The team at Channable built DBCritic as a way to find problems in schemas due to running into a variety of problems in their own (explained in this article). The tool itself is interestingly written in the little seen Idris language.

LIMIT vs FETCH FIRST ROWS ... WITH TIES — I still remember how surprised I was when I first learnt that LIMIT/OFFSET is not standard SQL (though many systems support it). FETCH FIRST may be more portable, but there are some things to consider about its use.

An Operations-Free, Scalable & Flexible Postgres Alternative — Fauna combines the operational integrity and relational modeling of Postgres with an interface and architecture that fits better with modern app development in the cloud. The goodness of Postgres without its operational bottlenecks - Learn more about Fauna.

Understanding LATERAL Joins in Postgres — You can think of LATERAL joins as being a bit like a for-each loop where a subquery is run for each row of an initial result set. This can, of course, be very useful!

Paranoid SQL Execution on Postgres — Jeremy thinks of some ideas to execute SQL with a “paranoid” level of safety, by using timeouts, fully qualifying the names of things, using hot standbys, and more.

Simulating Temporal Tables with dblink and Replication Delay — A curious look at using delayed streaming replication and dblink (a way to execute queries on remote databases via another SQL query) to simulate temporal tables (i.e. to look back in time). This is such an odd but clever approach.

A Look at Row-Level Locks — A practical demo of how row-level locks are managed in Postgres and can be used together with object-level locks.

🌟 Time-Series 101: Get 10+ Tutorials, Sample Datasets & More — Explore 10+ tutorials - complete with sample datasets and SQL queries - to get up and running with time-series data analysis. Topics range from analyzing crypto trends to DevOps & IoT monitoring, building awesome visualizations & beyond. 🚀

Understanding pg_repack: What Can Go Wrong and How to Avoid It — pg_repack is a tool to remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes, without holding an exclusive lock over said tables, but it’s not without its pain points.

Ora2PG Now Supports oracle_fdw to Increase Data Migration Speed — Ora2Pg is an open source Oracle to Postgres migration tool and it can now use oracle_fdw for faster performance, particularly on tables using BLOB columns. This is due to the new versions v22.0 and 22.1 having just been released (with more features than just the above).

Postgres Permissions and Materialized Views — Materialized views let you persist the result of a query, but keeping them suitably refreshed by a non-owner can require some work on the permissions.

▶  New Ways to Get Started with Azure Database for PostgreSQL — A snappy 20-minute introduction to running Postgres on Azure, particularly in its Citus-flavored ‘Hyperscale’ form. Claire is one of the most prolific suppliers of links to Postgres Weekly, by the way, so it’s neat to see her talking about Postgres live.

How JOINs Between Local and Distributed Postgres Tables Work with Citus — Citus makes it possible to distribute tables across multiple nodes in a cluster and even to JOIN between them. This post is a basic introduction to how it works.

Calculating Per-Operation Times in EXPLAIN ANALYZE — EXPLAIN ANALYZE is a great help for digging into query performance issues but breaking down the timings can be tricky. Here’s some pointers.

Using GeoDjango and PostGIS in Django — How to get started with GeoDjango (a Django extension for building GIS webapps) and PostGIS to work with geospatial data in Postgres, gives a high level overview of different types of spatial data and then a bunch of examples.

Setting up PostGIS on Ubuntu 20.04.2 — How to get PostGIS set up on Ubuntu 20.04, how to install PostGIS packages and how to install the PostGIS extension into Postgres.

Ask HN: Do You Self-Host Your Database? — I’ve seen the self host vs managed database debate all over the place recently, so it was neat to see Hacker News take it on. Given the audience it’s perhaps unsurprising the majority expressed a preference for maintaining full control.

Creating Constraints Over Multiple Rows — An aeronautically themed example of the nuances involved in different types of transaction isolation levels and working on multiple rows.

A Tale of Making a Company-Wide Standard psqlrc — When you’re in a company with lots of developers and Postgres servers, it can be helpful to extend psql to show more information in its prompt to help everyone know what they’re working on. Easier said than done!

PostgreSQL 14 Beta 2 Released — A month after beta 1 comes beta 2 packed with bug fixes for things people found in the first beta. As always, you can get the best idea of what’s in Postgres 14 via the draft release notes.

Better Range Types in Postgres 14: Turning 100 Lines of SQL Into 3 — I’m always going to take fewer lines of code rather than more, so I was won over by the title alone. It turns out the forthcoming Postgres 14 adds support for multi range types which provides extra power to some pure SQL solutions.. an example of which is provided here.

pg_notify_exporter: A Listen/Notify Exporter for Prometheus — Uses LISTEN/NOTIFY to export to Prometheus events triggered in table operations (INSERT, UPDATE and DELETE).

Logging Tips for Postgres, Featuring Your Slow Queries — A look at a useful log-related setting (log_min_duration_statement) to help identify performance issues. A lot of the post is about Crunchy and LogDNA, though, so you may prefer the logging settings docs.

Zero-Downtime 'Schema Migrations' in Postgres using Views — It’s not exactly a classical sort of migration but.. it's an interesting line of thought.

▶️ Creating Sample Postgres Data: What Are Your Options? — Watch this short how-to video series for 4 ways to generate realistic sample data for benchmarking, demos & beyond 🚀

From MD5 to SCRAM-SHA-256 — Postgres 10 and beyond support the scram-sha-256 mechanism for password hashing and authentication, although you might well still be using MD5. This post touches on the benefits and approach to switching.

A New Kind of Database: Combining Postgres and Citus Tables — Citus is commonly used to make Postgres more distributed and scalable but you still get to enjoy all of Postgres’s usual features and even blend them together. This post digs into Citus 10’s support for foreign keys between local Postgres tables and Citus reference tables.

What Does 'First Normal Form' Actually Mean? — Normalization is a fundamental concept in relational database design but there are different lengths to which you can go. This explains first normal form (aka 1NF). Then you can move on to 2NF and 3NF which are fundamental concepts worth knowing about even if you don’t memorize their definitions.

How (and Why) to Become a Postgres Contributor — A “collection of advice” to help anyone keen to make the jump from having never contributed to contributing just a little. The keys are to learn about the development process, setting up a dev environment for Postgres on your local machine, and biting off something very small.

Talking about the Postgres Optimizer at CMU — Last week we featured Robert Haas’s recent talk about Postgres’s query optimizer but he’s written up some notes on particular parts of the talk if you didn’t have to time to watch it all.

▶  On Postgres's Optimizer Methodology — EDB’s Robert Haas, a long time major contributor to Postgres, gave an hour long talk about two key parts of query optimization: join planning and statistics. This is technical stuff, but Robert does a great job of making reasonably opaque processes accessible, and knowing this stuff will up your Postgres game.

Cross Joins in Postgres: Problems and Use Cases — A cross join or cross product between two tables is essentially their cartesian product, the combination of every row of one table with those of another.

'Ignore Nulls' in Postgres — Despite the lack of specific ‘ignore nulls’ syntax, you can mimic such behavior by defining custom aggregates.

Do You Need Redis? Postgres Does Queuing, Locking, and Pub/Sub.. — I’m a huge Redis fan and while it’s common to have a mainstream database (like Postgres) and Redis running side by side, you could potentially just use Postgres for things like job queues or locks. There's only so far this can scale, however.. (I did say I'm a Redis fan!)