Postgresql – Best Approach to storing chat history

The simplest way I can think of which keeps the types is the following: create as many columns in your application_settings table as many different types you have. This means normally a boolean, a numeric, a date and a text field. So your table would look like

CREATE TABLE application_settings (
      id serial PRIMARY KEY
    , setting_name text NOT NULL -- possibly UNIQUE
    , boolean_setting boolean
    , numeric_setting numeric -- or this can be an integer, too
    , date_setting date -- again, this can be timestamp, too
    , text_setting text
);

If you plan to keep only one row of each setting (no historical data, then set the setting_name field to UNIQUE. And if you keep exactly one value in a row (which is what I'd expect), then you can add a check constraint like

, CHECK (
    (boolean_setting IS NOT NULL)::integer + 
    (numeric_setting IS NOT NULL)::integer + 
    (date_setting IS NOT NULL)::integer + 
    (text_setting IS NOT NULL)::integer 
    = 1
  )

Depending on how you pass the values into this table, you may or may not need some sort of validation as well. This may involve checking setting names against the type of values provided and a list or a range of valid inputs.

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.

In your specific case, however, there may be reasons to look specifically at PostgreSQL. The major issue is programmability and advanced indexing but there are other reasons too (basically in InnoDB your table is contained in the pkey index, while in PostgreSQL you can do a physical-order scan of the table (and this has advantages when pulling a lot of rows). Major disadvantages might include not liking the binary type interfaces (and often saving/retrieving the values can use more memory than I would like). As for filesystem I know a lot of people who are very happy with XFS and also with ZFS.

Finally one specific tradeoff here is the Pg 8k data page size. It sounds like your time series would be larger than this, and so would be TOASTed (moved to extended storage). This speeds up many queries, but it also slows down retrieving the specific time series slightly because extra physical files must be scanned.

As for programmability, basically you could write functions that can look inside a time series for specific useful information and then you can index the output of this function. Consequently this can be used to speed up searches because the function doesn't have to be run.