202 reputation
311
bio website growse.com
location
age
visits member for 3 years, 1 month
seen Sep 17 at 15:54

Aug
14
awarded  Yearling
Aug
13
awarded  Notable Question
Jul
2
awarded  Curious
Jun
25
awarded  Popular Question
Jun
5
awarded  Popular Question
Apr
24
awarded  Notable Question
Apr
1
awarded  Popular Question
Mar
19
awarded  Popular Question
Mar
17
awarded  Popular Question
Jun
27
comment Will a delete then insert within a transaction cascade?
Well, I basically did the bulk insert into a temp table and then merged that with the original table. The bulk insert still takes around 6s, and the merge takes around 15-20s. The biggest issue is that I'm currently blanket updating on match, so I'm at least generating an update statement for each row in the table. This surely can be optimized, but I'd have to compare 90 columns for equality, and it didn't seem to be worth it.
Jun
27
awarded  Commentator
Jun
27
accepted Will a delete then insert within a transaction cascade?
Jun
27
comment Will a delete then insert within a transaction cascade?
Merge wins. Fairly epic merge statement with 90 columns, but it works.
Jun
27
comment Will a delete then insert within a transaction cascade?
This sounds like a sensible idea. I'll do some investigating. As mentioned in my above comment though, performance might be an issue, but I guess I'll just have to test.
Jun
27
comment Will a delete then insert within a transaction cascade?
UPDATE or MERGE would be valid approaches as well, I guess it'd just require more logic from the application doing the data ingest as to whether each row needs to be inserted, deleted or updated. Also, wouldn't MERGE be significantly slower than a bulk insert? I can currently insert 150,000 rows with 90 columns in about 4 seconds, so I don't know how long a merge operation would take.
Jun
26
asked Will a delete then insert within a transaction cascade?
Apr
29
comment Improve sort performance in PostgreSQL?
I did already have an index on comments.article_id, and adding one to articles.type didn't seem to do a lot. I'll try tweaking some of the server settings though, thanks for that.
Apr
29
comment Improve sort performance in PostgreSQL?
I tried adding an index with create index idx_articles_type_datestamp on articles(type ASC, datestamp DESC) but that didn't seem to make much difference. I'll see what I can do with forcing django to do a COUNT OVER, but am I right in thinking that's Pg-specific?
Apr
29
asked Improve sort performance in PostgreSQL?
Feb
29
comment Postgres: trying to fix some unicode encoding issues
Brilliant! This works on the \u0080 characters, I just need to replace the odd characters before that now. psql is printing those on resultsets as It<C3><A2>s, so I'm assuming I'm just looking for unicode chars c3 and a2? Google tells me these characters equate to â, but I'm not sure what escape sequence I need to use to find this - \uc3a2?