Does varchar(n) use less disk space than varchar() or text?

Some time ago on Slack some person said:

varchar is better (storage efficiency), i recommend using it for less than 2048 chars, for the best : TEXT

There was discussion that followed, the person that claimed this efficiency never backed their claim, saying only that:

VARChar takes much less ‘place' than TEXT … but have to face it to believe it

and when I asked for

Show me db fiddle with queries that show this difference and I'll believe

I got:

HA ha ! i let you dig it  i've some work ….

I reacted (over reacted), and the thread died.

I know (knew?) that varchar/text are the same internally (wrote about it), but perhaps there have been recent change?

Continue reading Does varchar(n) use less disk space than varchar() or text?

I have PostgreSQL, loaded some data, and have app using it. Now what?

I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.

What to use, how, and why? This is what this post is all about.

Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?

Waiting for 9.4 – Tablespace changes

On 18th of January, Stephen Frost committed patch:

Add ALTER TABLESPACE ... MOVE command
 
This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of
objects from one tablespace to another.  This can be extremely handy and avoids
a lot of error-prone scripting.  ALTER TABLESPACE ... MOVE will only move
objects the user owns, will notify the user if no objects were found, and can
be used to move ALL objects or specific types of objects (TABLES, INDEXES, or
MATERIALIZED VIEWS).

He followed it, with another commit:

Add CREATE TABLESPACE ... WITH ... Options
 
Tablespaces have a few options which can be set on them to give PG hints
as to how the tablespace behaves (perhaps it's faster for sequential
scans, or better able to handle random access, etc).  These options were
only available through the ALTER TABLESPACE command.
 
This adds the ability to set these options at CREATE TABLESPACE time,
removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to
get the correct options set on the tablespace.
 
Vik Fearing, reviewed by Michael Paquier.

Continue reading Waiting for 9.4 – Tablespace changes

dell powervault md1000 – storage test

i recently got new toy for tests – brand new dell powervault md1000.

what's this, you ask? basically – a rather nice das (direct attached storage) from dell.

the box i got had 15 sas discs, each disc being 72gb, 15krpm.

since this will be used as database storage, i wanted to make some performance tests.

Continue reading dell powervault md1000 – storage test