All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

11
votes
1answer
777 views

Why are the plans different if the queries are logically alike?

I wrote two functions to answer Day 3's first homework question from Seven Databases in Seven Weeks. Create a stored procedure where you can input a movie title or actor's name you like, and it ...
11
votes
2answers
3k views

What happens in PostgreSQL checkpoint?

Here's part of my checkpoint log: 2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log ...
11
votes
2answers
8k views

What is the search_path for a given database and user?

I can see the current search_path with: show search_path ; And I can set the search_path for the current session with: set search_path = "$user", public, postgis; As well, I can permanently set ...
11
votes
1answer
15k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
11
votes
1answer
2k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
11
votes
5answers
7k views

Tools to migrate from SQLite to PostgreSQL

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
11
votes
1answer
1k views

BLOBs or references in PostgreSQL

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
11
votes
2answers
486 views

Biological Sequences of UniProt in PostgreSQL

What is the best way to store UniProt biological sequences in PostreSQL? Data Details We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month. The length of ...
11
votes
1answer
4k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
11
votes
1answer
470 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
11
votes
0answers
430 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
10
votes
3answers
26k views

How to drop all connections to a specific database without stopping the server?

I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases. How can I do ...
10
votes
4answers
19k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
10
votes
4answers
27k views

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...
10
votes
3answers
6k views

How should I represent a latitude and longitude in Postgres without using PostGIS?

How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.
10
votes
2answers
9k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
10
votes
3answers
9k views

Why did Postgres UPDATE take 39 hours?

I have a Postgres table with ~2.1 million rows. I ran the below update on it: WITH stops AS ( SELECT id, rank() OVER (ORDER BY offense_timestamp, defendant_dl, ...
10
votes
4answers
2k views

Should I invest the time to change the column type from CHAR(36) to UUID?

I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema. One of the tables has 16M rows (about 3.5M to 4 M records per shard), ...
10
votes
3answers
775 views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
10
votes
1answer
2k views

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, ...
10
votes
2answers
3k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
10
votes
2answers
11k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the ...
10
votes
3answers
8k views

Is there a way to access temporary tables of other sessions in postgres?

I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis ...
10
votes
2answers
11k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
10
votes
2answers
11k views

How do I detach all other users from a postgres database?

I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive ...
10
votes
2answers
6k views

Should I use the PostgreSQL bit string?

I've been learning about the bit string data type lately, and I am quite curious about: At the bottom of this doc page there is the sentence: ... plus 5 or 8 bytes overhead depending on the ...
10
votes
4answers
6k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
10
votes
3answers
2k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
10
votes
3answers
1k views

Where does the magic column “name” come from?

I got this by accident: db=> select name from site; ERROR: column "name" does not exist LINE 1: select name from site; ^ db=> select site.name from site; name --------------...
10
votes
2answers
5k views

PostgreSQL: schema diff/patch tool [closed]

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
10
votes
3answers
6k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in this SO post. What I would like to know is: are there ...
10
votes
3answers
423 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
10
votes
2answers
149 views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
10
votes
1answer
1k views

Why is SELECT * much faster than selecting all columns (in a different column order) by name?

On a table with columns a,b,c,d,e,f,g,h,i,j,k I get: select * from misty order by a limit 25; Time: 302.068 ms And: select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25; Time: 1258.451 ms ...
10
votes
3answers
6k views

VACUUM FREEZE vs. VACUUM FULL

Can someone explain the difference between these types of VACUUM in PostgreSQL? I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am ...
10
votes
1answer
8k views

How to determine the sweet spot between pool size and database connections in PostgreSQL

We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on ...
10
votes
2answers
6k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
10
votes
1answer
8k views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
10
votes
1answer
4k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
10
votes
2answers
19k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
10
votes
1answer
13k views

How to run recurring tasks on a Postgresql database without a cron-like tool?

I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent. ...
10
votes
2answers
4k views

Load balancer versus connection pooling — is there a difference?

I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is ...
10
votes
1answer
2k views

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The ...
10
votes
1answer
19k views

Usage of COPY FROM STDIN in postgres

I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org. I understand the commands, since I previously ...
10
votes
3answers
3k views

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? I'...
10
votes
2answers
2k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
10
votes
1answer
962 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
10
votes
2answers
5k views

Can I have one table be an “alias”, or “symlink”, to another?

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables. After a discussion with a colleague about the potential to ...
10
votes
6answers
12k views

SQL Query Formatter [closed]

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux (...
10
votes
2answers
5k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...