Upgrade your partitioning from inheritance to declarative

Before PostgreSQL 10, Postgres users partitioned their data using inheritance based partitioning. The method used constraints to define the partitions and rules or triggers to route the data to appropriate partition. A user had to write and maintain code for all that. PostgreSQL 10 introduced declarative partitioning, which is much easier to setup and requires almost no maintenance. PostgreSQL 11
is adding a number of partitioning related enhancements that work with declarative partitioning. Users who have implemented inheritance based partitioning would want to move to declarative partitioning (after upgrading to v11, of course) to benefit from those features. Here’s how they can do so.

3 pleasantly surprising PostgreSQL Indexing tricks

Partial Indexes

On one of the projects I worked on had the following code:

Message.where(sent: false).find_each(&:process)

It runs every hour or so to send enqueued messages. The Message#process method set sent to true after sending the message. Only new messages had sent set to false. The code above doesn’t care about sent messages at all.

The problem is that there was an index on messages.sent. It encompassed all rows. 99% of messages were marked as sent so 99% of entries in the index were unused. The app cared only about those 1% with sent == false.

PostgreSQL to the rescue! We can create an index on a subset of rows that satisfy a WHERE condition. In this case:

CREATE INDEX messages_sent_true ON messages(sent) WHERE NOT sent;

This command will index only those rows that aren’t marked as sent.

You can create partial indexes in vanilla Rails. Just run:

add_index :messages, :sent, where: "NOT sent"

When used right, partial indexes are smaller and faster than full-table indexes.

How to do histograms in PostgreSQL

SELECT * FROM histogram($table_name_or_subquery, $column_name);  

. . . to give sweet results like this, in a check of the distribution of 2016 political contributions in Vermont:

fec=# SELECT * FROM histogram('(SELECT * FROM small_donors_vt LIMIT 50000)', 'transaction_amt');

 bucket |   range   | freq |       bar       
--------+-----------+------+-----------------
      1 | [0,9]     | 2744 | ******
      2 | [10,19]   | 5630 | *************
      3 | [20,29]   | 6383 | ***************
      4 | [30,39]   | 1290 | ***
      5 | [40,49]   |  369 | *
      6 | [50,59]   | 3541 | ********
      7 | [60,69]   |  174 | 
      8 | [70,79]   |  313 | *
      9 | [80,89]   |  171 | 
     10 | [90,99]   |   65 | 
     11 | [100,109] | 2363 | ******
     12 | [110,119] |   51 | 
     13 | [120,129] |  115 | 
     14 | [130,139] |   32 | 
     15 | [140,146] |   11 | 
     16 | [150,159] |  187 | 
     17 | [160,169] |   24 | 
     18 | [170,177] |   33 | 
     19 | [180,189] |   19 | 
     20 | [191,199] |   24 | 
     21 | [200,200] |  795 | **