L

Felipe wants to read My Father Before Me: How Fathers and Sons Influence Each Other Throughout Their Lives by Michael J. Diamond

It sounds like click-bait, or one of those late night TV promotions – “Improve your database performance by 100% – by changing just this one setting!” But in this case, it’s true – you can drastically improve PostgreSQL on Windows performance by changing one configuration setting – and we made this the default in our Postgres by BigSQL distribution for 9.2 thru 9.6.

tl;dr – if you have high query load, change “update_process_title” to ‘off’ on Windows, and get 100% more throughput.

Most Postgres DBA’s already know that they need to tune settings for shared buffers, WAL segments, checkpoints, etc, to get the best performance from their database. If you are running PostgreSQL on Windows, there’s another setting that you need to look at, specifically “update_process_title”. Changing this setting from “on” to “off” can improve throughput on a heavy query load by close to 100%

We ran a series of benchmark tests in our performance lab and you can see the dramatic improvement in the graphs displayed. We tested PostgreSQL 9.5 on a 16-core Windows server with fast SSD drives using a standard pgbench run in both read-only and read-write modes. Scaling from 4 to 40 clients shows a plateau in throughput (measured by TPS) after 8 clients when the setting is set to “on”. Changing the update_process_title setting to “off” allows PostgreSQL to continue to scale throughput, showing increasing TPS up to 40 clients. The throughput at 32 read-only clients increases from 20K TPS to 58K TPS (180% higher) and at 40 clients continues to climb to 76K TPS (270% higher).

This performance gain is seen for both read-only and read-write workloads. With 32 clients, the write throughput increases from 2,700 TPS to 7,700 TPS (180% higher) and at 40 clients continues to climb to 8,200 (200% higher).

The update_process_title setting controls whether or not Postgres will update the process description that you see when querying the system list of running commands based on the current SQL statement being processed. On Linux this is done using ps, on Windows it requires the ProcessExplorer tool. Updating the process description becomes a bottleneck on Windows, and limits the throughput even on a high-end server. Not many Windows admins actually use this information on a regular basis, so unless you are actively debugging a slow or long running process using this process information, you should leave this turned off.

Takayuki Tsunakawa originally tracked down this bottleneck and created a patch for PostgreSQL 9.6 that has changed the default to be ‘off’ on Windows. We have made the same setting change in BigSQL distributions of version 9.2 thru 9.5 as well as 9.6. So even if you’re not ready to move to the new 9.6 version, when you install Postgres by BigSQL on Windows you are getting the best performance out-of-the-box.

 

Natalie gave 4 stars to What Some Of You Were (Paperback) by Christopher Keane
bookshelves: good-theology
A compilation of stories that have originated with Liberty Christian Ministries. They specialise in dealing with Christians (and others) struggling with same-sex attraction.

The homosexuality issue seems to have become seriously controversial in Christian circles and many avoid discussing it altogether. The church of England has sadly compromised its biblical stance as have many other denominations. The result is that some Christians are confused about where they should stand and others think that if they pretend the issue doesn't exist it will go away. There is also a climate of fear about expressing biblical views on the issue due to the general direction of the law-makers. But this sin with all its associated damage will not just suddenly vanish anymore than any other sin. We must not give in to groups like Stonewall who want to abolish Christian views on the subject altogether.

There is a difference between saying homosexuality is unacceptable to God and being homophobic. When one says the Bible stands against the performance of homosexual acts, that is not homophobic. It is not vilification to say that God rejects some human sexual behaviour. And it is no more homophobic than rejecting Christ and his messengers is Christophobic. Paul condemns a range of human behaviours (1 Corinthians 6 vs 9-11.) Homosexuality is on the list, alongside drunkenness and greed. Paul does not suggest one is better or worse than the other-these kinds of actions are incompatible with being the people of God.

This book will help Christians to better understand same-sex attraction and how to interact with those who are struggling and desire to change. There is a big difference between someone who says that they are gay, Christian and proud and someone who acknowledges their difficulty and desires to deal with it biblically. The latter are the people that really need our support and help not our condemnation and judgement. All of us are prone to weakness and temptation, why is homosexuality suddenly the big taboo?

Let's make it clear. Heterosexual adultery is no more and no less of a sin than homosexuality. Christians do not argue for heterosexuality but for obedience to God. That has implications for all of us, no matter what our gender orientation.

The book has testimonies from those who are attempting to live biblically despite their same-sex attraction either through praying and hoping for change in their orientation or through life-long celibacy. There are success stories for both approaches. It also documents a parental perspective--when a child comes out as gay. Also when a husband or wife tells their spouse they are struggling.

I particularly liked the appendix chapters which deal with

1. How we went gay (as a nation)
2. Is homosexuality biologically determined?
3. On homosexuality and change
4. Homosexuality in the New Testament

This is a good book that will not only encourage Christians to give proper support to those struggling but will clarify where Christians should stand on the issue of homosexuality.

Felipe liked Douglas Wilson's review of The Fundamentals: A Testimony to the Truth: "In the early part of the twentieth century a controversy in the church erupted between the modernists and the fundamentalists. The fundamentalists got their name from the fact that they published an enormous number of booklets defending "the fundamentals," by which they mean the infallibility of the Bible, the deity of Christ, etc. Those booklets were published in four volumes, and I have to say that I am quite proud of my fundamentalist fathers. They acquitted themselves well, and with great learning. They were (to my view) a tad too accommodating with some things (e.g. age of the earth), but for the most part they held the line wonderfully. The last essay in Volume 1 has this marvelous line, addressing the modernists, who haven't changed a bit in the last century. "A striking characteristic of these people is a persistent ignoring of what is written on the other side." The date marks my completion of the first volume. I will note when I finish the other three volumes in the body of the text here. Finished the second volume in September 2016."

E sumam com essa porcaria de computador, internet e smartfones também!

¯(ツ)/¯

EDIT: E os teares! Destruam os teares!
EDIT2: Pela volta dos ascensoristas!
EDIT3: E dos digitadores também!
EDIT4: Não... dos digitadores não... eles trarão computadores com eles.

Tenho a mania terrível de ir na contramão do meu tempo. Na verdade, não é terrível, mas salutar. Há formas de pensar, valores, livros, comportamentos, hábitos que, hoje, começam a ser esquecidos, mas não perderam sua importância. Podem inclusive estar completamente esquecidos por alguns, mas continuam emitindo sinais inquestionáveis de que, se recuperados, têm o poder de melhorar nossa vida.

Sou um aficionado da tecnologia. Um novo software — que esteja ligado, de alguma forma, à escrita ou à leitura — sempre me atrai. E, graças à tecnologia, meu trabalho se tornou, nos últimos anos, menos cansativo, pois pude suprimir etapas que me faziam perder tempo — como copiar, para um arquivo Word, as anotações com que sempre preencho os livros que leio. Leitores de e-books tornaram-se não só úteis, mas indispensáveis.

Entretanto, a onipresença do teclado me incomodava. O distanciamento de uma forma de escrita que me obrigasse a desenhar as palavras pareceu-me, a partir de certo momento, uma perda estética — ainda mais para mim, que sempre apreciei as canetas-tinteiro, a textura dos diferentes papéis, o odor e as cores das tintas. Havia uma perda sensorial que me perturbava.

O que era uma impressão vaga, desconforto impreciso, ganhou corpo quando li o estudo de Pam A. Mueller e Daniel M. Oppenheimer a respeito de como tomar notas em laptops resulta num processamento mais superficial das ideias. No primeiro momento, desconfiei do estudo — não seria mais uma conclusão apocalíptica? Depois, refletindo, comparando as conclusões dos pesquisadores ao que tantos escritores afirmam, comecei a questionar meu julgamento: não, concluí, voltar a escrever com canetas-tinteiro não se tratava apenas de nostalgia, ainda que esse sentimento estivesse presente.

A única forma de descobrir os efeitos da escrita à mão seria voltar aos velhos instrumentos — e quando decidi fazê-lo, percebi que, sim, eu desenvolvia as ideias com mais facilidade, com maior rapidez. O texto brotava com uma celeridade que eu havia esquecido.

Pode parecer pedante, mas reutilizar a caneta-tinteiro, ver o desenho das letras no papel, alimenta uma espécie de prazer. Tudo me parece mais real, mais vivo. Estou conectado ao meu próprio eu de uma forma mais clara, mais intensa. A própria cadência da mão sobre o papel, desenhando os sinais que me acompanham desde a infância, quando minha mãe ensinou-me a escrever em pedaços de papel polvilhados de farinha, tudo me torna mais produtivo, mais próximo da minha índole. A escrita deixou ser um ofício, ofício de que me orgulho, para ser também uma forma de aconchego.

Estou convencido de que as canetas e o papel trouxeram-me uma conexão parcialmente perdida entre meus pensamentos e a linguagem, a elaboração do texto. Escrever à mão talvez produza outros tipos de sinapses. Ou talvez eu esteja apenas sonhando. Mas meus escritos, com certeza, agora refletem melhor minha personalidade.

The post De volta ao papel e às canetas-tinteiro appeared first on Rodrigo Gurgel.

At GUADEC 2016 in Karlsruhe, Germany, Jonathan Blandford challenged the GNOME project to rethink how its desktop software uses network access. The GNOME desktop assumes Internet connectivity is always available, which has the side effect of making the software stack considerably less useful and, indeed, usable to people who live in those places regarded as the developing world.

Julia Reda, MEP: "Proprietary Software threatens Democracy"

Julia Reda ended the QtCon, a conference for the Free Software community, with a closing keynote on, among other things, Free Software in the European Public Sector.

Ms Reda, a member of the EU Parliament for the Pirate Party, explained how proprietary software, software that forbids users from studying and modifying it, has often left regulators in the dark, becoming a liability for and often a threat to the well-being and health of citizens.

An example of this, she said, is the recent Dieselgate scandal, in which auto-mobile manufacturers installed software that cheated instruments that measured fumes in test environments, only to spew illegal amounts of toxic exhaust into the atmosphere the moment they went on the road.

Ms Reda also explained how medical devices running proprietary software posed a health hazard for patients. She gave the example of a woman with a pacemaker who collapsed while climbing some stairs due to a bug in her device. Doctors and technicians had no way of diagnosing and correcting the problem as they did not have access to the code.

Also worrying is the threat software with restrictive licenses pose to democracy itself. The trend of substituting traditional voting ballots with voting machines is especially worrying, because, as these machines are not considered a threat to national security, their software also goes unaudited and is, in fact, unauditable in most cases.

And, although voting machines are built and programmed by private companies, they are commissioned by public entities and paid for with public money, money taken from citizens' taxes. However, there are no universal EU regulations that force companies, or, indeed, public organisations, to make the source code available to the citizens that have paid for it, said Ms Reda.

Furthermore, she noted that, despite the fact Free Software technologies (web servers, CMSs, email servers, and so on) are used extensively throughout the public administration, the public sector assumes very little responsibility in the way of giving back to the community via patches or even bug reports.

Ms Reda said that the solution to this very dismal state of affairs is a multi-pronged one. She commended the Free Software Foundation Europe for its work in advocating for all software commissioned by public entities and paid with public money, be made available under free/libre licenses for everyone. She also noted that to get governments on the side of Free Software it is essential to make them see its merits.

Only like this, she said, would it be possible to make legislators regulate coherently in favour of free/libre technologies.

Support FSFE, join the Fellowship
Make a one time donation

Douglas gave 4 stars to "But God...": The Two Words at the Heart of the Gospel (Paperback) by Casey Lute
This is a very encouraging little book, pointing to two words that function as the hinge of all gospel living. There is a problem, a dilemma, a crisis, a trouble, and God’s people are up against it. This happens time and again in the Scriptures. And the next two words are but God . . .

Casey Lute walks through the Scriptures, pointing to nine key instances of this. He starts with Noah. “But God remembered Noah and all the beasts and all the livestock that were with him in the ark. And God made a wind blow over the earth, and the waters subsided” (Gen. 8:1, ESV).

The God of the Bible is the God who saves, and He saves us in the middle of the story. The problem is sketched out and made clear, and then, when all hope is lost, we hear the words but God. God loves cliffhangers.

But God remembered Noah. But God delivered Israel at the Red Sea. But God raised Jesus from the dead. This book is a quick read, but quite meaty for all that.

Frederico wants to read A Fênix Islamista: O Estado Islâmico e a Reconfiguração do Oriente Médio by Loretta Napoleoni

Frederico wants to read A Batalha do Avaí - A beleza da barbárie: a Guerra do Paraguai pintada por Pedro Américo by Lilia Moritz Schwarcz

Felipe wants to read On the Government of God by Salvian

Felipe liked Fabrício Tavares De Moraes's review of Christian Political Action in an Age of Revolution:

Felipe liked Fabrício Tavares De Moraes's review of Admirable Evasions: How Psychology Undermines Morality:

PostgreSQL introduced the JSONB type in 9.4 with considerable celebration. (Well, about as much as you can expect for a new data type in an RDBMS.) It’s a wonderful feature: a format that lets you store blobs in the lingua franca of modern web services, without requiring re-parsing whenever you want to access a field, and in a way that enables indexing for complicated predicates like containment of other JSON blobs. It meaningfully extends PostgreSQL and makes it a viable choice for a lot of document store workflows. And it fits nicely in a startup engineering context: just add a properties column to the end of your table for all the other attributes you might want to store down the road, and your schema is now officially Future Proof ^TM.

We lean on JSONB heavily at Heap, and it’s a natural fit, as we have APIs that allow customers to attach arbitrary properties to events we collect. Recently, I’ve gotten a few questions about the benefits and drawbacks of using JSONB to store the entirety of a table – why have anything but an id and a data blob?

The idea of not having to explicitly manage a schema appeals to a lot of people, so it shouldn’t be surprising to see JSONB used this way. But there are considerable performance costs to doing so, some of which aren’t immediately obvious. There is great material for deciding which of JSON, JSONB, or hstore is right for your project, but the correct choice is often “none of the above.” ^[1] Here are a few reasons why.

Hidden Cost #1: Slow Queries Due To Lack Of Statistics

For traditional data types, PostgreSQL stores statistics about the distribution of values in each column of each table, such as:

• the number of distinct values seen
• the most common values
• the fraction of entries that are NULL
• for ordered types, a histogram sketch of the distribution of values in the column

For a given query, the query planner uses these statistics to estimate which execution plan will be the fastest. For example, let’s make a table with 1 million “measurements” of three values, each chosen at uniform random from {0, 1}. Each measurement was taken by one of 10,000 scientists, and each scientist comes from one of three labs:

Let’s say we want to get the tick marks in which all three values were 0 — which should be about 1/8th of them — and see how many times each lab was represented amongst the corresponding scientists. Our query will look something like this:

And our query plan will look something like this: https://explain.depesz.com/s/H4oY

This is what we’d hope to see: the planner knows from our table statistics that about 1/8th of the rows in measurements will have value_1, value_2, and value_3 equal to 0, so about 125,000 of them will need to be joined with a scientist’s lab, and the database does so via a hash join. That is, load the contents of scientist_labs into a hash table keyed on scientist_id, scan through the matching rows from measurements, and look each one up in the hash table by its scientist_id value. The execution is fast — about 300 ms on my machine.

Let’s say we instead store our measurements as JSONB blobs, like this:

The analogous read query would look like this:

The performance is dramatically worse — a whopping 584 seconds on my laptop, about 2000x slower: https://explain.depesz.com/s/zJiT

The underlying reason is that PostgreSQL doesn’t know how to keep statistics on the values of fields within JSONB columns. It has no way of knowing, for example, that record ->> 'value_2' = 0 will be true about 50% of the time, so it relies on a hardcoded estimate of 0.1%. So, it estimates that 0.1% of 0.1% of 0.1% of the measurements table will be relevant (which it rounds up to ~1 row). As a result, it chooses a nested loop join: for each row in measurements that passes our filter, look up the corresponding lab_name in scientist_labs via the primary key of the latter table. But since there are ~125,000 such measurements, instead of ~1, this turns out to take an eternity. ^[2]

As always, accurate statistics are a critical ingredient to good database performance. In their absence, the planner can’t determine which join algorithms, join orders, or scan types will make your query fast. The result is that innocent queries will blow up on you. This is one of the hidden costs of JSONB: your data doesn’t have statistics, so the query planner is flying blind.

This is not an academic consideration. This caused production issues for us, and the only way to get around them was to disable nested loops entirely as a join option, with a global setting of enable_nestloop = off. Ordinarily, you should never do something like that.

This probably won’t bite you in a key-value / document-store workload, but it’s easy to run into this if you’re using JSONB along with analytical queries.

Hidden Cost #2: Larger Table Footprint

Under the hood, PostgreSQL’s JSON datatype stores your blobs as strings that it happens to know are valid JSON. The JSONB encoding has a bit more overhead, with the upside that you don’t need to parse the JSON to retrieve a particular field. In both cases, at the very least, the database stores each key and value in every row. PostgreSQL doesn’t do anything clever to deduplicate commonly occurring keys.

Using the above measurements table again, the initial non-JSONB version of our table takes up 79 mb of disk space, whereas the JSONB variant takes 164 mb — more than twice as much. That is, the majority of our table contents are the the strings value_1, value_2, value_3, and scientist_id, repeated over and over again. So, in this case, you would need to pay for twice as much disk, not to mention follow-on effects that make all sorts of operations slower or more expensive. The original schema will cache much better, or might fit entirely in memory. The smaller size means it will also require half as much i/o for large reads or maintenance operations.

For a less contrived anecdote, we found a disk space savings of about 30% by pulling 45 commonly used fields out of JSONB and into first-class columns. On a petabyte-scale dataset, that turns out to be a pretty big win.

As a rule of thumb, each column costs about 1 bit of overhead for each row in your table, regardless of whether the column’s value is null.^[3] So, for example, if an optional field is going to have a ten-character key in your JSONB blobs, and thus cost at least 80 bits to store the key in each row in which it’s present, it will save space to give it a first-class column if it’s present in at least 1/80th of your rows.

Know another innocuous change with big performance implications? Ping me @danlovesproofs.

We’re constantly evaluating alternative schemas and indexing strategies for serving ad hoc queries across hundreds of billions of events. Interested in working with us? Shoot us a note at jobs@heapanalytics.com.

New comment on Leandro's review of Panaceia
by Rodrigo Constantino

Por favor, leia os romances do Mainardi , em especial:

A queda
Malthus
Arquipélago
Contra o Brasil
Polígono das Secas

Douglas gave 5 stars to Till We Have Faces (Paperback) by C.S. Lewis
bookshelves: fiction
Stupendous. World class. Top drawer.

Finished an audio version of it in August of 2016. I have read this a total of three times. Once when I was young, and I didn't like it. The second time was in 2003, and I thought it was great. This time, and greater still.

New comment on Leonardo's review of Cornelius Van Til: o homem e o mito
by John W. Robbins

Existem alguns autores que ao fazer uma análise crítica de outro autor que discordem, geram respeito e reflexão nos leitores; este não é o caso de Robbins! Alguns fatores me fizeram dar nota 2 (e não nota 1): 1) A capa é bonita. 2) Eu concordo com a crítica dele ao estilo e a forma de comunicação de Van Til. 3) Eu gosto da editora.
Para mim, esses são os únicos pontos positivos da obra.
Minhas críticas são: 1) Seu estilo irônico, zombador e desrespeitoso em alguns momentos. 2) A citação de textos de Van Til soltos e fora de contexto para criticar uma doutrina. 3) A má compreensão de Van Til em alguns pontos, principalmente quanto sua visão do conhecimento de Deus. 4) Ao criticar alguns de serem "fans" do "mito" Van Til, ele demonstra ser a mesmo coisa com relação a Clark. 5) Sua análise e critica superficial de Van Til. Creio que o próprio Clark desaprovaria essa obra.

A tonalidade da escrita se assemelha a uma criança birrenta defendendo seu super herói favorito.

They write:

"Now imagine if you owned a computing device that you could easily fix yourself and inexpensively upgrade as needed. So, instead of having to shell out for a completely new computer, you could simply spend around US$50 to upgrade — which, by the way, you could easily do in SECONDS, by pushing a button on the side of your device and just popping in a new computer card. Doesn’t that sound like the way it should be?"

This project certainly sounds appealing, but only if the computer hardware is designed and configured to run software that does as much as possible to respect your freedom and ensure your control over your device. Fortunately, one option you have when backing this project is to purchase a Libre Tea Computer Card. After working closely with the developers and reviewing a sample test board, we are confident that their plans are to create a device that can achieve our Respects Your Freedom (RYF) certification. And because the project is running their crowdfunding on Crowd Supply, users can financially support them anonymously and without the use of proprietary Javascript.

The project is being developed by Luke Kenneth Casson Leighton of Rhombus-Tech and is sponsored by Christopher Waid of ThinkPenguin, a company that sells multiple RYF-certified hardware products. It is exciting to see passionate free software advocates in our community working with OEMs to produce a computer hardware product capable of achieving RYF certification. We hope that this is the first of many computing systems they are able to design and build that respect your freedom.

The Libre Tea Computer Card is built with an Allwinner A20 dual core processor configured to use the main CPU for graphics; it has 2 GB of RAM and 8 GB of NAND Flash; and it will come pre-installed with Parabola GNU/Linux-libre, an FSF-endorsed fully-free operating system.

We encourage you to back the Libre Tea Computer Card. We'll have to do another evaluation once it is actually produced to be sure it meets our certification standards, but we have high hopes. Their funding deadline is August 26th, so don't delay!

US Chief Information Officer Tony Scott introduces the Federal Source Code Policy, on the White House blog. "By making source code available for sharing and re-use across Federal agencies, we can avoid duplicative custom software purchases and promote innovation and collaboration across Federal agencies. By opening more of our code to the brightest minds inside and outside of government, we can enable them to work together to ensure that the code is reliable and effective in furthering our national objectives. And we can do all of this while remaining consistent with the Federal Government’s long-standing policy of technology neutrality, through which we seek to ensure that Federal investments in IT are merit-based, improve the performance of our government, and create value for the American people." (Thanks to David A. Wheeler)

An Uber technical blog of July 2016 described the perception of “many Postgres limitations”. Regrettably, a number of important technical points are either not correct or not wholly correct because they overlook many optimizations in PostgreSQL that were added specifically to address the cases discussed. In most cases, those limitations were actually true in the distant past of 5-10 years ago, so that leaves us with the impression of comparing MySQL as it is now with PostgreSQL as it was a decade ago. This is no doubt because the post was actually written some time/years? ago and only recently published.

This document looks in detail at those points to ensure we have detailed information available for a wider audience, so nobody is confused by PostgreSQL’s capabilities.

Having said that, I very much welcome the raising of those points and also wish to show that the PostgreSQL project and 2ndQuadrant are responsive to feedback. To do this, detailed follow-ups are noted for immediate action.

These points were noted in the blog
* Poor replica MVCC support
* Inefficient architecture for writes
* Inefficient data replication
* Difficulty upgrading to newer releases

Poor replica MVCC support

“If a streaming replica has an open transaction, updates to the database are blocked if they affect rows held open by the transaction. In this situation, Postgres pauses the WAL application thread until the transaction has ended.”

This is true, though misses the point that a parameter exists to control that behaviour, so that when
hot_standby_feedback = on
the described behaviour does not occur in normal circumstances. This is supported from PostgreSQL 9.1 (2011) and above. If you’re not using it, please consider doing so.

Later, this comment leads to the conclusion “Postgres replicas … can’t implement MVCC” which is wholly incorrect and a major misunderstanding. PostgreSQL replicas certainly allow access to data with full MVCC semantics.

Inefficient architecture for writes

“If old transactions need to reference a row for the purposes of MVCC MySQL copies the old row into a special area called the rollback segment.”
…
“This design also makes vacuuming and compaction more efficient. All of the rows that are eligible to be vacuumed are available directly in the rollback segment. By comparison, the Postgres autovacuum process has to do full table scans to identify deleted rows.”

Moving old rows to a rollback segment adds time to the write path for UPDATEs, but that point isn’t mentioned. PostgreSQL is more efficient architecture for writes in relation to MVCC because it doesn’t need to do as many push-ups.

Later, if the workload requires that we access old rows from the rollback segment that is also more expensive. That is not always needed, yet it is very common for longer running queries to need to access older data. However, if all transactions are roughly the same short duration access to the rollback segment is seldom needed, which just happens to make benchmark results appear good while real-world applications suffer.

By contrast, PostgreSQL has multiple optimizations that improve vacuuming and compaction. First, an optimization called HOT improves vacuuming in heavily updated parts of a table (since 2007), while the visibility map ensures that VACUUM can avoid full table scans (since 2008).

Whether rollback segments help or hinder an application depend on the specific use case and it’s much more complex than this first appears.

Next, we discuss indexes…

“With Postgres, the primary index and secondary indexes all point directly to the on-disk tuple offsets.”

This point is correct; PostgreSQL indexes currently use a direct pointer between the index entry and the heap tuple version. InnoDB secondary indexes are “indirect indexes” in that they do not refer to the heap tuple version directly, they contain the value of the Primary Key (PK) of the tuple.

Comparing direct and indirect indexes we see
* direct indexes have links that go index → heap
* indirect indexes have links that go index → PK index → heap

Indirect indexes store the PK values of the rows they index, so if the PK columns are wide or contain multiple columns the index will use significantly more disk space than a direct index, making them even less efficient for both read and write (as stated in MySQL docs). Also indirect indexes have index search time >=2 times worse than direct indexes, which slows down both reads (SELECTs) and searched writes (UPDATEs and DELETEs).
Performance that is >=100% slower is understated as just a “slight disadvantage” [of MySQL].

“When a tuple location changes, all indexes must be updated.”

This is misleading, since it ignores the important Heap Only Tuple (HOT) optimization that was introduced in PostgreSQL 8.3 in 2007. The HOT optimization means that in the common case, a new row version does not require any new index entries, a point which effectively nullifies the various conclusions that are drawn from it regarding both inefficiency of writes and inefficiency of the replication protocol.

“However, these indexes still must be updated with the creation of a new row tuple in the database for the row record. For tables with a large number of secondary indexes, these superfluous steps can cause enormous inefficiencies.”

As a result of ignoring the HOT optimization this description appears to discuss the common case, rather than the uncommon case. It is currently true that for direct indexes if any one of the indexed columns change then new index pointers are required for all indexes. It seems possible for PostgreSQL to optimize this further and I’ve come up with various designs and will be looking to implement this best fairly soon.

Although they have a higher read overhead, indirect indexes have the useful property that if a table has multiple secondary indexes then an update of one secondary index does not affect the other secondary indexes if their column values remain unchanged. This makes indirect indexes useful only for the case where an application needs indexes that would be infrequently used for read, yet with a high update rate that does not touch those columns.

Thus, it is possible to construct cases in which PostgreSQL consistently beats InnoDB, or vice versa. In the “common case” PostgreSQL beats InnoDB on reads and is roughly equal on writes for btree access. What we should note is that PostgreSQL has the widest selection of index types of any database system and this is an area of strength, not weakness.

The current architecture of PostgreSQL is that all index types are “direct”, whereas in InnoDB primary indexes are “direct” and secondary indexes “indirect”. There is no inherent architectural limitation that prevents PostgreSQL from also using indirect indexes, though it is true that has not been added yet.

We’ve done a short feasibility study and it appears straightforward to implement indirect indexes for PostgreSQL, as an option at create index time. We will pursue this if the HOT optimizations discussed above aren’t as useful or possible, giving us a second approach for further optimization. Additional index optimizations have also been suggested.

Inefficient data replication

“However, the verbosity of the Postgres replication protocol can still cause an overwhelming amount of data for a database that uses a lot of indexes.”

Again, these comments discuss MySQL replication which can be characterized as Logical Replication. PostgreSQL provides both physical and logical replication. All of the benefits discussed for MySQL replication are shared by PostgreSQL’s logical replication. There are also benefits for physical replication in many cases, which is why PostgreSQL provides both logical and physical replication as options.

PostgreSQL physical replication protocol itself is not verbose – this comment is roughly the same as the “inefficient writes” discussion: if PostgreSQL optimizes away index updates then they do not generate any entries in the transaction log (WAL), so there is no inefficiency. Also, the comment doesn’t actually say what we mean by “overwhelming”. What this discussion doesn’t consider is the performance of replication apply. Physical replication is faster than logical replication because including the index pointers in the replication stream allows us to insert them directly into the index, rather than needing to search the index for the right point for insertion. Including the index pointers actually increases not decreases performance, even though the replication bandwidth requirement is higher.

PostgreSQL Logical Replication is available via 2ndQuadrant’s pglogical and will be available in PostgreSQL 10.0 in core.

MySQL “Statement-based replication is usually the most compact but can require replicas to apply expensive statements to update small amounts of data. On the other hand, row-based replication, akin to the Postgres WAL replication, is more verbose but results in more predictable and efficient updates on the replicas.”

Yes, statement-based replication is more efficient in terms of bandwidth, but even less efficient in terms of the performance of applying changes to receiving servers. Most importantly, it leads to various problems and in various cases replication may not work as expected, involving developers in diagnosing operational problems. PostgreSQL probably won’t adopt statement-based replication.

Difficulty upgrading to newer releases

“the basic design of the on-disk representation in 9.2 hasn’t changed significantly since at least the Postgres 8.3 release (now nearly 10 years old).”

This is described as if it were a bad thing, but actually it’s a good thing and is what allows major version upgrades to occur quickly without unloading and reloading data.

“We started out with Postgres 9.1 and successfully completed the upgrade process to move to Postgres 9.2. However, the process took so many hours that we couldn’t afford to do the process again. By the time Postgres 9.3 came out, Uber’s growth increased our dataset substantially, so the upgrade would have been even lengthier.”

The pg_upgrade -k option provides an easy and effective upgrade mechanism. Pg_upgrade does require some downtime, which is why 2ndQuadrant has been actively writing logical replication for some years, focusing on zero-downtime upgrade.

Although the logical replication upgrade is only currently available from 9.4 to 9.5, 9.4 to 9.6 and 9.5 to 9.6, there is more good news coming. 2ndQuadrant is working on highly efficient upgrades from earlier major releases, starting with 9.1 → 9.5/9.6. When PostgreSQL 9.1 is desupported later in 2016 this will allow people using 9.1 to upgrade to the latest versions. This is available as a private service, so if you need zero-downtime upgrade from 9.1 upwards please get in touch.

In 2017, upgrades from 9.2 and 9.3 will also be supported, allowing everybody to upgrade efficiently with zero-downtime prior to the de-supporting of those versions.