database

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.

Fauna DB: Global Transactional NoSQL

Productivity, safety, and operational simplicity in a unified distributed database.

From the team that scaled Twitter.

Database Modelization Anti-Patterns

The entity attribue values or EAV is a design that tries to accommodate with a lack of specifications. In our application, we have to deal with parameters and new parameters may be added at each release. It’s not clear which parameters we need, we just want a place to manage them easily, and we are already using a database server after all. So there we go:

.. The model makes it very easy to add things to it, and very difficult to make sense of the accumulated data, or to use them effectively in SQL, making it an anti-pattern.

Multiple Values per Column

create table tweet
(
id bigint primary key,
date timestamptz,
message text,
tags text
);
Data would then be added with a semicolon separator, for instance, or maybe a pipe | char, or in some cases with a fancy Unicode separator char such as §, ¶ or ¦. Here we find a classic semicolon:
id │ date │ message │ tags
════════════════════╪══════╪═════════╪════════════════════════
720553530088669185 │ … │ … │ #NY17
720553531665682434 │ … │ … │ #Endomondo;#endorphins
(2 rows)

Several things are very hard to do when you have several tags hidden in a text column using a separator:
Tag Search
To implement searching for a list of messages containing a single given tag, this model forces a substring search which is much less efficient than direct search.