Wednesday, September 17, 2014

RDS for Postgres: List of Supported Extensions

Today I learned that Amazon doesn't keep any list of extensions supported in PostgreSQL. Instead, their documentation tells you to start a psql session and run 'SHOW rds.extensions'. But that creates a chicken-and-egg situation if you have an app that needs extensions, and you're trying to decide whether to migrate.

As of 2021-12-16 (PostgreSQL 12.6)

address_standardizer
address_standardizer_data_us
amcheck
aws_commons
aws_l
ambda
aws_s3
bloom
btree_gin
btree_gist
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hll
hstore
hstore_plperl
intagg
intarray
ip4r
isn
jsonb_plperl
log_fdw
ltree
oracle_fdw
orafce
pageinspect
pgaudit
pgcrypto
pglogical
pgrouting
pgrowlocks
pgstattuple
pgtap
pg_bigm
pg_buffercache
pg_cron
pg_freespacemap
pg_hint_plan
pg_partman
pg_prewarm
pg_proctab
pg_repack
pg_similarity
pg_stat_statements
pg_transport
pg_trgm
pg_visibility
plcoffee
plls
plperl
plpgsql
plprofiler
pltcl
plv8
postgis
postgis_tiger_geocoder
postgis_raster
postgis_topology
postgres_fdw
prefix
rdkit
sslinfo
tablefunc
test_parser
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp


Update 2018-10-18 (I'm pleased to see they added hll)

address_standardizer
address_standardizer_data_us
amcheck
bloom
btree_gin
btree_gist
chkpass
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hll
hstore
hstore_plperl
intagg
intarray
ip4r
isn
log_fdw
ltree
orafce
pageinspect
pg_buffercache
pg_freespacemap
pg_hint_plan
pg_prewarm
pg_repack
pg_similarity
pg_stat_statements
pg_trgm
pg_visibility
pgaudit
pgcrypto
pglogical
pgrouting
pgrowlocks
pgstattuple
plcoffee
plls
plperl
plpgsql
pltcl
plv8
postgis
postgis_tiger_geocoder
postgis_topology
postgres_fdw
prefix
sslinfo
tablefunc
test_parser
tsearch2
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp

As of 2014-09-17 (RDS PostgreSQL 9.3.3).

btree_gin
btree_gist
chkpass
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hstore
intagg
intarray
isn
ltree
pgcrypto
pgrowlocks
pg_trgm
plperl
plpgsql
pltcl
postgis
postgis_tiger_geocoder
postgis_topology
sslinfo
tablefunc
tsearch2
unaccent
uuid-ossp
btree_gin
btree_gist
chkpass
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hstore
intagg
intarray
isn
ltree
pgcrypto
pgrowlocks
pg_trgm
plperl
plpgsql
pltcl
postgis
postgis_tiger_geocoder
postgis_topology
sslinfo
tablefunc
tsearch2
unaccent
uuid-ossp

Wednesday, April 2, 2014

Putting stats_temp_directory on a ramdisk

This hack is an old chestnut among PostgreSQL performance tuners, but it doesn't seem to be widely known elsewhere. That's a shame, because it's pure win, and it's ridiculously easy to set up. You don't even need to restart PostgreSQL.

Here's the situation: PostgreSQL writes certain temporary statistics. These go in the dir given by the stats_temp_directory setting. By default, that's pg_stat_tmp in the data dir. Temp files get written a lot, but there's no need for them to persist.

That makes them perfect candidates for a ramdisk (a.k.a. RAM drive). A ramdisk is a chunk of memory treated as a block device by the OS. Because it's RAM, it's super-fast. As far as the app is concerned, the ramdisk just holds a filesystem that it can read and write like any other. Moreover, PostgreSQL generally only needs a few hundred kilobytes for stats_temp_directory; any modern server can fit that in RAM.

In Linux, you set up a ramdisk like this:

As root:

'mkdir /var/lib/pgsql_stats_tmp' [1]

'chmod 777 /var/lib/pgsql_stats_tmp'

'chmod +t /var/lib/pgsql_stats_tmp'

Add this line to /etc/fstab. That 2G is an upper limit; the system will use only as much as it needs.

tmpfs /var/lib/pgsql_stats_tmp tmpfs size=2G,uid=postgres,gid=postgres 0 0

'mount /var/lib/pgsql_stats_tmp'

Then, as postgres:

Change the stats_temp_directory setting in postgresql.conf:

stats_temp_directory = '/var/lib/pgsql_stats_tmp'

Tell PostgreSQL to re-read its configuration:

'pg_ctl -D YOUR_DATA_DIR reload'

And that's it!

Other operating systems have different ways to set up ramdisks. Perhaps I'll cover them in a later post.

[1] The directory /var/lib/pgsql_stats_tmp is an arbitrary choice, but it works well for Debian's filesystem layout.