6 ms·

See also: postgres with SKIP LOCKED. Great talk on it from Citus Con 2022: Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 -- https://www.youtube

See also: postgres with SKIP LOCKED. Great talk on it from Citus Con 2022: Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 -- https://www.youtube.com/watch?v=WIRy1Ws47ic&list=PLlrxD0HtieHjSzUZYCMvqffEU5jykfPTd&index=20 https://www.youtube.com/watch?v=WIRy1Ws47ic&list=PLlrxD0Htie...) Citus is a horizontal scale out extension for postgres -- imagine the power of those two things together! JK you don't have to imagine: https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-postgresql/ https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partm...

andrewstuart 3y ago
I wrote an implementation in Python. https://github.com/starqueue/starqueue https://github.com/starqueue/starqueue The code is in there for Postgres, MS SQL and MySQL (which all support SKIP LOCKED) though at some point I abandoned all but Postgres. If I was to write another message queue then I wouldn’t use a database, I’d use the file system based around Linux file moves, which are atomic. What I really want is a message queue that is fast and requires zero config, file based message queues are both…. better than a database.
- gchq-7703 3y ago
  Interesting. What makes you want to switch to the file system? I wrote one for a project[0] a while back (for MongoDB) and it didn't seem like the database introduced too much complexity. I didn't write the implementation from scratch, but the couple hundred lines of code were easy to reason about. [0] https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue
  - deleted 3y ago
    [deleted]
  - andrewstuart 3y ago
    The filesystem means zero configuration. I found almost all message queues to be horribly complex to configure, debug and run. Even database queues require a lot of config, relative to using the file system. I did actually write a file system based message queue in Rust and it instantly maxed out the disk at about 30,000 messages a second. It did about 7 million messages a second when run as a purely RAM message queue but that didn’t use file system at all. It depends what you’re doing of course… running a bank on a file system queue wouldn’t make sense. A fast message queue should be a tiny executable that you run and you’re in business in seconds, no faffing around with even a minute of config. I just hate configuration in general.
    - Izkata 3y ago
      > I did actually write a file system based message queue in Rust and it instantly maxed out the disk at about 30,000 messages a second. It did about 7 million messages a second when run as a purely RAM message queue but that didn’t use file system at all. Did you try an in-memory filesystem through tmpfs?
    - orangepurple 3y ago
      > running a bank on a file system queue wouldn’t make sense Bank transfers are done by a text file sent over FTP (file transfer protocol; circa 1971) https://orientalbank.com/assets/Pdfs/ach/ACH_ORIGINATION_AGREEMENT_JULY_2018.pdf https://orientalbank.com/assets/Pdfs/ach/ACH_ORIGINATION_AGR...
      - lmz 3y ago
        Some of them are more advanced and use SFTP (circa 1997).
    - sitkack 3y ago
      Database config should be two connection strings, 1 for the admin user that creates the tables and anther for the queue user. Everything else should be stored in the database itself. Each queue should be in its own set of tables. Large blobs may or may not be referenced to an external file. Shouldn't a message send be worst case a CAS. It really seems like all the work around garbage collection would have some use for in-memory high speed queues. Are you familiar with the LMAX Disruptor? Is is a Java based cross thread messaging library used for day trading applications. https://lmax-exchange.github.io/disruptor/ https://lmax-exchange.github.io/disruptor/
- moron4hire 3y ago
  I really feel like file systems aren't used for enough things. File systems come with so much useful metadata. I've experimented with using the file system for storing configuration data where each config value is a single file. Nested structures just use directories. The name of the file is the field name. The file extension is a hint about the data type contained within (.txt is obvious, but I also liked .bool). Parsing data is trivial. I don't need any special viewing or editing tools, just a file manager and text editor. You can see when a specific config value was changed by checking the file update time. You don't have to load the whole configuration just to access one field. And you could conceivably TAR up the whole thing if you wanted to transmit it somewhere. I use it to configure little sub-projects in my personal website. I really like it, but I shudder to think of the complaining I'd hear from other developers if I ever used it in a work project, just because it's not whatever they've ever seen before and would require a moment of two of thinking on their behalf to get over ingrained habits.
  - djbusby 3y ago
    I've done and do this too. Its worked awesome for 20+ years.
    - moron4hire 3y ago
      Oh man, symlinks to share common configuration.
  - bboygravity 3y ago
    Sounds very much like how .ini files work and/or how regedit works on windows? :p Not a bad idea at all, but perhaps re-inventing a very old wheel?
    - moron4hire 3y ago
      It's not at all like INI files. INI files have field names and section delimiters in them. An INI files might be: [UI] DarkMode=1 Theme=solarized [Options] Indent=4 InventMode=tabs Whereas this file system config would have individual files per value. <user-home>/.config/<app-name>/UI/DarkMode.bool --> 1 It's a single byte file. You read the entire file contents and if it's not zero, it's true. The existence of the file tells you whether or not to use a default value. An INI file would have to be fully parsed before we know whether it contains a value for that config value. <user-home>/.config/<app-name>/UI/Theme.txt --> solarized You read the entire file contents, trim leading and trailing whitespace and toLower for good measure if you want, then validate against your list of installed themes. Done. No goofy JSON or YAML parser in site. And what reinvention is there? If you're just using a system that already exists, you're not reinventing anything.
- don-code 3y ago
  I actually did this once (over SMB on Windows, though) - and unintentionally crippled our corporate SAN with all of its polling and locking activity. I had a cluster of 20 workers which would poll every five seconds for messages, and I believe we had an EMC VNX storage appliance. I never did figure out why that was enough to bring the whole thing to its knees, but IT was very quick to track the problem back to me.
xvinci 3y ago
Since you seem to be from citusdata: I used cstore_fdw 2 - 3 years back and at least when paired with TPC-H it was horrendously broken for both small (10 gig) and large (100 gig) datasets. It has been integrated into some other product by the time being, I hope you managed to improve it.
- hardwaresofton 3y ago
  I'm definitely not from Citus data -- just a pg zealot fighting the culture war. If you want to reach people who can actually help, you probably want to check this link: https://github.com/citusdata/cstore_fdw/issues https://github.com/citusdata/cstore_fdw/issues

andrewstuart 3y ago

I wrote an implementation in Python. https://github.com/starqueue/starqueue https://github.com/starqueue/starqueue The code is in there for Postgres, MS SQL and MySQL (which all support SKIP LOCKED) though at some point I abandoned all but Postgres. If I was to write another message queue then I wouldn’t use a database, I’d use the file system based around Linux file moves, which are atomic. What I really want is a message queue that is fast and requires zero config, file based message queues are both…. better than a database.

gchq-7703 3y ago
Interesting. What makes you want to switch to the file system? I wrote one for a project[0] a while back (for MongoDB) and it didn't seem like the database introduced too much complexity. I didn't write the implementation from scratch, but the couple hundred lines of code were easy to reason about. [0] https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue
- deleted 3y ago
  [deleted]
- andrewstuart 3y ago
  The filesystem means zero configuration. I found almost all message queues to be horribly complex to configure, debug and run. Even database queues require a lot of config, relative to using the file system. I did actually write a file system based message queue in Rust and it instantly maxed out the disk at about 30,000 messages a second. It did about 7 million messages a second when run as a purely RAM message queue but that didn’t use file system at all. It depends what you’re doing of course… running a bank on a file system queue wouldn’t make sense. A fast message queue should be a tiny executable that you run and you’re in business in seconds, no faffing around with even a minute of config. I just hate configuration in general.
  - Izkata 3y ago
    > I did actually write a file system based message queue in Rust and it instantly maxed out the disk at about 30,000 messages a second. It did about 7 million messages a second when run as a purely RAM message queue but that didn’t use file system at all. Did you try an in-memory filesystem through tmpfs?
  - orangepurple 3y ago
    > running a bank on a file system queue wouldn’t make sense Bank transfers are done by a text file sent over FTP (file transfer protocol; circa 1971) https://orientalbank.com/assets/Pdfs/ach/ACH_ORIGINATION_AGREEMENT_JULY_2018.pdf https://orientalbank.com/assets/Pdfs/ach/ACH_ORIGINATION_AGR...
    - lmz 3y ago
      Some of them are more advanced and use SFTP (circa 1997).
  - sitkack 3y ago
    Database config should be two connection strings, 1 for the admin user that creates the tables and anther for the queue user. Everything else should be stored in the database itself. Each queue should be in its own set of tables. Large blobs may or may not be referenced to an external file. Shouldn't a message send be worst case a CAS. It really seems like all the work around garbage collection would have some use for in-memory high speed queues. Are you familiar with the LMAX Disruptor? Is is a Java based cross thread messaging library used for day trading applications. https://lmax-exchange.github.io/disruptor/ https://lmax-exchange.github.io/disruptor/
moron4hire 3y ago
I really feel like file systems aren't used for enough things. File systems come with so much useful metadata. I've experimented with using the file system for storing configuration data where each config value is a single file. Nested structures just use directories. The name of the file is the field name. The file extension is a hint about the data type contained within (.txt is obvious, but I also liked .bool). Parsing data is trivial. I don't need any special viewing or editing tools, just a file manager and text editor. You can see when a specific config value was changed by checking the file update time. You don't have to load the whole configuration just to access one field. And you could conceivably TAR up the whole thing if you wanted to transmit it somewhere. I use it to configure little sub-projects in my personal website. I really like it, but I shudder to think of the complaining I'd hear from other developers if I ever used it in a work project, just because it's not whatever they've ever seen before and would require a moment of two of thinking on their behalf to get over ingrained habits.
- djbusby 3y ago
  I've done and do this too. Its worked awesome for 20+ years.
  - moron4hire 3y ago
    Oh man, symlinks to share common configuration.
- bboygravity 3y ago
  Sounds very much like how .ini files work and/or how regedit works on windows? :p Not a bad idea at all, but perhaps re-inventing a very old wheel?
  - moron4hire 3y ago
    It's not at all like INI files. INI files have field names and section delimiters in them. An INI files might be: [UI] DarkMode=1 Theme=solarized [Options] Indent=4 InventMode=tabs Whereas this file system config would have individual files per value. <user-home>/.config/<app-name>/UI/DarkMode.bool --> 1 It's a single byte file. You read the entire file contents and if it's not zero, it's true. The existence of the file tells you whether or not to use a default value. An INI file would have to be fully parsed before we know whether it contains a value for that config value. <user-home>/.config/<app-name>/UI/Theme.txt --> solarized You read the entire file contents, trim leading and trailing whitespace and toLower for good measure if you want, then validate against your list of installed themes. Done. No goofy JSON or YAML parser in site. And what reinvention is there? If you're just using a system that already exists, you're not reinventing anything.
don-code 3y ago
I actually did this once (over SMB on Windows, though) - and unintentionally crippled our corporate SAN with all of its polling and locking activity. I had a cluster of 20 workers which would poll every five seconds for messages, and I believe we had an EMC VNX storage appliance. I never did figure out why that was enough to bring the whole thing to its knees, but IT was very quick to track the problem back to me.

xvinci 3y ago

Since you seem to be from citusdata: I used cstore_fdw 2 - 3 years back and at least when paired with TPC-H it was horrendously broken for both small (10 gig) and large (100 gig) datasets. It has been integrated into some other product by the time being, I hope you managed to improve it.

hardwaresofton 3y ago
I'm definitely not from Citus data -- just a pg zealot fighting the culture war. If you want to reach people who can actually help, you probably want to check this link: https://github.com/citusdata/cstore_fdw/issues https://github.com/citusdata/cstore_fdw/issues