3 ms·

Part of the reason why PostgreSQL has so any knobs is that these things are not always thing that cookie cutter approaches work with. Although if you do need t

Part of the reason why PostgreSQL has so any knobs is that these things are not always thing that cookie cutter approaches work with. Although if you do need to worry about these, it probably is your job and you probably are at least going to learn it. I am not convinced about his list of "stupid db tricks you should not do." For example: 1) Sessions in the db are sometimes a good thing and sometimes not. They do have a real performance cost (we have a query in LedgerSMB that for large db's takes 20x longer because of having to do this, but the performance cost is necessary and still worth it, though we will probably offer non-web-based alternatives instead later where this wont be necessary). Of course that query is actually using another table to provide "discretionary locks" of rows to a session.... and those locks MUST persist across transactions because of the fact we are mapping to a series of HTTP requests... 2) I dont know about celery queues, but with listen/notify, you can do some really cool message queuing in PostgreSQL. 3) Usually when my app has to store files attached to data, I usually find that it's simpler to put it in the db than the filesystem. That guarantees that the files are in the backups among other things. Also for larger files, PostgreSQL's lob interface (up to 2gb) provides seek operations and more. performance issues here end up occurring outside PostgreSQL. I am with him on very long-running transactions. COPY is good for some things, but if you are trying to create more portable code you probably want to do something like insert foo (....) values (...), (....), (....).... I would also suggest it is important to know the difference between LIKE '%this%' and full text searching on PostgreSQL. These are not simple drop-in equivalents. However additionally LIKE '%this%' cannot use an index (though like 'this%' can, and you can do full text indexing on Pg).

jeltz 14y ago
Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams. http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137099 http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137...
- einhverfr 14y ago
  But pg_trgm isn't really the same either, is it? I have looked at pg_trgm primarily for handling misspellings and suggested alternatives. Also it wasn't clear to me how "%this%" would be differentiated from "his thin snake."
  - jeltz 14y ago
    Yes, pg_trgm was built for that but someone figured out how LIKE could be hacked to use the trigram indexes (gist_trgm_ops, gin_trgm_ops). So if you have a * _trgm_ops index on the column normal LIKE and ILIKE queries may use that index. I assume your example would be a false index hit which then is necessary to verify against the real value. The same would apply to make sure 'This' is not a hit when doing a case sensitive search for '%this%'. So index-only LIKE scans are not possible with * _trgm_ops indexes. EDIT: I just realized how awesome the extensibility of PostgreSQL is. An extension can make a core operator such as LIKE indexable in an entirely new way without touching the core code.
    - einhverfr 14y ago
      The extensibility is really cool. And yeah, that looks like a very useful thing. Thanks for the pointer.
- jeltz 14y ago
  Found there is also a third party module, made by the same guys who made LIKE able to use pg_trgm indexes, called wildspeed. http://www.sai.msu.su/~megera/wiki/wildspeed http://www.sai.msu.su/~megera/wiki/wildspeed It may be faster than using pg_trgm but I have no idea if it is still maintained.

jeltz 14y ago

Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams. http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137099 http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137...

einhverfr 14y ago
But pg_trgm isn't really the same either, is it? I have looked at pg_trgm primarily for handling misspellings and suggested alternatives. Also it wasn't clear to me how "%this%" would be differentiated from "his thin snake."
- jeltz 14y ago
  Yes, pg_trgm was built for that but someone figured out how LIKE could be hacked to use the trigram indexes (gist_trgm_ops, gin_trgm_ops). So if you have a * _trgm_ops index on the column normal LIKE and ILIKE queries may use that index. I assume your example would be a false index hit which then is necessary to verify against the real value. The same would apply to make sure 'This' is not a hit when doing a case sensitive search for '%this%'. So index-only LIKE scans are not possible with * _trgm_ops indexes. EDIT: I just realized how awesome the extensibility of PostgreSQL is. An extension can make a core operator such as LIKE indexable in an entirely new way without touching the core code.
  - einhverfr 14y ago
    The extensibility is really cool. And yeah, that looks like a very useful thing. Thanks for the pointer.
jeltz 14y ago
Found there is also a third party module, made by the same guys who made LIKE able to use pg_trgm indexes, called wildspeed. http://www.sai.msu.su/~megera/wiki/wildspeed http://www.sai.msu.su/~megera/wiki/wildspeed It may be faster than using pg_trgm but I have no idea if it is still maintained.