postgresql – Page 39 – select * from depesz;

Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

On 16th of July, Kevin Grittner committed patch:

Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
 
This allows reads to continue without any blocking while a REFRESH
runs.  The new data appears atomically as part of transaction
commit.
 
Review questioned the Assert that a matview was not a system
relation.  This will be addressed separately.
 
Reviewed by Hitoshi Harada, Robert Haas, Andres Freund.
Merged after review with security patch f3ab5d4.

Continue reading Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

On 28th of June, Simon Riggs committed patch:

ALTER TABLE ... ALTER CONSTRAINT for FKs
 
Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE
can be altered to DEFERRABLE and back.
 
Review by Abhijit Menon-Sen

Continue reading Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

Bloat removal by tuples moving

Looong time ago, I wrote a piece about removing bloat by moving rows away from the end of table, and vacuuming it.

This is/was very slow, and was optimized (to some extent) by Nathan Thom, but his blogpost vanished. Besides, later on we got great tool: pg_reorg (or, as it's currently named: pg_repack).

But recently I was in position where I couldn't pg_reorg. So I had to look for other way. And I found it 🙂

Continue reading Bloat removal by tuples moving

Explaining the unexplainable – part 5

In previous posts in this series, I talked about how to read EXPLAIN output, and what each line (operation/node) means.

Now, in the final post, I will try to explain how it happens that Pg chooses “Operation X" over “Operation Y".

Continue reading Explaining the unexplainable – part 5

Explaining the unexplainable – part 4

In this, hopefully 2nd to last, post in the series, I will cover the rest of usually happening operations that you can see in your explain outputs.

Continue reading Explaining the unexplainable – part 4

A tale of automating tests of Pg with Bash

Word of warning: this blogpost is about thing related to Bash (well, maybe other shells too, didn't really test), but since I found it while doing Pg work, and it might bite someone else doing Pg related work, I decided to add it to “postgresql" tag.

So, due to some work I had to do, I needed a quick, repeatable way to setup some Pg instances, replication between them, and some data loader. All very simple, no real problems. At least that's what I thought…

Continue reading A tale of automating tests of Pg with Bash

Explaining the unexplainable – part 3

In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).

Today, we'll move towards more complicated operations.

Continue reading Explaining the unexplainable – part 3

Explaining the unexplainable – part 2

Last time I wrote about what explain output shows. Now I'd like to talk more about various types of “nodes" / operations that you might see in explain plans.

Continue reading Explaining the unexplainable – part 2

Win a book contest – PostgreSQL Backup and Restore How-to

pg backup book cover

Around the time that Xzilla wrote about the book, Packt contacted me and asked for a review.

Since I generally don't really read technical books, I declined the offer, but Sandy from Packt was very persistent, and asked if I could inform about book giveaway contest.

The book is definitely PostgreSQL related, and Xzilla suggested to check it out, so here it goes:

Continue reading Win a book contest – PostgreSQL Backup and Restore How-to

Explaining the unexplainable

One of the first things new DBA hears is “Use the EXPLAIN". And upon first try he/she is greeted with incomprehensible:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
   Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
   Sort Method: quicksort  Memory: 43kB
   ->  Hash Join  (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
         Hash Cond: (p.pronamespace = n.oid)
         ->  Seq Scan on pg_proc p  (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
               Filter: pg_function_is_visible(oid)
         ->  Hash  (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
                     Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))

What does it even mean?

Continue reading Explaining the unexplainable

=$
|

Tag: postgresql