Lesser known but powerful features of Postgres:
- Postgres can cache too. Use shared_buffer config parameter in the Postgres config file to know how much memory it will use for caching data. Use pg_buffercache view to see what’s occupying the shared buffer cache of your instance. Use pg_prewarm function to load table data into either the OS cache or the Postgres buffer cache.
- Postgres can search quickly through text. It has a special data type tsvector, and a set of functions, like to_tsvector and to_tsquery to do this.
- Postgres provides a powerful server-side function environment in many programming languages. Pre-process as much data as you can on the Postgres server with server-side functions to cut down on the latency arising from passing too much data back & forth between your application servers and your database.
- Postgres has some powerful extensions. PostGIS is a specialized extension that can be used for geospatial data manipulation &running location queries in SQL. hstore extension allows storing and searching simple key-value pairs.
Full post here, 9 mins read