Changelog

Craig Kerstiens on why common table expressions (CTEs) are so cool: For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. … [CTEs] actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. Click through for examples of these in use.

This is the first major release since a year ago when 10 shipped. Lots of goodies as always, with a focus on performance improvements for very large databases and high computational workloads. Click through for all the bits and bobs.

Hasura helps you build GraphQL apps backed by Postgres or incrementally move to GraphQL for existing applications using Postgres. This looks superb.

If pub/sub, table inheritance, foreign data wrappers, triggers, or range/array types in Postgres are unfamiliar to you, click through.

How does Odyssey scale? Odyssey can significantly scale processing performance by specifying a number of additional worker threads. Each worker thread is responsible for authentication and proxying client-to-server and server-to-client requests. All worker threads are sharing global server connection pools. Multi-threaded design plays important role in SSL/TLS performance. Currently in beta, but the authors are using it in production so it’s likely usable at this stage.

Dimitri Fontaine, a PostgreSQL major contributor, has written an awesome blog series on PostgreSQL data types to show how to benefit from the PostgreSQL concept of a data type. The PostgreSQL concept of a data type is more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions. This allows an application developer to rely on PostgreSQL for more complex queries, having the processing happen where the data is — for instance when implementing advanced JOIN operations, then retrieving only the data set that is interesting for the application. The posts in this series were extracted from his book Mastering PostgreSQL in Application Development — which teaches SQL to developers so they can replace thousands of lines of code with very simple queries.

This was posted back in March, but it’s news to me: A long-running project has been JIT-compiling SQL queries in PostgreSQL by making use of LLVM’s just-in-time compilation support, rather than passing SQL queries through Postgres’ interpreter. With the LLVM JIT’ed queries, more efficient code is generated by being able to make more use of run-time information and can especially help in increasing the performance of complex SQL queries. JIT-compiling expressions for PostgreSQL has been found to be up to ~20%+ faster in database tests like TPC-H. Creating indexes was found to be even 5~19% faster with this JIT mode Hopefully this feature will progress quick enough to land in Postgres 11. 🙏

PostGraphile is the new incarnation of PostGraphQL (project history), which introspects your Postgres database schema and creates a fully functional GraphQL API for it. I’ve been poking around with these tools as I get acquainted with the provider side of GraphQL. I don’t think we’ll end up using PostGraphile if/when we ship our public Changelog API (news + podcasts), because I’m a control freak. But it’s been great for getting started quickly and seeing what’s possible. Highly recommended 👌

Craig Kerstiens put out a tweet the other day, asking: Whats your favorite hidden gem of Postgres that you wish more people knew about? The list of resulting hidden gems is… long. Thankfully, he aggregated them in a nice, readable, off-Twitter format. Give it a scan, you’re sure to learn something new.