Scott Marlowe
  • Member for 10 years, 4 months
  • Last seen more than 4 years ago
How can I specify the position for a new column in PostgreSQL?
30 votes

You'll need to recreate the table if you want a certain order. Just do something like: alter table tablename rename to oldtable; create table tablename (column defs go here); insert into tablename (...

View answer
Configuring PostgreSQL for write performance
Accepted answer
25 votes

1 Gigabyte a day is not that high of a write load. Spread out throughout the day, that comes out to about 50kbytes a second. A slow USB thumb drive could handle that. I'm assuming it's more bursty ...

View answer
VACUUM FREEZE vs. VACUUM FULL
Accepted answer
17 votes

Here's a short concise answer. Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now). Vacuum freeze marks a table'...

View answer
Understanding time format of the EXPLAIN command - Postgres
11 votes

The first number is how long it took to return the first row in that step. The second number is how long it took to return the last row.

View answer
PostgreSQL: How to create full copy of database schema in same database?
9 votes

pg_dump -n schema_name > dump.sql vi dump.sql # edit the schema name psql: psql -f dump.sql If you're stuck with php then use either back tics `/usr/bin/pg_dump-n myschema mydb -U username > /...

View answer
Function Performance
9 votes

You can do some very interesting stuff using user defined functions (UDF) in postgresql. For instance, there's dozens of possible languages you can use. The built in pl/sql and pl/pgsql are both ...

View answer
New Job and Company uses PostgreSQL
7 votes

In terms of support, there are some very basic differences you should be prepared for that have nothing to do with the databases themselves, but how they are supported. PostgreSQL support comes from ...

View answer
Load balancer versus connection pooling -- is there a difference?
Accepted answer
7 votes

With PostgreSQL you have two different areas that can do pooling, either at the app layer (i.e. jdbc built in pooling etc) or in an intermediate layer that sits between the app and the db(s), such as ...

View answer
PostgreSQL 8.3 - issues with autovacuum
Accepted answer
7 votes

I run some extremely busy 8.3 db servers. When I first started working on them, they were blowing out their free space map settings and going off the rails on a semi-weekly basis. The solution was ...

View answer
How to perform asynchronous cascade slave replication of PostgreSQL database?
4 votes

It's Postgres or PostgreSQL, not PostGre. The method you want is streaming replication or Point In Time Recovery (PITR) which is supported in 9.0 and above. It's covered in the docs. Read up on it, ...

View answer
How to change the datatype of a column from integer to money?
4 votes

Try ALTER TABLE products ALTER COLUMN price TYPE money USING price::money btw what error did you get on your second try up there?

View answer
How do I detach all other users from a postgres database?
4 votes

The issue here is twofold, first you need to disconnect those users, and second you need to keep them out of your server. Rather than revoking connect perms, I usually use pg_hba.conf to refuse new ...

View answer
postgres \dt functionality change from v9.4 to 9.6?
Accepted answer
3 votes

Check the search_path for each user you're trying to check on. SHOW search_path; If they're different you can set them with SET search_path TO mySchema, public;

View answer
Plpgsql seems to be deleting and inserting instead of updating - Why?
3 votes

PostgreSQL doesn't really do updates, it does the equivalent to delete and insert in terms of how it does them. It leaves the old version of the row in place and creates a new version that all future ...

View answer
tuning postgresql for large amounts of ram
2 votes

If EVERY query is that much slower something is terribly wrong with the server or something. In my experience each db has a few things it's better at than the other, but performance wise pgsql is ...

View answer
Another "log_min_duration_statement is ignored" question
1 votes

Check individual users and / or dbs for settings that override server settings for logging: smarlowe=# alter user smarlowe set log_statement='all'; ALTER ROLE smarlowe=# alter database smarlowe set ...

View answer
pgpool-II configuring multiple master databases
1 votes

OK your first decision is WHERE to split the silos out, in the application layer, or in the db layer. If your application will keep track of the silos then you can run individual pgbouncer or pgpool ...

View answer
Degrading PostgreSQL 8.4 write performance
1 votes

Please clarify this statement: Disabling and tuning autovacuum did not give any results What did you do to tune autovacuum? What evidence do you have that the database isn't getting bloated during ...

View answer
Postgresql Cost-Based Vacuum Delay parameters
1 votes

This really depends on your system, and what kind of load it has to handle. Got 100 SSD drives on a SAN with 8Gigs of battery backed cache? Got a single 5400RPM SATA drive?

View answer