118 reputation
4
bio website aryehleib.com
location
age
visits member for 2 years, 4 months
seen Sep 17 at 14:02

Jul
14
comment Optimizing bulk update performance in PostgreSQL
I wrote a simple helper class in python to automate the process.
Jul
10
comment Optimizing bulk update performance in PostgreSQL
@ErwinBrandstetter Interesting. It seems to depend on the server version. I have reproduced the error on 8.4 and 9.1 using psycopg2 adaptor and using the psql client‌​. On 9.3 there is no error. See my comments in the first script. I'm not sure if there's a question to post here, but it may be worth soliciting some information on one of the postgresql lists.
Jul
9
comment Optimizing bulk update performance in PostgreSQL
I up-voted before testing, but it seems there is a race condition. After DROP TABLE tbl is executed but before COMMIT, a query from another session on tbl can already have been 'translated' to its oid and would then fail when the lock is released because it 'could not open relation with OID nnn.' Locking pg_class only seems to help if the other session hasn't queried tbl once already. Is there a workaround? If not, it should be emphasized that this approach may not be a viable option for a live production db.
May
29
comment What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables
@dbenhur The link seems to be dead now.
May
28
awarded  Supporter
Sep
29
awarded  Excavator
Sep
29
awarded  Editor
Sep
29
revised How to insert (file) data into a PostgreSQL bytea column
Updated psycopg doc link
Sep
29
suggested suggested edit on How to insert (file) data into a PostgreSQL bytea column
Sep
27
comment How to insert (file) data into a PostgreSQL bytea column
The link to the psycopg docs is broken and my edit seems to have been rejected(!?). Here is the current location.
Sep
26
suggested suggested edit on How to insert (file) data into a PostgreSQL bytea column
Aug
7
comment PostgreSQL COPY: is it always faster to delete and recreate indexes?
I went ahead and loaded all the data in a single transaction with no index, first doing TRUNCATE. Loading the data was much faster than the network transfer. I'll reproduce some meaningful times when I can. Rebuilding the index afterwards took about 30 minutes, with 170m records. I set maintenance_work_mem up to 9G but it only used a little over 3G. VACUUM ANALYZE on the table took about 15 minutes (!) after that. Next time I import records I should be able to provide times for COPY operations with the index.
Aug
1
comment PostgreSQL COPY: is it always faster to delete and recreate indexes?
I was afraid that would be the answer. I was looking for a quick estimation so I can just get the import done reasonably quickly. I expect that in the future I will only need to COPY a few hundred thousand records at a time.
Aug
1
awarded  Student
Jul
31
asked PostgreSQL COPY: is it always faster to delete and recreate indexes?
Jul
5
answered PostgreSQL PITR restore