Bookmarks (744)

Using CREATE DOMAIN to Create New Data Types — If you want to create more advanced server side data type constraints than the default data types can provide, you can create your own data types (domains).

Amazon RDS Now Works on AWS Outposts — Let’s untangle this. RDS (Relational Database Service) is AWS’s service for running Postgres (and MySQL) in the cloud. AWS Outpost is a way to take the AWS experience into your own datacenter. So now you can use AWS’s cloud database service on AWS’s hardware but within your own physical environment. OK - got it!

Bitnine Looks to Scale Postgres with AGE — AgensGraph is a transactional graph database built on Postgres but has now morphed into a new project called AGE and is being incubated by Apache.

Generating Random Strings and Integers That Actually Aren’t — A look at creating “random-looking” coupon codes or similar strings in a PL/pgSQL function.

Noisia: A 'Harmful Workload Generator' for Postgres — Creates things like deadlocks, transactions that do nothing and queries that produce on-disk temporary files. Why? For testing, stress-testing your setup, etc. Use carefully and with caution.

Enterprise-Ready PostgreSQL | EDB Supercharges PostgreSQL — A portfolio of service offerings to help you on your PostgreSQL journey. Data migration. Hybrid cloud. High availability.

More Postgres 13 Features: Deduplication in B-Tree Indexes — Postgres 13, due later this year, has a feature that can decrease the size of B-tree indexes by way of avoiding duplication. This post includes a contrived example to show off the difference.

Generated Columns vs. Triggers in Postgres 12 — Postgres 12 introduced the idea of generated columns - columns which are generated from other columns’ values. This post compares the performance against using triggers to similar ends.

Calculating Differences Between Rows in SQL — If you have lots of rows that represent changing values (such as time series data, say) being able to calculate the differences between the values over time could be useful, here are some approaches.

PDF: Running Postgres in Kubernetes — A slidedeck based rundown of the top options for running Postgres in Kubernetes and some of the features contained within each.

Setting Up ora2pg for an Oracle to Postgres Migration — I suspect if you have access to an Oracle database, you might not be looking around for tutorials randomly, but if you are.. and if you need to migrate the data to Postgres.. ora2pg and this tutorial can help.

Postgres Anonymization on Demand — If your database stores personally identifiable information – and it probably does! – anonymization can help protect that data while still keeping the database useful. This articles digs into an approach for applying anonymization globally, covering all applications, with minimal code rewrites.

Timescale Cloud: Hosted, High-Performance PostgreSQL — Now available in 75+ regions across 3 major clouds. Analytics, performance, and scale. 👉 Start free, no credit card required.

Can Case Comparison Be Controlled? — “Let’s go over some Postgres case-precision behaviors like the handling of character strings, identifiers, and keywords.” A handy primer to case comparison issues here.

13 Tips to Improve Insert Performance on Postgres and TimescaleDB — The first five tips are related to Postgres in general, with the remainder being specific to TimescaleDB, a time series data extension for Postgres.

On Join Strategies and Performance — It’s worth understanding the three forms of join strategy Postgres uses when joining relations and how you can influence them with indexes.

A Story of Indexing JSONB Columns — A neat mix of story and tip when it comes to working with JSONB at scale.

Using the system_stats Extension to Monitor Postgres — system_stats is a new extension (out of EDB, formerly EnterpriseDB) made up of several stored procedures you can use to monitor Postgres (or, more accurately, the server it’s running on).

Testing Extensions with GitHub Actions — pgxn-tools provides a Docker image with scripts to install and run any version of Postgres and this can be brought together with a CI/CD platform like GitHub Actions to test Postgres extensions on multiple Postgres versions.

Whitepaper: BDR - Advanced Clustering & Scaling for PostgreSQL — Learn more about Advanced Clustering & Scaling for PostgreSQL with BDR including use cases, architectures, features & benefits. BDR provides multi-master replication with AlwaysOn availability, worldwide clusters, & Cloud Native deployments for PostgreSQL databases.

Wrapping Db2 with Postgres — Db2 is a family of database products developed by IBM and focused on enterprise use. If you need to migrate Db2 data to Postgres, db2_fdw provides a way to do it.

'10' Things Postgres Could Improve — An in-progress four part series (part 2 here) covering topics like transaction ID issues and replication.

📊 PDF: It's Time for a JSON/JSONB Great Unification — This dense, technical slidedeck is for you if you’re really into your JSON and you really care about Postgres’s JSON capabilities. It digs into JSON support in a forthcoming SQL standard and what future versions of Postgres can do to support this by unifying JSON and JSONB into one new data type.

Postgres 13 Beta 1 Now in Amazon RDS Database Preview Environment — RDS lets you run a managed Postgres deployment on AWS and the Preview Environment lets you play with pre-release versions if you want to test your tooling, etc.

How to Force a Table to Have Just One Row — Bruce demonstrates how to force a table to have at most one row by creating a unique expression index on a constant, with no column name references.

EnterpriseDB Rebrands to EDB — EnterpriseDB are a popular company in the Postgres space so if you now see 'EDB' anywhere, well.. it's them :-)

SQL Trickery: Hypothetical Aggregates — You can use this technique to determine the rank of a hypothetical value within an existing set.

tuned, Postgres, and You — tuned is a dynamic adaptive system tuning daemon (from Red Hat) that tunes system settings dynamically depending on usage. This post demonstrates creating a tuned profile for Postgres in particular.

Reasons to Migrate from Oracle to Postgres — This is truly a case of ‘preaching to the choir’ in this newsletter, but if you need any more reasons when chatting to others.. 😄

Jepsen Finds a Bug in Postgres 12.3 — The name ‘Jepsen’ is sure to stir emotions in any database server creator as their analyses of distributed systems (such as databases!) often reveal all sorts of edge cases and flaws. And so it goes with Postgres with a bug in serializable isolation being found. This is technical stuff, but it’s great to see systems like Postgres placed under such rigorous analysis.

A Free, Multi-Node, Petabyte Scale, Time-Series Database on Postgres — Read more about Timescale’s multi-node time-series database and why they’re making it available for free.

Controlling Runaway Queries with Statement Timeouts — A well tuned database on good hardware is easily capable of many thousands of queries per second but long lived queries can quickly cause things to back up. Here’s one defense against queries that want to live forever.

Understanding User Management in PgBouncer — PgBouncer is a popular connection proxy and pooler for Postgres, but it doesn’t just pass things straight through to Postgres, it can handle authentication directly itself. This article clears up how that works.

XgeneCloud: Instant REST and GraphQL APIs on any Database — Where ‘any’ includes Postgres, naturally :-) There’s quite a lot of pieces to this and they seem to be forming a company around the tech, but it’s open source nonetheless.

Citus 9.3 Released: The Postgres Horizontal Scaling Extension — Citus is the now Microsoft-owned Postgres extension that turns Postgres into a distributed database that can be more powerfully used across multiple servers. 9.3 improves the distributed SQL support, now with full support for window functions.

Webinar | Distributed SQL: A Modern, Cloud-Native PostgreSQL — Hitting challenges scaling PostgreSQL, deploying it in the cloud, or using it with microservice architecture? A Distributed SQL database might be a better fit for your workload. Tune in tomorrow, June 18, for an architecture review and live demo.

How One Word in Postgres Unlocked a 9x Performance Improvement — The creator of a personal finance tool experienced a user whose data caused a flood of INSERTs large enough to cause a problem. Here’s the tale of how a simple RETURNING clause enabled a huge optimization in the process.

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

SQLancer: A Tool for Detecting Logic Bugs in Database Systems — Considered a ‘research prototype’ for now, SQLancer’s job is to stress a database system into returning inconsistent or illogical results. Written in Java, it supports several databases (including Postgres).

PgHero 2.5: A Performance Dashboard for Postgres — Built in Ruby. And, yes, we have a newsletter for that. 😁

pgsql-http: An HTTP Client Extension for Postgres — If you need to make HTTP requests direct from Postgres, this is one way to go and links against libcurl.

A Step-by-Step Way to Backup a Heroku Postgres Database to an AWS S3 Bucket — Another variation on this that’s worth doing IMO is turning on S3 versioning so you can easily get rolling backups and keep a consistent filename.

10 Common Postgres Errors — Some quick fire common errors and warnings to watch out for, with symptoms and solutions, around things like memory, disk space, and permissions.