426 reputation
37
bio website
location Netherlands
age 38
visits member for 3 years, 1 month
seen Nov 5 at 10:25

Jul
10
awarded  Yearling
Mar
19
answered PGAdmin3 connection with non standard SSH port and SSH tunnel
May
21
awarded  Constituent
May
21
awarded  Caucus
May
19
answered Moving postgresql data directory
May
18
comment How to make continues cluster?
AFAIK you can't. I guess you want this because of a performance issue. If you describe your tables and the data in them and the kind of queries you are running on it someone might be able to give an alternate solution.
Apr
19
comment Equivalent of MRG_MYISAM in databases other than MySQL?
The only thing in postgresql I can think of that might be used to solve the same problem is FOREIGN DATA WRAPPERS wiki.postgresql.org/wiki/Foreign_data_wrappers. These allow you to access data from another database which doesn't even have to be postgresql. However this feature isn't very mature yet. As this is part of the SQL standard I would expect some other big vendors to also support this.
Apr
1
answered Passing argument in trigger dynamically
Mar
31
comment which postgres do i have installed? what directory needs to be in my path to run initdb?
Error's about the UNIX domain socket are because either postgresql is not running or the version which is running has put it in another location then the psql version. You can either try to find a version on psql that uses the same path or use TCP/IP by specifying -h 127.0.0.1
Mar
31
comment which postgres do i have installed? what directory needs to be in my path to run initdb?
On unix/osx the current directory is often NOT in the path as this is considered a security risk. To run something from the current directory you can explicitly specify the current directory by doing ./initdb (a single dot is the current directory)
Mar
27
answered How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Mar
27
comment Optimize query execution based on two datetime columns
I had a similar question once for postgres and at that time a solution was to use geometric types. The basic principal is to convert the ranges to boxes and then query for overlapping boxes. It is not as efficient as having real range types (postgresql has them since 9.2) but it was at least reasonable fast.
Mar
24
comment Postgresql query speed
Because of the condition "object_mqs"."file_format" = 7 it looks to me like the first three LEFT JOIN's can be changed to normal JOIN's without changing the result. This may help the planner to come up with a faster plan.
Mar
7
comment Tuning Postgres config for new Database Server
What kind of raid controllers are on the new and old machine? And do they have a battery backup unit?
Mar
5
comment What's the best way to backup DB files via network on Linux and Solaris machines?
@Eytan, when syncing large files, rsync splits it in blocks and compares these blocks by using checksums. Only if the checksum doesn't match the block is transferred. BTW this only works if rsync is actually running on both sides of the link either by using an rsync server or using rsync over ssh. Not when the network transfer is hidden by something like an NFS share.
Mar
4
comment Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12
If you build from source it probably is very possible. Just make sure to install postgresql 9.2 packages before building postgis. If there are no 9.2 packages for ubuntu you can build postgresql from source to.
Mar
4
comment What's the best way to backup DB files via network on Linux and Solaris machines?
rsync is very good at keeping a copy in sync as it doesn't transfer data which isn't modified which can save lots of bandwidth. tar + bzip2 is a better option if this is a one time thing. Depending on the link speed you might want to use a low compression setting or use gzip so the CPU can keep up with the network (bzip2 is very cpu intensive).
Feb
27
awarded  Editor
Feb
27
revised is my postgresql-server thread-safe?
added 377 characters in body
Feb
27
comment Single column join vs multicolumn join
Thank you for pointing out that the DB has a hard time making the right estimates. The merge join is indeed requiring a lot of temp disk accesses because of high memory requirements. As an experiment I disabled merge_join which made it decide to use a hash join. Thought performance was much better it is still some what behind the single column join (1.5 times).