During my time with PostgreSQL I found some things that I love. I found some things that I hate (very little of them). And some things that I would like to get, but, so far, I don't have them.
Of course, PostgreSQL being Open Source, I could add it myself, if only I would know some (real) C and had necessary skills. Which I don't. But anyway, decided to write my personal wishlist for PostgreSQL – maybe someone will say “hey, that would be cool, and I know how to write it" 🙂
Number one is the most controversial, so let's start with it, so I can get over with it to something that is less likely to aggravate people:
I want ability to force my point of view of data to PostgreSQL. I know that we have stats. And stats will get better. But the stats and smartness of planner only go so far. What's more – at any point in time there are LOTS of instances where people use older versions of PostgreSQL – so they can't reap benefits on newest changes to planner/optimizer. Adding hints would make their lives easier. Is it nice? No. It is elegant? No. Is it potential foot-gun? Of course. Do I think that we should have it: YES!
Change settings for other backends
Very often I could use ability to, for example, force logging of currently running query (or all future queries) for given backend. Simple case – backend is misbehaving. I see that it's running a lot of quick queries, but it's also idling a lot. I'd love to be able to:
ALTER BACKEND 1234 SET log_statement = all;
Or perhaps change it's application_name. Or do anything else that I could. Right now I can only cancel query or kill backend.
This is somewhat related to previous point.
Some time ago we had only currently running query. Now we have “last running query" and current state – the distinction becomes obvious when dealing with IDLE in transaction connections.
But I want more.
I want to be able to (dynamically?) change limit of memory per query visible in pg_stat_activity. Or have the “last queries" an array, with given max limit of entries, or max mem usage.
I'm working usually on machines with at least 32-64 GB of RAM, and > 100 GB is not unheard of. And when I do the calculation that “last 10 queries, each, let's assume – 10kB, 200 backends – we'd need whopping 20 megabytes", and I realize that we don't have this – WHY?!
My work is 90% of time DBA, and not DB Programmer. So I want more insight into what's happening. This backend – what it's doing. And what it has been doing lately?
I know there is Cron. I know there is pgAgent. But I'd prefer to have something built-in. With standardized, db-accessible logs, configuration, privileges, and so on.
External solutions will always cause more problems. Starting with authentication. Solution that is built-in would be simpler to maintain, easier to explain and friendlier for users.
Global variables in pl/PgSQL
Something like %_SHARED in pl/Perl. I know I can abuse GUC's. I know I can have temporary (or even normal) tables for variables. But I'd like normal variables that can be stored per session or even absolutely globally. Why? Why not?!
Hashes (dictionaries) in pl/PgSQL
This is related to previous point. And I know that I can use hstore. Or JSON. But it just doesn't look nice. Not to mention that I worry (perhaps I shouldn't) about performance of doing:
hstore_variable := hstore_variable || hstore( 'some_key', 'some_value' )
A zillion times.
This is very selfish. I know we have “DO", but for simple/quick tasks, I much prefer to have my own execute(), like:
create function execute(text) returns void as $$ begin execute $1; end; $$ language plpgsql;
And then I can: select execute(format(……)) from …;
Simple, and to the point. Not to mention that clearer (in my personal opinion) than DO command.
And that's it. I could probably think of some other things (built-in sharding with fault tolerance or differential textual backups for example) but the ones I listed seem to me as more important, or at least – more important for larger number of people (or for me personally).
While re-reading the list I noticed that it's really not a lot of things. And that's OK – I am very happy with PostgreSQL, it's developers, Core Team. I just would love to get a bit more.