New In PostgreSQL 12: Generated Columns
> PostgreSQL 12 comes with a new feature called generated columns. Other popular RDBMSes already support generated columns as “computed columns” or “virtual columns.” With Postgres 12, you can now use it in PostgreSQL as well. Read on to learn more.
Postgres Execution Plans - Field Glossary
> There are lots of guides out there to the basics of execution plans, but a lot are quite scarce on the details - how to interpret particular values, what they really mean, and where the pitfalls are.
> We’ve spent a lot of time over the last 18 months learning, clarifying, and downright misinterpreting how each of these fields work — and there’s still further for us to go on that.
> But we have come a long way, and I’d like to share the guide that I wish had existed when we started out — a glossary of the most common fields you’ll see on the operations in a query plan, and a detailed description of what each one means.
Thoughts on Arbitrary Pagination
> Pagination is the act of breaking a data set into multiple pages to limit the amount of data that has to be processed and sent by a server at once. We’re going to be changing how pagination works on crates.io, and I wanted to share some musings about the issues with supporting this as a generic abstraction. While I’m going to be talking about some PostgreSQL internals in this article, the general ideas presented apply to any SQL database.
ORMs are backwards
> I think all ORM users have a journey from ‘there should be a way to’ to ‘this is saving me so much work’ to ‘I have to reach into the vending machine to get my change out’.
Postgres full-text search is Good Enough!
> When you have to build a web application, you are often asked to add search. The magnifying glass is something that we now add to wireframes without even knowing what we are going to search.
> The search has become an important feature and we’ve seen a big increase in the popularity of tools like elasticsearch and SOLR which are both based on lucene. They are great tools but before going down the road of Weapons of Mass Destruction Search, maybe what you need is something a bit lighter which is simply good enough!
New in Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures
> Everyone’s favorite database, PostgreSQL, has a new release coming out soon: Postgres 11
> In this post we take a look at some of the new features that are part of the release, and in particular review the things you may need to monitor, or can utilize to increase your application and query performance.
Are we there yet? Are we there yet?
PostgreSQL 11 and Just In Time Compilation of Queries
> One of the big changes in the next PostgreSQL release is the result of Andres Freund’s work on the query executor engine. Andres has been working on this part of the system for a while now, and in the next release we are going to see a new component in the execution engine: a JIT expression compiler!
> In our benchmarking, PostgreSQL 11 JIT is an awesome piece of technology and provides up to 29.31% speed improvements, executing TPC-H Q1 at scale factor 10 in 20.5s instead of 29s when using PostgreSQL 10.
How not to structure your database-backed web applications: a study of performance bugs in the wild
> This is a fascinating study of the problems people get into when using ORMs to handle persistence concerns in their web applications. The authors study real-world applications and distil a catalogue of common performance anti-patterns. There are a bunch of familiar things in the list, and a few that surprised me with the amount of difference they can make. By fixing many of the issues that they find, Yang et al., are able to quantify how many lines of code it takes to address the issue, and what performance improvement the fix delivers.
> Note that fundamentally a lot of the issues stem from the fact that the ‘O’ in ORM could just as easily stand for ‘Opaque.’
We Sell Bonds!
> “Users don’t change passwords,” Benny replied. “That would break single sign-on. If a user changes their password in their home system, their company will submit a change request to us to modify the password on ‘We Sell Bonds!’.”
SQL Keys in Depth
> The internet is full of dogmatic commandments for choosing and using keys in relational databases. At times it verges on a holy war: should you use natural or artificial keys? Auto-incrementing integers, UUIDs?
Trying to Represent a Tree Structure Using Postgres
> This week I’ll publish a series of blog posts on the Postgres LTREE extension. I’ll get started today by trying to insert a tree structure into a Postgres table using standard SQL, and during the rest of the week I’ll take a close look at LTREE: how to install it, how to use it, and how it works.
pspg - Postgres Pager
> Everybody who uses psql uses less pager. It is working well, but there is not any special support for tabular data. I found few projects, but no one was completed for this purpose. I decided to write some small specialized pager for usage as psql pager.
WordPress WPDB SQL Injection
> The correct fix is to ditch this whole prepare mechanism (which returns a string SQL query). Do what basically everyone else does and return a statement/query object or execute the query directly. That way you can’t double-prepare a string.
HoTTSQL: Proving query rewrites with univalent SQL semantics
> Query rewriting is a vital part of SQL query optimisation, in which rewrite rules are applied to a query to transform it into forms with (hopefully!) a lower execution cost. Clearly when a query is rewritten we still want it to mean the same thing as the original – we call this semantic preserving. If you take Q1 and turn it into Q2, then for all database schemas and table instances Q1 and Q2 need to return the same results. Since query rewriting is used extensively in SQL engines, it may come as a surprise to you to learn that we don’t actually have proofs that many of the common rewrite rules actually are semantic preserving! Until now.
PostgreSQL 10 Released
> The PostgreSQL 10 release includes significant enhancements to effectively implement the divide and conquer strategy, including native logical replication, declarative table partitioning, and improved query parallelism.
All the cloud things.
Random Postgres Things
Learn new things about postgres.
PostgreSQL: 2017-05-11 Security Update Release
Nothing critical, plus 90 more bug fixes.
How to Calculate Multiple Aggregate Functions in a Single Query
> In the end, there were 32 queries in total (or 8 in my example) with all the possible combinations of predicates. Needless to say that running them all took quite a while, because the table had around 200M records and only one predicate could profit from an index.
> But in fact, the improvement is really easy. There are several options to calculate all these counts in a single query
PG Phriday: Through the Window