Is there a required format for table name prefix? I know that in MySQL installations it must end in an underscore like prefix_ but I want to know if that is also the required format for Postgres and SQLite.
There is no requirement for the prefix to end in an underscore for any type of database but it can be beneficial for readability.

It is just a string of characters that Drupal prepends to table names in queries so it can contain any characters that are legal to use in table names in your chosen type of database.

Database prefixes are also not necessary at all unless you are using a single database for multiple purposes, for example you have all your drupal tables prefixed with drupal_ and then have other non-drupal tables without a prefix or with a different prefix.

That way you can easily tell which tables are for what.

This is usually only necessary if your hosting provider limits the number of databases you are allowed or sometimes in some more advanced Drupal configurations.

The way Drupal uses it is that if you use one of the db functions like db_select(), db_insert(), db_update(), db_delete() it automatically appends any prefix value you have configured.

When using db_query() it will also do this for any table name wrapped in braces, eg:

db_query("SELECT * FROM {node}");