Hot answers tagged postgresql
12
You may be able to achieve better performance by searching first in rows with higher frequencies. This can be achieved by 'granulating' the frequencies and then stepping through them procedurally, for example as follows:
--testbed and lexikon dummy data:
begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set ...
12
Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:
CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
...
12
It certainly is. We discussed that in great detail under this related question.
It also depends on the data types of the indexed columns. An index on, say, two integer columns is exactly as big as an index on one. Space is typically allocated in chunks of 8 bytes. For one integer (4 bytes) that's another 4 bytes of padding.
The keywords here are data ...
11
I found that I had an extremely similar problem, namely that postgres was opening a socket in /var/pgsql_socket_alt where none of my software expects to look, but the solution to my problem was not only a problem with my $PATH.
I had to create the directory /var/pgsql_socket, chown it to myself, and set unix_socket_directory in postgresql.conf (located in ...
11
This is one of those "It depends" questions.
Performance depends on resources, contention, configuration, and the VM engine
Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll ...
11
Production ready?
Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example.
Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using ...
10
In Postgresql these terminal commands list the databases available
el@defiant$ /bin/psql -h localhost --username=pgadmin --list
Or the command stated more simply:
psql -U pgadmin -l
Those commands print this on the terminal:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access ...
10
Setup
I am building on @Jack's setup, firstly because that saves time (kudos to Jack) and secondly to make it easier for people to follow and compare. Tested with PostgreSQL 9.1.4.
CREATE SCHEMA x;
SET search_path = x;
CREATE TABLE lexikon (
id serial
,word text
,frequency int
,lset int
);
INSERT INTO lexikon(word, frequency, ...
10
The real answer will be in the PostgreSQL logs, in /var/lib/pgsql/data/pg_log.
However, before you take any action: It is vital that you take a file system level copy of your database before attempting repair if any of your data is valuable to you. See http://wiki.postgresql.org/wiki/Corruption . You must copy the whole data directory. On Fedora that's ...
10
Crash Safety
PostgreSQL is crash-safe within certain limits. It guarantees to always preserve committed data if the database system crashes or the host it's on reboots/loses power/crashes unexpectedly. That's what the D in ACID means - atomicity, consistency, isolation, durability.
The critical feature in PostgreSQL's crash safety is the write-ahead log ...
10
You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link:
Create the backup:
pg_dump mydb > db.sql
Copy db.sql to the new server (specific command depends on OS)
Go to the new server
createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do)
...
10
You mostly answered the question yourself already. I have a few morsels to add:
In PostgreSQL (and probably other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():
SUM((invoice_amount > 100)::int))
Or use it in a NULLIF() expression and COUNT():
COUNT(NULLIF(invoice_amount > ...
10
In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables.
I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad.
Tune the storage parameters of your tables and indexes
Besides adding several indexes to the ...
10
In other words, you want subset to be unique if type = 'true'.
A partial unique index will do that:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';
This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer:
PostgreSQL multi-column unique constraint and NULL ...
9
The perhaps easiest way is to do a full dump from the old server and pipe the result straight into the new server. Like this:
pg_dump -h old_server_ip -p 5432 -U username dbname | psql -h localhost -p 5432 -U username dbname
Do this as a superuser. By default the postgres user is a superuser, but you might've created others.
UPDATE: In case you move data ...
9
You should be able to use auto-explain. Turn it on and
SET auto_explain.log_min_duration = 0;
and you should get the plans in your log for all statements run in that session.
You might also want to set
SET auto_explain.log_analyze = true;
but you'll essentially run everything double - once for 'real' and once to EXPLAIN ANALYZE on. During a ...
9
PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:
Use views where possible
Where a view is not suitable, use an SQL function
Where an ...
9
Generally, col IS NULL is a possible candidate for a (default) b-tree index search.
I quote the manual here:
Also, an IS NULL or IS NOT NULL condition on an index column can be
used with a B-tree index.
To get proof, disable sequential scans in a test session (only!).
SET enable_seqscan = OFF;
I quote the manual here:
enable_seqscan (boolean)
...
9
No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.
You can't exactly ...
9
This blogpost should answer. Full disclosure: it's my blogpost.
Summary:
If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root – solution is easy.
Edit your pg_hba.conf file, and at the beginning of it put:
local all all trust
or (depending on your ...
9
Index names in PostgreSQL
Index names are unique across a single database schema.
Index names cannot be the same as any other table, view, sequence, user-defined composite type or index in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)
If you do not care about the name of the index, you can ...
8
In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:
SELECT *
FROM information_schema.columns
WHERE table_schema = your_schema
AND table_name = your_table
8
There's no simple answer to your question, but here are a few things to think about.
First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems. There are 32TB databases on PostgreSQL out ...
8
Playing a bit with pg_buffercache, I could get answers to some of your questions.
This is quite obvious, but the results for (5) also show that answer is YES
I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.)
Since the planner is who decides whether to use an index or not, we can say YES, it ...
8
For a single string
You can apply the window function row_number() to remember a distinct order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the ordinal number of the original position in the string.
You could try and simply omit the ORDER BY to get the position "as is":
SELECT *, ...
8
I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead).
I tried to make different results comparable by using more than one ...
8
The collation determines the comparison semantics.
If I try
CREATE TABLE [word](
[id] [int] IDENTITY(0,1) NOT NULL,
[value] [nvarchar](255) COLLATE Latin1_General_100_CI_AS NULL
);
It only returns ἀπὸ.
Changing the suffix to AI for accent insensitive returns ἀπό also.
On my install I have tried every collation and 1526 return 1 ...
8
Short rules of thumb. (Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms. Don't assume you will always work on PostgreSQL.)
Index every primary key.
Index every foreign key.
Index every column used in a JOIN clause.
Index every column used in a WHERE clause.
Study your documentation to learn the ...
8
I am assuming data type text for the relevant columns.
CREATE TABLE prefix (code text, name text, price int);
CREATE TABLE num (number text, time int);
"Simple" Solution
SELECT DISTINCT ON (1)
n.number, p.code
FROM num n
JOIN prefix p ON right(n.number, -1) LIKE (p.code || '%')
ORDER BY n.number, p.code DESC;
Key elements:
DISTINCT ON is a ...
7
I had the same issue installing 9.1.4 on Windows 7. I managed to find a solution online that worked.
The steps I followed are:
Uninstall PostgreSQL
Delete the postgres user if it still exists.
net user postgres /delete
Create the postgres user with a password you can remember
Add the postgres user to the Administrators group
Add the postgres user to ...
Only top voted, non community-wiki answers of a minimum length are eligible
