Reputation
18,644
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
1 14 41
Newest
 Announcer
Impact
~421k people reached

1d
comment Why would you need a tablespace?
You can also have indexes for a table on a different disk than the table, which can have huge performance benefits, and lets you use faster but less reliable storage for indexes (since you can re-create them if they are lost).
2d
revised How to view the query of another session in pg_stat_activity without being super user?
added 351 characters in body
2d
comment Why have both a postmaster and postgres executable?
@ConcernedOfTunbridgeWells Makes sense, and I assumed something along those lines. Amusingly, that's exactly what PostgreSQL now does on Windows since it doesn't support copy-on-write co-processes with fork(), so we've come full circle.
2d
comment Why have both a postmaster and postgres executable?
@a_horse_with_no_name Very good point, and no, that's Windows-specific; under Linux pg_ctl will just refuse to start postgres if invoked as root, and you're expected to sudo. Windows has the ability to drop capabilities, which Linux doesn't really, and it also has a truly horrible tool (runas.exe) for invoking as another user where Linux has nice, simple sudo, so it makes sense to do it a different way on Windows.
2d
comment PostgreSQL bytea vs
This might be one of the few sensible uses for array use in the authorative data model, since you save a lot of disk space by avoiding the 24 to 28-byte row overhead. Arrays are also compressed and stored out-of-line if long enough.
2d
answered Why have both a postmaster and postgres executable?
2d
answered How should I index a UUID in Postgres?
May
25
comment Ongoing Administration comparison PostgreSQL vs Firebird
PostgreSQL does require some ongoing administration if you want optimal performance. For most purposes though, all you need is backups (preferably pg_dump and pgbarman for point-in-time recovery) and some basic monitoring of disk space, both of which any RDBMS will need. Either way you'll want to focus heavily on automation. You might want to look at things like WAL-E and check_postgres.
May
22
comment Is there some tricks to better index a URL column in postgres?
I'd write a simplify_url function, then use it in my queries and create an expression index on it.
May
20
answered Use a value for a Postgresql setting in a trigger
May
18
comment What are the compression options for storing large amounts of text in Postgres?
We use quite a low ratio compression algorithm though, because it must have a low CPU cost. So it can still be worth doing another form of compresion and setting EXTERNAL storage on the column.
May
18
comment Writing audit trail funtionality in postgres, can I use pl/V8 instead of pl/pgSQL?
Seems perfectly reasonable, I don't see why it's a concern.
May
17
comment Autovacuum on windows
On Windows, PostgreSQL can't set the process name, so you'll only see postgres.exe.
May
17
answered How to change database encoding in an Amazon RDS Postgresql instance?
May
16
comment Managing View Dependencies
Yep, or just keep your view definitions as SQL scripts in git
May
15
revised Unable to grant permission on table
added 63 characters in body
May
15
answered Unable to grant permission on table
May
13
comment Does installing some PostgreSQL options create some related tables?
In old, pre-EXTENSION databases it was typical for contrib modules to create tables in public. Now you just CREATE EXTENSION. If you're updating from an old version DB, look into CREATE EXTENSION ... FROM UNPACKAGED
May
6
comment Postgresql unique history
This is all managed by the readline library, which has its own separate configuration. Take a look at readline configuration options.
May
5
comment can't connect to remote postgresql database
I'd say the DB didn't restart correctly when you restarted it. Check the PostgreSQL log files for the reason why - probably a typo in pg_hba.conf.