Tag Info

Hot answers tagged

5

Q: Does PostgreSQL uses a hash function for checking equality of integer arrays or does it perform a brute-force algorithm comparing one-by-one the elements of the array? Not according to Array Functions and Operators in the doc: Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the ...


4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc. Then work towards meeting those targets. That is the ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


3

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. Create a new tablespace on the other disk. ...


3

Failover - automatic or otherwise - isn't provided directly by PostgreSQL. You'll need external tools like repmgr. The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer. If you do choose to use autofailover, you ...


3

This error message is new in postgres and is indeed related to the replication bug fixed in 9.3.3 As mentioned in the git commit from Mon, 17 Mar 2014: The recently-fixed bug in WAL replay could result in not finding a parent tuple for a heap-only tuple. The existing code would either Assert or generate an invalid index entry, neither of which is ...


3

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet. To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row ...


2

1) as you already have discovered, you can't use b-tree as the index size is bigger than the page size 2) given: As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data. You would have to use GIN. And no, GIN doesn't use hash functions ...


2

RETURN QUERY EXECUTE was introduced with Postgres 8.4. Your version is just too old and unsupported by now. Upgrade to a more recent version. Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front. Returning anonymous records without a column ...


2

This is just guesswork: SELECT t.id, t.position, y.position AS y_position, t.created_at FROM tbl t JOIN tbl y USING (id) WHERE y.created_at = t.created_at - 1; Basically, you seem to be after a self-join. Using table aliases you can join the same table multiple times in one query. You can just subtract an integer from a date - assuming it is a date, ...


2

Start with the manual page on Write Ahead Log wal_writer_delay (integer) Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of ...


2

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database: If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added. An ...


2

You don't need window functions: UPDATE site_stats SET total_users = totalUsers, monthly_users = monthlyUsers, weekly_users = weeklyUsers FROM ( SELECT u.local_site AS site, count (u.*) AS totalUsers, count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, count(CASE WHEN u.last_visit ...


2

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


2

Current (true at least in PostgreSQL 9.4 and older) PostgreSQL releases have single-threaded WAL recovery. This means that replay of the write-ahead log occurs in only one recovery worker, and is thus able to benefit less from I/O concurrency than a normal running master. This can result in WAL replay lagging behind in cases where the replica and master ...


2

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...


1

You've left out so much that your chances of recovering useful data are negligible. pg_clog contains the commit/rollback logs. Without these, the system doesn't know which parts of the database files are valid and which are not. (gross oversimplification, but hey). pg_xlog, the write-ahead logs. Without these, the database can't handle incomplete writes, ...


1

bigserial is just shorthand for creating a sequence: CREATE TABLE tablename ( colname BIGSERIAL ); Equals CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL ); Also, if you these are your primary keys, then they are already indexed, so you don't need to index ...


1

If I guessed the missing pieces correctly, this query using a crosstab() function should do the job (and much faster than the original - except for small cardinalities where not much can be gained to begin with): It requires that you install the additional module tablefunc first (once per database. Detailed instructions in this related answer on SO: ...


1

there is another way to reduce your bloat space when you can remove tuples in heap pages, this method use less adding space to reduce your bloat table. LIKE exp : pg93@db-172-16-3-150-> psql psql (9.3.3) Type "help" for help. digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp); CREATE TABLE digoal=# insert into t_bloat ...


1

If your environment has disk space more than this table's "real data(after reduced)" size. you can use pg_reorg reduce this table's bloat space. or you can use londiste3 increment copy this table, and use less time to exchange. but if your env has no more space than this table, you can't reduce.


1

It's the data at the start of the command for an entire database. According to the manpage: It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). and in SQL Dump: Dumps created by pg_dump are internally consistent, meaning, the dump ...


1

You were close. Your last idea is actually the way to go: log_statement = none log_min_duration_statement = 10000 Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV. This related ...



Only top voted, non community-wiki answers of a minimum length are eligible