Concurrently numb

Here we go again. I'm not dead, not yet.
I've just moved home and started a new job and I'll be honest, before this job I've just played with toys.

With this post I want to start a talk over indexes, how good they can be and how a wrong action or assumption can buy you a one way ticket to disaster.
So, let's start with  the concurrent clause in the CREATE INDEX statement and a short description of the PostgreSQL's general purpose indexes, the b-trees.

Index physical structure

PostgreSQL implements the b-tree index access using the Lehman and Yao's high-concurrency B-tree management algorithm, to find out an extensive description you can check out the README file in the source directorysrc/backend/access/nbtree/README
The chapter on database physical storage give you a good explain how the data files are organized and how is organized the block structure but doesn't tell too much about the index blocks.
An index block is quite similar to the heap block, with a 24 bytes long page header with references to the corresponding WAL entry and timeline, plus the pointers to the begin of the free space, to the end of the free space and to the beginning of the special space.
After the header resides the item pointers sectionto the tuples stored in the end of the page, usually 4 bytes on 32 bit and 8 bytes on windows and 64 bit architectures.
The last section, the special space, is usualy 16 bytes long and stores the pointers to the related pages inside the tree. Another difference with data files is the presence of the metapage, the first page which stores the pointers to the root page, or pages. With this implementation is possible to have the root page split.

Table and index space overhead

Any item in PostgreSQL have special attributes identified with negative att num in the pg_attribute system table.
According with this structure every item have an extra overhead of 27 bytes even if the table doesn't have columns.
Another difference with the heap pages is the fill factor, fixed to 0.7 for non leaf pages and 0.9, but adjustable, for leaf pages.

The visibility map and index only scans

Up to the version 9.1 the index is used only to track the ordered data pointer to the corresponding heap block.
As the index carries the values in an ordered ans structured tree is theoretically possible to read only the index to get the data if the SELECT contains only for the indexed columns.
Another point to my mantra SELECT * FROM table; IS THE ROOT OF EVIL.
The problem with the index only scan is related to the tuple visibility.
The index inside the transaction can be temporarily out of date for deleted tuples this can produce inconsistent results.
The visibility map is then used to track the visible tuples to keep the index read consistent.
The problem with this approach, before the version 9.2, is that this fork is not crash safe and, in the case of instance crash, could result in an out of date map.
In the version 9.2 visibility map changes is logged on the WAL making it the map crash safe and enabling the index only scan.

Bloating indexes

As any PostgreSQL expert should know UPDATE stands for INSERT.
Everytime an update is performed a new row version is created with xmin set to the current transaction XID and the previous version have the xmax set to the same XID.
This permit the magic of MVCC without the use of the rollback segment as Oracle does.
The problem with this approach is that often updated tables can bloat faster and the last updated tuples will move across the datafiles every time a new block is created to store the newer versions.
When this happens if there's an index referencing the tuple, this must be updated as well with a new row version that points to the new heap block.
If this in a table, that is a sequential not ordered collections of blocks, add the block in the table's physical end, the same for the index require the index to be transversed to find where it is the updated row and then the new block creation must follow the b-tree structure in a very expensive process.
Even worse, if the updated value is in a composite index and is not the first column the index entry point is unknown and the read for the update require a great amout of cpu cycles and disk seek.
Then the cherry on the cake. The vacuum process cannot mark an index block reusable if there inside mixed live and dead tuples.
I think you got the point.
Indexes can bloat with values scattered over multiple blocks where dead tuples slow down the search process. As the optimizer doesn't know about this, an index scan over a bloated index can have horrible performances.
And I didn't mentioned the waste of space...

This is a job for REINDEX

Of course PostgreSQL comes with good maintenance statements like VACUUM or CLUSTER, for the tables, and REINDEX for the indexes.
Reindex builds a new index, updates the system catalogue to point to the new one and drop the old one. Looks simple? Not exactly.

To do this a write exclusive lock must be acquired on the table and this prevents any write action for the time needed to reindex.
The read is not affected as the REINDEX needs only a consistent snapshot to build the new relation.
The index build requires the data to be sorted and if the amount doesn't fit in the work_mem value this will be performed on disk slowing down the entire process.
The exclusive lock have one big advantage, if there's some process blocking the REINDEX to acquire the lock the procedure will not start.
This doesn't happen with the concurrent rebuild (REINDEX thanks Rassilon does not have the CONCURRENT clause) and this can give you an extra free ticket to the previously mentioned disaster.

Concurrently what?

The concurrent build create a new INVALID index immediately visible, then performs a second table scan to update the index with the values changed during the creation, then acquires an exclusive lock over the table to validate the index. The point of failure is the last step. As no check is performed before the index build is started if there's any blocking process, for example pg_dump, the last step will wait for the lock, meanwhile the INVALID index is attached and updated adding overhead to the table's operations.
If you cancel the query the index is not dropped automatically. It stays in place in INVALID state since is dropped.
But the drop requires an exclusive lock on the pg_class table and if the blocking process is still around...
Now, what happens if you create an unique index and during the second table scan the unique condition is violated by transactions not visible when the first scan is started? The index build is aborted, same as before, INVALID index attached to the table.
You see. Do you like aisle or window?

Wrap up

Let's review things.

• Index bloats, sooner or later you'll need to cope with it
• Periodical reindex reclaim space and improve speed
• If a write operation is slow take a look for indexes  
• Reindex locks the writes but not the read
• It's possible to create a new index without the write lock, beware the dragon

Don't let your improbability grow to infinite

After three posts on self celebrating, best practice and scary things now is the time to start talking about the DBA stuff.As you should already discovered this blog is not for beginners. I think there's too much howtos for basic things like installation and table creation, all scattered around the internet.
My intention is explore the unknown topics and try to give a clear solution for any possible problem.
This post will cover the database housekeeping, things to keep under your pillow to let the database run efficiently and let you sleep without worries.

All the section's titles are from Douglas Adams The Hitchhikers Guide To The Galaxy, no copyright infringement intended. I used it because I love it and I never forget my towel. And by the way, THE ANSWER IS 42

Time is an illusion. Lunchtime doubly so.

PostgreSQL comes with a powerful feature called MVCC which stands for Multi Version Concurrency Control. The database track, for each transaction, which data is visible and which not simply assigning a trasaction id, XID, when a new tuple is inserted and when the same tuple is deleted. Each tuple carries two system columns, xmin and xmax, used to determine the visibility in a simple way. Anything greater than the current transaction id is in the future and then invisible. This works absolutely good except one big caveat. The XID is a 4 byte integer and every 4 billions transactions wraps.In the early database versions this behaviour forced the DBA to initdb a new data directory and reload the data from a pg_dump every 4 billions transactions. The risk was the sudden disappearance of all data at XID wrap.
To avoid this a new comparison method was adopted in later versions. The modulo-2^31 method guarantee, for each integer, 2 billions are greater, and then in the future, and 2 billions are lesser, then in the past. A new special XID called frozenXID which is in the past for any real XID was introduced to store in a safe condition the tuples with old transactions id.

The VACUUM  when finds a tuple which XID age is greater than the configuration parameter vacuum_freeze_min_age freezes the xmin value keeping it safe from the wraparound danger.
When one of the databases becomes dangerously near to the wraparound failure the server start emitting messages in the log, when this become too near to the wraparound the system will shut down and refuse to start any new transaction. The limit is 1 million transactions and is valid for each database in the cluster.
So, to avoid any problem the periodic vacuum must include all databases, even template1 and postgres.

mostly harmless

Indexes are a wonderful thing, they order data and give the direct access to the data block without need to read the entire table.Most people believe an index creation is a good thing, even if the index is not used. That's wrong as useless indexes put overhead in the table activity and the query planner can be confused by their presence.

An index block read require the so called random access on disk which, by default, its cost is 4 times a corresponding sequential access. An index block, up to the version 9.1, does not carry the data but only the pointer to the heap block where the data is stored.

Any index read consists in at least two disk read as one is for the index block and another one for the data block. Smaller tables does not use indexes because is quicker a sequential scan to get the data in the shared buffer. Same for queries with no where condition as the entire table must be loaded in memory to satisfy the backend's request.

Any update to the tuple is, because the MVCC, a new insert with a new xmin and this does not affect the index if the new tuple resides in same block but, if the new tuple is stored in a new block the index require to be updated and this generate dead tuples in the index block itself.
Index blocks with dead and live tuples prevent vacuum to recycle the block and cause the index bloat, wasting space on disk and affecting the index performance.

The only solution to fix this is doing an index rebuild with reindex, usually not a cheap operation.
Useless indexes can affect the query planner itself as wrong performance assumption is done.
Being a balanced data structure, assuming we're talking about the btree index type, the scan require an entry point determined by the where condition, if this is covered by the index only partially the scan will cost more than expected and the query become slower than expected as the entry point cannot be determined on the root block.

Sometimes slow queries execution plans show one or more index scan.
This is can happen if the index is used with a wrong the entry point in the where condition.
To fix this a new index according the where condition can solve the problem but in that case keep in mind the problem caused by the overhead.
To check the index usage the system table pg_stat_all_indexes show the index name, the schema name and three columns

idx_scan

idx_tup_read

idx_tup_fetch

Respectively, the number of index scan initiated, the number of index block read for the index and the live tuple read with the index.
Zero or lower values in the idx_scan column should alert you about the index usage and let you ask why this index was created.
High difference between idx_tup_read and idx_tup_fetch will show the index is actually used but no data is returned, probably because the where condition is too much stringent.
To solve this problems does require deep knowledge about the application itself and probably a meeting with the developers to find a better solution.

The Ravenous Bugblatter Beast of Traal

To avoid problems in the query planner and before doing any sort of analysis in performance tuning the first question to ask is:are the database statistics up to date?
The database statistics are one of the most important things to keep after. Out of date statistics can result in unpredictable behaviour in the query planner with subsequent performance downgrade.
To explain this I'll show you a little example.
An absolute minimal table where the transactions are stored can have a transaction id, the product code and the transaction timestamp.

CREATE TABLE 
        t_transact
        (
           i_id_trn bigserial,
           v_prod_code character varying (100),
           d_trn_datetime timestamp with time zone DEFAULT current_timestamp,
           CONSTRAINT pk_id_trn PRIMARY KEY (i_id_trn)
        )
;

To speed up the read on the d_trn_datetime field let's create a new index.

CREATE INDEX idx_trn_datetime
        ON t_transact
        USING btree
        (d_trn_datetime )
;

Now let's insert ten records in this table.

INSERT INTO 
    t_transact
    (v_prod_code)

SELECT
     v_prod_code
FROM
     (
         SELECT
             generate_series(1,10),
             'tst_prod' as v_prod_code
     ) sl_prod

;

and check the execution plan used by the following query

SELECT 
        i_id_trn,
        v_prod_code,
        d_trn_datetime 

FROM 
       t_transact
WHERE
       d_trn_datetime='2012-09-02 15:42:20.447888+01'::timestamp with time zone
;

"Bitmap Heap Scan on t_transact  (cost=4.27..9.61 rows=2 width=234) (actual time=0.070..0.134 rows=10 loops=1)"
"  Recheck Cond: (d_trn_datetime = '2012-09-02 15:42:20.447888+01'::timestamp with time zone)"
"  ->  Bitmap Index Scan on idx_trn_datetime  (cost=0.00..4.27 rows=2 width=0) (actual time=0.043..0.043 rows=10 loops=1)"
"        Index Cond: (d_trn_datetime = '2012-09-02 15:42:20.447888+01'::timestamp with time zone)"
"Total runtime: 0.333 ms"

The planner choose a bitmap scan on the index to retrieve the requested rows, assuming the index is cheaper than the table scan.
After running an analyze on the table t_transact this is the new execution plan

"Seq Scan on t_transact  (cost=0.00..1.12 rows=10 width=25) (actual time=0.021..0.082 rows=10 loops=1)"
"  Filter: (d_trn_datetime = '2012-09-02 15:42:20.447888+01'::timestamp with time zone)"
"Total runtime: 0.198 ms"

Obviously the table with 10 rows consist in one single 8k block and the sequential scan is the fastest way to get the data. Without statistics the database did the wrong assumption the index is a better choice resulting in a query 40% slower as two disk access are required.
In the same way, if the table grow big and the database doesn't know the sequential scan will result in slower queries.

DON'T PANIC!

Reviewing the previous sections here some house keeping suggestions

• run vacuum full every 2 billion transactions on any database in the cluster
• check the index usage and do reindex if tables are updated often
• check the statistics are up to date to avoid planner confusion