Re: Pet Peeves?

On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's Slony Replication support!
>

A couple of the things weighing in on my mind right now (I could be
utterly wrong in all of them):

- In place upgrade. Seriously, 3.5TB DBs make me cry...
- Lack of good documentation for the binary copy input format for 'copy
foo from stdin with binary'. Also, I don't *seem* to be able to use the
same file that I copied out for copy in, which was a total surprise.
- The fetch time with lots of byteas is really bad - selecting them out
effectively *forces* scattered IO, even in what would normally be seq
IO. It would be really nice if you did all the grabbing of rows that
was required and then *at the end* fetched the appropriate bytea fields
and re-aggregated them appropriately. This is a *HUGE* performance
killer.
- Bytea copy input format is *exceedingly* large - and the dual parser
thing requiring two backslashes doesn't help!. W T F, I have got to be
missing something.
Consider the case where I want to write an int16_t. What should be
sprintf(s, "\\%o", i);

becomes

sprintf(s, "\\\\%03o\\\\%03o", (i & 0x00FF), (i & 0xFF00));

- Query planning with heavy partitioning takes a huge hit, and this
isn't helped by multiple cores on the same box. It would be very nice
of subpartitions could simply be ignored if their parent partition
wasn't required, but the planner still takes locks on them.
- The ability to add a table to the inheritance structure without
obtaining an acc ex lock would be really nice.
- The ability to rebuild a table or index concurrently would be nice,
especially if it automatically picked up interim changes and applied
them before switching out and dropping the table.
- Slony is really too slow to use for large quantities of data shipping.
IIRC we had to move off of it when the DB was still sub 1 TB.
- Lots of temp table creation/dropping plays havoc with the catalog
tables and eventually requires a full maintenance window to resolve.
- Creating an empty table with foreign keys requires an acc ex lock on
all tables. Blargh.
- It'd be nice if the query planner was more "stable" - sometimes the
queries run fast, and then sometimes they randomly take 2 hours for a
delete that normally runs in a couple of minutes.

There's (alot) more, but I can't recall it all because I'm overall
pretty happy with Postgres.

-Mark