PostgreSQL Config Storage – Efficient Methods

database-designdynamic-sqlperformanceplpgsqlpostgresql

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact.
Lets take currency as an example. We store currency as numeric, but when displayed we want it to be of a certain format, say to_char(_numeric_value, 'FM999G999G999G999G990D00').

The obvious solution is to create a table that stores this format and uses is whenever needed. But since this is very commonly called function we don't want it to be slow.
The solution I came up with is to re-compile format function each time config is changed. I use on-update trigger for that and also store function source-code. This way performance should be the same as if the format was hardcoded, but it doesn't seem to perform as expected.

What are down sides of this solution, am I missing something? Maybe there's an even better solution?

sqlfiddle is down for me currently, so I will post all necessary DDL/DML here.

CREATE TABLE test_config(money_format text);
CREATE TABLE test_config_fncs(fnc_name text, fnc_source TEXT);

--This is function for performance comparison.
CREATE OR REPLACE FUNCTION test_fnc_common_format_money_no_precompile(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, money_format)FROM test_config;
END;
$BODY$
    LANGUAGE plpgsql STRICT STABLE;

CREATE OR REPLACE FUNCTION test_tfnc_config_fnc_precompiler()
    RETURNS trigger AS
$BODY$
BEGIN
    IF (NOT (NEW.money_format = OLD.money_format)) THEN
        EXECUTE 
        (
            SELECT 
                    replace(fnc_source, '>money_mask<', NEW.money_format)
                FROM test_config_fncs 
                WHERE fnc_name='test_fnc_common_format_money'
        );
    END IF;
    RETURN NEW;
END;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER test_tg_config_aiu_fnc_precompile
    AFTER UPDATE
    ON test_config
    FOR EACH ROW
    EXECUTE PROCEDURE test_tfnc_config_fnc_precompiler();
-- DDL DONE
-- INITIAL INSERTS:
INSERT INTO test_config(money_format, date_format) values (null);

INSERT INTO test_config_fncs(fnc_name, fnc_source) VALUES ('test_fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION test_fnc_common_format_money(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, '>money_mask<');
END;
$BODY$
    LANGUAGE plpgsql STRICT IMMUTABLE;
$fnc$);

--TEST QUERIES:
UPDATE test_config SET money_format='FM999G999G999G999G990D00';

SELECT * FROM test_fnc_common_format_money(11111.2222); -- runtime 1700ms per 1 million queries
SELECT * FROM test_fnc_common_format_money_no_precompile(11111.2222); -- 13100ms per 1 million (!)

Best Answer

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements.

General advice

For simple functions as displayed:

  1. Use simple SQL function (LANGUAGE sql) which can be inlined in the context of an outer query. SQL is preferable for simple functions that are typically nested.

  2. Remove the STRICT modifier. Contrary to what you might expect, this is probably not going to help with simple SQL functions, because it can prevent said inlining. (STRICT may help with expensive functions, though.)

Multiple users

To accommodate multiple users with different but more or less stable preferences, you could improve your design building on the typical default search_path setting serach_path = "$user",public.
Create a separate version for each user with the same function name in their respective private schemas. Unless schema-qualified, each user will see his own version of the function fnc_common_format_money(). And they can all use the same queries.

Switch schema

Or, if we are dealing with changing requirements for a single user, you could switch the search_path manually (and an arsenal of functions with it):

SET search_path = "$user", verbose_format, public

verbose_format being one name of a schema with specialized functions. Could also come before "$user".
Related:

You may not need a regime for dynamic settings any more. If you still do, extend your table test_config with a column username (or schemaname) to store a separate row for each user (schema) and adapt your code accordingly. Make it a bit simpler and safer while being at it:

Code suggestions

For the "multiple users" scenario.

CREATE OR REPLACE FUNCTION fnc_test_config_precompiler()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE (
      SELECT format(fnc_source, NEW.username, NEW.money_format)
      FROM   test_config_fncs 
      WHERE  fnc_name = 'test_fnc_common_format_money'
      );

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER test_config_precompile
AFTER INSERT OR UPDATE OF username, money_format      -- !!
ON test_config
FOR EACH ROW EXECUTE PROCEDURE fnc_test_config_precompiler();

...

INSERT INTO test_config_fncs(fnc_name, fnc_source)
VALUES ('fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION %$1I.fnc_common_format_money(_value numeric)
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT to_char(_value, %$2L)';
$fnc$);

Major points

  • Build one function per user setting. (Optional, but the rest of the advice applies in any case.)

  • Build conditions into the trigger directly and call trigger function only after relevant columns have been changed or on INSERT (this way you do not have a reference to OLD in the trigger function, which would not work with the additional call ON INSERT. The simple trigger only creates required functions and does not take care of orphaned versions.

  • Parameter name inside the body of an SQL function requires pg 9.2+.

  • Use format() to inject (sanitized!) parameters into the CREATE FUNCTION statement. Closing a possible security issue with SQL injection.

Related Question