Author: depesz

What is the overhead of logging?

update at the end of the post!

There obviously is one – after all, logging information has to be more expensive than not logging it. But how big is it? And more importantly, what is the difference between logging to stderr/file, csvlog and syslog? And what about syslog to remote machine?

Let's see.

Continue reading What is the overhead of logging?

Pick a task to work on

There is new blogpost on this subject!

There are cases where system stores list of things to do, and then there are some worker processes that check the list, pick something to work on, do it, and remove from the list.

Proper solution is to use some kind of queuing system. There is even PgQ which works withing PostgreSQL, but some people are not happy with it, as it requires compilation and installation. So they just use plain selects.

Will that work OK?

Continue reading Pick a task to work on

Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs

On 18th of July, Stephen Frost committed patch:

WITH CHECK OPTION support for auto-updatable VIEWs
 
For simple views which are automatically updatable, this patch allows
the user to specify what level of checking should be done on records
being inserted or updated.  For 'LOCAL CHECK', new tuples are validated
against the conditionals of the view they are being inserted into, while
for 'CASCADED CHECK' the new tuples are validated against the
conditionals for all views involved (from the top down).
 
This option is part of the SQL specification.
 
Dean Rasheed, reviewed by Pavel Stehule

Continue reading Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs

Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

On 29th of July, Greg Stark committed patch:

Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)
 
Author: Andrew Gierth, David Fetter
Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost

Continue reading Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

Waiting for 9.4 – Implement the FILTER clause for aggregate function calls.

On 17th of July, Noah Misch committed patch:

Implement the FILTER clause for aggregate function calls.
 
This is SQL-standard with a few extensions, namely support for
subqueries and outer references in clause expressions.
 
catversion bump due to change in Aggref and WindowFunc.
 
David Fetter, reviewed by Dean Rasheed.

Continue reading Waiting for 9.4 – Implement the FILTER clause for aggregate function calls.

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