30

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes. Extension pguint But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution: In addition to various unsigned integer types,...


24

For only 400 stations, this query will be massively faster: SELECT s.station_id, l.submitted_at, l.level_sensor FROM station s CROSS JOIN LATERAL ( SELECT submitted_at, level_sensor FROM station_logs WHERE station_id = s.station_id ORDER BY submitted_at DESC NULLS LAST LIMIT 1 ) l; dbfiddle here (comparing plans for this query, ...


23

You can list the running backends with SELECT * FROM pg_stat_activity; identify the process trying to rollback something which is working on this table. Find its pid. You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT. After that, you can likely DROP that table. If even ...


22

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side ...


22

The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way: [ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END; Have a look at ...


16

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1). CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL, column_that_we_will_drop TEXT ) ; -- Function that ...


16

BTree My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end up having to read both the index and the parts of the table that the index points too... Not necessarily — Having a btree index that is 'covering' will be the ...


16

Non-SSL connections can be disabled through pg_hba.conf. For instance, it may start like this: # allow local connections through Unix domain sockets local all all peer # allow non-encrypted local TCP connections with passwords host all all 127.0.0.1/32 md5 host all all ::1/128 md5 # reject any other non-encrypted TCP ...


15

When running into an error like this, be sure to run barman switch-xlog --force --archive <server_name> as noted in the barman documentation to verify the WAL archiving process.


15

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can run the following SELECT tab.relname, cls.relname, am.amname FROM pg_index idx JOIN pg_class cls ON cls.oid=idx.indexrelid JOIN pg_class tab ON tab.oid=idx....


15

If you have superuser access, you can use: select * from pg_ls_dir('pg_xlog'); which will return one row for each file in the directory pg_xlog. As the size for a WAL segment is fixed, you can easily calculate the total size by multiplying the number of rows by 16MB: select count(*) * pg_size_bytes(current_setting('wal_segment_size')) as total_size from ...


13

The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. So it's no insurance against deadlocks. Most importantly, add a consistent order to input rows with ORDER BY. To make sure the order is enforced I use a CTE, which materializes the result. (...


12

The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly. Separate columns beat a combined json or jsonb column in every performance aspect and in several other aspects, too: More sophisticated type system, the full range of functionality (...


12

1. You know the column names For Postgres 9.6 or later, use num_nulls() SELECT id, num_nulls(a,b,c,d,e) FROM tbl; Returns your desired result exactly, for any mix of data types. The manual: num_nulls(VARIADIC "any") ... returns the number of null arguments For Postgres 9.5 or older, convert to text[], array_remove(arr, null) and use the ...


11

The following code will be able to use a GIN index on the jsonb column: SELECT COUNT(*) FROM results WHERE subjects @> '[{"subject": "B/MATH", "grade": "A"}]' The difference to your example is that I don't unpack the array, I just query the jsonb column directly. The GIN index can be used for the @> operator on jsonb columns, but it can't be used for ...


11

You can use this query to find out all tables with TOAST tables: SELECT oid::regclass, reltoastrelid::regclass, pg_relation_size(reltoastrelid) AS toast_size FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 ORDER BY 3 DESC; To find out which of the columns in the table consumes most space, you could try a query like SELECT ...


10

We also don't ever update primary keys. In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking: FOR NO KEY UPDATE Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to ...


9

Before I posted this question, I had two tests running but I wasn't sure if they would ever finish. I was hoping to get some insight into how to speed it up if they never finished. Thankfully, one of the tests did finish. I also got good results by trying the suggestions that jjanes posted. Using Postgres 9, I ran two tests: Inserting the 2 billion ...


9

To solve this problem I did the following: "Easy" explanation: For this part I slightly added to the table definition provided by the OP. I'm firmly of the belief that DDL should be used to the maximum extent possible to "guide" the whole database programming process and could be much more powerful - an example of this would be SQL in ...


8

You need to install a specific EXTENSION in your database: CREATE EXTENSION btree_gist ; According to PostgreSQL documentation on btree_gist: btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with ...


8

This may or may not work - I'm basing this off a gut feeling that it's joining your tables before the group and filter. I suggest trying the following: filter and group using a CTE before attempting the join: with __posts as( select user_id, count(1) as num_posts from posts group by ...


8

You can do that in a single query using a writeable CTE: with updated as ( UPDATE bankdetails SET bank_details = 'mysore', "PAN"= 123, bank_acc = 456, "UAN" = 789, tax = 'myy' WHERE bankdetails.user_id = 79 returning * ) INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) ...


8

The database the dump file was taken from has a certain foreign key constraint defined. But your target database does not. Probably because someone went and added it to the dumped database since the last dump. Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them. It ...


8

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple distinguish recursive and non-recursive part of query by present of that table. Meantime for that present nice workaround - you may use nested CTE (WITH statement), and ...


8

Suspected main issues (synopsis) You need to run ANALYZE after a major version upgrade with pg_upgrade. Table statistics are not copied. Possibly tune autovacuum settings, too. A multicolumn index on (which_group_id, submitted_on DESC) should serve this query much better. Query Formatted query without noise and with table aliases for better readability: ...


8

It turns out that the vast majority of my write throughput was coming from the stats collector. Because of the very high number of relations in my database, the stats data is unusually large. I was able to diagnose the problem by temporarily clearing the stats: SELECT pg_stat_reset() Which led to an immediate, dramatic drop in my write throughput. To ...


8

Problem I believe currently SET LOGGED rewrites the table using the WAL (essentially doing the whole operation), and rewrites the indexes. So I found a thread about this on the lists A new relfilenode is filled with the data - the old one, including the init fork, gets removed by the normal mechanics of rewriting rels. There was a long ...


8

Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine). A data change trigger is declared as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive some arguments specified in ...


8

It's not sufficient that 195.249.206.131 resolves to kommune.horsholm.dk that matches the wildcard. After checking that, Postgres will try to resolve kommune.horsholm.dk and find that it doesn't resolve to 195.249.206.131 (as it doesn't resolve at all in this particular case) $ host kommune.horsholm.dk Host kommune.horsholm.dk not found: 3(NXDOMAIN) Doc ...


8

You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See: Get distinct on one column, order by another PostgreSQL DISTINCT ON with different ORDER BY SELECT * FROM ( SELECT DISTINCT ON (col1) col1, col2, col3 FROM test ORDER BY col1, col3 DESC ) sub ORDER BY col3 DESC, col2; Assuming that col2 ...


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