---
title: "How Postgres used fsync() incorrectly for 20 years"
publication: "Postgres Weekly"
issue: 292
published_at: "2019-02-13"
canonical_url: "https://postgresweekly.com/issues/292"
---

The latest Postgres news, articles, tools, and projects.       

#292 — February 13, 2019 

[Read on the Web](https://postgresweekly.com/issues/292)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/ochjwlkwxnggjvhl94sk.jpg)](https://dzone.com/articles/loading-terabytes-of-data-from-postgres-into-bigqu)   

[Loading Terabytes of Data from Postgres into BigQuery](https://dzone.com/articles/loading-terabytes-of-data-from-postgres-into-bigqu) — A quick look at how you can transfer large amounts of data from a Postgres database into Google’s [BigQuery](https://cloud.google.com/bigquery/), perhaps for analytics work or even opening it up to the public. 

Pavel Tiunov   [![\](https://copm.s3.amazonaws.com/27676a9c.png)](https://pgexperts.com) 

[Because Your Data Is Your Business](https://pgexperts.com) — PGX's open source experts provide the database and application skills necessary to solve database problems, accelerate existing applications, refactor infrastructure, and develop new applications. 

PostgreSQL Experts, Inc. sponsor   

▶[How Postgres Used `fsync` Incorrectly for 20 Years](https://fosdem.org/2019/schedule/event/postgresql_fsync/) — An interesting look into an issue not just faced by Postgres but by many systems that thought `fsync` (a system call that flushes file buffers into a final committed/persisted state) worked in a way that it actually doesn’t. With _“disastrous consequences for data durability/consistency”_ this is an interesting area to learn about if you like digging deep. **Hate video? There’s more info in [this Postgres wiki entry.](https://wiki.postgresql.org/wiki/Fsync_Errors)** 

Tomas Vondra   

[SortSupport: How Postgres Sorts at Speed](https://brandur.org/sortsupport) — This is pretty technical, but also a great read if you want to understand _SortSupport_, one of the systems within Postgres for improving sort performance. _“Sorting for types that have had Sortsupport implemented usually gets twice as fast or more, a speedup that transfers directly into common database operations like `ORDER BY`, `DISTINCT`, and `CREATE INDEX`.”_ 

Brandur Leach   

[An Overview of the Index Changes in Postgres 11](https://severalnines.com/blog/overview-index-changes-postgresql-11) — PostgreSQL 11 introduced several changes to the way it handles database indexes. This post provides an overview and how you can take advantage of them. 

Amit Jain   

▶[Breaking Postgres at Scale](https://fosdem.org/2019/schedule/event/breaking_postgresql_on_scale/) — A 50 minute talk from FOSDEM digging into some of the scaling “discontinuities” in Postgres and how to mitigate them. If you don’t want your database failing in weird and wonderful ways as you scale, this is a valuable talk. 

Christophe Pettus   

[pgDash Insights to Maximize PostgreSQL Performance](https://pgdash.io) — pgDash provides time-series dashboards, diagnostics, analytics and more to help you maximize the performance of your Postgres deployment. 

pgDash by RapidLoop sponsor   

[The Most Useful Postgres Extension? `pg_stat_statements`](https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/) — Inspired by a talk in the PostgreSQL devroom at FOSDEM, a post on the usefulness of the `pg_stat_statements` extensionand why every Postgres developer should have it in their toolbox. 

Craig Kerstiens   

[Connection Pooling, A Performance Best Practice for using Azure Database for PostgreSQL](https://azure.microsoft.com/en-us/blog/performance-best-practices-for-using-azure-database-for-postgresql-connection-pooling/) — The latest in a series of posts on improving the performance and scale of databases on Azure’s platform. 

Parikshit Savjani   

[Move Fast and Migrate Things: How We Automated Migrations in Postgres](https://benchling.engineering/move-fast-and-migrate-things-how-we-automated-migrations-in-postgres-d60aba0fc3d4) — Benchling is a life science data management platform which means they need to work with a _lot_ of data, and [as with Braintree/PayPal last week](https://medium.com/braintree-product-technology/postgresql-at-scale-database-schema-changes-without-downtime-20d3749ed680), it’s neat to see how they approach migrations at their level. 

Vineet Gopal       supported by [![\](https://res.cloudinary.com/cpress/image/upload/v1545062952/na5isnukkrtglivbcqvs.png)](https://connect.strongdm.com/schedule-demo)  

💡 Tip of the Week 

How to see the elapsed time for your queries in `psql`
 _by Peter Cooper_  

For some reason I'm particularly keen to know how long things take. I even have a setting on my shell to include the [running time of the last command](https://jakemccrary.com/blog/2015/05/03/put-the-last-commands-run-time-in-your-bash-prompt/) in my prompt. But how can we get something similar when working with `psql`? 

It's easier than you might think! Just type: 

`\timing` 

And psql responds with `"Timing is on."` You can also add `on` or `off` to the end if you want to be explicit. 

Now we get basic timings when we run queries:   

![](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/qhneibej28rs6gl3vilr.jpg)    

If you _really_ like this and want it to be permanent, create a [`.psqlrc`](https://thoughtbot.com/blog/an-explained-psqlrc) file in your home directory and put `\timing` inside and it'll be run automatically each time you load psql. 

Of course, this tip is solely to improve your day to day psql experience. If you want to _really_ dig into query times seriously, prepend your queries with `EXPLAIN ANALYZE` instead and you'll get more insight into why the timings are as they are. But that's a tip for another day.. :-)  

This week's tip is sponsored by _[strongDM](https://connect.strongdm.com/schedule-demo)_, the best way to secure access to your servers/databases. [Schedule a 10 min demo.](https://connect.strongdm.com/schedule-demo) 

If you have any ideas for your own tips, [reach out here](https://cooperpress.typeform.com/to/bTSq7v) - we'll pay $50 for your tip if we use it in an issue.          

Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens) 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom