PostgreSQL tracking bug

[GoogleCodeExporter]

Filing this issue to track all of the issues related to fuuka on pgsql.

I was pretty much "forced" to move /a/ to Postgres because /a/ was making
MySQL shit itself and make the archive hang for all boards. This was even
after disabling /a/ reports.

Sadly, full text search still suffers from the same problem, it's slow as
fuck. Fulltext search on /a/ killing things was one of the main reasons why
I switched, too. It's currently disabled.

See my saga <a
href="http://archives.postgresql.org/pgsql-performance/2009-07/msg00190.php">her
e</a>.

Good things:
- Gives us flexibility to have fancy indexes. This one, for example:
"CREATE INDEX timestamp_subnum_zero_index_a ON a USING btree ("timestamp")
WHERE subnum <> 0". It makes "View in Ghost mode" a lot better.

- Doesn't lock everyone else out when someone is doing a search that takes
too long

- We can do cool things with triggers and PL/pgSQL functions (does anyone
even use MySQL's stored procedures?)

- Actually has basic stuff MySQL lacks, like EXCEPT. You have to fake it
using JOINs in MySQL.

Bad things:
- Due to the MVCC model, count() is slow as fuck. It's a good thing we
switched to MyISAM right at the beginning, because InnoDB suffers from the
same problem.

This makes reports unusable.

We'll probably need to do the whole reports thing with separate tables and
triggers/stored procedures on insert. Downsides: Waste of space (don't
really give a crap about this one), slower inserts, reports all need to be
rewritten. Upside: instant reports, no holding the database hostage when a
big one runs.

- Full text searching in PostgreSQL is still a bit green. For example,
their to_tsquery() function expects properly formatted stuff with | and &
operators, so to have proper boolean operator support, we'd need to parse
the queries and turn then into something like that. I'm currently using
plainto_tsquery(), which just ANDs all words. There's no "quote matching"
either, but that can easily be implemented with something like this:
select * from a, to_tsquery('I & love & touhou') query where comment_tsv @@
query and comment ilike '%i love touhou%' order by timestamp desc limit 24
offset 0;
It does require more application logic.

And sadly, my "solution" for fixing the fulltext search <a
href="http://archives.postgresql.org/pgsql-performance/2009-07/msg00220.php">her
e</a>
doesn't really work because there's some weird bug in btree_gin, it giving
me bogus results. Look at <a
href="http://pgsql.privatepaste.com/5219TutUMk">this.</a> What the hell.

- There is no equivalent for MySQL's REPLACE. I'm currently using the
following rule:
CREATE RULE replace_a AS ON INSERT TO a WHERE (EXISTS (SELECT 1 FROM a
WHERE ((a.num = new.num) AND (a.subnum = new.subnum)))) DO INSTEAD UPDATE a
SET id = new.id, num = new.num, subnum = new.subnum, parent = new.parent,
"timestamp" = new."timestamp", preview = new.preview, preview_w =
new.preview_w, preview_h = new.preview_h, media = new.media, media_w =
new.media_w, media_h = new.media_h, media_size = new.media_size, media_hash
= new.media_hash, media_filename = new.media_filename, spoiler =
new.spoiler, deleted = new.deleted, capcode = new.capcode, email =
new.email, name = new.name, trip = new.trip, title = new.title, comment =
new.comment, delpass = new.delpass WHERE ((a.num = new.num) AND (a.subnum =
new.subnum));

This is a very ugly thing to do. Ideally, the application should know when
to update and when to insert. Fixing this will fix the image deletions
problem deleting stuff on our end, too.

Original issue reported on code.google.com by eksopl on 21 Jul 2009 at 2:49

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

How the hell do you make links here?

1 - http://archives.postgresql.org/pgsql-performance/2009-07/msg00190.php
2 - http://archives.postgresql.org/pgsql-performance/2009-07/msg00220.php
3 - http://pgsql.privatepaste.com/5219TutUMk

Original comment by eksopl on 21 Jul 2009 at 2:52

[GoogleCodeExporter]

I ended up giving up on PostgreSQL. It'd require rewriting a lot of queries we 
have and come up with a completely different concept for some (eg: reports).

Original comment by eksopl on 9 Oct 2010 at 5:06

• Changed state: WontFix