PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
11
votes
3answers
2k views
Is the CPU performance relevant for a database server?
This is a purely theoretical question. Let's say I have an application deployed on multiple servers.
A load balancer,
Multiple/scalable applications servers
A (single) database server (for the ...
14
votes
2answers
4k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
13
votes
5answers
3k views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
45
votes
5answers
3k views
Do stored procedures prevent SQL injection?
Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
6
votes
4answers
3k views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name from
spelers s
WHERE s.name LIKE 'B%' or s.name like 'D%'
order by 1
I was wondering ...
7
votes
3answers
457 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
6
votes
3answers
2k views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
3
votes
1answer
472 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 ...
8
votes
3answers
682 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
5
votes
3answers
198 views
Casting issue when calling a function with composite type parameter
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.
what could be the issue?
CREATE OR REPLACE FUNCTION ...
20
votes
1answer
941 views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
7
votes
2answers
546 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
9
votes
1answer
385 views
Why does NOT IN with a set containing NULL always return FALSE/NULL?
I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.
...
6
votes
1answer
361 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
4
votes
1answer
1k views
PostgreSQL failover and replication
I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.
I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
5
votes
2answers
3k views
Force drop db while others may be connected in postgresql
I need to remove a database in postgresql DB. How can I do it even if there are active connections? I need a sort of -force flag, that will drop all connections and then the DB.
How can I implement ...
3
votes
2answers
341 views
PostgreSQL unexplained table bloat
I have a table in a Postgres 8.2.15 database. The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB. A few weeks later ...
2
votes
1answer
243 views
What AWS instance to choose for write-heavy PostgreSQL database server
We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At
the moment we are using a smaller cloud service for ...
2
votes
1answer
802 views
I need to run VACUUM FULL with no available disk space
I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
2
votes
2answers
445 views
postgresql view with max min with id
I have the following table:
CREATE TABLE trans (
id SERIAL PRIMARY KEY,
trans_date date,
trans_time time
);
I want to have the following view
CREATE OR REPLACE VIEW daily_trans ...
9
votes
4answers
14k views
How to insert (file) data into a PostgreSQL bytea column
This question is not about bytea v. oid v. blobs v. large objects, etc.
I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
9
votes
3answers
299 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 ...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
3
votes
1answer
280 views
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
3
votes
2answers
641 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 ...
3
votes
2answers
528 views
Span single transaction over several sequential psql sessions
I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions.
When I connect to ...
1
vote
4answers
159 views
Daily and Differential Backups
Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:
The first time, make a full backup.
The second time, only the changes since the last ...
1
vote
2answers
324 views
Running a CTE query in a loop using PL/pgSQL
I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
1
vote
4answers
950 views
migrating tables from Postgres to SQL Server 2008
(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...)
I have a Postgres 8.4.x database with over 100 tables. ...
0
votes
1answer
54 views
Create log file only for my executed queries
I have been using the following to set up the log file:
log_statement='all',
log_collector='on',
log_destination='on',
log_filename='filename'
After that, for testing I just executed a query ...
0
votes
1answer
291 views
Postgres Incremental Backup (Continuous Archiving WAL)
I need to take incremental backup of Postgres DB.
I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want.
As per Continuous Archiving, I ...
0
votes
1answer
78 views
Converting SQL Server 2005 schema to PostegreSQL 9
I have a fairly simple SQL Server 2005 schema that I exported to a tSQL file.
I am looking for a tool, preferably open source and runs on Win 7 (Ubuntu 2nd choice) that will convert the transact SQL ...
14
votes
1answer
11k views
How do I get the current unix timestamp from PostgreSQL?
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
4
votes
3answers
3k views
Automating failover in PostgreSQL 9.1
How does one setup two identical servers for automatic failover in PostgreSQL 9.1.
OS
Centos 5
PostgreSQL 9.1 compiled from source
The postgres user account exists on both machines and has a ...
15
votes
3answers
6k views
What are the drawbacks with using UUID or GUID as a primary key?
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
7
votes
1answer
2k views
How to migrate large blob table from mysql to postgresql?
I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
6
votes
2answers
601 views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
5
votes
1answer
392 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 ...
5
votes
1answer
1k views
PostgreSQL Failover - What tools should I use?
Here is the scenario:
There are two machines running CentOS 6.2 - machine0 and machine1
Both have PostgreSQL 9.1 installed.
One of them should be active, as a master system and through asynchronous ...
14
votes
5answers
11k views
How do I find PostgreSQL's data directory?
I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
9
votes
5answers
847 views
Efficiently select beginning and end of multiple contiguous ranges in Postgresql query
I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
8
votes
2answers
2k views
Are regular VACUUM ANALYZE stil recommended under 9.1?
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
I have a largish database ...
7
votes
4answers
727 views
Locking issue with concurrent DELETE / INSERT in PostgreSQL
This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:
CREATE TABLE test (id INT PRIMARY KEY);
and a few rows:
INSERT INTO TEST VALUES (1);
INSERT INTO TEST ...
6
votes
1answer
354 views
Equality query on NVARCHAR column yields multiple results in SQL Server 2012
I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard).
I've noticed an interesting phenomenon when querying unicode words. Given the definition:
CREATE ...
6
votes
3answers
316 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?
...
6
votes
2answers
1k views
Streaming replication and failover on PostgreSQL
I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
4
votes
1answer
875 views
PostgreSQL: Unable to run repmgr cloned database
I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.
I am using the instructions on this guide: ...
3
votes
2answers
2k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
1
vote
2answers
632 views
Creating High Availability Cluster with PostgreSQL
I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven't found any good way to do this on PostgreSQL.
Where should I start and what are ...
