6,999 reputation
31640
bio website
location Berlin, Germany
age 38
visits member for 2 years, 10 months
seen 24 mins ago

45m
comment UPDATE/DELETE performance on partitioned table
@Kuba the how did you get 0 rows once and 13821 in an other run?
2h
comment UPDATE/DELETE performance on partitioned table
You are doing some dirty magic. The EXPLAIN ANALYZE output shows 0 rows affected. Afterwards, you are able to delete a number of rows. Twice. (Note that the plans show that the DELETE via the parent table is even faster.) Could you clarify what you are actually doing?
2h
comment Postgresql - Streaming replication - STONITH
Agreed, one has to try other methods only if they have the time and the ability to deal with possible corruption.
2h
revised Postgresql - Streaming replication - STONITH
added 20 characters in body
13h
awarded  Enlightened
13h
awarded  Nice Answer
1d
comment UPDATE/DELETE performance on partitioned table
Inspect the output of \d events. Do you see a trigger that fires on DELETE?
1d
comment Reassigning surrogate keys?
I am wondering if you use OID in a rather unorthodox sense here. See postgresql.org/docs/9.3/static/… and postgresql.org/docs/9.3/static/sql-createtable.html for details.
1d
comment Email open rate calculations in subqueries with postgres
Try casting at least one of your sum() expressions as numeric. (Compare the results of SELECT 1/5 and SELECT 1::numeric/5!)
1d
awarded  postgresql
Nov
18
comment copy data from public schema to another schema in postgresql
If you do pg_restore -l -f db/my_dump.backup, you'll see that the objects are dumped, meaning that the restore step does not do what you expect from it. If you are doing this regularly, the best way (in my view) is to store the database object definitions under version control, then roll them out on demand to a new schema with a few lines of shell script.
Nov
17
comment How to enable implicit casts in PostgreSQL >=9.2?
@CraigRinger I think this is more or less the full answer one can give here.
Nov
17
comment pgAgent Job's “last result” is continuously “running”
Could you show what the job is supposed to do, together with the table structure?
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
See dba.stackexchange.com/questions/82558/… We actually have some DBs with up to 1200 allowed connections but it is far from being optimal.
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
Now I've checked the server options you linked. As they offer SSDs for every size, it makes life a bit easier as disk reads are much cheaper than with spinning disks. I'd suggest putting as many of you DBs on the cheapest option (where the relative disk size is the biggest) as you can, and run some benchmarks there.
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
One of the biggest concerns I see with solution 1 is the number of connections. If you have 8000 shops and 2-3 users logged in from each, it means something around 20k connections. This is well beyond the capabilities of a simple PostgreSQL installation. Even using some connection pooling, this would be more than tricky. The size of the machine usually depends on the size of the DB. If the whole DB fits into memory, you are in a very comfortable situation. Could you possibly tell how big the average DB is (in disk size)?
Nov
13
comment Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?
You can also keep the common columns in a single table and set up the specific types as tables referencing this central one via foreign keys.
Nov
13
comment Problem using pg_dump in Windows 7
Have you tried doing this in two steps, first writing the pg_dump output into a file and then feeding it to psql? And an actual error message (which may even show the row number it originates from) would be more than handy.
Nov
12
comment How to manipulate join order of PostgreSQL optimizer?
Why would you do that? Without modifying the source code, you cannot achieve this.
Nov
12
comment Trigger or Stored Procedure?
While I agree with you, I have to add a con: with many of these triggers it will be very hard to follow what happens on a simple INSERT. I used to spend much time debugging funny behaviour, partly induced by my denormalization triggers.